Hi there,
I just did a simple update to the latest version of mysql 8 and a select statement i have stopped retrieving results. The same statement worked on the previous version of mysql 8.
When i run the statement in phpmyadmin everything is retrived properly
Any suggestions are appreciated.
Well, you show two queries in your THIRD-PARTY posted display! Please show us HERE which one is giving you the problem. By the way, did you display the query to see if it is being created correctly? Sometimes the creation of a query is causing the error. Perhaps your $where is not being placed into the query correctly. How did you debug this so far?
the query that is giving me problems is the select one
if ($count){
$query = "SELECT id, search_text, category, sticky, name, vip, times_completed, size, added, type, anonymous, descr, numfiles, filename, owner FROM downloads $where $orderby {$pager['limit']}";
$res = mysqli_query($GLOBALS["___mysqli_ston"], $query) or sqlerr(__FILE__, __LINE__);
}else{
unset($query);
}but this depends on the other one places above
and yes ive turn to believe myself that the problem could be with the $where variable
ive did a var_dump and with that var_dump ive run it in phpmyadmin and all results displayed properly
but for some reason it doesnt want to display on the page itself
well…if the var_dump display statement works in phpmyadmin doesnt that mean the $where variable is correct?
Try debugging the query like this:
Just a way to debug the problem by displaying the actual data used in your query. I have never used a GLOBAL inside a query before. Seems like an odd way to do it. Normally, your connection would just be assign to a standard variable not a global. At least this way you can see what is in your connection string and your live query. Then, that might tell you what the problem really is.
Also, you should consider moving up to PDO as it is more secure than MySQLi.
i saw the actual data used in the query and it is the correct one.
also i saw the actual data of the $where variable and its still the correct one
but for some reason nothing is displayed on the page despite the fact if i use the actual data from the query it is being displayed in the database without issues
edit:
using the die() method you requested…i get Fatal error: Uncaught Error: Object of class mysqli could not be converted to string from that line
So, my guess is that it is most likely your $GLOBALS array is not correct.
If the die is not showing that value, it must be wrong. Can you look where you create that value and see if it is stored correctly?
Three underscores and mysqli_ston is that correctly created?
Or change the DIE to display $___mysqli_ston instead of the $GLOBAL version…
changed it… i still get the same
ive changed it like this
die(“connection:< br>< br>query:< br>” . $query);
and now i get the exact same query data i got when i used var_dump
and if i use the query data in phpmyadmin i can retrive the results properly
Well, have you tested your connection otherwise? If the query runs on the phpMyADMIN page for the database under SQL, then it is not a query issue. It must be the connection that is not working. Have you tested just “SELECT * FROM downloads” and see if it is getting records? If the query works directly, it should work as-is on the webpage if the connection is correct. I have to leave, will check in on this thread when I get back. Good luck.
yes yes… in phpmyadmin it does get the results properly
@ErnieAlex, this code is the result of using a mysql_ to mysqli_ converter. The global variable is a mysqli object and trying to output it as a string inside a die() statement is in itself what is producing that error. You should be able to print_r() or var_dump() it. That the code is getting past the first query without die’ing, the connection is probably okay.
Was only the database version upgraded? There’s usually a update (routine) to run to check/update all the tables to insure they are compatible with the database changes. Do you know if this was ran?
Is the code after the point where the second query is being executed at actually running? What does adding the following after the $res = … line show -
print_r($res);
// i'm pretty sure the above will contain the number of rows matched by the query (haven't used mysqli lately), if not add the following line too -
echo $res->num_rows;If the above debugging shows a result set from the query, then the problem is in the code trying to display the results (most likely an issue with a data-type comparison in the code.) If the above shows an empty result set, the problem is most likely with the LIMIT clause, in that it looks okay, so that when you copy/paste the sql statement into phpmyadmin it works, but when used in the the php code, it doesn’t correspond to any range of rows.
with the print_r i get mysqli_result Object ( [current_field] => 0 [field_count] => 17 [lengths] => [num_rows] => 50 [type] => 0 ) 50
and at the 1st query where the $where variable is defined i get
mysqli_result Object ( [current_field] => 0 [field_count] => 1 [lengths] => [num_rows] => 1 [type] => 0 ) 1
yes only the mysql was updated and i dont know if that check was ran
i just did the update as i usually do from command line
So, the output you are getting says there’s 50 rows in the result set. The problem IS in the code trying to use the result from that query to produce the output on the web page.
hmm.and how should i change it ?
the output result is based on how each member sets his output for that page
the default is 50 results
You would need to post the code for anyone here to have a chance at helping with what’s wrong with it. I did give a likely cause -
the code was posted in the pastebin above in the 1st post
$where = (is_countable($wherea) ? count($wherea) : 0) > 0 ? 'WHERE '.implode(' AND ', $wherea) : '';
$res = mysqli_query($GLOBALS["___mysqli_ston"], "SELECT COUNT(id) FROM downloads $where AND category NOT IN (45,46,57)") or sqlerr(__FILE__, __LINE__);
$row = mysqli_fetch_row($res);
$count = (int)$row[0];
$downloadperpage = $CURUSER["downloadperpage"];
if (!$downloadperpage) $downloadperpage = 50;
$pager = pager($downloadperpage, $count, "downloads.php?" . $addparam);
if ($count){
$query = "SELECT id, search_text, category, sticky, name, vip, times_completed, size, added, type, anonymous, descr, numfiles, filename, owner FROM downloads $where $orderby {$pager['limit']}";
$res = mysqli_query($GLOBALS["___mysqli_ston"], $query) or sqlerr(__FILE__, __LINE__);
}else{
unset($query);
}
data-type comparison means something with null and not null?
i’ve read some things about that recently
That’s not the code trying to use the result from the query to produce the output on the page. I give up. 
those 2 are the only select statements from the page.
i cant give you something which it doesnt exist
edit: i’ve put the exact same code, exact same database data on another server with the exact same mysql and php version and it works
everything works
what the…