[RESOLVED] UPDATE QUERY (from closed thread reopened)

[dcjones]

Hi all,

I thought I had a result with a previous thread, but I was wrong.

I am trying to UPDATE multiple rows in a table with different data. This is what I am working with:

mysql_select_db($database_flightdata, $flightdata);
$query_flightdata = "SELECT cd.flightid, cd.flightstatus, fd.id,   fd.stat FROM flight_client cd , flightdata fd WHERE cd.flightid =  fd.id ";
$flightdata = mysql_query($query_flightdata, $flightdata) or die(mysql_error());

while ( $row = mysql_fetch_assoc( $flightdata ) ) {
    $stat = empty( $row_flightdata['stat'] )? 'NULL' : $row_flightdata['stat']; // make sure this stat havea value
    $sql = "UPDATE flight_client SET flightstatus=".$row['stat']." WHERE " . $row['id'] . " = " . $row['flightid'] ;
    echo "$sql<br />"; // test the last one and the first one in phpmyadmin...
    // mysql_query($sql) or die(mysql_error());
} 
$totalRows_flightdata = mysql_num_rows($flightdata);

When I run the script the output is:

UPDATE flight_client SET flightstatus=11 WHERE 170 = 170
UPDATE flight_client SET flightstatus=14 WHERE 188 = 188
UPDATE flight_client SET flightstatus=9 WHERE 200 = 200

But the table I am trying to UPDATE is not getting updated.

Can anyone see where I am going wrong.

[djjjozsi]

Use:

    $sql = "UPDATE flight_client SET flightstatus=".$row['stat']." WHERE id = " . $row['flightid'] ;

Your primary key field should inserted in your query and not its value.

( for others... I know this can make in one query )

[dcjones]

Hi djjjozsi and many thnks for your reply.

I have changed the code to yours and the result is still the same. My head is spinning because this should be so easy. Any more ideas, please.

mysql_select_db($database_flightdata, $flightdata);
$query_flightdata = "SELECT cd.flightid, cd.flightstatus, fd.id,   fd.stat FROM flight_client cd , flightdata fd WHERE fd.id = cd.flightid";
$flightdata = mysql_query($query_flightdata, $flightdata) or die(mysql_error());

while ( $row = mysql_fetch_assoc( $flightdata ) ) {
    $stat = empty( $row_flightdata['stat'] )? 'NULL' : $row_flightdata['stat']; // make sure this stat havea value
    $sql = "UPDATE flight_client SET flightstatus=".$row['stat']." WHERE id = " . $row['flightid'] ; 
    echo "$sql<br />"; // test the last one and the first one in phpmyadmin...
    // mysql_query($sql) or die(mysql_error());
}
$totalRows_flightdata = mysql_num_rows($flightdata); 

[djjjozsi]

be careful woth the array names inside your while cycle:
the while has an array:

 $row = mysql_fetch_assoc( $flightdata )

And inside the while cycle you're using $row_flightdata

rename one of the variants.

[dcjones]

Hi djjjozsi, again many thanks for your time.

I am not to sure what you mean, can you expand on that.

mysql_select_db($database_flightdata, $flightdata);
$query_flightdata = "SELECT cd.flightid, cd.flightstatus, fd.id,   fd.stat FROM flight_client cd , flightdata fd WHERE fd.id = cd.flightid";
$flightdata = mysql_query($query_flightdata, $flightdata) or die(mysql_error());


while ( $row = mysql_fetch_assoc( $flightdata ) ) {
    $stat = empty( $row['stat'] )? 'NULL' : $row['stat']; // make sure this stat havea value
    $sql = "UPDATE flight_client SET flightstatus=".$row['stat']." WHERE flightid  = " . $row['id'] ; 
    echo "$sql<br />"; 
	echo "$stat<br />";// test the last one and the first one in phpmyadmin...
    // mysql_query($sql) or die(mysql_error());
}
$totalRows_flightdata = mysql_num_rows($flightdata); 

[djjjozsi]

now i see the changes in your code. Is it working ?

[dcjones]

Hi djjjozsi,

No, its still the same results. When I echo out the result I get:

UPDATE flight_client SET flightstatus=400 WHERE flightid = 170
UPDATE flight_client SET flightstatus=500 WHERE flightid = 171
UPDATE flight_client SET flightstatus=600 WHERE flightid = 172

But when I look at the "flight_client" table the "flightstatus" field has not been updated.

[djjjozsi]

did you try it in phpmyadmin? if not do it.

if its working fine, remove the comment sign ( // ) from the code

    // mysql_query($sql) or die(mysql_error()); 

[dcjones]

Hi djjjozsi,

You are the man, its working.

I cannot thank you enough, may I wish you a merry Christmas and a happy and healthy year.

[djjjozsi]

😃 Thanks. Seems to you.