updating multiple fields in the table

[milan.sabic]

I have a table that looks like this

room          date        price
128       02-09-2013       15
128       03-09-2013       17 
128       04-09-2013       19

If I want to update the field price depending on the dates,

First I have the form with the data:

while($rows=mysql_fetch_array($listprices))

<? $date[]=$rows[date]; ?>
<p style="width:100px;float:left;text-align:center;"><input name="price[]" type="text" value="<? echo $rows[´price´]; ?>"></p>

After that update sql

UPDATE tablename SET price='".$_POST['price'][$i]."' WHERE date='$date[$i]'";

What am I doing wrong?

[pritaeas]

The date format should be 'yyyy-mm-dd'

[Rahul47]

The code you posted is not clear. What is order of your code. Please be clear. If Possible add a snapshot for your question.

[milan.sabic]

at the top in the php code I am getting the data from the DB:

$pricesql="SELECT * FROM my_table";
$pricesqlresult=mysql_query($pricesql);

In the body I am having the following in order to list the results

<?php
while($rows=mysql_fetch_array($pricesqlresult)){
$date= $row['date'];
?>
<p><input name="price[]" type="text" value="<? echo $rows['price']; ?>"></p>
<?php
}
?>

at the bottom of the page I have a php code with following sql:

<?php
if($_SERVER['REQUEST_METHOD'] == "POST"){
for($i=0;$i<$count;$i++){
$sql1="UPDATE my_table SET price='".$_POST['price'][$i]."' WHERE date='$date[$i]'";
$result99=mysql_query($sql1);
}
}

mysql_close();
?>

I do not know why the code does not update.

Thanks

[Rahul47]

In your top php code, you have done a mistake.
Your Code: $pricesqlresult=mysql_query($pricesql);

It should be : $pricesqlresult=mysql_query($con,$pricesql);
Where $con is object of SQL connection. Such as,

$con=mysqli_connect("myhost","myuser","mypassw","mydatabase");

[Rahul47]

Further more you can add a check to see if connection is fine as follows:

<?php
$con=mysqli_connect("localhost","my_user","my_password","my_db");

// Check connection
if (mysqli_connect_errno($con))
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
?>

Morever your code is vulnerable to XSS and SQLI as you havent sanitized your data beofre using in UPDATE query. Check for that too.

[milan.sabic]

Connection is ok, i could update the table if i use normal variables, as soon as i use arrays it does not update. I know about sql injections but i need to sort the update first

[milan.sabic]

If i use SET price='1' WHERE date='2013-09-02' for example it works fine, i have a problem somwhere in array definition

[milan.sabic]

_POST['price'][$i] is not recognized

[Rahul47]

Damn it, My Bad . .. how could I not see this: LOL . .. My BAD .

Here it is: .$_POST['price'][$i]

$_POST[] array is one dimensional array associative array. What you are trying is assigning it a 2nd dimension. That's why it is not recognized.

[milan.sabic]

what is the difference between yours and mine

".$_POST['price'][$i]."

i am sorry but i am seeing the same thing

[Rahul47]

I pointed out that $_POST['price'][$i] was a mistake, it should be .$_POST['price'] <-- One dimensional.

[milan.sabic]

isn´t that going to change only one piece of data instead of the whole column.

[Rahul47]

Let's have a look at your Query:

UPDATE my_table SET price='".$_POST['price']."' WHERE date='$date[$i]'"

That will SET price = <what you enter> WHERE date=<whatever in $date>.

[milan.sabic]

that is not working ;-)

[1stDAN]

In your statement
3. $date= $row['date'];

$date is single variable (scalar), but in

4. $sql1="UPDATE my_table SET price='".$_POST['price'][$i]."' WHERE date='$date[$i]'";

now $date is an array, there is something wrong.

There sould be an array index, like $i = 0; then statement 2. while ...; 3. $date[$i++] = $row['date'];

Also $count seems to be unknown. I thing $count should be set to $i just before line 8. ?>

(Only a simple hint: Instead of $pricesql="SELECT * FROM my_table"; it is almost saver (against future changes of the table structure) to list all necessary columns: $pricesql="SELECT room, date, price FROM my_table";)

[milan.sabic]

i made mistake the date is not

<? $date=$rows['date']; ?>

but

<? $date[]=$rows['date']; ?>

and $count is

$count=mysql_num_rows($pricesql);