a reusable query with modifications?

Question

i'm using this mysql query alongwith php to search for multiple keywords:

$query = "SELECT cQuotes, vAuthor, cArabic, vReference FROM ".$table." WHERE (";
  $countFields = count($arrayFields);
  while ($a < $countFields)
  {
    while ($b < $countSearch)
    {
      $query = $query."$arrayFields[$a] LIKE '%$arraySearch[$b]%'";
      $b++;
      if ($b < $countSearch)
      {
        $query = $query." AND ";
      }
    }
    $b = 0;
    $a++;
    if ($a < $countFields)
    {
      $query = $query.") OR (";
    }
  }
  $query = $query.")";
  $result = mysql_query($query, $conn)

i'd like to reuse this query with a few modifications to it (for instance, the WHERE clause remains the same, while i query the number of rows using COUNT), but it doesn't seem practical to repeat the code again for a few additions. any suggestions?

Answer 1

<?php

$table = "myTable";
$justCount = true;
$requiredFields = array('cQuotes', 'vAuthor', 'cArabic', 'vReference');
$arrayFields = array('cQuotes','vAuthor');
$arraySearch = array('blah','foo','bar');

///////////////
$selectWhat = $justCount ? "COUNT(*)" : implode(',', $requiredFields);

$wherePart = array();
foreach($arraySearch as $search)
{
    $subWherePart = array();
            foreach($arrayFields as $field)
    {
        $subWherePart[] = $field . " LIKE '%" . $search ."%'";
    }
    $wherePart[] = "(" . implode(" AND ", $subWherePart) . ")";
}

$query = "SELECT " . $selectWhat . " FROM " . $table
        . " WHERE " . implode(" OR ", $wherePart);

?>

don't forget to filter input search words to avoid SQL Injeciton.

Answer 2

You could pull that code out into a separate function, then send it a parameter telling the function what version of the query you want. The function would then construct the query and return the string. I also think prepared statements might be beneficial to you.

Answer 3

I don't understand exactly what you're doing since there's code missing, but I'd suggest the following:

Don't use while with arrays; use foreach it's much more compact and that's what it was made for.

Don't concatenate strings manually, use implode()

Don't add complexity to your SQL to count result; use MYSQL's FOUND_ROWS() instead.

On a somewhat unrelated note I'd suggest upgrading from PHP's mysql library to mysqli. It allows multiple queries, which will make your life easier.

Answer 4

You could try using a query builder library or ORM, especially if this problem is happening repeatedly. They allow you to create SQL functionally. I would suggest using Doctrine or Sqloo (spoiler alert: I'm the creator of Sqloo). Since you can use them to functionally create SQL, you can even pass partial queries around since they object, to allow for a very high reuse of code.

A few examples for Doctrine and Sqloo.