0

In JavaScript, I'm using an Ajax call and I'm sending an array of shops.
Except for the last element in the array all the shops already exist in the DB.
The last element is a new shop.
The PHP code should get the array and update all the shops that are already in the DB and insert the new one after the last row of the table.
The PHP code uses $wpdb->insert() to insert the last shop but for some reason it doesn't always insert it after the table's last row.
The insert parameters include values for all the fields except for the index. This field is marked as auto_increment in the DB and indeed the inserted row get the expected index number.
But it's not inserted after the last row as expected.
What can be the cause?

Here's the php code:

function updateshops($shopsArray){
    global $wpdb;

    $sArray = $_POST["shopsArray"];
    $aSize = sizeof($sArray);

    for ($i=0; $i<$aSize; $i++)
    {
        if ($sArray[$i]['shopId']!=-1)  // update existing records
            $wpdb->update( 'wp_shops', array( 'shopName'=>$sArray[$i]['shopName'], 'shopAddress'=>$sArray[$i]['shopAddress'] ), array('shopId'=>$sArray[$i]['shopId']));

        else    // add new records
            $wpdb->insert( 'wp_shops', array( 'userId'=>$sArray[0]['userId'], 'shopName'=>$sArray[$i]['shopName'], 'shopAddress'=>$sArray[$i]['shopAddress'], 'shopLat'=>45, 'shopLng'=>20 ));
    }
}
6
  • Post you table schema i.e. SHOW CREATE TABLE wp_shops. Plus you should be quoting your array keys, they're not constants! Jun 26, 2011 at 11:14
  • @TheDeadMedic, thanks. Added the quotes. Not sure what you mean by the table schema but when I use the query you wrote I get CREATE TABLE 'wp_shops' ( 'shopId' bigi....
    – Ash
    Jun 26, 2011 at 12:33
  • 1
    I just re-read your answer and clocked the auto-increment. In which case, don't worry about the CREATE TABLE.... So what do you mean 'last row'? I think you're just after an ORDER BY shopID ASC clause :) Jun 26, 2011 at 12:50
  • @TheDeadMedic, when I look at the table in the MySQL screen after inserting the row I expect to see it added as the last row. But instead it seems it is being added almost randomly between the rows. Yes, I guess ORDER BY shopID ASC will do the trick but I'd rather have it working the right way in the first place.
    – Ash
    Jun 26, 2011 at 13:40
  • This is definitely down to your schema. As @anu just pointed out, you probably don't have a primary key on your table. Jun 26, 2011 at 15:38

1 Answer 1

2

This really has almost nothing to do with WordPress.

You probably haven't created a primary key for your shops table, which should probably be shopid (assuming you're using normal column naming conventions)

3
  • I added a primary key attribute to `shopId' but the results are the same.
    – Ash
    Jun 26, 2011 at 16:00
  • Sounds like you don't have a primary key - results are listed (by default) by the primary key. Add it, or you'll be paying the price of slow queries down the line! Jun 26, 2011 at 16:11
  • I did add a primary key but ORDER BY shopID ASC was also needed. Thanks for both helpers.
    – Ash
    Jun 28, 2011 at 7:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.