| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-17-12, 13:24
|
|
Registered User
|
|
Join Date: Sep 2012
Location: London and Bucarest
Posts: 24
|
|
|
how do i create an if clause within a mySQL query sub
|
|
Hi everyone.
i am trying to create an "if clause" within a sub query of a mySQL QUERY. i have enclosed the query below .
This is the part of the query that i want to place in an "If Clause"
PHP Code:
WHERE user_id IS NOT NULL
if(!empty($Co_lo) )
{
AND job_id IN ( 1,2,3,4,5,6,7,8,9,10 )
}
The Search Query.
PHP Code:
function worker_general_search ( $Co_lo= null, $last_login=null , $limit=null )
{
global $dbc;
$select = " SELECT
u.user_id,
u.first_name,
cl.country_id,
col.country,
jb.job_category_sort
" ;
$from = " FROM
users u
LEFT OUTER JOIN countrylocation cl
ON u.user_id = cl.user_id
LEFT OUTER JOIN countrylist col
ON cl.country_id = col.country_id
LEFT OUTER JOIN
( SELECT user_id,
GROUP_CONCAT(job_id) AS job_category_sort
FROM worker_category
WHERE user_id IS NOT NULL
if(!empty($Co_lo) )
{
AND job_id IN ( 1,2,3,4,5,6,7,8,9,10 )
}
GROUP
BY user_id
) AS jb
ON jb.user_id = u.user_id
";
$where = " WHERE u.live = '1'" ;
$order= " ORDER BY u.first_name DESC $limit ";
$query = $select.$from.$where. $order ;
// $query = $select.$from.$where. $GROUPBY. $order ;
$result = @mysqli_query ($dbc, $query)or trigger_error("Query: $query\n<br />MySQL Error: " . mysqli_error($dbc)) ;
return $result ;
}
the function is a search box. so i need the if clause beucase i only want the "AND" condition to apply if the user specifies it. that is why it needs to be contained within an "IF CLAUSE".
Thank you so much for your kind attention
|
|
12-17-12, 13:45
|
|
Jaded Developer
|
|
Join Date: Nov 2004
Location: out on a limb
Posts: 10,594
|
|
?
PHP Code:
$WhereClause = ' WHERE user_id IS NOT NULL';
if(!empty($Co_lo) )
{ $WhereClause .= ' AND job_id IN ( 1,2,3,4,5,6,7,8,9,10)';
}
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton 
|
|
12-18-12, 02:20
|
|
Registered User
|
|
Join Date: Sep 2012
Location: London and Bucarest
Posts: 24
|
|
|
|
Hi everyone
thank you healdem for his reply. However, the code is still not working. i keep getting the following error report:
Quote:
Notice: Query: SELECT u.user_id, u.first_name, cl.country_id, col.country, jb.job_category_sort FROM users u LEFT OUTER JOIN countrylocation cl ON u.user_id = cl.user_id LEFT OUTER JOIN countrylist col ON cl.country_id = col.country_id LEFT OUTER JOIN ( SELECT user_id, GROUP_CONCAT(job_id) AS job_category_sort FROM worker_category = ' WHERE user_id IS NOT NULL'; if(!empty( 1,2,3,4,5,6,7,8,9,10) ) { .= ' AND job_id IN ( 1,2,3,4,5,6,7,8,9,10)'; } GROUP BY user_id ) AS jb ON jb.user_id = u.user_id WHERE u.live = '1' ORDER BY u.first_name DESC
MySQL Error: You have an error in your SQL syntax; check the manua in db_fns.php on line 112
|
I have tested the code without the addiction of the variables and "if clause" and it works perfectly; so,the thing causing the syntax error is the addiction of the variables and the "if clause" within the subquery.
i have enclosed my full SQL function below.
Does anyone have any advice or recommendation of how i should proceed
warm regards
Andreea
PHP Code:
function worker_general_search ( $Co_lo= null, $jobs_cat=null, $limit=null )
{
global $dbc;
$WhereClause='';
$select = " SELECT
u.user_id,
u.first_name,
cl.country_id,
col.country,
jb.job_category_sort
" ;
$from = " FROM
users u
LEFT OUTER JOIN countrylocation cl
ON u.user_id = cl.user_id
LEFT OUTER JOIN countrylist col
ON cl.country_id = col.country_id
LEFT OUTER JOIN
( SELECT user_id,
GROUP_CONCAT(job_id) AS job_category_sort
FROM worker_category
$WhereClause = ' WHERE user_id IS NOT NULL';
if(!empty($jobs_cat) )
{ $WhereClause .= ' AND job_id IN ( $jobs_cat)';
}
GROUP
BY user_id
) AS jb
ON jb.user_id = u.user_id
";
$where = " WHERE u.live = '1'" ;
if(!empty($Co_lo) )
{
$where .= " AND cl.country_id = '$Co_lo'";
}
$order= " ORDER BY u.first_name DESC $limit ";
$query = $select.$from.$where. $order ;
$result = @mysqli_query ($dbc, $query)or trigger_error("Query: $query\n<br />MySQL Error: " . mysqli_error($dbc)) ;
return $result ;
}
$jobs_cat =" 1,2,3,4,5,6,7,8,9,10";
$Co_lo='';
$worker_search = worker_general_search ( $Co_lo , $jobs_cat );
|
|
12-18-12, 05:29
|
|
Jaded Developer
|
|
Join Date: Nov 2004
Location: out on a limb
Posts: 10,594
|
|
that 'if' clause is PHP building a fragment of SQL in your where clause it is not valid SQL.
im ghuessing what you really want is soemthign sort of similar to:-
PHP Code:
if(!empty($Co_lo) ) //if the user has specified a co_lo add it
{ $Where = " AND cl.country_id = '$Co_lo'";
} else //set our defaults
{ $Where .= ' AND job_id IN ( 1,2,3,4,5,6,7,8,9,10)';
}
which repalces
PHP Code:
if(!empty($Co_lo) )
{
$where .= " AND cl.country_id = '$Co_lo'";
}
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
|
|
12-18-12, 08:06
|
|
Registered User
|
|
Join Date: Sep 2012
Location: London and Bucarest
Posts: 24
|
|
Hello again everyone
special thanks to healdem and to R937 for their help.
i applied the changes and it indeed worked. i have enclosed the code below.
i have one final question on this issue that i am not clear about. You will note from my code that i have applied the " if clause " ( equating multiple values to multiple values) in a subquery of my FROM CLAUSE of my query.
but this is not acting as a filter. the reason for my equating the conditions was to be able to filter out users whose profiles do not match with the given multiple values.
Should i not therefore be doing the equating of values ( the "if clause") in the WHERE CLAUSE of my query.
i wold really appreciate some advice on this. sorry to take up so much of everyone time, but i do feel that this is the final hurdle on this matter.
PHP Code:
function worker_general_search ( $Co_lo= null, $jobs_cat=null , $limit=null )
{
global $dbc;
$WhereClause='';
if(!empty($jobs_cat) ) //if the user has specified a co_lo add it
{ $WhereClause = " AND job_id IN ( $jobs_cat)";
} else //set our defaults
{ $WhereClause = '';
}
$select = " SELECT
u.user_id,
u.first_name,
cl.country_id,
col.country,
jb.job_category_sort
" ;
$from = " FROM
users u
LEFT OUTER JOIN countrylocation cl
ON u.user_id = cl.user_id
LEFT OUTER JOIN countrylist col
ON cl.country_id = col.country_id
LEFT OUTER JOIN
( SELECT user_id,
GROUP_CONCAT(job_id) AS job_category_sort
FROM worker_category
WHERE user_id IS NOT NULL
$WhereClause
GROUP
BY user_id
) AS jb
ON jb.user_id = u.user_id
";
$where = " WHERE u.live = '1'" ;
if(!empty($Co_lo) )
{
$where .= " AND cl.country_id = '$Co_lo'";
}
$order= " ORDER BY u.first_name DESC $limit ";
$query = $select.$from.$where. $order ;
// $query = $select.$from.$where. $GROUPBY. $order ;
$result = @mysqli_query ($dbc, $query)or trigger_error("Query: $query\n<br />MySQL Error: " . mysqli_error($dbc)) ;
return $result ;
}
//$Co_lo , $jobs_cat
$jobs_cat =" 1,2,3,4,5,6,7,8,9,10";
// $jobs_cat ="'7'";$Co_lo, $jobs_cat
$Co_lo='';
$worker_search = worker_general_search ( $Co_lo, $jobs_cat );
|
|
12-18-12, 09:19
|
|
Jaded Developer
|
|
Join Date: Nov 2004
Location: out on a limb
Posts: 10,594
|
|
SQL select, selects the columns to be displayed
the where clause filters the rows to be returned, essentially each part of the where clause must return true for a row to be returend, but as its using boolean logic you can use OR AND NOT and so on to make a compound where clause
so you could use somethign like
where (ProduceType = 'Citrus' AND Produce NOT in ('Orange', 'Lemon')) OR ProduceType = 'Legumes' OR Produce like 'Green%'
to return all rows which
are Citrus (fruits) but NOT Oramges or Lemons
OR whose ProductType is Legumes
OR whose Product starts with Green
there is nothing stopping you putting in mutually exclusive terms such as
where ProduceType = 'Citrus' AND Produce = like 'Potato'
after all SQL isn't all knowing if you ask it a stupid question it will return what it can.
so if this SQL statement isn't doing what you expect then look at your where clause(s). they are the ones that filter out the unedded rows. if you are not gettimng only the rows you expect then look to the where clause
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
how do i create an if clause within a mySQL query sub
