Select query does not show results

PHP
1

Hi there,

I am still learning php and would be glad if somebody help me to understand the processes of the following select query.

The below stated select query does not contain any results (rows) in its result set if some variables contain no entry in the database (in the respective row). Why is this the case and how can I still retrieve results even if some variables contain no entries?

Here is the query

p2g.id and p2gtext are part of the p2g table
name, email are part of the author table

try
{
$sql = ‘SELECT p2g.id, p2gtext, name, email
FROM p2g INNER JOIN author
ON authorid = author.id’;
$result = $pdo->query($sql);
}
catch (PDOException $e)
{
$error = 'Error fetching places to go: ’ . $e->getMessage();
include ‘error.html.php’;
exit();
}

if ($result->rowCount() === 0) {
$output = ‘Select query contains no entries.’;
include ‘p2g.html.php’;
include ‘output.html.php’;
exit();
}

In the table “author” are no entries made. Therefore the variables name and email are empty. The table p2g contains entries. If I run the query no results are shown (the if statement is applied)

I get the results if I do not “Inner Join” the author table, and therefore all variables have entries in respective rows.
I also get the results if I insert data into the author table. But only the results will be show which have an entry in ALL variables (in the respective row).

Why there is an entry needed in all variables for a select query to show the results?
And how can I alter the select query that results are still shown, even if not all variables contain an entry for the respective row?

Please email if you need better description of the problem

Thanks for help in advance!

2

I think what you need is a LEFT JOIN.

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

If you use an INNER JOIN, it will only return existing rows in both tables.

3

JOINs have not been easy for me to understand and I still need to think about them more than I’d like to.
*I’m guilty of having commited more than one of these Top 10 MySQL Mistakes Made By PHP Developers

Anyway, hopefully this will help Understanding JOINs in MySQL and Other Relational Databases

4

Dear xMog, thanks for your note.

Is there a possibility that I can change the select request, that all results (rows) of both tables are shown. This is because, for instance, I like to check whether each joke an author as been assigned in the author table ( the table which add with INNER JOIN).

Thanks for an answer in advance.

5

If you want to extract all jokes that DON’T have an author that exists in the autors table, add the red line to the WHERE statement:


SELECT 
    p2g.id
  , p2g.text
FROM p2g 
LEFT OUTER JOIN author
ON p2g.authorid = author.id
[COLOR="#FF0000"][B]WHERE author.id IS NULL[/B][/COLOR]
6

Dear Guido,

thanks for providing me that code! Can you also think of a select query in terms of: “Select all rows regardless whether the entry is null or sth, else in a row”? when using INNER JOIN between tables?

(I like to avoid to state for every variable a specific statement that it can be null or sth. else)

I would be glad if you can look at this problem.

7

The solution to that has already been given by others, and it isn’t an inner join :wink:

8

Guido, thanks for your quick reply, do you have a link for me where I can read about this problem? Or does this problem refer to a certain wording I could look up in the forum? It would be nice if you can give me a hint on it because I already tried to google it, but seems that I use the wrong words.

Thanks

9

If you want all rows from the first table, even if the authorid doesn’t exist in the authors table, just take off the WHERE condition from the query I posted.

As explained in this thread by xMog, and also in the article Mittineague linked to, INNER JOIN only gives you those rows where the two tables actually have the same authorid. The pictures in the article are very clear, did you read it?

10

Hi Guido,

thanks for your note, now with the explanations on the example you posted on my data I understand. Thanks and sorry for questioning a lot. Still very new complex topic for me. But lots of fun. Thanks again for being active on the post to help out!