[RESOLVED] SELECTing and UPDATEing all records

[NevadaSam]

SELECTing and UPDATEing all records

In MySQL database table I want to SELECT each record, change the way a date is formated in one field and write this new format to another field of that record. I want the script to continue through each record and do the whole database table.

I am having a problem with my do while loop. The first record is UPDATE alright, but the I get the error:
Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in C:\websites\test.php on line 43
Line# 43 is $Row = mysqli_fetch_assoc($QueryResult);

How can I do this?

<?php
$DBConnect = mysqli_connect($host,$user,$password)
	Or die("<p>Unable to connect to the database server.</p>"
	. "<p>Error code " . mysqli_connect_errno()
	. ": " . mysqli_connect_error()) . "</p>";
mysqli_select_db($DBConnect, $DBName)
	Or die("<p>Unable to select the database.</p>"
	. "<p>Error code " . mysqli_errno($DBConnect)
	. ": " . mysqli_error($DBConnect)) . "</p>";

$SendQuery = "SELECT * FROM table";

$QueryResult = mysqli_query($DBConnect, $SendQuery)
	Or die("<p>Unable to excute the requested query.</p>"
	. "<p>Error code " . mysqli_errno($DBConnect)
	. ": " . mysqli_error($DBConnect)) . "</p>";

$Row = mysqli_fetch_assoc($QueryResult);

do {
$TimeStamp = strtotime($Row['old_date_stamp']);

$SendQuery = "UPDATE table 
	SET date_stamp = '$TimeStamp'
	WHERE id = '{$Row['id']}'";

$QueryResult = mysqli_query($DBConnect, $SendQuery)
	Or die("<p>Unable to excute the requested query.</p>"
	. "<p>Error code " . mysqli_errno($DBConnect)
	. ": " . mysqli_error($DBConnect)) . "</p>";

echo $Row['id'] . "\t" . $TimeStamp . "\t" 
. $Row['old_date_stamp'] . "<br />";

$Row = mysqli_fetch_assoc($QueryResult);
} while ($Row);
?>

[NogDog]

You should be able to do this with one update query (instead of looping through the results of a select query):

If the old_date_stamp is a date or datetime field:

UPDATE table SET date_stamp = UNIX_TIMESTAMP(old_date_stamp) WHERE 1

If the old_date_stamp is a varchar or something else, then you'd need to use STR_TO_DATE() function to convert the value to a date/datetime type.

[drew010]

I think you are losing the $Row result resource by overwriting it in the end of the loop.
Try leaving the top part the same, but from the first
$Row = mysqli_fetch_assoc($QueryResult);
change it to this:

$Row = mysqli_fetch_assoc($QueryResult);

while ($Row = mysqli_fetch_assoc($QueryResult)) {
  $TimeStamp = strtotime($Row['old_date_stamp']);

  $SendQuery = "UPDATE table
        SET date_stamp = '$TimeStamp'
        WHERE id = '{$Row['id']}'";

  $QueryResult2 = mysqli_query($DBConnect, $SendQuery)
        Or die("<p>Unable to excute the requested query.</p>"
        . "<p>Error code " . mysqli_errno($DBConnect)
        . ": " . mysqli_error($DBConnect)) . "</p>";

echo $Row['id'] . "\t" . $TimeStamp . "\t"
. $Row['old_date_stamp'] . "<br />";
}
?>

[NevadaSam]

I see Drew, my 2nd $QuerryResult was overwriting my first. Thanks for that and showing me how to use the while statement instead of the do while.

Sam
;