dsaba Posted February 28, 2007 Share Posted February 28, 2007 ok i have done a lot of debugging and isolating to figure out in what situations a certain weird bug in my database happens i'm using mysql 4.1.18 and php version is 4 basically this is whats going on, i am running MANY updates, inserts, queries on my database from one connection in a script that process variables from an html form these values are being inserted into 3 tables in the database: mainfilelist, videos, and userinfo i believe that the sequence of my queries, updates, and inserts is causing the problem, and my hypothesis is that there are certain illegal/legal things you can do with mysql and php concerning the way you sequence queries, updates, and inserts into the SAME table these are the different sequences and there outcomes: sequence 1, success: --------------------------------- query mainfilelist with fetch_row update mainfilelist ---------------------------------- sequence 2, no success(the variables on the update come up NULL): ----------------------------- query mainfilelist with fetch_assoc insert into mainfilelist query mainfilelist with fetch_row update mainfilelist ---------------------------------- sequence 3, no success(the variables on the update come up NULL): -------------------------------- query userinfo with fetch_assoc insert into userinfo query userinfo with fetch_row update userinfo --------------------------------------- sequence 4, success(the last update the variable are properly updated) ------------------------------------------------ query userinfo with fetch_assoc update userinfo query userinfo with fetch_row update userinfo --------------------------------------------------- as you can see a pattern emerges, where success works with query-update and there is NO SUCCESS with query-insert-query-update i also gave information on what type of queries they were in case that matters please help me figure out why on the unsuccesful sequences the last update does not work thank u Quote Link to comment Share on other sites More sharing options...
btherl Posted February 28, 2007 Share Posted February 28, 2007 Can you post your script (or a minimized script which demonstrates the problem)? Quote Link to comment Share on other sites More sharing options...
btherl Posted February 28, 2007 Share Posted February 28, 2007 Also, have you printed out all of your mysql queries to ensure that they are what you expect them to be? Quote Link to comment Share on other sites More sharing options...
dsaba Posted February 28, 2007 Author Share Posted February 28, 2007 the problem is when i do a sequence like this all together, one after another this is when i query-insert-query-update in the userinfo table i'm not going to put the code for the mainfilelist table as the same thing happens the code is just way longer (more values are being inserted) but it has the same query-insert-query-update sequence make note the second query, queries data that the insert just before has just inserted the problem is on query5, when i look in the database with phpmyadmin the 'userlink' is null however when i run the other sequence that queries, updates, queries, and then updates the 'userlink' field in my database is not NULL, it DOES update it below is the code that pulls up a null value for 'userlink' $result3 = mysql_query("SELECT username FROM userinfo WHERE username='$svideosubmitter'"); $row3 = mysql_fetch_assoc($result3); $query4 = "INSERT INTO `userinfo` ( `username`, `userip`, `useremail`, `userwebsite`, `userlanguage`, `userfilecount`, `userlastactive` ) VALUES ( '$svideosubmitter','$svideosubmitterip','$svideosubmitteremail','$svideosubmitterwebsite','$svideosubmitlang','$userfilecountifnew','$svideodatetime' )"; $result6 = mysql_query("SELECT filesubmitter FROM mainfilelist WHERE filesubmitter='$svideosubmitter'"); $userfilecount = mysql_num_rows($result6); $result7 = mysql_query("SELECT userid FROM userinfo WHERE username='$svideosubmitter'"); $row7 = mysql_fetch_row($result7); $fetcheduserid = $row7[0]; $userlink = <<<EOT <a href="/userinfo.php?id=$fetcheduserid" target="_self">$svideosubmitter</a> EOT; $query5 = "UPDATE `userinfo` SET `userlink`='$userlink' WHERE `userid`='$fetcheduserid'"; now below is the sequence that does not make the 'userlink' value NULL $result3 = mysql_query("SELECT username FROM userinfo WHERE username='$svideosubmitter'"); $row3 = mysql_fetch_assoc($result3); $query3 = "UPDATE `userinfo` SET `userip`='$svideosubmitterip', `userlanguage`='$svideosubmitlang', `useremail`='$svideosubmitteremail', `userwebsite`='$svideosubmitterwebsite', `userfilecount`='$userfilecount', `userlastactive`='$svideodatetime' WHERE `username`='$svideosubmitter'"; $result4 = mysql_query("SELECT filesubmitter FROM mainfilelist WHERE filesubmitter='$svideosubmitter'"); $userfilecount = mysql_num_rows($result4); $result5 = mysql_query("SELECT userid FROM userinfo WHERE username='$svideosubmitter'"); $row5 = mysql_fetch_row($result5); $fetcheduserid = $row5[0]; $userlink = <<<EOT <a href="/userinfo.php?id=$fetcheduserid" target="_self">$svideosubmitter</a> EOT; $query5 = "UPDATE `userinfo` SET `userlink`='$userlink' WHERE `userid`='$fetcheduserid'"; it doesn't matter if either sequence is run i check the query with this piece of code and it always comes up success even though we know that one sequence does not $results5 = mysql_query( $query5 ); if( $results5 ) { echo( "query 5 is succesful for UPDATING userlink in 1 tables.<br>"); } else { die( "query 5 failed see mysql error for more info.<br>" . mysql_error() ); } Quote Link to comment Share on other sites More sharing options...
dsaba Posted February 28, 2007 Author Share Posted February 28, 2007 $result3 = mysql_query("SELECT username FROM userinfo WHERE username='$svideosubmitter'"); $row3 = mysql_fetch_assoc($result3); switch ($row3['username']) { case "$svideosubmitter" : $query3 = "UPDATE `userinfo` SET `userip`='$svideosubmitterip', `userlanguage`='$svideosubmitlang', `useremail`='$svideosubmitteremail', `userwebsite`='$svideosubmitterwebsite', `userfilecount`='$userfilecount', `userlastactive`='$svideodatetime' WHERE `username`='$svideosubmitter'"; //make userfilecount variable $result4 = mysql_query("SELECT filesubmitter FROM mainfilelist WHERE filesubmitter='$svideosubmitter'"); $userfilecount = mysql_num_rows($result4); $result5 = mysql_query("SELECT userid FROM userinfo WHERE username='$svideosubmitter'"); $row5 = mysql_fetch_row($result5); $fetcheduserid = $row5[0]; $userlink = <<<EOT <a href="/userinfo.php?id=$fetcheduserid" target="_self">$svideosubmitter</a> EOT; $query5 = "UPDATE `userinfo` SET `userlink`='$userlink' WHERE `userid`='$fetcheduserid'"; echo $query5; break; default : $userfilecountifnew = "1"; $query4 = "INSERT INTO `userinfo` ( `username`, `userip`, `useremail`, `userwebsite`, `userlanguage`, `userfilecount`, `userlastactive` ) VALUES ( '$svideosubmitter','$svideosubmitterip','$svideosubmitteremail','$svideosubmitterwebsite','$svideosubmitlang','$userfilecountifnew','$svideodatetime' )"; $result6 = mysql_query("SELECT filesubmitter FROM mainfilelist WHERE filesubmitter='$svideosubmitter'"); $userfilecount = mysql_num_rows($result6); $result7 = mysql_query("SELECT userid FROM userinfo WHERE username='$svideosubmitter'"); $row7 = mysql_fetch_row($result7); $fetcheduserid = $row7[0]; $userlink = <<<EOT <a href="/userinfo.php?id=$fetcheduserid" target="_self">$svideosubmitter</a> EOT; $query5 = "UPDATE `userinfo` SET `userlink`='$userlink' WHERE `userid`='$fetcheduserid'"; echo $query5; break; } Quote Link to comment 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.