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
– Simon Forsberg, Commented Oct 15, 2013 at 23:06
You could avoid a lot of these mistakes by spending a small amount of time reading a PHP survival guide. — tadman
– tadman, Commented Oct 15, 2013 at 23:29

scrowler · Accepted Answer · 2013-10-16 19:35:44Z

3

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());
}

edited Oct 16, 2013 at 19:35

answered Oct 15, 2013 at 23:09

scrowler

24.4k10 gold badges67 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Simon Forsberg Over a year ago

Your answer is better than mine. Removed mine and upvoted yours

chrpust Over a year ago

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 :)?

w3c_ee · Accepted Answer · 2013-10-15 23:34:13Z

-1

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.

answered Oct 15, 2013 at 23:34

w3c_ee

114 bronze badges

Collectives™ on Stack Overflow

Update all rows in MySql in while loop PHP

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related