Update all rows in MySql in while loop PHP

Question

I have a problem. I want to update values in MySql with a while loop. My code so far: Here I get the rows from the database:

$sql = mysql_query("SELECT * FROM Price WHERE idCar='$idCar'");
while($row = mysql_fetch_array($sql)){
$i = $i+1;
$price = $row["price"];
$newdate= $row["newdate"];
$idCar = $row["idCar"];
$idPrice = $row["idPrice"];
$sqlsearch .= '  <tr>
    <td height="31" align="left" valign="middle"><div align="right">Dato:</div></td>
    <td height="31" align="left" valign="middle">
    <fieldset id="example_1"><input type="text" name="newdate" class="field" cols="42" rows="8" id="datepicker'. $i .'" value='. $newdate.' /> 
    &emsp;&emsp;Pris:&nbsp;<input type="text" name="price" class="field" cols="42" rows="8" id="price" value='. $price .' />&emsp;&emsp;&emsp;&emsp;
    Priceid:&nbsp;<input type="text" name="idPrice" class="field" cols="42" rows="8" id="idPrice" value='. $idPrice .' />
    </fieldset></td>
    </tr>';

Here I want to update the new values:

$idCar = $_GET['idCar'];

$sqlupdate = mysql_query("UPDATE Price SET newdate='$newdate', price='$price' WHERE idPrice='".$idPrice."'");

while($row = mysql_query($sqlupdate)){
    $price = $row["price"];
    $newdate = $row["newdate"];
    $idPrice = $row["idPrice"];
    }

First of all **please avoid using mysql_\* functions, they are deprecated**, use mysqli_\* or mysql_pdo\* functions instead. — Simon Forsberg, Oct 15 '13 at 23:06
Read about [How to prevent SQL-injection attacks](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) here — Simon Forsberg, Oct 15 '13 at 23:14
You could avoid a lot of these mistakes by spending a small amount of time reading a [PHP survival guide](http://phptherightway.com/). — tadman, Oct 15 '13 at 23:29

scrowler · Accepted Answer · 2013-10-16T19:35:44.753

When calling an SQL update query, you don't need to loop through it. Are you trying to retrieve the updated values again? (You have them in PHP variables already).

Your first code snippet looks fine (other than the broken record around here at the moment of mysql_* is deprecated, use mysqli_* etc...), but your second one:

$sqlupdate = mysql_query("UPDATE Price SET newdate='$newdate', price='$price' WHERE idPrice='".$idPrice."'");

This line will update all records with idPrice = {contents of idPrice} (putting LIMIT 1 if you only expect one record to update won't hurt you at all). You don't need to loop it, and I can't see what you're actually doing.

You should then be able to determine if the query was successful:

if($sqlupdate)
    echo 'Success';
else
    echo 'Failed: ' . mysql_error();

I'm assuming this code will go inside your current loop from example one?

--edit-- example:

// get info from db
$sql = mysql_query("SELECT * FROM Price WHERE idCar='$idCar'");
while($row = mysql_fetch_array($sql)){
    $i = $i+1;
    $price = $row["price"];
    $newdate= $row["newdate"];
    $idCar = $row["idCar"];
    $idPrice = $row["idPrice"];

    // display info
    $sqlsearch .= '  <tr>
    <td height="31" align="left" valign="middle"><div align="right">Dato:</div></td>
    <td height="31" align="left" valign="middle">
    <fieldset id="example_1"><input type="text" name="newdate" class="field" cols="42" rows="8" id="datepicker'. $i .'" value='. $newdate.' /> 
    &emsp;&emsp;Pris:&nbsp;<input type="text" name="price" class="field" cols="42" rows="8" id="price" value='. $price .' />&emsp;&emsp;&emsp;&emsp;
    Priceid:&nbsp;<input type="text" name="idPrice" class="field" cols="42" rows="8" id="idPrice" value='. $idPrice .' />
    </fieldset></td>
    </tr>';

    $newdate = 'Your value to update';
    $price = 'Your price to update';
    // update data
    $sqlupdate = mysql_query("UPDATE Price SET newdate='$newdate', price='$price' WHERE idPrice='".$idPrice."'");
    // exit with error message if there was an error
    if(!$sqlupdate) die('Error:' . mysql_error());
}

Your answer is better than mine. Removed mine and upvoted yours — Simon Forsberg, Oct 15 '13 at 23:15
Hey Scrowler Thank you so much for the answer. Arhh so I don't need to loop though with a while loop then I save the data to the database! Can you give a small exsample of how to get data from the database, display it in a form and then change some of it and last save it to db again :)? — chrpust, Oct 16 '13 at 08:15

score -1 · Answer 2 · answered Oct 15 '13 at 23:34

mysql_query() returns a resultValue, not data.

You would then grab rows like:

  $myRv = mysql_query("SQL GOES HERE");

  while($row=mysql_fetch_assoc($myRv)) {
    // do somehting with $row
  }

BUT update/inserts don't return data with the mysql_fetch_assoc or other functions.

Update all rows in MySql in while loop PHP

2 Answers2