Database problem

[PatrickG]

Hi, I've been coding for quite some time now but I have a problem I can't solve. I hope you can help me. I've added a flowchart so you can see my situation. 

I have a csv file with multiple lines for one person, one person can have multiple functions. The uid is an, really, an unique id and that's what I'm checking when the record is initially inserted in the database. 

I can't get it to work. It's stuck with updating the record.

Perhaps you can help me.

 

Kind regards,

Patrick Goosen

[maxxd]

What error are you getting? Your flow looks pretty standard and mostly correct, so post some code - please use the <> button to do that.

[PatrickG]

The code is quite large so I'll shorten it.

[PatrickG]

for ($a=0; $a < $teller; $a++) {
	$subarray=explode(";", $rows[$a]);

    $function=$subarray[0];
	$lastname=addslashes($subarray[4]);
 	$sms1=$subarray[5];
	
    $uid=uid($lastname, $sms1);
	
    # this is what Im trying.                    
	if ($db->query("SELECT uid FROM data WHERE uid=$uid")) { $db->query("UPDATE data SET function2='$function'"); }
  
	$result = $db->query("INSERT INTO data (`function`,`uid`) values ('$function', '$uid')");
}  

It's something like this. If I'm missing a quote no problem in the real code it's there.

[mac_gyver]

2 minutes ago, PatrickG said:

if ($db->query("SELECT uid FROM data WHERE uid=$uid"))

the boolean value returned by the exaction of a SELECT query only indicates that the query executed without error (a true value) or failed due to a query error (a false value.) it doesn't indicate that the query match any data. a query that doesn't match any data is a successful query, but returns an empty result set.

there's a single query that does what you want. an INSERT ... ON DUPLICATE KEY UPDATE ... query. you would define the uid column as a unique index if it is not already defined that way. there's no need to try to select data in order to decide if you should insert new data or update existing data and in fact, there's a race condition in the existing code where multiple concurrent instances of your script can all find that data doesn't exist and will all attempt to insert the same data, resulting in duplicates or in errors.

also, use a prepared query when supplying external, unknown, dynamic values to a query when it gets executed. there's two reasons for doing this - 1) addslashes() doesn't protect against every possible character that can break the sql query syntax, since it doesn't take into account the characters set of your database tables, which is why php remove its ridiculous magic_quotes a long time ago, and 2) it will provide a performance increase when executing the same query more than once within one instance of your script.

[PatrickG]

This is getting over my head, mysql (and technical English) is not my strongest point. Can you update my code with the on duplicate? Please?

[mac_gyver]

https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

[PatrickG]

Thanks.