[SOLVED] Optional WHERE in Select query

[yandoo]

Hi there,

 

I was hoping for a little help with my project, please. 

 

I have a select query that calls records from a table in database:

$test= ("SELECT * FROM animal WHERE CID = '$cid_value' ORDER BY '$orderby' ASC");

 

 

An exisiting form (PLEASE SEE ATTACHMENT) that has a drop down menu that detemrines how the records will be orderd by $orderby...Additionally 2 Radio Buttons were added with a value of either 1 & 0.

 

<form name="reorder" method="GET" action="view_animal.php">
              <span class="style13">
<select name="orderby" onChange="if(this.value != '-') document.reorder.submit();">
  <option "-">Select one...</option>
  <option value="AnimalID">ID</option>
  <option value="Name">Name</option>
  <option value="AnimalTypeID">Animal Type</option>
</select>

// BELOW IS RADIO BUTTON (CID VALUE) CODE 
<input type="radio" name="cid_value" value="1">
<span class="style15"> Local Records </span></span>
                <input type="radio" name="cid_value" value="0">
                <span class="style15">All Records</span> 
            </form>

if (isset($_GET['orderby'])) {
     $orderby = $_GET['orderby'];
}else{
     $orderby = "AnimalID";
}
//BELOW IS THE CID VALUE CODE
if (isset($_GET['cid_value'])) {
     $cid_value = $_GET['cid_value'];
}else{
     $cid_value = 1; // default value is 1
}

 

The bit im having trouble with is the WHERE CID = $cid_value... .All the records in my table have a CID field in them, when details of records are stationed locally they have a CID value of 1. All other details of records that are NOT local will have EITHER have any CID number BUT NOT 1, in their field OR have a NULL value.....

 

I manged to get this working in in some respect as i can view the local records when the "LOCAL RECORDS" checkbox is ticked (as expected) but when the "ALL RECORDS" radio button is ticked it doesnt find any records, even though they EITHER  NULL OR have a CID value of any number BUT NOT 1.

 

I noticed that the value of the "All RECORDS" Radio button = "0". Could this be the problem?? Because obviously i have NO records in my table with a CID number of 0!??

 

Basically i need to get it so if the user ticks "LOCAL RECORDS" then records with a CID value of 1 will be displayed (which works ok!) And when a user ticks "ALL RECORDS" all records will be displayed but NOT records with CID value of 1.

 

If anybody can please help me, it would be great..

 

Kind Regards

 

 

 

 

[attachment deleted by admin]

[mbeals]

i think what you are attempting to do is this:

 

if local records is checked -> retrieve all records where CID == 1

 

if all records is checked -> retrieve all records where CID != 1

 

right?

 

If so,

 

something like:

 

if (isset($_GET['cid_value'])) {
     $cid_eq = '!=';
}else{
     $cid_eq = '='; // default value is 1
}

 

$test= ("SELECT * FROM animal WHERE CID $cid_eq '1' ORDER BY '$orderby' ASC");

 

Note:  this probably isn't the ideal way to handle this, as it can become more complex if you plan on limiting to other CID types

 

I didn't test this code, but it should do the trick.

[yandoo]

Hi there,

 

Thanks for the quick reply- thats brilliant, its exactly what i meant and it works perfectly for its purpose..

 

Thank you

 

Kindest Regards