[SOLVED] mysql query/insert/update sequence rules?

[dsaba]

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

[btherl]

Can you post your script (or a minimized script which demonstrates the problem)?

[btherl]

Also, have you printed out all of your mysql queries to ensure that they are what you expect them to be?

[dsaba]

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() );
}

[dsaba]

$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;
}