$wpdb->replace / Replace or update primary key

Question

I write code to save data in to the database table.

I want Replace a row in a table if it exists or insert a new row in a table if the row did not already exist.

http://codex.wordpress.org/Class_Reference/wpdb

I use $wpdb->replace function and then add $wpdb->insert_id to end of code.

For Update all fields , I using replace 'id' That is primary key.

I add the id field to array

   $wpdb->replace( $wpdb->prefix . 'fafa', 
   array( 
   'id',
   'title' =>  trim($row->item(0)->nodeValue) ,
   'liveprice' =>  trim($row->item(2)->nodeValue)  ,
   'changing' =>   trim($row->item(4)->nodeValue)  ,
   'lowest' =>   trim($row->item(6)->nodeValue)  ,
   'topest' =>   trim($row->item(8)->nodeValue)  ,
   'time' =>   trim($row->item(10)->nodeValue)   ), 
   array( 
   '%d',
   '%s',
   '%s',
   '%s',
   '%s',
   '%s',
   '%s'
) );
 $wpdb->insert_Id;

Answer 1

You Can use This solution..

How to delete all records from or empty a custom database table?

In this solution First delete all records an then insert new records

(your table should be have records before to delete query because If run $delete you give error 'No find row' ) and No need to use replace I us insert

add this following query before your function. (this query delete all records )

$delete = $wpdb->query("TRUNCATE TABLE `wp_table_name`");




$delete = $wpdb->query("TRUNCATE TABLE `wp_table_name`"); /// delete all records

   $wpdb->insert( $wpdb->prefix . 'fafa', 
   array( 
   'title' =>  trim($row->item(0)->nodeValue) ,
   'liveprice' =>  trim($row->item(2)->nodeValue)  ,
   'changing' =>   trim($row->item(4)->nodeValue)  ,
   'lowest' =>   trim($row->item(6)->nodeValue)  ,
   'topest' =>   trim($row->item(8)->nodeValue)  ,
   'time' =>   trim($row->item(10)->nodeValue)   ), 
   array( 
   '%s',
   '%s',
   '%s',
   '%s',
   '%s',
   '%s'
) );

run foreach

Note: If your code is in a loop you should insert this query before loop.

     $delete = $wpdb->query("TRUNCATE TABLE `wp_table_name`");
    foreach (){

// code

    }

Answer 2

replace will only replace a row if one of the column values you supply is a primary key or unique index, and that value is identical to an existing row. If your table has neither a primary key or unique index, it will always only insert new rows. You will have to first query for existing data to decide if something should be deleted and a new row inserted.

See REPLACE in MySQL documentation for more information.

Answer 3

The responses to this question do not give a clear answer and I cannot see how the chosen answer is correct because the question does not say anything about deleting all the rows in the table and there can be no need to TRUNCATE the table in order to replace the values in a row. So here is an answer albeit a bit late:

If you are using the replace() function where there is an id in the replace then it means that the record may exist in the first place otherwise you would always go straight to the insert() function to add a new record. Therefore there must be some field other than the primary key (id) that defines whether the record exists or not. In the above example lets assume that the title is the important field that defines whether a new record is needed or the fields in a record need to be updated.

I always to use the get_var() function with the replace() function as follows:

First check if the record exists (for the important field) so that the id can be passed on to the replace function as follows:

$recID = $wpdb->get_var( "SELECT id FROM ".($wpdb->prefix . 'fafa')." WHERE title LIKE ".$title."'");

Now use the replace function that will create a new record or update the fields for the $recID found:

$wpdb->replace( $wpdb->prefix . 'fafa', 
   array( 
    'id'        => $recID,
    'title'     => trim($row->item(0)->nodeValue) ,
    'liveprice' => trim($row->item(2)->nodeValue) ,
    'changing'  => trim($row->item(4)->nodeValue) ,
    'lowest'    => trim($row->item(6)->nodeValue) ,
    'topest'    => trim($row->item(8)->nodeValue) ,
    'time'      => trim($row->item(10)->nodeValue) ), 
   array(
    '%d', '%s', '%s', '%s', '%s', '%s', '%s' )
);

Finally, after replace, the ID generated from the AUTO_INCREMENT column can be accessed using:

$wpdb->insert_id;

I hope that this helps someone who has a similar question to the original.