PHP/Mysql - AND/OR help

[herghost]

Evening everybody!

 

What I am trying to do is display infomation from a database, each item has a  catid and a subid.

 

I can display the correct information when I am looking at a particular subid by using:

 

$catid = $_GET['catid'];
if(isset($_GET['subid'])){
$subid = $_GET['subid'];
}
else $subid = '';?>

//other code


$query = mysql_query("SELECT * FROM merchants WHERE catid= '$catid' AND subid= '$subid'");

 

But I cant figure a way to change this so if the AND is not met it will still display the info based on $catid?

 

Basically I want to display all the records with a particular catid, regardles of subid, but only if subid does not exits?!

 

Does this make sense?

 

Basically I think I am after and AND/OR

 

I hope you can see what I am on about!

 

Cheers

 

 

[MatthewJ]

$catid = $_GET['catid'];
if(isset($_GET['subid'])){
$subid = $_GET['subid'];
$query = mysql_query("SELECT * FROM merchants WHERE catid= '$catid' AND subid= '$subid'");
}
else { 
$query = mysql_query("SELECT * FROM merchants WHERE catid= '$catid'");
} ?>

 

Couldn't you just do something like that?

[herghost]

Hi MatthewJ,

 

Thanks for your reply, I have tried the following

 

if(isset($subid)){
$query = mysql_query("SELECT * FROM merchants WHERE catid= '$catid' AND subid= '$subid'");
echo mysql_error() ;
}

else{
$query = mysql_query("SELECT * FROM merchants WHERE catid= '$catid'");
echo mysql_error() ;
}





while($row = mysql_fetch_assoc($query)){

$_SESSION['name'] = $row['name'];
$_SESSION['short'] = $row['short'];
$_SESSION['per'] = $row['percent'];
$_SESSION['m_id'] = $row['m_id'];


echo $_SESSION['name'];

}

 

However nothing displays for the second query which is meant to show everything with a catid

 

It does still show when both elements are their

[OldWest]

$query = mysql_query("SELECT * FROM merchants WHERE subid = '$subid' AND (catid = '$catid' OR catid = "")"); - I did not test this, but I think this is what you are trying to do:

 

Find all results where $subid and $catid have a match BUT also include $catids with no value (null)?

[herghost]

Thanks,

 

Almost there! however this shows the rows in a database which do not have a catid (because I haven't chosen a cat for these rows yet!) when they should only have a catid

 

[OldWest]

$query = mysql_query("SELECT * FROM merchants WHERE (subid = '$subid' AND subid = "") AND (catid != "")");

 

NOT TESTED! But this should do what you want I think... Give it a shot.

[herghost]

This just stops it displaying anything at all, and I thought it would be simple

[OldWest]

Try this:

 

$query = mysql_query("SELECT * FROM merchants WHERE subid = '$subid' AND (catid != "")");

[herghost]

Back to square one with that one, displays when both are present but nothing when just catid is present

[OldWest]

Back to square one with that one, displays when both are present but nothing when just catid is present

 

Can you please rephrase what you are trying to do?

 

Do you want to show all results regardless if either field is empty?

 

OR

 

What is the exact output you expect? Please clarify.

[herghost]

Ok,

 

Basically say I have 3 items in my database, all with a catid of 1, 2 of the items have a subid of 2 and the remaing has a subid of 3

 

Basically I then have a menu tree of one top cat and 2 sub cats, when I click on the top cat I wnat to display all three results, or when I click on a sub cat then just the results for that subcat will appear.

 

All this is contained on one page. in my real world application this is called view.php.

 

So if you are viewing the top cat then the url is view.php?catid=2 and when using a sub cat its view.php?catid=2&subid=3

 

Does that make more sense?

 

Thanks for all your help thus far

[herghost]

Well I had a think and came up with this:

 

$catid = $_GET['catid'];
if(isset($_GET['subid'])){
$subid = $_GET['subid'];
}
else $subid = '0';

if($subid > 0)
{
$query = mysql_query("SELECT * FROM merchants WHERE catid= '$catid' AND subid= '$subid'");
echo mysql_error() ;
}

else
{
$query = mysql_query("SELECT * FROM merchants WHERE catid= '$catid'");
echo mysql_error() ;

}

 

Which does the trick nicely

 

 

 

 

[worldcom]

$catid = $_GET['catid'];

if(isset($_GET['subid'])){

$subid = $_GET['subid'];

}else $subid = '%';

?>

 

I know you have it solved but you could try putting a wildcard in for $subid ( % ) and it should return all subid results with only 1 query.