SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select query does not show results

    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. #2
    SitePoint Zealot bronze trophy xMog's Avatar
    Join Date
    Mar 2011
    Posts
    159
    Mentioned
    3 Post(s)
    Tagged
    2 Thread(s)
    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. #3
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,260
    Mentioned
    196 Post(s)
    Tagged
    2 Thread(s)
    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. #4
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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:

    Code:
    SELECT 
        p2g.id
      , p2g.text
    FROM p2g 
    LEFT OUTER JOIN author
    ON p2g.authorid = author.id
    WHERE author.id IS NULL

  6. #6
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    The solution to that has already been given by others, and it isn't an inner join

  8. #8
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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. #10
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •