I need to dynamically build up the SQL statement and the parameters based on user input. The length of the sql statement and the number of parameters changes based on user input. I am trying to use this tutorial and apply it to my code. Here is the code:
$query = "SELECT p.*, s.*
FROM product p
INNER JOIN product_shop ps
ON ps.p_id = p.p_id
INNER JOIN shop s
ON s.s_id = ps.s_id
WHERE s.country = ?";
$a_params[] = $place['country'];
$a_param_type[] = "s";
// prepare and bind
$param_type = '';
foreach ($place as $key => $value) {
if ($key === "country") {
continue;
}
$query .= " and s.$key = ?";
$a_params[] = $value;
$a_param_type[] = "s";
}
/* Prepare statement */
$stmt = $conn->prepare($query);
if($stmt === false) {
trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->errno . ' ' . $conn->error, E_USER_ERROR);
}
$a_params[] = $a_param_type;
/* use call_user_func_array, as $stmt->bind_param('s', $param); does not accept params array */
call_user_func_array(array($stmt, 'bind_param'), $a_params);
/* Execute statement */
$stmt->execute();
$meta = $stmt->result_metadata();
I know $place['country']
will always be populated. The sql statement is correct. It is:
"SELECT p.*, s.*
FROM product p
INNER JOIN product_shop ps
ON ps.p_id = p.p_id
INNER JOIN shop s
ON s.s_id = ps.s_id
WHERE s.country = ? and s.suburb = ? and s.city = ? and s.province = ?"
Don't mind the " " chars, they have no effect on the sql statement.
In:
call_user_func_array(array($stmt, 'bind_param'), $a_params);
the value of $a_params
is:
0:"New Zealand"
1:"Grey Lynn"
2:"Auckland"
3:"Auckland"
4:array(4)
0:"s"
1:"s"
2:"s"
3:"s"
In:
$meta = $stmt->result_metadata();
the value of $meta becomes:
current_field:0
field_count:13
lengths:null
num_rows:0
type:1
Meaning that no rows were selected from the database. I have executed this sql on the database manually and it returns rows. What is wrong with my code, that makes it return no rows from the database?
EDIT: I saw that this answer says to put the "ssss" at the start of the $params
so I did that and got this error in the $stmt
object:
errno:2031
error:"No data supplied for parameters in prepared statement"
error_list:array(1)
propertyNode.hasAttribute is not a function