0

I have two databases. Both are MySQL 8.x. Both have the exact same database schema, database name, database user, database password, and charset. The only difference is they are on different servers.

If I run my PHP code using the database on server 1, it works fine. If I run my PHP code using the database on server 2, every mysqli_query returns false.

I can see my database connection to server 2 is fine (if I run mysqli_stat on the mysqli object it outputs what I would expect). If I connect to server 2 via the command line and manually run the SQL queries which are failing, they return normal results as expected.

Everything I am doing is standard:

Connection:

$db = mysqli_connect($mysql_hostname, $mysql_user, $mysql_password, $mysql_database, $mysql_port) or die("Could not connect database");
mysqli_set_charset($db, $mysql_charset);
if (mysqli_connect_errno()) {
    throw new Exception("Failed to connect to MySQL: " . mysqli_connect_error());
}

Example query:

global $db;

$query = "SELECT * FROM users";
$result = mysqli_query($db,$query);

$array = array();
if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        $array[] = $row;
    }
}

I cannot figure this out.

Can any of you think of any reason mysqli_query would fail on a database it can connect to and using a query which is valid?

Server 1 is on my local machine, server 2 is a managed digital ocean database.

Stiofán
  • 453
  • 5
  • 17
  • 1
    No need to "think" or, rather, guess about reasons. This is not how programming goes. there is always the actual reason. All you need is to ask your database – Your Common Sense Feb 06 '22 at 15:30
  • There could be lots of reasons. Just ask it to tell you the reason and then you'll know. – ADyson Feb 06 '22 at 15:40
  • 2
    If mysqli_query() returns false, then you can find out why the query failed by looking at the result of [mysqli_error($db)](https://www.php.net/mysqli_error). Or you can call `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` (*before* mysqli_connect()) so that mysqli_query() will throw an exception with an error message if the query fails. – rickdenhaan Feb 06 '22 at 15:52
  • @ADyson replying with "ask your database" (as you and YourCommonSense said) isn't constructive help or how normal people communicate. You could learn a lot from studying how rickdenhaan communicates. Please try to help people instead of being condescending. – Stiofán Feb 07 '22 at 08:59
  • But at the same moment, the question was closed with the link in the blue box above which tells you exactly how to solve it. Or you could use some initiative with the hints given and type something like "how to ask mysqli for error" or similar into google, and also find the answer very quickly. Do you realise how many questions of the same nature are posted on here every single day, and get closed as duplicates of the same thing, because people don't seem to just use a search engine or read the mysqli manual? It's not surprising people get a bit frustrated and tired of repeating themselves. – ADyson Feb 07 '22 at 09:46
  • To be clear, this site is not a helpdesk. Think of it more like an encyclopaedia of useful snippets of information. If a solution to something already exists, it doesn't need saying again. See also [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/questions/261592/how-much-research-effort-is-expected-of-stack-overflow-users) . I'm sorry if you felt that the remarks were somehow condescending, but that wasn't really the intention, it was just stating some facts: there is a way to find out what the error is, and it's already easy to discover that way – ADyson Feb 07 '22 at 09:48
  • This site is for asking questions and getting answers. If you don't want to be helpful, please keep your condescending comments to yourself. Responses such as "Just ask [the database] to tell you and then you'll know" is not only unhelpful, it's smarmy. This is my last comment. – Stiofán Feb 07 '22 at 14:58
  • It's for asking questions, but not for asking the same questions over and over. Again - this got an answer, in the nominated duplicate in the blue box. The question was unnecessary because the answer already exists and can be found with a trivial google search, or by reading the mysqli manual. It wasn't necessary to provide the answer again, via the answer box or the comments, because it already exists. The comments it did receive were intended to be hints, no more than that. The community is not here to do _all_ the thinking (or googling) for everyone. We'll _help_ with new stuff, not repeats – ADyson Feb 07 '22 at 15:05
  • If you think the comments are inappropriate please go ahead and flag them, but they really are just stating the facts of the situation - if you ask your code what the error is, it'll tell you. If you don't, it won't. _How_ to ask it is already well documented and - I'll labour the point one more time for 100% clarity - doesn't need explaining again. People are very ready to take comments etc too personally when actually others are just articulating the situation as they see it. It's the quality of the _question_ which is judged, not the person asking it. Goodbye, and good luck :-). – ADyson Feb 07 '22 at 15:07

0 Answers0