CODEWITHSUNDEEP
phpmysqlunionmultiple-tables
Norse
Norse

Reputation: 5757

Error while trying to query two tables with UNION ALL

I'm trying to query a single result from two tables in my database. Here is my code: 

$pageid = mysql_real_escape_string($_GET['id']);
$query = sprintf("(SELECT * FROM classifieds WHERE pageid = '$pageid' LIMIT 1)
UNION ALL
(SELECT * FROM resumes WHERE pageid = '$pageid' LIMIT 1)");
$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {
    echo $row['title'] . "<br/>";
    }

pageid is a URL variable. This code is trying to use that variable, query the database with it, and return the result. I get this error: 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

Hitting a wall here. Can't figure out why this error keeps happening. Any help == thanks.

Upvotes: 1

Views: 267

Answers (3)

codaddict
codaddict

Reputation: 455040

Add this error handling to your code:

$result = mysql_query($query);
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

The execution of your query failed, as a result $result was set to false which is not a valid MySQL result resource and hence the error.

Once possible cause of the problem is that the two tables have unequal number of columns. UNION ALL expects its sub queries to return equal number of columns.

Upvotes: 0

ivan
ivan

Reputation: 430

Execute your query directly and see if it returns any rows. BTW, what is the use of sprintf?

Upvotes: 0

Zohaib
Zohaib

Reputation: 7116

When using UNION the number of columns and types of columns, of both queries must be same.

Now as you are quering two different tables, I guess it is safe to assume that either number of columns are different or corresponding column types do not match.

try using something like this

select col1, col2 from classifieds.......
Union All
select col1, col2

but again, number and corresponding column types should be same.

Upvotes: 1

Related Questions