www.webdeveloper.com
Results 1 to 9 of 9

Thread: sortable database PDO

  1. #1
    Join Date
    May 2007
    Posts
    17

    sortable database PDO

    Hi,

    I'm currently updating my website from php4 to 5 and using PDO to access the mySQL database. In the admin section I've been using phpriot's sortable database, but can't figure out the right equivalent syntax for PDO.

    Used to be:
    PHP Code:
    $query sprintf('UPDATE table SET ranking = %d WHERE item_id = %d',
                             
    $ranking,
                             
    $item_id);

            
    mysql_query($query);
            
    $ranking++; 
    The best I've been able to come up with is
    PHP Code:
    $query sprintf('UPDATE table SET ranking = ? WHERE item_id = ?',
                             
    $ranking,
                             
    $item_id);
            
    $stmt $conn->prepare($query);
            
    $stmt->execute(array($_POST['%d'], $_POST['%d']));

            
    $ranking++; 
    To no effect...

    Any help is much appreciated!

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,921
    It would be something like this:
    PHP Code:
    $query 'UPDATE table SET ranking = ? WHERE item_id = ?';
    $stmt $conn->prepare($query);
    $stmt->bindParam(1$rankingPDO::PARAM_INT);
    $stmt->bindParam(2$item_idPDO::PARAM_INT);
    $stmt->execute();
    $ranking++; 
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    May 2007
    Posts
    17
    thanks for the reply nogdog, but still no luck i'm afraid...

    far as i understand it the whole thing is based on a ajax request containing an array with the updated order of item ids in the table separated by ampersands. A function called processItemsOrder($key) is then triggered, where $key is the array.

    The complete function (using regular mysql requests) is as follows:

    PHP Code:
    function processItemsOrder($key)
    {
        if (!isset(
    $_POST[$key]) || !is_array($_POST[$key]))
            return;

            
    // a function which stores the items in an array, has been modified to PDO    
            
    $items getItems();
            
    // no idea what this is, doesn't seem to be doing anything
            
    $queries = array();
        
    $ranking 1;

        foreach (
    $_POST[$key] as $item_id
        {
            if (!
    array_key_exists($item_id$items))
                continue;

            
    $query sprintf('UPDATE table SET ranking = %d WHERE item_id = %d',
                             
    $ranking,
                             
    $item_id);

            
    mysql_query($query);
            
    $ranking++;
        }


  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,921
    Where do you do your PDO instantiation? (Or do you?)
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    May 2007
    Posts
    17
    above the processItemsOrder function first, to display the items on the page, and a second time in a separate file which handles the ajax request. The separate file only calls the processItemsOrder function, but it's separate because its an ajax request.

  6. #6
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,921
    You'll probably have to pass the PDO object into that function as another argument.
    PHP Code:
    <?php
    function processItemsOrder($key$db)
    {
       if (!isset(
    $_POST[$key]) || !is_array($_POST[$key])) return;
       
    // a function which stores the items in an array, has been modified to PDO
       
    $items getItems();
       
    // no idea what this is, doesn't seem to be doing anything
       
    $queries = array();
       
    $ranking 1;
       
    $stmt $db->prepare('Update table SET ranking = ? WHERE item_id = ?');
       foreach(
    $_POST[$key] as $item_id) {
          if (!
    array_key_exists($item_id$items)) continue;
          
    $stmt->bindParam(1$rankingPDO::PARAM_INT);
          
    $stmt->bindParam(2$item_idPDO::PARAM_INT);
          
    $stmt->execute();
          
    $ranking++;
       }
    }

    // calling code:

    // instantiate PDO database object:
    $dsn "mysql:host=localhost;dbname=your_database_name";
    $db = new PDO($dsn'john_doe''clever_password');

    // call the function
    $key whateverYouDoToGetTheKey();
    processItmesOrder($key$db);
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  7. #7
    Join Date
    May 2007
    Posts
    17
    nothing again, no errors either, i had tried the same thing (passing the connection as an argument) at some point, but thanks for your time nogdog!

    Probably give it another try tomorrow, if all else fails guess I'll just use a separate function to connect to the database via old school mysql requests. Not very sexy but at least it works...

  8. #8
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,921
    Do you have all error reporting turned on? E.g.: at the top of the main script:
    PHP Code:
    <?php
    ini_set
    ('display_errors'1); // set to 0 for production version
    error_reporting(E_ALL);
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  9. #9
    Join Date
    May 2007
    Posts
    17
    Notices! Thanks dude, that's certainly going to help, must have been a bit to quick when i set up php.ini locally. To be continued...

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles