Page 1 of 1

Filter 2 UNION joined table columns by URL Parameter

Posted: Fri Oct 09, 2009 7:14 am
by koolsamule
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'

Code: Select all

$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?

Re: Filter 2 UNION joined table columns by URL Parameter

Posted: Sat Oct 10, 2009 12:14 am
by Robert07
If you echo $query_rsJobs after you create it, what does the query look like? And what is the output of "show create table tbl_projects"
and
"show create table tbl_jobs"?

In this type of situation I would suggest paring down the query until you get something that works, then building it back up, piece by piece, so you can figure out what mysql doesn't like and fix it.