0

Can anyone tell me why the first query echoes out results, while the second query returns null in the result set?

$sql1 = '
    SELECT
        SQL_CALC_FOUND_ROWS
        *
    FROM
        table1
    LEFT JOIN table2 ON id_a = id_b 
    LEFT JOIN table3 ON id_c = id_d
    WHERE id_d IS NOT NULL 
    AND l_type <> 2 
    AND l_status = 2 
    AND v_status >= 1 
    AND l_end_date >= CURDATE() 
    ORDER BY 1 ASC 
    LIMIT 20
$sql2 = '
    SELECT
        SQL_CALC_FOUND_ROWS
        *
    FROM
        table1
    LEFT JOIN table2 ON id_a = id_b 
    LEFT JOIN table3 ON id_c = id_d
    WHERE id_d IS NOT NULL 
    AND id_d = ? //the only difference
    AND l_type <> 2 
    AND l_status = 2 
    AND v_status >= 1 
    AND l_end_date >= CURDATE() 
    ORDER BY 1 ASC 
    LIMIT 20

I am preparing, binding and executing the placeholder parameter like every other hundreds of times in the past. I'm baffled, why won't the second query give any results?

Let me know if my question is lacking detail - my code is as simple as this so there's not much more to it at all.

I've even tried replacing the placeholder with the actual value, which is a string of numbers like 123456789. The query works fine in phpmyadmin when I test it locally too. What could be the problem?

This is what I've tried, but no errors appear. It's just a blank page.

$stmt2 = $db->prepare($sql2); 
if (false === $stmt2) { 
print_r($db->error_list); 
echo mysqli_error($db); 
echo $stmt2->error; 
}

Where $db is $db = new mysqli($dbHost, $dbUser, $dbPass, $dbName);

ArabianMaiden
  • 505
  • 7
  • 24
  • 3
    Maybe there's an error. Try using `mysqli_error($conn)` or equivalent to see what's the error? – Praveen Kumar Purushothaman Mar 11 '19 at 22:03
  • ...or even better make the MySQLi/PDO throw exceptions on errors. – Dharman Mar 11 '19 at 22:05
  • Could you be so kind to show me how? This is what I've tried, but no errors appear. It's just blank. ```$stmt2 = $db->prepare($sql2); if (false === $stmt2) { print_r($db->error_list); echo mysqli_error($db); echo $stmt2->error; }``` Where $db is ```$db = new mysqli($dbHost, $dbUser, $dbPass, $dbName);``` – ArabianMaiden Mar 11 '19 at 22:06
  • 1
    Note: A lot of problems can be detected and resolved by [enabling exceptions in `mysqli`](https://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli) so any mistakes made aren’t easily ignored. Many return values cannot be ignored, you must pay attention to each one. Exceptions don’t require individual checking, they can be caught at a higher level in the code. – tadman Mar 11 '19 at 22:09
  • 1
    Try to get out of the habit of doing things like `if (false === $stmt2)` and instead do `if (!$stmt2)` as many of these functions are designed to return boolean-compatible values. These may not necessarily be literal false. You'll also want to avoid mixing procedural and object-oriented calling styles and instead stick to object-oriented only: `$db->error` instead of the much more verbose `mysqli_error($db)`. – tadman Mar 11 '19 at 22:09
  • @tadman I've tried each of the error checking you mentioned. It's still just a blank page. I always could never get the error checking right - mysql doesn't like me... – ArabianMaiden Mar 11 '19 at 22:12
  • Could you show us the code where you bind the params? Maybe you used incorrect type? – Dharman Mar 11 '19 at 22:26
  • If you enable the exceptions then you'll get blank pages, but you'll also get descriptive diagnostics in your server's error log, wherever that is. Look around to see where that is and you'll have way more to work with in terms of feedback from the server. – tadman Mar 11 '19 at 22:34
  • If `fetch_assoc()` returns `null` that means there are 0 results. If the query failed, you would get an error message ("Call to a member function fetch_assoc() on boolean"). If you're 100% sure this query should give a result for a particular value of `id_d`, something's going wrong where you're using `bind_param()`. If you can edit your question and add that part of your code, we can see if we can spot the problem. – rickdenhaan Mar 11 '19 at 23:01

0 Answers0