Need help with php script that updates records

[kadeous]

I am updating records using a cron job that works perfectly fine. The issue I have is that the script is updating all records using the information from the first row matching my WHERE condition.

I'm using the following to query the database for data.

The row I'm searching for information from is time in the database, and mtime is a row that holds max time for the test.

$query = "SELECT * FROM info WHERE class = 'Math'";
$data = mysqli_query($db, $query);
$row = mysqli_fetch_array($data);

$time = 5;
// Begin and IF statement to update the rows
if ($row['time'] != $row['mtime']) {
 $updatetime = $query = "UPDATE info set time=time+$time, WHERE class = 'Math'";
} // Close If

So now what I want to be able to do is rather then having it only checking the mtime from the first row it pulls data from because each row has different mtime values. I would like to learn how to grab the first row check time vrs mtime update then move to the next record.

Thanks for all help ahead of time!

[rocknbil]

Welcome aboard kadeous, looks to me you need a while loop there to do what you want. Second, if you do this, your update as you have it planned will update ALL records were class='MATH', which I don't think is what you want.

// You've **already** selected all records where class='MATH'. 
// your update inside the while loop should only 
// update the CURRENT record. 
$time = 5; 
while ($row = mysqli_fetch_array($data)) { 
 if ($row['time'] != $row['mtime']) { 
 $this_id=$row[0]; // first in $row array, use $row['id'] if you want 
 $query = "UPDATE info set time=time+$time where id=$this_id"; 
 // execute the update here 
 } 
} 

See how I "locked" it just to update this one record? then the while goes on to the next record, resetting $this_id, to the end of the table.

Note also, you can recycle $query. Once it's executed, you can re-use it in an inner loop.

$query = "select * from table"; 
// execute 
while ($row [etc]) { 
 $query = "some other query"; 
} 

You just save a few bytes of memory by not populating an extra variable.

A last note,

set time=time+$time

this only works if time is a numeric field (unquoted), if it's varchar, you'll have to do something else. Preferably - make it a numeric field. :-)

[Readie]

$query = "UPDATE info set time=time+$time where id=$this_id"; 

Could make this a bit more efficient with limit:

$query = "UPDATE info set time=time+$time where id=$this_id limit 1";

That will mean that this query will only apply to one row - so the server will stop looking for more rows to update after it has updated one row. (Bear in mind that, because we are in a while loop, this same query will be executed multiple times - so it will still update all the rows tou want it to - it'll just do it that little bit quicker)

[kadeous]

thanks a million, I'm going to go give this a try.. and also thank you both for the excellent explanations!

[kadeous]

I just want to thank you both sooo very much! I implemented what you said, and I understand what I was missing. It's now working perfectly! Kudos!

[rocknbil]

Could make this a bit more efficient with limit:
$query = "UPDATE info set time=time+$time where id=$this_id limit 1";

How so? If id is unique auto increment, it's only going to select one record and doesn't continue searching the entire table. (?) Or maybe something I didn't know.

[Readie]

How so? If id is unique auto increment,

Ah ofcourse. Unique index -.-

Ignore me :)

[rocknbil]

Via a few PM messages, for anyone stumbling on this, it turns out there's really no need for an inner while loop in this query. This can all be performed in a single select statement.

if any 1 of the values doesn't match it will update all of the values.

update table set field1=field1+$field1, field2=field2+$field1, field3=field3+$field3, field4=field4+$field4 where ((field1 != mfield1) or (field2 != mfield2) or (field3 != mfield3) or (field4 != mfield4)) and class='Math';

------------------
Note to kadeous: note I've removed the quotes in this version, instead of

set field1=field1+'$field1'

The reason being, quoting a numeric value may cast the variable as a string, and instead of 1+1= 2 you may get 11.
------------------

The previous approach was

select * from table where class='Math';

Which does step through the records one by one, then uses PHP to compare the values and update on any match. This works, but it's horribly inefficient and likely to be slow because it's selecting every record matching class='Math', then doing a second update as it steps through.

So why not just update the records you want, restricting them with a where clause, in a single select statement? It won't update any records except the ones matching on this WHERE:

where ((field1 != mfield1) or (field2 != mfield2) or (field3 != mfield3) or (field4 != mfield4)) and class='Math';

IMPORTANT NOTE about original post: I believe it was posted as an example, but J.I.C., time is a mySQL data type and as posted will cause an error. If you must name a field "time" (you shouldn't,) be sure to backtick the field or table name. backtick `````, not quote ':

$query = "UPDATE info set `time`=`time`+$time where id=$this_id";

[rocknbil]

LOL . . . or, if this is the problem, not the goal,

if any 1 of the values doesn't match it will update all of the values.

We're back to a loop, because you can't compile a set based on a variable where (that I know of.) But it's still one update statement, we just do it 4 times, and if there's no matching records, it won't update anything at all.

$fields = Array( 
'health' => 1, 
'energy' => 1, 
'stamina' => 1, 
'honor' => 1 
); 
// 
foreach ($fields as $key=>$value) { 
 $mfield = 'm' . $key; // make "mhealth" etc 
 $query = "update information set $field=$field+$value where $field=$mfield and class='Math'; 
 // execute query here 
} 

I put them in an associative array so you can vary the values, if they are always going to be 1, or a constant,

$increment=1; 
$fields = Array('health','energy','stamina','honor'); 
// 
foreach ($fields as $fld) { 
 $mfield = 'm' . $fld; 
 $query = "update information set $field=$field+$increment where $field=$mfield and class='Math'; 
 // execute query here 
} 

[kadeous]

So if I'm understanding this then the code overall should look like this:

<?

require_once('connectvars.php');

// Connect to the database and establish values for replenishing Zaltens

$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$query = "SELECT * FROM information WHERE class = 'Math'";
$data = mysqli_query($dbc, $query);

$increment=1;
$fields = Array('health','energy','stamina','honor');
//
while($row = mysqli_fetch_array($data)) {
$this_id = $row[0];
foreach ($fields as $fld) {
$mfield = 'm' . $fld;
$query = "UPDATE information SET '$field'='$field'+'$increment' WHERE '$field' != '$mfield' && id = $this_id";
// execute query here
mysqli_query($dbc, $query);
} // End Foreach
} // End While

mysqli_close($dbc);

?>

I figured you would need the while loop still to only impact one row at a time, I tried the above and nothing updated so I must not be on the same page here. You had to throw an array at me didn't you! :P

[rocknbil]

I figured you would need the while loop still to only impact one row at a time

Nope, you **still** don't need a while loop. It will only impact records that match on the where . . . **IF** you get the where right. I had a typo, why didn't you catch it? :-P

All along you've been doing

if field != mfield

and I had

if field = mfield

Looking at your code, try this. If your records aren't updating, not sure what to say other than start echoing out statements and reviewing the data, something's just not matching. Math, MATH, math maybe?

<? 
 require_once('connectvars.php'); 
 // Connect to the database and establish values for replenishing Zaltens 
 $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME); 
 // 
 $increment=1; 
 $fields = Array('health','energy','stamina','honor'); 
 // 
 foreach ($fields as $fld) {  
 $mfield = 'm' . $fld;  
 $query = "update information set $field=$field+$increment where $field!=$mfield and class='Math';  
 $data = mysqli_query($dbc, $query) or die("cannot execute: ". mysql_error()); 
 // Still troubles? Echo this, copy and paste into phpadmin 
 // echo "$query<br>\n"; 
 }  
 mysqli_close($dbc); 
?>  

I didn't do it on purpose, lol . . .