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.
AdChoices