[SOLVED] Filter 2 UNION joined table columns by URL Parameter

[kool_samule]

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?

[kickstart]

Hi

 

The WHERE FK_projid = %s doesn't refer to any select statement.

 

Either surround your current pair of selects with brackets and use a "SELECT * FROM" before them with an added alias name after them (so your where clause refers to the subselect), or repeat the WHERE FK_projid = %s in both of the SELECTs you already have.

 

For example:-

 

$query_rsJobs = sprintf("
SELECT *
FROM ((
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
)) SomeAlias
WHERE FK_projid = %s", GetSQLValueString($colname_rsJobs, "int"));

 

or something like this:-

 

$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
WHERE FK_projid = %1$s
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 = %1$s', GetSQLValueString($colname_rsJobs, "int"));

 

All the best

 

Keith

[kool_samule]

Hi Keith,

 

Thanks for that, however, I get this error when I try either:

 

PHP Notice: Undefined variable: row_rsJobs in C:\Inetpub\QuickTrace\admin\project_details.php on line 330 [repeated]

 

Which refers to the table where the results are displayed....

[kickstart]

Hi

 

That is just an informatory message saying a variable hasn't been set before it is used. What is on line 330? Have you uncommented the line //$row_rsJobs = mysql_fetch_assoc($rsJobs);?

 

All the best

 

Keith