CODEWITHSUNDEEP
phpmysql
chrpust
chrpust

Reputation: 17

Update all rows in MySql in while loop PHP

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"];
    }

Upvotes: 0

Views: 12048

Answers (2)

scrowler
scrowler

Reputation: 24406

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

Upvotes: 3

w3c_ee
w3c_ee

Reputation: 11

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.

Upvotes: -1

Related Questions