PHP Mysqli returns no rows, while manual SQL query

问题:

I have a block of PHP code which is called through AJAX to insert a value into a table, then return a list of matching values from that table. The insert query runs just fine, but the select query returns nothing. If I go into phpmyadmin and manually run the select query, I get the expected result set, but in the PHP code, fetch_assoc() returns null. I've confirmed that all variables are being passed correctly, but the code never enters the while loop because there are no results in the set. Below is the set of relevant code:

if (isset($_POST['action']) && $_POST['action'] == "link" && !empty($_POST['link_id']) && !empty($_POST['cat_id'])) {
    require_once(realpath(dirname(__FILE__)) . "/../config/config.php");
    $link_id = (int)$_POST['link_id'];
    $category_id = (int)$_POST['cat_id'];
    $query = $conn->query("INSERT INTO links_to_categories (link_id, category_id) VALUES ({$link_id}, {$category_id})");
    if ($query) {
        $cats = $conn->query("SELECT * FROM links_to_categories lc LEFT JOIN categories c ON lc.category_id = c.category_id WHERE lc.link_id = {$link_id}");
        while ($cat = $cats->fetch_assoc()) {
            echo "{$cat['category_name']} (<a href=\"#\" onclick=\"unlinkCategory({$link_id}, {$category_id});return false;\">x</a>)<br>";
        }
    }
    die();
}

This and one other function (which is nearly identical but does a DELETE instead of INSERT) are the only ones in my code that have this problem. I have a dozen other queries that are working properly, including the same SELECT query in a different part of the code which runs fine, so this is very puzzling.

回答1:

-- Is it true that Bill Gates hit a jackpot in a lottery?
-- Yes, quite true. Only it was not Bill Gates but John Doe, not in a lottery but black jack, not jackpot but $10 and not won but lost.

This "nearly identical" spoils the whole thing. To blame mysqli, you have to run exactly the same query without any assumptions.

There is actually whole PHP program to build your query dynamically. There are surely could be errors in that program. So, if your query runs not the way you expect, you have to verify the result of the program, namely - the very query itself.

Did you ever had an idea to echo the resulting query out, instead of running it? Just to be sure that it is indeed the query you expect? If not - it's time to do so.

And of course it should be exact set of queries, not different from ones you run manually.

If you are indeed running exactly the same static query from mysqli, and result is different from whatever else client - then you are connecting to different servers with these clients. While mysqli will never interfere with your queries. You can be positively sure of that.