PHP Building of MySQL Query String from Passed Variables

Question

I have a HTML search which is passing variables via $_GET to a PHP which uses these passed variables to build a query string. The problem I am facing is building a query string that may only contain one search criteria or it may contain multiple. If only one criterion is used for the search then there is no need for an "AND" statement in the query. If there are multiple criteria used then "AND" will be needed between each criteria. How can one handle this "AND" related problem?

    <?php

    $IKfield01 = (isset($_GET['field01']) ? $_GET['field01'] : null);
    $IKfield02 = (isset($_GET['field02']) ? $_GET['field02'] : null);
    $IKfield03 = (isset($_GET['field03']) ? $_GET['field03'] : null);
    $IKfield04 = (isset($_GET['field04']) ? $_GET['field04'] : null);
    $IKfield05 = (isset($_GET['field05']) ? $_GET['field05'] : null);
    $IKfield06 = (isset($_GET['field06']) ? $_GET['field06'] : null);
    $IKfield07 = (isset($_GET['field07']) ? $_GET['field07'] : null);
    $IKfield08 = (isset($_GET['field08']) ? $_GET['field08'] : null);
    $IKfield09 = (isset($_GET['field09']) ? $_GET['field09'] : null);
    $IKfield10 = (isset($_GET['field10']) ? $_GET['field10'] : null);

    $searchfield01 = mysqli_real_escape_string($mysqli,$IKfield01);
    $searchfield02 = mysqli_real_escape_string($mysqli,$IKfield02);
    $searchfield03 = mysqli_real_escape_string($mysqli,$IKfield03);
    $searchfield04 = mysqli_real_escape_string($mysqli,$IKfield04);
    $searchfield05 = mysqli_real_escape_string($mysqli,$IKfield05);
    $searchfield06 = mysqli_real_escape_string($mysqli,$IKfield06);
    $searchfield07 = mysqli_real_escape_string($mysqli,$IKfield07);
    $searchfield08 = mysqli_real_escape_string($mysqli,$IKfield08);
    $searchfield09 = mysqli_real_escape_string($mysqli,$IKfield09);
    $searchfield10 = mysqli_real_escape_string($mysqli,$IKfield10);

    $prequery = "SELECT * FROM table WHERE";
    $prequery1 = "";
    $prequery2 = "";
    $prequery3 = "";
    $prequery4 = "";
    $prequery5 = "";
    $prequery6 = "";
    $prequery7 = "";
    $prequery8 = "";
    $prequery9 = "";
    $prequery10 = "";
    $prequery11 = "";
    $prequery12 = " LIMIT $maxsearch";

    if ($searchfield01 != '') $prequery2 = "genus LIKE '%$searchfield01%'";
    if ($searchfield02 != '') $prequery3 = "AND specificEpithet LIKE '%$searchfield02%'";
    if ($searchfield03 != '') $prequery4 = "AND stateProvince LIKE '%$searchfield03%'";
    if ($searchfield04 != '') $prequery5 = "AND county LIKE '%$searchfield04%'";
    if ($searchfield05 != '') $prequery6= "AND family LIKE '%$searchfield05%'";
    if ($searchfield06 != '') $prequery7 = "AND locality LIKE '%$searchfield06%'";
    if ($searchfield07 != '') $prequery8 = "AND OtherCatalogNumbers LIKE '%$searchfield07%'";
    if ($searchfield08 != '') $prequery9 = "AND recordedBy LIKE '%$searchfield08%'";
    if ($searchfield09 != '') $prequery10 = "AND recordNumber LIKE '$searchfield09'";
    if ($searchfield10 != '') $prequery11 = "AND catalogNumber LIKE '%$searchfield10%'";

    $query = "$prequery $prequery2 $prequery3 $prequery4 $prequery5 $prequery6 $prequery7 $prequery8 $prequery9 $prequery10 $prequery11 $prequery12";

    $row_count = 0;
    $result = mysql_query($query) or die("MS-Query Error in select-query");
    $querystats=mysql_num_rows($result); 
    $resultcounter=1;

    while ($row = mysql_fetch_array($result))
    {
    $IKdfield01 = "$row[field01]";
    $IKdfield02 = "$row[field02]";
    $IKdfield03 = "$row[field03]";
    $IKdfield04 = "$row[field04]";
    $IKdfield05 = "$row[field05]";
    $IKdfield06 = "$row[field06]";
    $IKdfield07 = "$row[field07]";
    $IKdfield08 = "$row[field08]";
    $IKdfield09 = "$row[field09]";
    $IKdfield10 = "$row[field10]";
    $IKdfield11 = "$row[field11]";
    $IKdfield12 = "$row[field12]";
    $IKdfield13 = "$row[field13]";
    $IKdfield14 = "$row[field14]";
    $IKdfield15 = "$row[field15]";
    $IKdfield16 = "$row[field16]";
    $IKdfield17 = "$row[field17]";
    $IKdfield18 = "$row[field18]";
    $IKdfield19 = "$row[field19]";
    $IKdfield20 = "$row[field20]";
    $IKdfield21 = "$row[field21]";
    $IKdfield22 = "$row[field22]";
    $IKdfield23 = "$row[field23]";
    $IKdfield24 = "$row[field24]";
    $IKdfield25 = "$row[field25]";
    $IKdfield26 = "$row[field26]";
    $IKdfield27 = "$row[field27]";

    //output results

    echo "$IKfield01, $IKfield02, $IKfield03, $IKfield04, $IKfield05, $IKfield06, $IKfield07, $IKfield08, $IKfield09, $IKfield10, $IKfield11, $IKfield12";
    echo "$IKfield13, $IKfield14, $IKfield15, $IKfield16, $IKfield17, $IKfield18, $IKfield19, $IKfield20, $IKfield21, $IKfield22, $IKfield23, $IKfield24";
    echo "$IKfield25, $IKfield26, $IKfield27, (EOR) <br>";

    $resultcounter++;
    $row_count++;
    }

    ?>

Sir,You want AND IF more then on querystring variable is passed? — Vasim Shaikh, Feb 24 '16 at 17:49
Users may search against a single field or multiple fields. Thus query strings must be built in such a way that it can handle a single search criteria or multiple search criteria. I would love to build query strings from the $_GET variable(s). I have users doing searches in which Field01 is left blank. — QuePID, Feb 24 '16 at 18:09
@VasimVanzara: "GET Url truncate after 256 character" .. this is false for the [HTTP](http://stackoverflow.com/questions/417142/what-is-the-maximum-length-of-a-url-in-different-browsers) spec, Apache and PHP .. so what are you talking about? Do you have a reference for your claim? — Michael M, Feb 24 '16 at 18:53

score 1 · Accepted Answer · edited May 23 '17 at 10:28

1

You could use WHERE 1 so that you always end with and AND at every clause.

The other solution is to create a variable $where with the criteria and check if there's any content before adding clauses, if yes, you add an AND

<?php
$sql = "SELECT * FROM table"
$where = "";
// ...

if($myparam) {
    if(strlen($where) > 0) $where .= ' AND';
    $where .= " myparam ='myval'";
}
// ...
if(strlen($where) > 0) $sql = $sql . ' WHERE ' . $where;

edited May 23 '17 at 10:28

Community

1
1

answered Feb 24 '16 at 18:27

Keyne Viana

6,194
2
24
55

Your solution somewhat works as long contiguous fields had data entry. For example, if you have values for Field03, Field04, and Field08 you get a query string like this: SELECT * FROM table WHERE field03 ='field03' AND field04 ='field04' AND AND AND AND field08 ='field08' AND AND. Notice the extra AND's, this is because once you store data in the variable $where it always has a value greater than 0. The extra AND's present problems. – QuePID Feb 24 '16 at 22:02

score 0 · Answer 2 · answered Feb 24 '16 at 18:46

I would build an array of parameters, and implode them into a query:

$query_array = array();
$fields = array(
    1=>'genus', 
    2=>'specificEpithet', 
    3=>'stateProvince', 
    4=>'county',
    5=>'family',
    6=>'locality',
    7=>'OtherCatalogNumbers',
    8=>'recordedBy',
    9=>'recordNumber',
    10=>'catalogNumber'
    );
for($i = 1; $i <= 10; $i++){
    $field = 'field' . str_pad($i, 2, " ", STR_PAD_LEFT);
    if(!isset($_GET[$field])
        continue;

    $value = mysqli_real_escape_string($mysqli,$_GET[$field]);
    $query_array[] = $fields[$i] . ' LIKE %' . $value . '%';
}

$query = "SELECT * FROM table WHERE " . implode(' AND ', $query_array) . " LIMIT $maxsearch";
$row_count = 0;
$result = mysql_query($query) or die("MS-Query Error in select-query");

//etc

PHP Building of MySQL Query String from Passed Variables

2 Answers2