[RESOLVED] Select Query

[obrienkev]

Hi all,

I have a form where a user enters various details. All fields do not need to be filled in.
My query below checks for all entries but I want it to match user entries.

How is this done? Thanks.

$query = "SELECT * ".
			 	 "FROM fund f ".
				 "INNER JOIN ".
				 "fund_holdings fh ".
				 "ON f.fund_holdings_ID = fh.fund_holdings_ID ".
				 "INNER JOIN ".
				 "pricing_info p ".
				 "ON f.pricing_ID = p.pricing_ID ".
				 "INNER JOIN ".
				 "serviced s ".
				 "ON f.serviced_ID = s.serviced_ID ".
				 "WHERE f.fund_name = '$fund_name' ".
				 "AND f.fund_number = '$fund_number' ".
				 "AND f.serviced_ID = '$serviced_ID' ".
				 "AND f.fund_start = '$fund_start' ".
				 "AND f.closed = '$closed_fund' ".
				 "AND f.closed_date = '$closed_date' ".
				 "AND f.associated_entities = '$associated_entities' ".
				 "AND f.fund_of_fund = '$fund_of_fund' ".
				 "AND f.centralised_priced = '$centralised_priced' ".
				 "AND f.period = '$period' ".
				 "AND f.wkday = '$valuation_day' ".
				 "AND p.options_manual = '$options_manual'";
				 //echo '<p>'.$query.'</p>';
$result = mssql_query($query) or die('Select Query Error');

[dougal85]

From my understanding you want the query to change depending on what information the user provides?

in this case i think this is how you are best to do it ...
(i am just doing this off the top of my head - but you should get the idea).

I'll also assume you are using $_POST vars to pass the variabes to this page 😉 if not let me know and i'll correct the example

$query = "SELECT * FROM table WHERE"

if (isset($POST['fund_name'])) $query .= " f.fund_name = '$fund_name'";
if (isset($POST['fund_member'])) $query .= "AND f.fund_number = '$fund_number' "

and keep going to add all the parts you need.

I just done that quickly but it should start you off. Beware, i didnt take into account a few things - as i couldnt be bothered to be honest. For example, you need to take into account that there might be nothing passed in - mine doesnt look for that. Also if $fund_name IS blank then you will have an extra AND after the WHERE.

Hopefully that makes sense - i think i waffled a bit.

Also, before somebody attacks me for it... dont put POST vars straight into queries. 🙂

example purposes only.

[obrienkev]

Thanks Dougal.

But what if $fund_name isn't selected then the WHERE clause won't work?

[dougal85]

yeh, thats what i meant when i said "Also if $fund_name IS blank then you will have an extra AND after the WHERE."

you could set a boolean and when the first one it added change it to true, then you know you need to put an AND instard of it

I wasnt going to give you the full solution 😛

[obrienkev]

Here's my attempt but seems to be a problem with the if cluase syntax? Just get blank page when echoed.

$query = "SELECT * ".
			 	 "FROM fund f ".
				 "INNER JOIN ".
				 "fund_holdings fh ".
				 "ON f.fund_holdings_ID = fh.fund_holdings_ID ".
				 "INNER JOIN ".
				 "pricing_info p ".
				 "ON f.pricing_ID = p.pricing_ID ".
				 "INNER JOIN ".
				 "serviced s ".
				 "ON f.serviced_ID = s.serviced_ID ".
				 "WHERE p.options_manual = '$options_manual' ".

if (isset($fund_name)) $query .= "AND f.fund_name = '$fund_name' ".

if (isset($fund_member)) $query .= "AND f.fund_number = '$fund_number' ".

if (isset($serviced_ID)) $query .= "AND f.serviced_ID = '$serviced_ID' ".

if (isset($fund_start)) $query .= "WHERE f.fund_start = '$fund_start' ".

if (isset($closed_fund)) $query .= "WHERE f.closed = '$closed_fund' ".

if (isset($fund_of_fund)) $query .= "WHERE f.fund_of_fund = '$fund_of_fund' ".

if (isset($options_mean)) $query .= "WHERE p.options_mean = '$options_mean' ".

$query .= "";

echo $query;

[dougal85]

$query = "SELECT * ".
			 	 "FROM fund f ".
				 "INNER JOIN ".
				 "fund_holdings fh ".
				 "ON f.fund_holdings_ID = fh.fund_holdings_ID ".
				 "INNER JOIN ".
				 "pricing_info p ".
				 "ON f.pricing_ID = p.pricing_ID ".
				 "INNER JOIN ".
				 "serviced s ".
				 "ON f.serviced_ID = s.serviced_ID ".
				 "WHERE p.options_manual = '$options_manual' ";

if (isset($fund_name)) $query .= "AND f.fund_name = '$fund_name' ";

if (isset($fund_member)) $query .= "AND f.fund_number = '$fund_number' ";

if (isset($serviced_ID)) $query .= "AND f.serviced_ID = '$serviced_ID' ";

if (isset($fund_start)) $query .= "WHERE f.fund_start = '$fund_start' ";

if (isset($closed_fund)) $query .= "WHERE f.closed = '$closed_fund' ";

if (isset($fund_of_fund)) $query .= "WHERE f.fund_of_fund = '$fund_of_fund' ";

if (isset($options_mean)) $query .= "WHERE p.options_mean = '$options_mean' ";

echo $query;

[obrienkev]

Hi again,

Just after realising that my WHERE clause won't work correctly :o

WHERE p.options_manual = '$options_manual'

Never thought that it might have a differnet value in database!

Any ideas on resolving this. Maybe I could create a dummy ID of some sort?

Thanks

[obrienkev]

Hi again,

Why does the first if clause come into effect even when it is not set??

$query = "SELECT * ".
			 	 "FROM fund f ".
				 "INNER JOIN ".
				 "fund_holdings fh ".
				 "ON f.fund_holdings_ID = fh.fund_holdings_ID ".
				 "INNER JOIN ".
				 "pricing_info p ".
				 "ON f.pricing_ID = p.pricing_ID ".
				 "INNER JOIN ".
				 "serviced s ".
				 "ON f.serviced_ID = s.serviced_ID ".
				 "INNER JOIN ".
				 "dummy d ".
				 "ON d.dummy_ID = f.dummy_ID ".
				 "WHERE d.dummy_ID == '1' ";
			 echo '<p>FN'.$fund_name.'</p>';
			 echo '<p>MGR'.$ffnumber_mgr.'</p>';

if (isset($fund_name) || $fund_name != '') {$query .= "AND f.fund_name = '$fund_name' ";}

[dougal85]

if (isset($fund_name) || $fund_name != '')

change to

if (isset($fund_name) && $fund_name != '')

you want AND not OR.

Umm, should still not do it

try echo doing an echo of $fun_name to check what it is

if that doesnt help, try doing "echo isset($fund_name)"

[obrienkev]

This seems to work...

$query = "SELECT * ".
			 	 "FROM fund f ".
				 "INNER JOIN ".
				 "fund_holdings fh ".
				 "ON f.fund_holdings_ID = fh.fund_holdings_ID ".
				 "INNER JOIN ".
				 "pricing_info p ".
				 "ON f.pricing_ID = p.pricing_ID ".
				 "INNER JOIN ".
				 "serviced s ".
				 "ON f.serviced_ID = s.serviced_ID ".
				 "INNER JOIN ".
				 "dummy d ".
				 "ON d.dummy_ID = f.dummy_ID ".
				 "WHERE d.dummy_ID == '1' ";
			 echo '<p>FN'.$fund_name.'</p>';
			 echo '<p>MGR'.$ffnumber_mgr.'</p>';

if (isset($fund_name) && $fund_name != "") {$query .= "AND f.fund_name = '$fund_name' ";}

if (isset($fund_number) && $fund_number != '') $query .= "AND f.fund_number = '$fund_number' ";

if (isset($serviced_ID) && $serviced_ID != "0") $query .= "AND f.serviced_ID = '$serviced_ID' ";

Thanks!