Birrel Birrel - 7 months ago 10
PHP Question

PHP, MySQL - Get all results from pdo JOIN, even if no join matches?

I am doing a JOIN operation between a table and itself. The table schema is something like:

| id | name | parent |
| 0 | .... | ... |
| 1 | .... | ... |
| 2 | .... | ... |


The query looks like:

$qMarks = str_repeat('?,', count($arr) - 1) . '?';
$stmt = $db->prepare("SELECT t1.id AS t1id, t1.name AS t1name, t2.name AS t2name
FROM cats t1
JOIN cats t2 ON t1.parent = t2.id
WHERE t1.name IN ($qMarks)");
$stmt->execute($arr);
$result = $stmt->fetchAll();


So, I'm passing in an array of names
$arr
, and I am getting back the rows where there is a matching name to one of the items in the parameter array. This works fine, so long as there is also a matching
id
somewhere for
parent
.

But, sometimes the value for
parent
will be blank (empty cell). I still want to get those results, as long as the
t1.name IN ($qMarks)
condition is met.

How do I return the values, even if the
t1.parent
value in the table is blank?

Answer

Use a left join.

$stmt = $db->prepare("SELECT t1.id AS t1id, t1.name AS t1name, t2.name AS t2name 
                        FROM cats t1 
                        LEFT JOIN cats t2 ON t1.parent = t2.id
                        WHERE t1.name IN ($qMarks)");