Calling a second query if results are empty

Question

I'm calling an INNER JOIN Query to display results from a user if they have information in both tables 1 & 2 - this works fine. However, If said user doesn't have information in both tables then my variables come back undefined.

So I'd like to call my INNER JOIN query and if the rows are empty for Table1, it will call a second query to display the results from Table2.

(Table2 definitely has information stored. Table1 is optional depending on the user)

I also want to display results from Tables 1 & 2 if another user has information in both, I can't seem to get it working. Here's what I have so far;

$sql="SELECT * FROM Table1 INNER JOIN Table2 ON Table1.username = Table2.username WHERE Table1.username='" . $_SESSION['username']['1'] . "'";

$result=MySQL_Query($sql);
if (mysql_num_rows($result)==0)
{ 
    $sql2"SELECT * FROM Table2 WHERE username='" . $_SESSION['username']['1'] . "'";
    $result2=MySQL_Query($sql2);
}

while ($rows=mysql_fetch_array($result)($result2))
{
    $Username = $rows['username'] ." ";
    $Email = $rows['email'] . " ";
}

echo $Email; ?>

Why not use an `outer join` and then the one query will always return the data? — sgeddes, Dec 11 '15 at 18:18
Before I answer, I'll get these out of the way: First, the `mysql` driver is obsolete; use `mysqli` or `PDO`. Second: your code is vulnerable to SQL injection; use prepared statements. Third, source code is meant to be read by humans, and should be formatted accordingly. Fourth, this code doesn't actually run, does it? — Darwin von Corax, Dec 11 '15 at 18:24

score 0 · Answer 1 · answered Dec 11 '15 at 18:31

You can rewrite your query with an outer join as follows:

select
    t1.desired_info
    t2.desired_info
from
    table2 as t2
        left outer join
    table1 as t1
        on (t2.username = t1.username)
where
    t2.username = target_username;

The where clause will narrow the result set down to the user of interest and return 0 rows if the target_username does not exist. t2.desired_info will always come back while t1.desired_info will be null if that user is not present in that table.

You can inspect p1.desired_info in your php code first and if that does not appear then use p2.desired.

score 0 · Answer 2 · answered Dec 11 '15 at 20:57

Echo the suggestion to use OUTER JOIN, which joins all the selected rows in the left table to matching rows in the right, or to an empty row if there are no matching rows on the right.

Also, I've rewritten to use PDO and a prepared statement, to show you how its done. The values in the variables $dbhost, $dbname, $dbuser and dbpassword are supplied by your code.

<?php
    $dsn = "mysql:host=$dbhost;dbname=$dbname";
    $dbh = new PDO($dsn, $dbuser, $dbpassword);

    $sql = "SELECT *
              FROM table2 t1
              LEFT JOIN table1 t1 ON t1.username = t2.username
              WHERE t2.username = :username";

    // prepare() parses and compiles the query. Syntax error come out here.
    $stmt = $dbh->prepare($sql);
    // bindValue() handles quoting, escaping, type-matching and all that crap for you.
    $stmt->bindValue(':username', $_SESSION['username']['1']);
    $stmt->execute();
    while ($row = $stmt->fetch(PDO::FETCH_BOTH))
    {
        $Username = $rows['username'] ." ";
        $Email = $rows['email'] . " ";
        // and then do something useful with them
    }

The use of a prepared statement here gives a significant measure of protection against SQL injection attacks. As usual, I leave error handling as an exercise for the reader.

Calling a second query if results are empty

2 Answers2