Updating mysql database help

PHP
1

Hi guys,

Once again I’m here for your assistance if possible. My problem is this:

I have a script which imports a csv file into my mysql datatbase. There are several csv files which are inserted individually into the 1 product table. I need to update this data daily so I have used ‘REPLACE INTO’ as the query. I could only get this to work by not having an auto-increment id column in the table and used a product id as the primary key.

However, after testing I noticed that the page which displays the products was taking too long so I added the id column with auto increment and put that as a primary key and deleted the primary key on the product id column. This sorted the display issue but when I run my ‘REPLACE INTO’ query it just adds another row instead of replacing the row.

How can I keep my id column intact and still replace into the table my mysql data?

2

“taking too long” has nothing to do with whether or not you have an auto_increment id

i suggest you remove the auto_increment, go back to using the product id as the primary key, then your REPLACE INTO will continue to work properly, and you can address the performance of the query separately

3

Say for example you have 3 columns, id, name and desc.

id is an auto-increment integer and name has to be unique, you can use…


INSERT INTO demo (`name`, `desc`)VALUES
  ('foo', 'foo description'),
  ('bar', 'bar new description'),
  ('foo', 'foo new new description')
ON DUPLICATE KEY UPDATE `desc` = VALUES(`desc`)

Which would actually be faster than REPLACE INTO, AFAIK. Due to the fact that REPLACE INTO has to drop the record then re-insert.

Bear in mind I know nothing of SQL. :stuck_out_tongue:

[ot]
Excellent, Rudy is here! Proper SQL advice/input[/ot]

4

Once more great, quick advise from people who really know how to help the ‘learners’.

Anthony, as you may remember my last topic on here was about splitting csv files and my query is based on this.

My current, working code (which you helped me with) for splitting a csv and inserting into the database is:

<?php
$query = 'REPLACE INTO `table` (`merchant_id`, `merchant_name`, `aw_thumb_url`, `aw_product_id`, `brand_name`, `product_name`, `description`, `category_id`, `category_name`, `aw_deep_link`, `aw_image_url`, `search_price`, `delivery_cost`, `merchant_deep_link`, `ean`, `model_number`, `promotional_text`, `upc`, `specifications`, `mpn`)VALUES';

$line = '';
	#open file
	if(false !== ($handle = fopen('datafeed.csv', 'r'))){
    	#for every line in the csv file
    	while($count % 1000 ===0 && $line !==false){
				$line = fgetcsv($handle, 5000, ',', '"');
        		#append to the base sql string the record in sql format
        						
				$data .= sprintf(
            		"\\r\
(%d, '%s', '%s', '%s', '%s', '%s', '%s', %d, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', %d, '%s', '%s'),",
            	intval($line[0]),
            	mysql_real_escape_string($line[1]),
            	mysql_real_escape_string($line[2]),
            	mysql_real_escape_string($line[3]),
            	mysql_real_escape_string($line[4]),
            	mysql_real_escape_string($line[5]),
            	mysql_real_escape_string($line[6]),
            	intval($line[7]),
            	mysql_real_escape_string($line[8]),
				mysql_real_escape_string($line[9]),
            	mysql_real_escape_string($line[10]),
            	mysql_real_escape_string($line[11]),
            	mysql_real_escape_string($line[12]),
            	mysql_real_escape_string($line[13]),
            	mysql_real_escape_string($line[14]),
            	mysql_real_escape_string($line[15]),
            	mysql_real_escape_string($line[16]),
            	intval($line[17]),
            	mysql_real_escape_string($line[18]),
            	mysql_real_escape_string($line[19])
        		);
			
				$data = rtrim($data, ',');
				mysql_query($query.$data) or die("<b>Data could not be entered</b>.\
<br />Query: " . $query.$data . "<br />\
Error: (" . mysql_errno() . ") " . mysql_error());
				$data ="";
				}
			if ($line == false) {
			echo 'All data entered';
			echo '<p>';
			echo '<a href="http://admin.voucherhound.co.uk/datafeeds.php?done=1&feed='.$feed.'">Return back to Manual Datafeed Insertion</a>';
			}
			
    	}
?>

In what way would I impliment the UPDATE part of the query?

Cheers guys