Skip to main content
October 9, 2009
Answered

Filter 2 UNION joined table columns by URL Parameter

  • October 9, 2009
  • 1 reply
  • 618 views

_Hi Chaps,

I have some code, which I can't get to work, think I'm missing something somewhere.

I want to access two tables, and filter them by column 'FK_projid', which is in a URL parameter 'id'

$colname_rsJobs = "-1";
if (isset($_GET['id'])) {
  $colname_rsJobs = $_GET['id'];
}
mysql_select_db($database_conndb2, $conndb2);
$query_rsJobs = sprintf("
(
SELECT
tbl_jobs.FK_projid,
tbl_jobs.jobid,
tbl_jobs.jobname,
tbl_jobs.FK_langid,
tbl_languaget.langtname,
tbl_jobs.jobshipped
FROM
tbl_projects
INNER JOIN tbl_jobs
ON tbl_projects.projid=tbl_jobs.FK_projid
INNER JOIN tbl_languaget
ON tbl_languaget.langtid=tbl_jobs.FK_langid
)
UNION
(
SELECT
tbl_jobxml.FK_projid,
tbl_jobxml.jobid,
tbl_jobxml.jobname,
tbl_jobxml.FK_langid,
tbl_languaget.langtname,
tbl_jobxml.jobshipped
FROM
tbl_projects
INNER JOIN tbl_jobxml
ON tbl_projects.projid=tbl_jobxml.FK_projid
INNER JOIN tbl_languaget
ON tbl_languaget.langtid=tbl_jobxml.FK_langid
)
WHERE FK_projid = %s", GetSQLValueString($colname_rsJobs, "int"));

$rsJobs = mysql_query($query_rsJobs, $conndb2) or die(mysql_error());
//$row_rsJobs = mysql_fetch_assoc($rsJobs);
$totalRows_rsJobs = mysql_num_rows($rsJobs);

I get the error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE FK_projid = 3525' at line 32


3525 is correct, but I don't know what is stopping the code from working...any ideas?

This topic has been closed for replies.
Correct answer bregent

I haven't had a chance to look to closely, but you have your select statements wrapped in parens. Try removing those. Also, you have only applied the where clause the the second statement in the Union. Is that what you want?

1 reply

bregentCorrect answer
Participating Frequently
October 9, 2009

I haven't had a chance to look to closely, but you have your select statements wrapped in parens. Try removing those. Also, you have only applied the where clause the the second statement in the Union. Is that what you want?

October 9, 2009

Cool . . .sorted, thanks!