PHP compare 2 integers and update table with bigger one

Question

Firstly, sorry for my bad english. I hope you will understand what i need. I started a website with products bidding. I have a products page where users can see all the products and a button that send the user to a form where he cand input a new price for that product and it updates the existing price in the database with the new one. But i want this to happen only if the initial price is lower than the price that user inputs.

How can i do that ? I was thiking that i need another column newprice and compare it with price but i don't know how to do that...

This is the table "produse" :

id | titlu | stare | price | descriere
_____________________________________
 1 |  asd  |  asd  |  140 |    asd

This is the products page (produs_user.php)

<?php
include('connect.php');
?>

<?php
//create the query
$result = mysqli_query($con,"select * from produse");

//return the array and loop through each row
while ($row = mysqli_fetch_array($result))
{
?>

<div class="produse"> 
<div class="produse-header"> 
<h3><?php echo $row['titlu'];?></h3>
<h5><?php echo $row['stare'];?></h5>
</div>
<div class="produse-continut">
<textarea readonly><?php echo $row['descriere'];?></textarea>
</div>
<div class="produse-pret"> 
<p><?php echo $row['price'];?> lei</p>
</div>
<div class="produse-buton"> 
<form action="liciteaza.php">
  <input type="submit" value="Liciteaza">
</form>
</div></div>

<?php 
} 
mysqli_close($con);
?>

The above page sends users to liciteaza.php where i have a field where users inputs the new price This is the liciteaza.php

<?php
include('connect.php');
?>

<?php
//create the query
$result = mysqli_query($con,"select * from produse");

//return the array and loop through each row
while ($row = mysqli_fetch_array($result))
{
?>

<form method="post" action="update_pret.php">
<div class="produse"> 
<div class="produse-header"> 
<h3><?php echo $row['titlu'];?></h3>
<h5><?php echo $row['stare'];?></h5>
</div>
<div class="produse-continut">
<textarea readonly><?php echo $row['descriere'];?></textarea>
</div>
<div class="produse-pret">
<p><input name="price" class="modifica-pret" id="price" value="<?php echo $row['price']; ?>"></p>
<input name="id" type="hidden" id="id" value="<?php echo $row['id']; ?>">
</div>
<div class="produse-buton"> 
  <input type="submit" value="Liciteaza">
</form>
</div></div>

<?php 
} 
mysqli_close($con);
?>

That page send me to update_pret.php where i update the new price to mysql table

This is the update_pret.php

<?php
include('connect.php');

// update data in mysql database 
$result=mysqli_query($con,"UPDATE produse SET price='$_POST[price]' WHERE id='$_POST[id]'");

// if successfully updated. 
if($result)
{
header("location:produse_user.php");
}
else 
{
echo "ERROR";
}
mysqli_close($con);
?>

All these scripts are working perfectly but like i said i need this to work only when the new price chosen by the user is bigger than the initial price.

score 2 · Answer 1 · answered Jun 01 '14 at 12:40

2

One option would be to modify your where statement:

$result=mysqli_query($con,"UPDATE produse SET price='$_POST[price]' 
WHERE id='$_POST[id]' AND price < $_POST[price] ");

answered Jun 01 '14 at 12:40

Menelaos

23,508
18
90
155

This has the added advantage of NOT running an update if the price is not greater than the existing (because we filter with where). – Menelaos Jun 01 '14 at 12:43
My bad, +1, I have not noticed this, as I was editing my answer without viewing others'. – potashin Jun 01 '14 at 12:47

potashin · Accepted Answer · 2014-06-01T12:44:52.700

1

Fist solution : Using IF

UPDATE `produse` 
SET `price` = IF(`price` > '$_POST[price]', `price`, '$_POST[price]') 
WHERE `id` = '$_POST[id]'

Second solution : Using CASE statement:

UPDATE `produse` 
SET `price` = (CASE WHEN `price` > '$_POST[price]' THEN `price` ELSE  '$_POST[price]' END)
WHERE `id` = '$_POST[id]'

edited Jun 01 '14 at 12:44

answered Jun 01 '14 at 12:39

potashin

44,205
11
83
107

Thank you so much. It's working now. I used your first solution – Cosmin Jun 01 '14 at 12:44

score 1 · Answer 3 · edited May 23 '17 at 10:26

maythesource.com's answer also contains this update:

$result=mysqli_query($con,"UPDATE produse SET price='$_POST[price]' WHERE id='$_POST[id]' and price > $_POST[price]");

should solve your problem, however, you absolutely do not protect yourself against SQL injection. You should make sure that the user input does not contain malicious code updating or deleting something else.

Read about SQL Injection attack type here. You can use prepared statements as described here.

EDIT: As requested by maythesource.com, I provide an SQL injection example which could do harm. Let's suppose you use the code I have provided in my initial answer. $_POST['price'] has the value of:

66';

then it will update all your products in the produse table to have the price of 66, therefore you will lose all your prices. Imagine if all the real-estates in the auction will have 66 lei as price. Your boss will want your head :)

+1 Your right about the SQL injection part!! Maybe good to provide him with an example :) — Menelaos, Jun 01 '14 at 12:51
@maythesource.com, good point, it is good to illustrate the danger with an example. — Lajos Arpad, Jun 01 '14 at 13:14

score -1 · Answer 4 · answered Jun 01 '14 at 12:42

UPDATE produse SET price = '$_POST[price]' WHERE id='$_POST[id]' AND price < $_POST['price']

You should be able to check the sql response to see if the update actually affected any rows, so you can update the user accordingly.

Note that this is unsafe, you should assign $_POST['price'] to a var first, and check the contents (parse to a number), and use that var in the query. Inserting $_POST directly into a query allows attackers to modify the query.

PHP compare 2 integers and update table with bigger one

4 Answers4