PHP MYSQL Query problem

[paul2463]

Hello, I ma having a bit of trouble getting PHP to UPDATE fields in a table here is my code:

all variables such as $tda, $twe, $mon, $fardate have been initialised and the initial query works:
[code]
$query = "SELECT idVeh, insDate, tdaSent, tweSent, monSent FROM Veh WHERE insDate < '$fardate'";
$res = mysql_query($query) or die ('Error in Query: $query. ' . mysql_error());
$count = 0;
$count1 = 0;
if (mysql_num_rows($res))
{
   while($row = mysql_fetch_assoc($res))
   {
      if($row['insDate']==$tda)
      {
         $query2 = "UPDATE Veh SET tdaSent = 'true' WHERE $row.idVeh = idVeh";
         mysql_query($query2);
         count++;
      }
      elseif($row['insDate']==$twe)
      {
         $query3 = "UPDATE Veh SET tdaSent = 'true' WHERE $row.idVeh = idVeh";
         mysql_query($query3);
         count++;
      }
      elseif($row['insDate']==$mon)
      {
         $query4 = "UPDATE Veh SET tdaSent = 'true' WHERE $row.idVeh = idVeh";
         mysql_query($query4);
         count++;
      }
      else
      {
         $count1++;
      }
   }
   echo ("Number of Row with data changed = ");
   echo ($count);
   echo ("Number of Rows ignored = ");
   echo ($count1);
} [/code]

it seems to function and the correct counts are returned for the test data entered but nothing is changed in the table to reflect the UPDATE command anywhere.

where have I gone wrong in the UPDAT queries please?

[AndyB]

I suspect changes like this will fix it. It's not obvious why $query2, $query3, and $query4 are all identical ...

[code]while($row = mysql_fetch_assoc($res))
   {
      $idVeh = $row['idVeh'];
      if($row['insDate']==$tda)
      {
         $query2 = "UPDATE Veh SET tdaSent = 'true' WHERE idVeh = '$idVeh'";
         mysql_query($query2);
         count++;
      }[/code]

[paul2463]

thanks for looking but after I have walked away had a brew and walked back again I have solved it by adding some more STUFF ( what a lovely word, covers amultitude of sins)

for each of the IF statements I did the following

[code]if($row['insDate']==$tda)
      {
         $id = $row['idVeh'];
         $query2 = "UPDATE Veh SET tdaSent = 'true' WHERE idVeh = '$id'";
         mysql_query($query2);
         count++;
      }[/code]

just added the extra variable so not too many in the query and it works ......



thanks for the reply Andy
the queries were all identicle in the post because I just copied and pasted each of the IF statements into here

many thanks for the reply though, looks remarkably like the way I did it too

just took some thinking about thats all ( and a break)

I have also noticed that by trying to set it to TRUE using the word 'true' does not work I have to set it to '1' even though reading it back from the database it works using the words 'true' or 'false'
I realise the true and false are indeed either 1 or 0 but I thought I might be able to set it to a true value using the word 'true'

[AndyB]

Essentially, that's the same solution I posted (except I only determined the $id value once - before the conditionals instead of inside each one). The reason why the queries were failing was the query syntax was wrong. You had the right idea adding useful error messaging to your initial query. If that practice had been followed for the other queries, you would have spotted the problem much more easily.

I'm still not sure why you have three different conditional tests but use exactly the same query in each case.

I hope that was a [b]non-alcoholic brew[/b] that got the programming juices flowing :)

[paul2463]

[!--quoteo(post=387125:date=Jun 23 2006, 06:49 AM:name=AndyB)--][div class=\'quotetop\']QUOTE(AndyB @ Jun 23 2006, 06:49 AM) [snapback]387125[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Essentially, that's the same solution I posted (except I only determined the $id value once - before the conditionals instead of inside each one). The reason why the queries were failing was the query syntax was wrong. You had the right idea adding useful error messaging to your initial query. If that practice had been followed for the other queries, you would have spotted the problem much more easily.

I'm still not sure why you have three different conditional tests but use exactly the same query in each case.

I hope that was a [b]non-alcoholic brew[/b] that got the programming juices flowing :)
[/quote]

thanks again Andy, I have gone with your method and only determined the $id value the one time as well, as I said the queries read the same in here because I wrote the first one and then copied and pasted it for the other two forgetting to change values. It was not copied from my program directly it was hand typed into here as I am at work and we are not allowed to put anything that doesnt belong to the company into the company confusers. but thanks for the help and guidance over this problem.

no alcohol in this building - mores the pity...