Filter 2 UNION joined table columns by URL Parameter

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
koolsamule
Forum Contributor
Posts: 130
Joined: Fri Sep 25, 2009 10:03 am

Filter 2 UNION joined table columns by URL Parameter

Post 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?
User avatar
Robert07
Forum Contributor
Posts: 113
Joined: Tue Jun 17, 2008 1:41 pm

Re: Filter 2 UNION joined table columns by URL Parameter

Post 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.
Post Reply