Skip to main content
August 13, 2013
Question

How to bind params in a prepared statement with a dynamic number of fields

  • August 13, 2013
  • 0 replies
  • 945 views

I use php and MySql, I am currenly changing my website over to the MySQLi connection and changing the code to prepared statements, help would be much appreciated with the following:

My search form submits fields from user input to a results page, the results are sanitised then the current query mysql is processed on the database, as not all check boxes from the search form may have been ticked (fields 4-9) and as I needed to make the WHERE clause take all possible options in to consideration the code below builds the WHERE clause from expected values. This all works perfectly, however I now need to change over to MySQLi connect and use a prepared statement for this query and I would appreciate help as to how I can change the code below to do this. I have done some basic prepared statements  and set up the connection, I am struggling to insert the ? Place holders in to the query and am not sure how to bind the parametres as they are an unknow quantity, I may only have fileds 1- 4 passed to the results page or I may have all 10.

Current Query that works but needs changing to a prepared statement:

$expected = array('field1'   => 'text',

                                                           'field2' => 'text',

                                                           'field3' => 'text',

                                                           'field4' => 'int',

                               'field5'  => 'int',

                               'field6'  => 'int',

                               'field7'  => 'int',

                               'field9'  => 'int',

                               'field10' => 'text');

$query_search = "SELECT DISTINCT col1, col2, col3, col4, col5 FROM table1 ";

// Set a flag to indicate whether the query has a WHERE clause

$where = false;

// Loop through the associatiave array of expected search values

foreach ($expected as $var => $type) {

  if (isset($_GET[$var])) {

    $value = trim(urldecode($_GET[$var]));

    if (!empty($value)) {

      // Check if the value begins with > or <

      // If so, use it as the operator, and extract the value

      if ($value[0] == '>' || $value[0] == '<') {

        $operator = $value[0];

        $value = ltrim(substr($value, 1));

      } elseif (strtolower($type) != 'like') {

        $operator = '=';

      }

      // Check if the WHERE clause has been added yet

      if ($where) {

        $query_search .= ' AND ';

      } else {

        $query_search .= ' WHERE ';

        $where = true;

      }

      // Build the SQL query using the right operator and data type

      $type = strtolower($type);

      switch($type) {

        case 'like':

          $query_search .= "`$var` LIKE " . GetSQLValueString('%' .

$value . '%', "text");

          break;

        case 'int':

        case 'double':

        case 'date':

          $query_search .= "`$var` $operator " .

GetSQLValueString($value, "$type");

          break;

        default:

        $sql .= "`$var` = " . GetSQLValueString($value,

"$type");

      }

    }

  }

}

$query_search .= ' ORDER BY ABS(table.col1), table.col2 ASC'; }

// Here is the start of my prepared statement:

$stmt = $conn->stmt_init();

if ($stmt->prepare($query_search)) {

// not sure how to bind an unknow set of passed values

// also not sure where to put the ? Place holders in my current WHERE clause

$stmt->bind_param('ssss', $_GET['field1'], $_GET['field2'],  $_GET['field3'],);

$stmt->bind_result($col1, $col2, $col3, $col4, $col5);

$stmt->execute();

$stmt->store_result();

$numRows = $stmt->num_rows;

I look forward to any help and advise and thank you in advance.

This topic has been closed for replies.