PHP MySQL- Change Where statement according to GET variables

Question

Hi i have a table and i want to apply 4-5 sql select queries on this table

Table

id     name      product       price

1      Amit      car           100000
2      Sumit     car           300000
3      Naina     car           250000
4      Ashu      car           125000
5      Sanjay    scooter        40000
6      Rahul     scooter        32000

I want to apply 4-5 queries on this table like First i want to fetch all results where product = car

Query1:

SELECT * FROM Table WHERE product='car'

URL:  www.example.com/product/car

Output:

id     name      product       price

1      Amit      car           100000
2      Sumit     car           300000
3      Naina     car           250000
4      Ashu      car           125000

Query2:

SELECT * FROM Table WHERE product='car' ORDER BY price

URL:  www.example.com/product/car?sort=plth

Output:

id     name      product       price

1      Amit      car           100000
4      Ashu      car           125000
3      Naina     car           250000
2      Sumit     car           300000

Query3:

SELECT * FROM Table WHERE product='car' ORDER BY price DESC

URL:  www.example.com/product/car?sort=phtl

Output:

id     name      product       price

2      Sumit     car           300000
3      Naina     car           250000
4      Ashu      car           125000
1      Amit      car           100000

Is it possible to declare an if else statement in advance and use a variable in sql statement and write select statement once.

Like

$where="WHERE product='car'";

if(isset($_GET['sort'])){
  if(($_GET['sort'])=='plth'){
    $where="WHERE product='car' ORDER BY price"
  }
  elseif($_GET['sort'])=='phtl'){
    $where="WHERE product='car' ORDER BY price DESC"
  }
  ------
  ------
  ------
}

Query be like:

SELECT * FROM Table $where;

Thanks in advance...

@Qirel Yeah i am trying since hours...its not working don't know why.... — Ashish
– Ashish, Commented Dec 23, 2016 at 9:02
Debug it then. You know, we don't have access to your computer and cannot run your program to see why it doesn't work. — Your Common Sense
– Your Common Sense, Commented Dec 23, 2016 at 9:03
"not working" doesn't really describe the problem, makes it hard for us to help you ;-) doesn't work how? — Qirel
– Qirel, Commented Dec 23, 2016 at 9:04
@AlessandroMaglioccola Actually by mistake i was writing $where="product='car'" instead of $where="WHERE product='car'"... — Ashish
– Ashish, Commented Dec 23, 2016 at 14:20

Thorsten Kettner · Accepted Answer · 2016-12-23 09:31:33Z

1

You'd use a prepared statement and bind variables. As to ascending or descending order this is not so easy, because ASC and DESC are keywords, and bind variables can be used for column values only, not for keywords. But then, sorting prices descending is the same as sorting by price * -1 :-)

$product = "car";
$order = "DESC";

$pdo = new PDO("mysql:host=localhost;dbname=database", "username", "password");
$stmt = $pdo->prepare("select * " .
                      "from table " .
                      "where product = :product " .
                      "order by price * case when :order = 'DESC' then -1 else 1 end ");
$stmt->bindParam(':product', $product, PDO::PARAM_STR, 12);
$stmt->bindParam(':order', $order, PDO::PARAM_STR);
$stmt->execute();

Or, if you don't find CASE WHEN as readable, use

$order = -1;
...
              "order by price * :order ");
...
$stmt->bindParam(':order', $order, PDO::PARAM_INT);

edited Dec 23, 2016 at 9:31

answered Dec 23, 2016 at 9:17

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

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

Collectives™ on Stack Overflow

PHP MySQL- Change Where statement according to GET variables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related