Jump to content

Database problem


PatrickG

Recommended Posts

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

dbaseproblem.jpg

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.