[RESOLVED] Update multiple rows

[dcjones]

Hi all,

I need do update multiple rows in a table' This is what I am working with:

mysql_select_db($database_flightdata, $flightdata);
$query_flightdata = "SELECT cd.client_1d,   cd.client_mobile,   cd.airline,   cd.flightid, cd.record_status, fd.id,   fd.ArrDep,   fd.airline,   fd.fltno,   fd.`time`,   fd.est,   fd.city1,   fd.city2,   fd.stat,   fd.gate,   fd.term,   fd.type,   fd.reg,   fd.run,   fd.stand,   fd.date,   fd.zone FROM flight_data_client cd , flightdata fd WHERE cd.flightid =  fd.id ";
$flightdata = mysql_query($query_flightdata, $flightdata) or die(mysql_error());
$row_flightdata = mysql_fetch_assoc($flightdata);
$totalRows_flightdata = mysql_num_rows($flightdata);


mysql_query("UPDATE flight_data_client SET record_status=" . $row_flightdata['stat'] . " WHERE " . $row_flightdata['flightid'] = $row_flightdata['id']) 
or die(mysql_error());  

The problem I am having the flight_data_client table is getting updated but all the row have the same value for the "record_status" field.

Can anyone point me in the right direction.

[Weedpacket]

Your "=" sign is not part of the query right now - it needs to be in quotes.

[djjjozsi]

if you expect more then one row from a select, you need to use while cycle to get all the records.

see
mysql_fetch_assoc() in the php.net manual.

Create an $sql string and print before you run on a live database.

[dcjones]

Hi Weedpacket,

Thanks for your eyes, I never spotted that.

I have remove non required fields from the query.

I have made the change and put a loop in the script but it is only updating the first record.

mysql_select_db($database_flightdata, $flightdata);
$query_flightdata = "SELECT cd.flightid, cd.record_status, fd.id,   fd.stat FROM flight_data_client cd , flightdata fd WHERE cd.flightid =  fd.id ";
$flightdata = mysql_query($query_flightdata, $flightdata) or die(mysql_error());
$row_flightdata = mysql_fetch_assoc($flightdata);
$totalRows_flightdata = mysql_num_rows($flightdata);

while ($row = mysql_fetch_assoc($flightdata )) {
mysql_query("UPDATE flight_data_client SET record_status="  . $row_flightdata['stat'] . " WHERE " . $row_flightdata['id'] . " = " . $row_flightdata['flightid'] ."") 
or die(mysql_error());  

} 

Can you see where this is going wrng.

Thank for your input "djjjozsi".

[djjjozsi]

Remove the first:

$row_flightdata = mysql_fetch_assoc($flightdata); 

Comments inside your code.

<?php
//... inside the while make the update

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

//...
?>

[Weedpacket]

Since all the data is already in the database, why not do the update there as well?

Update
	flight_data_client, flightdata
set
	flight_data_client.record_status = flightdata.stat
where
	flightdata.id = flight_data_client.flightid

Though I'm wondering why the duplication of data is necessary in the first place.

[dcjones]

Hi again and thanks for all your help.

If I run the following query I get a result containing three records with all fields populated.

SELECT cd.flightid, cd.record_status, fd.id,   fd.stat FROM flight_data_client cd , flightdata fd WHERE cd.flightid =  fd.id

So I know that there is data.

If I continue to run the script(see below) I get the following:

UPDATE flight_data_client SET record_status=NULL WHERE = 
UPDATE flight_data_client SET record_status=NULL WHERE = 
UPDATE flight_data_client SET record_status=NULL WHERE = 

The script is:

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

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

Can I take from the output that the data is not being passed, or is there something wrong with the code.

Many thanks again for any help you can give me.

[dcjones]

Hi again,

I am getting closer to a result. I now have the following:

mysql_select_db($database_flightdata, $flightdata);
$query_flightdata = "SELECT cd.flightid, cd.record_status, fd.id,   fd.stat FROM flight_data_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 ) ) {

$flightstatus = $row['stat'];
$clientflightid = $row['id'];

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

The out to the screen is:

UPDATE flight_data_client SET record_status = 5 WHERE flightid =2
UPDATE flight_data_client SET record_status = 6 WHERE flightid =10
UPDATE flight_data_client SET record_status = 3 WHERE flightid =26

But, the UPDATE is not happening.

Any ideads as to why.

Again, many thanks for your help.

[dcjones]

Hi again,

I have resolved the problem.

I completely changed my approach.

mysql_select_db($database_flightdata, $flightdata);
$query_flightdata = "SELECT cd.flightid, cd.record_status, fd.id,   fd.stat FROM flight_data_client cd , flightdata fd WHERE cd.flightid =  fd.id ";
$flightdata = mysql_query($query_flightdata, $flightdata) or die(mysql_error());
$row = mysql_fetch_assoc( $flightdata );
$totalRows_flightdata = mysql_num_rows($flightdata);

$_POST['record_status'] = $row['stat'];
$_POST['flightid'] = $row['id'];


$query = "UPDATE flight_data_client SET "; 
    foreach($_POST as $aKey=>$aValue) 
	{ 
    $query = $query . " `$aKey` = $aValue , ";
	}
$query = rtrim($query, ' , ')." WHERE flightid = '" .$_POST['flightid'] ."'"; 
mysql_query($query) or die("My SQL didn't work in update query." . mysql_error() . $query. ''); 

Many thansk for all your input.

[Weedpacket]

I still don't think you need all that PHP.