Jon Jon - 4 months ago 21
PHP Question

PHP with MySQLi fetch does not return rows with NULL on LEFT JOIN

I have a table

matches
with rows containing reference IDs for sports, regions, countries and leagues among other things. I use
LEFT JOIN
to find the actual league name because not all matches have a league ID.

I use the following:

$stmt = $mysqli->prepare("
SELECT leagues.name, matches.league
FROM matches
LEFT JOIN leagues
ON leagues.short = matches.league
WHERE leagues.lang = ? AND matches.sport = ? AND matches.region = ? AND matches.country = ?
ORDER BY leagues.name ASC");

$stmt->bind_param('ssss',$lang,$sport_short,$region_short,$country_short);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($league,$league_short);


If I at this point use
var_dump($league)
or
var_dump($league_short)
I see the
NULL
values. However after I try to fetch the rows with
while ($stmt->fetch) {...}
no rows with
NULL
gets included, what am I missing?

Answer

In your query leagues.lang will also be NULL if no match is found and then it will be filtered out by the WHERE clause.

So you can either drop the leagues.lang = ? in the WHERE clause or you can modify it to something like this:

((leagues.short IS NOT NULL AND leagues.lang = ?) OR leagues.short is NULL)