I was working with another coder last night trying to resolve an issue. She uses mysqli and standard mysqli queries work fine on both her local system and hosted site. Using prepared statements and binding input works on her local machine but NOT the hosted site. I tested her code and it works just fine… Is it possible that mysqli prepare and bind_param are not activated but mysqli is?
The mysqli library is implemented using one file so that doesn’t sound likely unless they have either modified the file to remove security or overridden that security in some other way.
Can you post the prepare statement that doesn’t work.
Two things look like possible areas to look into
http://php.net/manual/en/mysqli.quickstart.prepared-statements.php
Prepared statements are using the so called binary protocol.
.
Prepared statements return unbuffered result sets by default. The results of the statement are not implicitly fetched and transferred from the server to the client for client-side buffering. The result set takes server resources until all results have been fetched by the client. Thus it is recommended to consume results timely. If a client fails to fetch all results or the client closes the statement before having fetched all data, the data has to be fetched implicitly by mysqli.
My guess it’s buffering involved here
Thanks guys for getting back to me.
$conn = new mysqli($servername, $username, $password, $dbname);
$sqlevents = "SELECT * FROM events
WHERE event_date BETWEEN ? AND ?";
$query = $conn->prepare($sqlevents);
$query->bind_param("ss", $first_day, $last_day);
$query->execute();
$result = $query->get_result();
while ($row = $result->fetch_assoc()){
//etc
These statements work on our local machines. She has verified DB table is on host site and run query in phpmyadmin and gets expected results.
Note the original query had CAST as DATE but we removed for testing.
"SELECT * FROM events
WHERE event_date BETWEEN CAST(? AS DATE) AND
CAST(? AS DATE)";
Also note she has regular mysqli queries like this that work on hosted site. She’s converting to binding input.
$ses_sql="select username from tbl_user where username='$user_check'";
$result = $conn->query($ses_sql);
$row=mysqli_fetch_assoc($result);
Are you guys getting any errors? If you are, are the errors saying something like this
If it is, you need to wrap your get_result and while loop around an if statement.
As the sample query you show is selecting based on dates, could there be differences in the date formats between your test servers and the hosted ones? Or do other queries that don’t use dates have the same issue?
As she lives on the other side or the world there is a big time delay in getting answers so sorry for not replying sooner.
We added try catch to a sample test page and received no errors or results. We also removed the bind_param and WHERE section and just used prepare and got no errors or results.
Does NOT work
<?php
include('includes/connection.php');
mysqli_report(MYSQLI_REPORT_STRICT);
error_reporting( E_ALL );
try {
$events_array = array();
$sqlevents = "SELECT * FROM events";
$query = $conn->prepare($sqlevents);
$query->execute();
$result = $query->get_result();
while ($row = $result->fetch_assoc()){
$events_array[$row['event_date']][$row['event_id']] = $row;
}
} catch (mysqli_sql_exception $e) {
throw $e;
}
?>
This does work.
$events_array = array();
$sqlevents = "SELECT * FROM events";
$query = $conn->query($sqlevents);
while ($row = $query ->fetch_assoc()){
$events_array[$row['event_date']][$row['event_id']] = $row;
}
She indicated that she is going to forget about using prepared statements and move forward with other projects, Still it would be nice to know what the issue is.
I do not know what version of php she has on her hosted site. That might be the problem.
Working for me. Both live and local.
$conn = new mysqli($servername, $username, $password, $dbname);
$id = 1;
$sqlevents = "SELECT id, name, email FROM users WHERE id = ?";
$query = $conn->prepare($sqlevents);
$query->bind_param("i", $id);
$query->execute();
$query->store_result();
if($query->num_rows) {
$query = $conn->prepare("SELECT id, name, email FROM users WHERE id = ?");
$query->bind_param("i", $id);
$query->execute();
$result = $query->get_result();
while($row = $result->fetch_assoc()) {
print_r($row);
}
} else {
print('What you are looking for does not exist in the database');
}
Although get_result is only for 5.3 and higher (Link), I have an alternative suggestion.
$conn = new mysqli($servername, $username, $password, $dbname);
$id = 1;
$sqlevents = "SELECT id, name, email FROM users WHERE id = ?";
$query = $conn->prepare($sqlevents);
$query->bind_param("i", $id);
$query->execute();
$query->store_result();
if($query->num_rows) {
$query->bind_result($id, $name, $email);
while($query->fetch()) {
$row = array('id' => $id, 'name' => $name, 'email' => $email);
print_r($row);
}
} else {
print('What you are looking for does not exist in the database');
}
Just use bind_result with $query->fetch and then throw the bind variables together in an array along with their respective keys. It’s just an alternative suggestion. Also, this is just an example. I’m not sure if it’ll work with your desired SQL string.
EDIT: Also, what I hate about MySQLi_* is that if you want to use get_result, you’ll have to actually re-invent the wheels and do a 2nd query for get_result otherwise, you’ll get an error complaining about the fetch_assoc. I’ve also tested the code I posted without the store_result and with the try-catch method. It works, but I believe store_result is needed because it stores the result from the previous query, but then for the 2nd query, you need to actually get the result which this could actually be redundant. So I think the alternative suggestion could be useful in this case.
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.