The SQL works fine in MySQL and returns plenty results, however when run from PHP it returns no results
Why would that happen
There are no errors either.
$query = "SELECT ms.message, count(m.messageid), sum(p.received) FROM messagestore ms LEFT JOIN messages m ON ms.messagecode = m.messagecode INNER JOIN profiles p ON p.profile = m.profile WHERE m.state = 5 LIMIT 10";
$stmt = mysqli_stmt_prepare($sql_connect, $query);
You have only posted a part of the code. We need to see the part that is supposed to actually access the database to retrieve the results in order to figure out what is happening.
The returned result resource should be passed to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data.
So, to retrieve the results, you can use a while loop as below:
while ($row = mysql_fetch_assoc($result)) {
# code...
}
As Felgall said earlier, can you post the rest of the SQL-related code? I don’t use mysqli myself, but reading up on this my concern would be $sql_connect in there - specifically do you call mysqli_stmt_init() at some point? I’m only guessing based on what I’d use for variable names though. Without any parameters, is there much point using a prepared statement here?
I don’t use mysqli but as mentioned, how you connect would make a difference in the way you query. Maybe this query style would work for your connection type.
$sql = "SELECT ms.message, count(m.messageid), sum(p.received) FROM messagestore ms LEFT JOIN messages m ON ms.messagecode = m.messagecode INNER JOIN profiles p ON p.profile = m.profile WHERE m.state = 5 LIMIT 10";
$query = $sql_connect->prepare($sql);
$query->execute();
$result = $query->get_result();
while ($row = $result->fetch_assoc()){
//etc
}