reducing mysql statements that differ only in ORDER BY field name

Question

I'm trying to sort data by different fields ascending and descending. But I have different mysql pdo statements for the 4 fields I have (8 queries total):

$stmt1 = $po->prepare("SELECT * FROM tabname WHERE categ=:categ ORDER BY field1 DESC");
$stmt2 = $po->prepare("SELECT * FROM tabname WHERE categ=:categ ORDER BY field1 ASC");
$stmt3 = $po->prepare("SELECT * FROM tabname WHERE categ=:categ ORDER BY field2 DESC");
$stmt4 = $po->prepare("SELECT * FROM tabname WHERE categ=:categ ORDER BY field3 ASC");
$stmt5 = $po->prepare("SELECT * FROM tabname WHERE categ=:categ ORDER BY field3 DESC");
$stmt6 = $po->prepare("SELECT * FROM tabname WHERE categ=:categ ORDER BY field3 ASC");
$stmt7 = $po->prepare("SELECT * FROM tabname WHERE categ=:categ ORDER BY field4 DESC");
$stmt8 = $po->prepare("SELECT * FROM tabname WHERE categ=:categ ORDER BY field4 ASC");

Based on input, I pick the right statement and bind and execute it.

if ($sortcode == 1){ 
   $stmt1->bindParam(':categ', $categ, PDO::PARAM_STR);
   $stmt1->execute();
   $fetched = $stmt1->fetchAll(PDO::FETCH_ASSOC);
} else if ($sortcode == 2){
   $stmt2->bindParam(':categ', $categ, PDO::PARAM_STR);
   $stmt2->execute();
   $fetched = $stmt2->fetchAll(PDO::FETCH_ASSOC);
} else if ($sortcode == 3){
   $stmt3->bindParam(':categ', $categ, PDO::PARAM_STR);
   $stmt3->execute();
   $fetched = $stmt3->fetchAll(PDO::FETCH_ASSOC);
}
//repeat the block 5 more times, for a total of 8

This doesn't look right at all. Since the select statements only differ int he name of the field and the desc/asc, is there a better way to get the $sortcode and compact the code that follows?

I guess I could state the question more specifically as: is there a way I could have a single statement/single pdo statement that binds the field name and asc/decs dynamically?

Answer 1

You can order by column number, which can be parameterized. Using an ORDER BY in this fashion is usually clearer if you specify the columns in the select clause. I'm not sure if ASC/DESC can be parameterized, though...

Answer 2

You can always build the queries dynamically. Store the map between $sortcode and the columns, the SELECT * FROM tabname WHERE categ=:categ part of the query, get the right column name based on $sortcode and add the ORDER BY ... part.

Answer 3

Use associative arrays to hold the prepared statements.

Your input is a column and a sorting method, right? So prepare the queries by:

$columns = array("field1", "field2", "field3", "field4");
$orders = array("asc", "desc");
$queries = array();
foreach($columns as $col) {
  $queries[$column] = array();
  foreach ($orders as $order) {
     $queries[$column][$order] = $po->prepare("SELECT * FROM tabname WHERE categ=:categ ORDER BY $column $order");
  }
}

Now, to look up the correct query, you don't need some synthetic code -- you just look it up by column and order directly.

To look up a query, instead of having your users input a number from 1-8, have them input a column and an order. Imagine that the column is in the variable $col and the order is in $ord. Just say $queries[$col][$ord].

If for some reason you have to use the number (why?), then you need a slightly different strategy. In that case, you store the queries by that number.

$columns = array("field1", "field2", "field3", "field4");
$orders = array("asc", "desc");
$queries = array();
$i = 0;
foreach($columns as $col) {
  foreach ($orders as $order) {
     $i = $i + 1;
     $queries[$i] = $po->prepare("SELECT * FROM tabname WHERE categ=:categ ORDER BY $column $order");
  }
}

In other words, you ought to be storing queries according to how you plan to look them up.