sortable database PDO

[mrblobby]

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:

$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

$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!

[NogDog]

It would be something like this:

$query = 'UPDATE table SET ranking = ? WHERE item_id = ?';
$stmt = $conn->prepare($query);
$stmt->bindParam(1, $ranking, PDO::PARAM_INT);
$stmt->bindParam(2, $item_id, PDO::PARAM_INT);
$stmt->execute();
$ranking++; 

[mrblobby]

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:

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++;
    }
} 

[NogDog]

Where do you do your PDO instantiation? (Or do you?)

[mrblobby]

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.

[NogDog]

You'll probably have to pass the PDO object into that function as another argument.

<?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, $ranking, PDO::PARAM_INT);
      $stmt->bindParam(2, $item_id, PDO::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);

[mrblobby]

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...

[NogDog]

Do you have all error reporting turned on? E.g.: at the top of the main script:

<?php
ini_set('display_errors', 1); // set to 0 for production version
error_reporting(E_ALL);

[mrblobby]

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...