How to UNION ALL two SELECT statements?

Question

I have 2 tables, one looks like this:

TABLE ONE

id | Last Name | First Name | Username | Password | Secret Question

and another that looks like this:

TABLE TWO

id | Hobby | Country | 

I want to combine a Select statement that grabs data from both tables and output the results. The following code:

$select = mysql_query("

    SELECT * FROM table_one WHERE Username = 'Bob'

    UNION ALL

    SELECT * FROM table_two WHERE Hobby = 'Baseball'

");

while ($return = mysql_fetch_assoc($select)) {

$userName = $return['Username'];

$hobby = $return['Hobby'];

}

echo "$userName likes $hobby";

results in a The used SELECT statements have a different number of columns error, what am I doing wrong?

---

EDIT:

Running this gives the following error: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/studentw/public_html/foo.php on line 16

$select = mysql_query("

SELECT FROM TABLE_ONE t1
  INNER JOIN TABLE_TWO t2
    ON t1.id = t2.id
WHERE t1.Username = 'Bob'
  AND t2.Hobby = 'Baseball'

");

while ($return = mysql_fetch_assoc($select)) {

    $firstName = $return['Username'];
    $hobby = $return['Hobby'];

}

echo "$firstName likes $hobby";

The values in the tables are:

TABLE ONE

id | Last Name | First Name | Username | Password | Secret Question

 1 | Hughes | Bobby | Bob | 123 | Maiden name?

TABLE TWO

id | Hobby | Country | 

 1 | Baseball | USA

Answer 1

I think you want a JOIN, not a UNION or UNION ALL.

SELECT FROM TABLE_ONE t1
  INNER JOIN TABLE_TWO t2
    ON t1.id = t2.id
WHERE t1.Username = 'Bob'
  AND t2.Hobby = 'Baseball'

Unions require that the data columns of each table be the same number and type, and basically give you a concatenation of multiple rows from different tables. Joins, on the other hand, essentially expand one table into a wider one with more columns by literally joining another table's columns to it.

When you do a join, you need to specify how the rows of one table correspond to the other; in this case, I'm assuming that your Id field is supposed to be a primary/foreign key linking the tables together. Apologies if that is incorrect-- if that is so, I will need more information in order to properly help you.

---

Per your edited question:

I'll first link you to the documentation for the mysql_query function. It has one required and one optional argument; the second argument is your connection handle. If you don't specify it, then PHP assumes that the last connection opened with mysql_connect is the one you want to use. So my first question is, did you call mysql_connect properly and did that call work successfully?

If you're sure the mysql_connect call worked, then I'm not sure what the problem could be. I don't think it could hurt, though, to assign the result of mysql_connect to a variable so you can explicitly specify the connection in mysql_query. Maybe something like the following:

$conn = mysql_connect("localhost:3306", "username", "password");
$query = "select * from some_table"; // obviously use your own query here
$result = mysql_query($query, $conn);

Let me know if that doesn't work.

Answer 2

Yes, Platinum Azure is correct, what you need is a join. However, your table design needs some help.

You need to relate the tables to each other. Easy way to do this is:

1) Add a column to table 1: "hobby_id" 2) Then each record record in table 1 needs to have the id from table 2 in the field "hobby_id"

Table 1:

id | Last Name | First Name | Username | Password | Secret Question | hobby_id
1 | Hughes | Bobby | Bob | 123 | Maiden name? | 2
2 | Smith | Mike | Msmith | mypass | usual | 1

Table 2:

id | Hobby | Country | 
1 | Baseball | USA
2 | Hockey | Canada
3 | Horse Racing | Japan

See how hobby_id relates to id in table 2? So, Table 1, Bobby, plays Hockey and Mike plays Baseball. No one matches horse racing.

So to query these tables, it would look like this:

select table1.`last name`,table1.`first name`, table2.`hobby`  from table1
inner join table2 on table1.hobby_id = table2.id

The result would be:

Hughes | Bobby | Hockey
Smith | Mike | Baseball

and your php code then looks like this:

while ($return = mysql_fetch_assoc($select)) {

    echo $return['first name']." ".$return['last name']." likes ".$return['hobby']."<br/>\n";

}