PatrickG Posted May 2, 2023 Share Posted May 2, 2023 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 Quote Link to comment https://forums.phpfreaks.com/topic/316237-database-problem/ Share on other sites More sharing options...
maxxd Posted May 2, 2023 Share Posted May 2, 2023 What error are you getting? Your flow looks pretty standard and mostly correct, so post some code - please use the <> button to do that. Quote Link to comment https://forums.phpfreaks.com/topic/316237-database-problem/#findComment-1607898 Share on other sites More sharing options...
PatrickG Posted May 2, 2023 Author Share Posted May 2, 2023 The code is quite large so I'll shorten it. Quote Link to comment https://forums.phpfreaks.com/topic/316237-database-problem/#findComment-1607899 Share on other sites More sharing options...
PatrickG Posted May 2, 2023 Author Share Posted May 2, 2023 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. Quote Link to comment https://forums.phpfreaks.com/topic/316237-database-problem/#findComment-1607901 Share on other sites More sharing options...
mac_gyver Posted May 2, 2023 Share Posted May 2, 2023 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. Quote Link to comment https://forums.phpfreaks.com/topic/316237-database-problem/#findComment-1607902 Share on other sites More sharing options...
PatrickG Posted May 2, 2023 Author Share Posted May 2, 2023 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? Quote Link to comment https://forums.phpfreaks.com/topic/316237-database-problem/#findComment-1607903 Share on other sites More sharing options...
mac_gyver Posted May 2, 2023 Share Posted May 2, 2023 https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html Quote Link to comment https://forums.phpfreaks.com/topic/316237-database-problem/#findComment-1607904 Share on other sites More sharing options...
PatrickG Posted May 2, 2023 Author Share Posted May 2, 2023 Thanks. Quote Link to comment https://forums.phpfreaks.com/topic/316237-database-problem/#findComment-1607908 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.