尝试使用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.

我正在尝试从数据库中的两个表中查询单个结果。这是我的代码： p>

  $ pageid = mysql_real_escape_string（$ _ GET ['id']）; 
 $ query = sprintf（“（SELECT * FROM classifieds WHERE pageid ='  $ pageid'LIMIT 1）
UNION ALL 
（SELECT * FROM恢复WHERE pageid ='$ pageid'LIMIT 1）“）; 
 $ result = mysql_query（$ query）; 
 
while（$ row = mysql_fetch_array（  $ result））{
 echo $ row ['title']。  “＆lt; br /＆gt;”; 
} 
  code>  pre> 
 
   pageid  code>是一个网址变量。 此代码尝试使用该变量，使用它查询数据库，并返回结果。 我收到此错误： p> 
 
 
 警告：mysql_fetch_array（）：提供的参数不是有效的MySQL结果资源
  code>  pre> 
 
 在这里打墙。 无法弄清楚为什么这个错误不断发生。 任何帮助==谢谢。 p> 
  div>

答

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.

答

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

答

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.

尝试使用UNION ALL查询两个表时出错

相关推荐