0

How would I shorten this mysql query for usage in php? 

    <?php  
    $sql = "SELECT * FROM $this->usernameid where
    name LIKE '%$term%' OR
    manufacture1 LIKE '%$term%' OR
    manufacture2 LIKE '%$term%' OR
    manufacture3 LIKE '%$term%' OR
    manufacture4 LIKE '%$term%' OR
    manufacture5 LIKE '%$term%' OR
    manufacture6 LIKE '%$term%' OR
    manufacture7 LIKE '%$term%' OR
    manufacture8 LIKE '%$term%' OR
    manufacture9 LIKE '%$term%' OR
    manufacture10 LIKE '%$term%'
        ORDER BY $order1";
    ?>

Looking to do a while loop, as an example here is my $_POST for another part of the program.

   <?php

    $i = 1;
    while ($i < 10) {
        $manufacture[$i] = strtoupper(filterinput($_POST['manufacture' . $i]));
        $i++;
    };
    ?>
M-n
  • 35
  • 6

1 Answers1

0
// Base query
$baseQuery = "SELECT * FROM $this->usernameid WHERE name LIKE '%$term%' ";

// Get all columns from the table with manufacturers
$manufacturers = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA "
               . "WHERE TABLE_NAME = '{YOUR_TABLE}' "
               . "AND COLUMN_NAME LIKE 'manufacturer%';"
// Execute query
$getManufacturers = mysql_query( $manufacturers );
$addWhereClause = ''; // Additional WHERE clauses
// Loop over all the columns 'LIKE manufacturer%'
while ($manu = mysql_fetch_rows( $getManufacturers )) {
  // Add an additional clause for every manufacturer
  $addWhereClause .= "OR $manu LIKE '%$term% ";
}

// Append everything together and you have your dynamic query.
$query = $baseQuery.$addWhereClause."ORDER BY $order1;";
Mr. Polywhirl
  • 42,981
  • 12
  • 84
  • 132
  • [**Please, don't use `mysql_*` functions in new code**](http://stackoverflow.com/a/14110189/1723893). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). – NullPoiиteя Jan 04 '13 at 04:44
  • I understand they are being deprecated, but I am really only using them for proof on concept. It's the 2 query strings in my answer that really matter. The rest can be an exercise for OP. I know how to use msqli I am just too tired to think right now. [Here is an answer that I responded to using mysqli.](http://stackoverflow.com/a/14117228/1762224) – Mr. Polywhirl Jan 04 '13 at 04:50