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
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.
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
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 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)
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.
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?
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!