Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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 ));
    }
}
share|improve this question
    
Post you table schema i.e. SHOW CREATE TABLE wp_shops. Plus you should be quoting your array keys, they're not constants! –  TheDeadMedic Jun 26 '11 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 '11 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 :) –  TheDeadMedic Jun 26 '11 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 '11 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. –  TheDeadMedic Jun 26 '11 at 15:38

1 Answer 1

up vote 2 down vote accepted

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)

share|improve this answer
    
I added a primary key attribute to `shopId' but the results are the same. –  Ash Jun 26 '11 at 16:00
    
Problem solved, thanks. –  Ash Jun 26 '11 at 16:06
    
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! –  TheDeadMedic Jun 26 '11 at 16:11
    
I did add a primary key but ORDER BY shopID ASC was also needed. Thanks for both helpers. –  Ash Jun 28 '11 at 7:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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