attempting to use update in php script

[chewson]

I am attempting to update selected records using '$row[' elements in where clauses.  I recieve message 'Record Updated but when I check data base it hasn't changed.

CODE:

$query = "SELECT cr_num, cr_ci_num, cr_dte, cr_flag, ci_desc, ci_authority, ce_name, ce_email, ce_phone_ext

    FROM c_record, c_info, c_employee

    WHERE cr_num = ce_num

    AND cr_ci_num = ci_num

AND cr_dte > '{$dte60}' and  cr_dte <= '{$dte90}'

AND cr_flag = '00'";

 

$result = mysqli_query($dbc, $query)

    or die('ERROR querying database');

   

    while($row = mysqli_fetch_array($result))

  {

    $query = "UPDATE c_record SET cr_flag = '90'

    WHERE cr_num = '{$row[cr_num]}'

    AND cr_ci_num = '{$row[cr_ci_num]}'"

    or die('ERROR UPDATE');

    echo 'Update succesfull';

        echo "<br />\n"

[trq]

You never execute your UPDATE query.

 

But, why are you selecting the data in the first place?

[chewson]

The response i got was that the query was not executed- Why? I echoed all the selected fields and the data was there . I don't understand why the query was not executed. Help!

[chewson]

The response i got was that the query was not executed- Why? I echoed all the selected fields and the data was there . I don't understand why the query was not executed. Help!

I am selecting records that fall between a 60 and 90 day expiration date.  I report these records, but need to update the associated flag so that the  records will not be accessed again on other searches.

[uday8486]

You are not passing the update query to mysqli_query function so thats why its not updating. see your code.

[Pikachu2000]

In that case, it would be much easier and less prone to error if you store the primary key IDs of the SELECTed records in an array, then use the values in the resulting array in the WHERE clause of the UPDATE query. Something like this:

 

<?php
$query = "SELECT pk_id, field1, field2, field3 FROM table WHERE some_field = 'some_value'";
if( $result = mysqli_query($dbc, $query) ) {
while( $array = mysqli_fetch_assoc($result) ) {
	echo "report"; // all of your report-related stuff, could store in an array to display later as well.
	$ids_to_update[] = $array['pk_id']; // array of all records selected, to be used in the UPDATE query
}
$records_returned = mysqlI_num_rows( $result );
$imploded_ids = implode( ', ', $ids_to_update );
$query = "UPDATE table SET flag_field = 'set' WHERE pk_id IN( $imploded_ids )";
if( $result = mysqli_query($dbc, $query) ) {
	if( mysqli_affected_rows($dbc) !== $records_returned ) {
		echo "Possible dataset problem - Number of records updated doesn't match number selected.";
	} else  {
		echo "All selected records updated.";
	}
} else {
	echo "<br>Query $query<br>Failed with error: " . mysqli_error($dbc);
}
} else {
echo "<br>Query $query<br>Failed with error " . mysqli_error($dbc);
}

[chewson]

This worked.  Thank you very much.  I have another topic - does anyone know "CRON" and LENUX I need to write a 'Task Initiator ' for a server, my host tells me I can do this using CRON,and LENUX.

[trq]

It's Linux not Lenux. It's an operating system. If you have a question about cron, open another topic in the 'Other Server Software' board.