Help changing to mysqli... prepared statement producing an error message

[tessie]

Help changing to mysqli

 

I am trying to change previously working php pages from mysql to mysqli.

 

I am having problems with an error message.

Basically the page I have just changed produces a set of database results depending on list menu and check box criteria from user input. I run 2 queries, one to find the total number of records, and the other to retrieve the required subset for pagination. All works fine. However if there are no actual results found from the requested input my php script then uses an includes file which basically runs the same 2 queries but with the user criteria narrowed down so that similar options are presented to the user. This is where my problem lies, for some unknown to me reason the second set of queries is producing no results and an error:

'Attempt to read a row while there is no result set associated with the statement'

If I run the second set of queries without running the first set then the second set works fine so I know that it is not the statements themselves but something in my new code. How can the first set of queries be affecting the results of the second set?

Here is the code below which produces the first 2 queries that work, and below it the same code for the second 2 queries (which is from an 'Includes' file and is the same code exactly except the EXPECTED parameters have been narrowed down in the expected array and list of params for binding).

//list of expected possible fields input by user

$expected = array('location' => 'text',
                  'type' => 'text',
                  'beds' => 'text',
                  'price' => 'text',
                  'nbuild' => 'int',
                  'resale' => 'int',
                  'coastal' => 'int',
                  'seaview' => 'int',
                  'rural' => 'int',
                  'golf' => 'int',
                  'ppool' => 'int',
                  'comp' => 'int', 
                  'garden' => 'int',
                  'terrace' => 'int',
                  'aircon' => 'int',
                  'heating' => 'int',
                  'garage' => 'int',
                  'telephone' => 'int',
                  'furnished' => 'int',
                  'internet' => 'int',
                  'dpaid' => 'int',
                  'propid' => 'text');

define('SHOWMAX', 10);
// prepare SQL to get total records

$getTotal = 'SELECT COUNT(*) FROM detailstable JOIN newloctable ON detailstable.location=newloctable.newlocid JOIN typetable ON detailstable.type=typetable.typeid JOIN pricetable ON detailstable.price=pricetable.priceid JOIN bedtable ON detailstable.beds=bedtable.bedid JOIN photossale ON detailstable.detailsid=photossale.propsaleid ';
// 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) {
$getTotal .= ' AND ';
} else {
$getTotal .= ' WHERE ';
$where = true;
}
// Build the SQL query using the right operator and data type
$type = strtolower($type);
switch($type) {
case 'like':
$getTotal .= "`$var` LIKE ? ";
break;
case 'int':
case 'double':
case 'date':
$getTotal .= "`$var` $operator ? ";
break;
default:
$getTotal .= "`$var` = ? ";
}
}
}
}
$getTotal .= ' ORDER BY ABS(detailstable.trueprice), bedtable.number, detailstable.propid ASC'; 
$stmt = $conn->stmt_init();
if ($stmt->prepare($getTotal)) {
$params = array($_GET['location'], $_GET['type'], $_GET['beds'], $_GET['price'], $_GET['nbuild'], $_GET['resale'], $_GET['coastal'], $_GET['seaview'], $_GET['rural'], $_GET['golf'], $_GET['ppool'], $_GET['comp'], $_GET['garden'], $_GET['terrace'],
$_GET['aircon'], $_GET['heating'], $_GET['garage'], $_GET['telephone'], $_GET['furnished'], $_GET['internet'], $_GET['dpaid'], $_GET['propid']);
$params = array_filter($params);
$params = array_values($params);
if (!empty($params)) {
$types = '';
foreach($params as $param) {
// set param type
if (is_string($param)) {
$types .= 's'; // strings
} else if (is_int($param)) {
$types .= 'i'; // integer
} else if (is_float($param)) {
$types .= 'd'; // double
} else {
$types .= 'b'; // default: blob and unknown types
}
}
$bind_names[] = $types;
for ($i=0; $i<count($params);$i++) {
$bind_name = 'bind' . $i; 
$$bind_name = $params[$i]; 
$bind_names[] = &$$bind_name; 
}

call_user_func_array(array(&$stmt,'bind_param'),$bind_names);
}
$stmt->execute();
$stmt->store_result(); 
$stmt->bind_result($total);
$stmt->fetch();
// sort paging
$totalRecords = $total;
$stmt->free_result();
$stmt->close();
} 

// check that there is at least 1 result and if there is do the second part of the search
// if there is no result then I skip this second bit of code
if($totalRecords > '0') {
// check current page
if (isset($_GET['curPage'])) {
$curPage = $_GET['curPage'];
} else {
$curPage = 0;
}
// calculate the start row of the subset
$startRow = $curPage * SHOWMAX; 
$sql = "SELECT DISTINCT detailsid, trueprice, reduced, offers, `desc`, `propid`, `bathrooms`, `location`, `type`, `price`, `beds`, photossale.photo1, newloctable.newloc, typetable.style, bedtable.`number` FROM detailstable JOIN newloctable ON detailstable.location=newloctable.newlocid JOIN typetable ON detailstable.type=typetable.typeid JOIN pricetable ON detailstable.price=pricetable.priceid JOIN bedtable ON detailstable.beds=bedtable.bedid JOIN photossale ON detailstable.detailsid=photossale.propsaleid ";
// 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) {
$sql .= ' AND ';
} else {
$sql .= ' WHERE ';
$where = true;
}
// Build the SQL query using the right operator and data type
$type = strtolower($type);
switch($type) {
case 'like':
$sql .= "`$var` LIKE ? ";
break;
case 'int':
case 'double':
case 'date':
$sql .= "`$var` $operator ? ";
break;
default:
$sql .= "`$var` = ? ";
}
}
}
}
$sql .= " ORDER BY ABS(detailstable.trueprice), bedtable.number, detailstable.propid ASC LIMIT $startRow," . SHOWMAX; 
$stmt = $conn->stmt_init();
if ($stmt->prepare($sql)) {
$nextparams = $params;
if (!empty($nextparams)) {
$nexttypes = '';
foreach($nextparams as $nextparam) {
// set param type
if (is_string($nextparam)) {
$nexttypes .= 's'; // strings
} else if (is_int($nextparam)) {
$nexttypes .= 'i'; // integer
} else if (is_float($nextparam)) {
$nexttypes .= 'd'; // double
} else {
$nexttypes .= 'b'; // default: blob and unknown types
}
}
$newbind_names[] = $nexttypes;
for ($i=0; $i<count($nextparams);$i++) {
$newbind_name = 'bind' . $i; 
$$newbind_name = $nextparams[$i]; 
$newbind_names[] = &$$newbind_name; 
}

call_user_func_array(array(&$stmt,'bind_param'),$newbind_names);
}
$stmt->execute();
$stmt->store_result(); 
$stmt->bind_result($detailsid, $trueprice, $reduced, $offers, $desc, $propid, $bathrooms, $location, $type, $price, $beds, $photo1, $newloc, $style, $bednumber);
$numRows = $stmt->num_rows;
}
}

If a result was found in the part that checks the numbers of records then the second query is run and displayed using : while ($stmt->fetch()) { ...... to display the results and then I use $stmt->free_result();

$stmt->close();

All fine no problems.

If the first query did not find a result then the second query is skipped and instead the script then uses a php includes file with exactly the same script as above except the expected params are narrowed as below: THIS IS WHEN I GET NO RESULTS – when I do expect to get a result) AND AN ERROR OF :

Attempt to read a row while there is no result set associated with the statement.

Yet if I run the includes file and skip the whole of the above code the code in the includes file find the result no problem. It is like running the fisrt query is on the database is preventing the one in the includes file from working correctly.  Anyway code below from the includes file:

$expected = array('location' => 'text',
                  'type' => 'text',
                  'beds' => 'text',
                  'price' => 'text',
                  'dpaid' => 'int',
                  'propid' => 'text');

define('SHOWMAX', 10);
// prepare SQL to get total records

$getTotal = 'SELECT COUNT(*) FROM detailstable JOIN newloctable ON detailstable.location=newloctable.newlocid JOIN typetable ON detailstable.type=typetable.typeid JOIN pricetable ON detailstable.price=pricetable.priceid JOIN bedtable ON detailstable.beds=bedtable.bedid JOIN photossale ON detailstable.detailsid=photossale.propsaleid ';
// 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) {
$getTotal .= ' AND ';
} else {
$getTotal .= ' WHERE ';
$where = true;
}
// Build the SQL query using the right operator and data type
$type = strtolower($type);
switch($type) {
case 'like':
$getTotal .= "`$var` LIKE ? ";
break;
case 'int':
case 'double':
case 'date':
$getTotal .= "`$var` $operator ? ";
break;
default:
$getTotal .= "`$var` = ? ";
}
}
}
}
$getTotal .= ' ORDER BY ABS(detailstable.trueprice), bedtable.number, detailstable.propid ASC'; 
$stmt = $conn->stmt_init();
if ($stmt->prepare($getTotal)) {
$params = array($_GET['location'], $_GET['type'], $_GET['beds'], $_GET['price'], $_GET['dpaid'], $_GET['propid']);
$params = array_filter($params);
$params = array_values($params);
if (!empty($params)) {
$types = '';
foreach($params as $param) {
// set param type
if (is_string($param)) {
$types .= 's'; // strings
} else if (is_int($param)) {
$types .= 'i'; // integer
} else if (is_float($param)) {
$types .= 'd'; // double
} else {
$types .= 'b'; // default: blob and unknown types
}
}
$bind_names[] = $types;
for ($i=0; $i<count($params);$i++) {
$bind_name = 'bind' . $i; 
$$bind_name = $params[$i]; 
$bind_names[] = &$$bind_name; 
}

call_user_func_array(array(&$stmt,'bind_param'),$bind_names);
}
$stmt->execute();
$stmt->store_result(); 
$stmt->bind_result($total);
$stmt->fetch();
// sort paging
$totalRecords2 = $total;
echo $stmt->error;
$stmt->free_result();
$stmt->close();
// there is a result get the subset
if($totalRecords2 > '0') {
// check current page
if (isset($_GET['curPage'])) {
$curPage = $_GET['curPage'];
} else {
$curPage = 0;
}
// calculate the start row of the subset
$startRow = $curPage * SHOWMAX; 
$sql = "SELECT DISTINCT detailsid, trueprice, reduced, offers, `desc`, `propid`, `bathrooms`, `location`, `type`, `price`, `beds`, photossale.photo1, newloctable.newloc, typetable.style, bedtable.`number` FROM detailstable JOIN newloctable ON detailstable.location=newloctable.newlocid JOIN typetable ON detailstable.type=typetable.typeid JOIN pricetable ON detailstable.price=pricetable.priceid JOIN bedtable ON detailstable.beds=bedtable.bedid JOIN photossale ON detailstable.detailsid=photossale.propsaleid ";
// 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) {
$sql .= ' AND ';
} else {
$sql .= ' WHERE ';
$where = true;
}
// Build the SQL query using the right operator and data type
$type = strtolower($type);
switch($type) {
case 'like':
$sql .= "`$var` LIKE ? ";
break;
case 'int':
case 'double':
case 'date':
$sql .= "`$var` $operator ? ";
break;
default:
$sql .= "`$var` = ? ";
}
}
}
}
$sql .= " ORDER BY ABS(detailstable.trueprice), bedtable.number, detailstable.propid ASC LIMIT $startRow," . SHOWMAX; 
$stmt = $conn->stmt_init();
if ($stmt->prepare($sql)) {
$nextparams = $params;
if (!empty($nextparams)) {
$nexttypes = '';
foreach($nextparams as $nextparam) {
// set param type
if (is_string($nextparam)) {
$nexttypes .= 's'; // strings
} else if (is_int($nextparam)) {
$nexttypes .= 'i'; // integer
} else if (is_float($nextparam)) {
$nexttypes .= 'd'; // double
} else {
$nexttypes .= 'b'; // default: blob and unknown types
}
}
$newbind_names[] = $nexttypes;
for ($i=0; $i<count($nextparams);$i++) {
$newbind_name = 'bind' . $i; 
$$newbind_name = $nextparams[$i]; 
$newbind_names[] = &$$newbind_name; 
}

call_user_func_array(array(&$stmt,'bind_param'),$newbind_names);
}
$stmt->execute();
$stmt->store_result(); 
$stmt->bind_result($detailsid, $trueprice, $reduced, $offers, $desc, $propid, $bathrooms, $location, $type, $price, $beds, $photo1, $newloc, $style, $bednumber);
$numRows = $stmt->num_rows;
} 
}
}

Again here I would then display the results before closing and freeing the stmt or display a message to say there were no results found from the criteria. But instead I get the error message and echoing the value of $totalRecords2 is empty.

 

I have been pulling my hair out for days and days over this and as it is one of the first pages I am converting from mysql to mysqli I wonder if I am missing something very obvious to someone with more experience with the code.

I will be very grateful for any help, thank you in advance.

[mac_gyver]

this is indicative of an initialization problem. most likely your array used for the dynamic binding is being added to, rather than starting over, by the set of queries in the included file and the execute() statement is failing (you are not checking if the execute() actually ran) because the number of parameters in the query no longer matches the number of bind parameters.

 

1) you should ALWAYS check if a statement has failed or not before trying to use the result from that statement, in this case the execute() statement.

 

2) you should ALWAYS declare/initialize array variables before populating them in a loop.

 

---

 

also, you have a bunch of repetitive code, taking up your time typing or copy/pasting/changing it and frankly reducing the chance of someone in a help forum even looking at it.

 

each pair of related queries, the COUNT() query and the data retrieval query are very similar, but you have repeated line after line of code to build both of them, then repeated the code again in the included file. other than the COUNT() term or the list of SELECTed fields and the LIMIT clause they are the same (actually the count query doesn't need the ORDER BY term.)

 

build the parts of the query separately, once, then form the two queries using the appropriate parts. some of these parts, like the list of selected fields, the FROM ... JOIN term, and even the LIMIT x,y term are the same for the main queries and the included queries and could be reused.

 

to build the COUNT() query, you would just use -

 

$query = "SELECT COUNT(*) $from_term $where_term";

 

to build the data retrieval query -

 

$query = "SELECT $select_term $from_term $where_term $order_by_term $limit_term";

 

next, the two queries in the included file only differs from these two queries in the where term. the only code you need to run again is the code using your $expected array to build the where term. this code should be in a function that accepts the appropriate $expected array as an input parameter and returns the produced where term.

 

lastly, you are repeating the code that actually prepares and runs the query at least 4 times. you should move toward making a function or an OOP class (you can actually extend the mysqli class to add a 'prepared_query' function.)

 

this code would accept the query statement, the input bind type list (the 'ssis...'), and an array of input variables to bind), prepare the query, bind the input variables, execute the query, bind any result variables, fetch and return the data (all with some error checking logic for each step that can fail.) you would just call this function/class method each time you need to run a query, rather than repeating the code over an over.

 

by moving code into functions/classes and reusing the common parts of the queries, your main logic would be greatly simplified.

Edited November 17, 2013 by mac_gyver

[mac_gyver]

your main logic could/should look like this pseudo code/outline -

$expected = array('location' => 'text',
                  'type' => 'text',
                  'beds' => 'text',
                  'price' => 'text',
                  'nbuild' => 'int',
                  'resale' => 'int',
                  'coastal' => 'int',
                  'seaview' => 'int',
                  'rural' => 'int',
                  'golf' => 'int',
                  'ppool' => 'int',
                  'comp' => 'int',
                  'garden' => 'int',
                  'terrace' => 'int',
                  'aircon' => 'int',
                  'heating' => 'int',
                  'garage' => 'int',
                  'telephone' => 'int',
                  'furnished' => 'int',
                  'internet' => 'int',
                  'dpaid' => 'int',
                  'propid' => 'text');

define('SHOWMAX', 10);

// common parts of the query
$from_term = "FROM detailstable JOIN newloctable ON detailstable.location=newloctable.newlocid JOIN typetable ON detailstable.type=typetable.typeid JOIN pricetable ON detailstable.price=pricetable.priceid JOIN bedtable ON detailstable.beds=bedtable.bedid JOIN photossale ON detailstable.detailsid=photossale.propsaleid";
$select_term = "DISTINCT detailsid, trueprice, reduced, offers, `desc`, `propid`, `bathrooms`, `location`, `type`, `price`, `beds`, photossale.photo1, newloctable.newloc, typetable.style, bedtable.`number`";
$order_by_term = "ORDER BY ABS(detailstable.trueprice), bedtable.number, detailstable.propid ASC";

// build the specific where term
$where_term = build_where_term($expected); // assuming your code to do this is in a function

// first try the specific where term
$query = "SELECT COUNT(*) as total $from_term $where_term";

// you would need to dynamically build the $types and $input_data to go with the $query statement here...

// run the query and use the result (this count query will return exactly one row)
$result = $conn->prepared_query($query,$types,$input_data); // assuming a class method to do this

// get the count of matching rows
$totalRecords = $result[0]['total'];

if($totalRecords == 0) {
    // the specific query did not match any rows, use the general query
        
    $expected = array('location' => 'text',
      'type' => 'text',
      'beds' => 'text',
      'price' => 'text',
      'dpaid' => 'int',
      'propid' => 'text');

    // build the general where term
    $where_term = build_where_term($expected); // assuming your code to do this is in a function
    
    $query = "SELECT COUNT(*) as total $from_term $where_term";
    
    // you would need to dynamically build the $types and $input_data to go with the $query statement here...
    
    // run the query and use the result (this count query will return exactly one row)
    $result = $conn->prepared_query($query,$types,$input_data); // assuming a class method to do this

    // get the count of matching rows
    $totalRecords = $result[0]['total'];

}

// at this point, $totalRecords indicates if there are any rows, from either the first specific query or the second general query
// if there are any records, the $where_term is the appropriate value to use in the data retrieval query

if($totalRecords == 0) {
    // there are no results to display
    
    echo 'sorry, no match for the input values';
    
} else {
    // there are results to display

    
    // produce the limit term
    $startRow = $curPage * SHOWMAX;
    $limit_term = "LIMIT $startRow," . SHOWMAX;
    
    // this single data retrieval query will receive the appropriate $where_term from the above logic
    $query = "SELECT $select_term $from_term $where_term $order_by_term $limit_term";
    
    // you would need to dynamically build the $types and $input_data to go with the $query statement here...
    
    // run the query and use the result (this query can return any number of rows)
    $result = $conn->prepared_query($query,$types,$input_data); // assuming a class method to do this

    // use result from data retrieval query
    
}

[tessie]

Thank you so much for your reply and help, and for taking the time to actually read through my much duplicated code.

 

Firstly regarding the error, by applying a check to the execute statement and echoing the results of the arrays I can see that yes the dynamic binding is being added to as you said. The code I used was:

if (!$stmt->execute()) {
print_r($params2);
print_r($bind_names);
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}

I also understand the logic in checking to see if a statement has failed or not before trying to use the result from that statement, so again thank you for that. This is my first attempt to change to prepared statements and I have much to learn.

 

Secondly, re ' you should ALWAYS declare/initialize array variables before populating them in a loop' are you referring to the expected variables or the variables to be bound? As I though I did declare the expected variables as parameters but I must be wrong or you would not have written the above, so I am sorry I am not quite understanding. (I can get my desired result from changing the dynamic code that binds the parameters to $bind_names2, that way the second COUNT search does produce a result rather than an error but I would rather understand fully than just carry on blindly.

 

Thirdly, and most importantly:

As I look through your revised code logic to remove all my very overly duplicated code I can see clearly your method, however having so little experience of classes and functions I am struggling to fully understand this to be able to implement it, and I would really like to, it would certainly help me on my other of my pages too.

 

This is the first part I am not fully understanding:

// build the specific where term
$where_term = build_where_term($expected); // assuming your code to do this is in a function

// first try the specific where term
$query = "SELECT COUNT(*) as total $from_term $where_term";

I am not quite sure how to apply my existing $where_term to an actual function that I can call, specifically that this function accepts the appropriated $expected as an input parameter. I am sure it is not quite as simple as putting my current where_term (below) within a function?

Function build_where_term() {

// 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) {
$sql .= ' AND ';
} else {
$sql .= ' WHERE ';
$where = true;
}
// Build the SQL query using the right operator and data type
$type = strtolower($type);
switch($type) {
case 'like':
$sql .= "`$var` LIKE ? ";
break;
case 'int':
case 'double':
case 'date':
$sql .= "`$var` $operator ? ";
break;
default:
$sql .= "`$var` = ? ";
}
}
}
}

}

This is the second part I don't understand:// you would need to dynamically build the $types and $input_data to go with the $query statement here...is that not part of the $build_where_term function?Which finally brings me on to: 

// run the query and use the result 
$result = $conn->prepared_query($query,$types,$input_data); // assuming a class method to do this OR ADD A prepared_query FUNCTION

Again I am not sure how to go about creating a function that would accept the query statement, the input bind type list and an array of input variables to bind, prepare the query, bind the input variables, execute the query, bind any result variables, fetch and return the data (all with some error checking logic for each step that can fail.) So that I could call this function/class method each time I need to run the query.

I appreciate I am asking a lot of help here especially after your initial response, I do hope that you have the time to help me further.

Thank you and best regards.

[mac_gyver]

the array causing the initial(ization) problem is $bind_names[]. it is loaded with values in the main code, then added to in the included file. you need to add $bind_names = array(); before the usage in the included file.

 

as a side note, if/once this code is inside a function/method, the problem would not exist because each call of the function/method would recreate the array/destroy it when the function/method call ends and the database error would never have occurred.

[mac_gyver]

in case you revisit this thread, i looked at your actual code more and there's one bug and some code that isn't doing what you think.

 

the bug:

 

if someone does submit a value with a < or a > in front of it, you have logic to check for that and produce the $value without the < or > character. that $value is what you need to supply to the actual query. your current code uses the raw $_GET value and it still has the < or > character and if the query runs isn't going to produce the result you expect.

 

the loop where you are checking for the < or > characters is where you should store the $value into the $params array. this will also eliminate the need for the lines of code later that are setting $params to the array of $_GET variables and using array_filter() and array_values() on it.

 

 

the code not doing what you think:

 

by definition all $_GET variables are a string data type, no matter what the value in them is. the code using is_string(), is_int(), and is_float() is checking the type of variable, not what's in it and if you look at your $types value it will always be 'ssssss'. this will actually produce a query that works, but is not what you intend. the same loop mentioned above could be used to build the $types string, just add statements to the switch/case logic for each possible choice to add the appropriate type character to the $types variable. this will also eliminate the need for the foreach() loop building the $types value later in the code.

 

 

lastly, variable variables are messy and usually not needed. the following -

for ($i=0; $i<count($params);$i++) {
$bind_name = 'bind' . $i;
$bind_name = $params[$i];
$bind_names[] = &$bind_name; 
}

can be replaced by -

        for ($i=0; $i<count($params);$i++) {
            $bind_names[] = &$params[$i]; // use the $params data array directly
        }