[Resolved] Insert/Update

[cbrknight]

I am importing a csv file into a database. Finally got that done and as you all know... That is when the boss throws a monkey wrench into the works.

Now I have added a status field. This means that I have to allow the same files to be inserted. The first time the person is inserted to the database they are a trial customer... with a status of 2.....

When they pay they are upgraded to a status of 1. I would like to be able to do this from the same upload... What I have done is used a checkbox.

If checked then the customer is a trial customer.

If not checked the customer is a regular paid customer.

I then put an if statement..

If (trial == trialcust){
$status = 2;
}
else{
$status =1;
}

Then I have all the code to dedupe the database...

when I get to the insert I need to be able to insert new customers and still update existing customers...

My boss is using an older version of mysql so I cant use the

ON DUPLICATE KEY UPDATE function

Any ideas on how to do this on an older version of mysql?

[bastien]

i do this all the time....do a select based on some unique criteria (id if you have supplied on the csv) or names and if the user is present then do something or do nothing. If the customer is not present, add them.

[kpzani]

the previous answer is better but if you like gaffer tape and bluetack then you could do something like

$sql = "INSERT blah blah";
if (! @($sql)) {
// oops must be a duplicate key
$sql = "UPDATE blah blah";
if (! @($sql)) {
// oops real error
}
}

[Roger_Ramjet]

What version does REPLACE begin in? Does not say in the manual . If yours supports it then that's your answer.

Otherwise, the generic answer is an intermediate table. Insert csv into a copy table that is empty.

INNER JOIN for update
LEFT JOIN for delete
INSERT .. SELECT with a left join and is null to add new records

Do I need to be more explicit? I can if you don't follow.

The use of intermediate tables like this is old hat in mainstream dbs but not much used here in web apps. It is often used to speed up processing even when not required because of your type of problem.

[cbrknight]

Roger

I am actually using the original code you sent me before. I have an intermediate table. set up already.. how do I modify it to update a duplicate record and insert a new record?

[Roger_Ramjet]

Oh, I remember now.

So, you,ve loaded the csv, checked the record counts, and are ready to update existing records and add new ones.

The main question is: Do you want to replace ALL the existing records with the new data or not?

Are there any fields in the main table that you need to preserve the data?

[cbrknight]

actually there are a couple of fields that I want to preserve. The only one I really want to update is the status field...

[Roger_Ramjet]

To update existing rows' status only. Add any other fields that you want to update to the list after SET

UPDATE main INNER JOIN work USING (id) 
SET main.status=work.status

Get that working first and we'll wory about the insert afterwards.

[cbrknight]

Ok got the records to update... Now how do I get the records to insert if they are not already in the database?

[Roger_Ramjet]

You run an insert query, selecting the data from work table.

Now, if the id column is set to unique then duplicate records will be rejected. Depending on the version, duplicate keys would cause an error or not.

from the manual

Prior to MySQL 4.0.1, INSERT ... SELECT implicitly operates in IGNORE mode. As of MySQL 4.0.1, specify IGNORE explicitly to ignore records that would cause duplicate-key violations.

To future-proof the code it is best to use the IGNORE keyword even if not required by your version

INSERT IGNORE INTO main
SELECT * FROM work 

If the IGNORE keyword throws a syntax error in your version then take it out.

[Roger_Ramjet]

Finally, to check that all the data has been inserted run this

SELECT * FROM work LEFT JOIN main
WHERE main.id IS NULL

If it return any records then they have NOT been added to the main table.

[cbrknight]

K the insert and update both seem to be working. However I cant get the check to work... here is the code I am using to try to get the files to list if they are not inserted. what am I doing wrong?

// check if any files were not inserted
$sql5 = "SELECT * FROM users_working LEFT JOIN users_copy WHERE users_copy.validation IS NULL";
$r5 = mysql_query($sql5, $importer);
$c5 = mysql_num_rows($r5);
if ($c5>0){
echo($c5 . " Records were not inserted into database.<br /><br />");
while ($row = mysql_fetch_array($r5, MYSQL_BOTH)){
printf($row["fName"] . " " . $row["lName"] . " " . $row["validation"] . "<br />");
}
}

[Roger_Ramjet]

So what's not working? If you get nothing in the results then everything is OK, that's what you want.

[cbrknight]

Sorry... Let me clarify... I am getting this error

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in c:\inetpub\wwwroot\ncfdemo\paids\insert.php on line 92

[Roger_Ramjet]

No join condition, sorry my fault. See if you can figure out what it should be. The info is there in the other queries. You really must start to work some of this out yourself, not just blindly copy everything I post.

[cbrknight]

Hey... I am slow but I get there... I had to do some digging into the mysql manual. Most of what the have listed is for the new version of mysql...

I did "Hopefully" find what I was looking for....

$sql5 = "SELECT * FROM users_working LEFT JOIN users_copy ON users_working.validation=users_copy.validation WHERE users_copy.validation IS NULL";

I am not getting any errors so I am assuming that everything is being imported properly...

On the off chance do you know how to break the import to test if this is actually working? Or should I just take it on faith?