[RESOLVED] Add WHERE to query

[drax007]

Hi there,

I'm having a bit of a nightmare and was hoping for some help please 🙂

I currently have a simple form with list/menu and options that determine how records in a table are ordered by:

<form action="view_food.php" method="get" name="reorder" id="reorder">
                <select name="orderby" onchange="if(this.value != '-') document.reorder.submit();">
                  <option "-">Select one...</option>
                  <option value="FoodID">ID</option>
                  <option value="FoodName">Name</option>
                  <option value="AnimalTypeID">Food Type</option>
                  <option value="Username">User</option>
                </select>
              </form>

<?php
$currentPage = $_SERVER["PHP_SELF"];

if (isset($_GET['orderby'])) { 
$orderby= $_GET['orderby']; 
}else{ $orderby="FoodID"; }
//CHECKING TO SEE IF THE PERSON HAS CHOSEN TO ORDER BY A CERTAIN FIELD
?>

This all works fine at present, but as i have continued with my project i have found the need to add a new option in the list/menu and relate that option to a WHERE clause in my query. This will allow me to find all records WHERE tablefield "CID"= 6 - which is a static number.

for example:

$test= ("SELECT * FROM foods WHERE CID = 6 ORDER BY $orderby ASC");
     <option value="Where clause example">CID number</option>

Is it possible to add a WHERE clause to my existing query and present that as an option in the list/menu??

If anybody could please help me that would be great as i'm rather stuck.

Thank You 🙂

[halojoy]

Is it possible to add a WHERE clause to my existing query
and present that as an option in the list/menu??

Yes, and you can do this by your self ( To COPY work of others .. is BEING SMART!!! ):
Just COPY the way 'ORDER_BY' option is done in your form
.. and rename, of course, name="xxx" and the button value="xxx" text to: where

If you get into some trouble doing my advice
you are welcome back, to get more help

regars 🙂
halojoy

[drax007]

Hi,

thanks for the reply... Do you mean as two separate drown downs??

Heres what i got:

<form action="view_food.php" method="get" name="reorder" id="reorder">
                <select name="orderby" onchange="if(this.value != '-') document.reorder.submit();">
                  <option "-">Select one...</option>
                  <option value="FoodID">ID</option>
                  <option value="FoodName">Name</option>
                  <option value="AnimalTypeID">Food Type</option>
                  <option value="Username">User</option>

<option value="CID">CID</option>

            </select>
          </form>

<?php
$currentPage = $_SERVER["PHP_SELF"];

if (isset($_GET['orderby'])) { 
$orderby= $_GET['orderby']; 
$value= $_GET['orderby'];
}else{ $orderby="FoodID"; }
//CHECKING TO SEE IF THE PERSON HAS CHOSEN TO ORDER BY A CERTAIN FIELD


$test= ("SELECT * FROM animal WHERE CID= $value ORDER BY $orderby ASC");
$query_limit_test = sprintf("%s LIMIT %d, %d", $test, $startRow_test1, $maxRows_test1);
$test1 = mysql_query($query_limit_test, $woodside) or die(mysql_error());
$result = mysql_query($test) or die(mysql_error());
$row_test = mysql_fetch_assoc($result);
$totalRows_test = mysql_num_rows($result);
$row_test1 = mysql_fetch_assoc($test1);
?>

Im clearly missing something because its not working??? Im not sure how to assign the value. Im just looking to get the $value to be either 1 or !1...to bring up all records assigned to CID 1 and all records that arnt.... that can be detemined in options of drop down....

Please help 🙂

Thank You

[halojoy]

within form
you can have:
-input
-select
each of these returns a value with some: name="orderby"

for select .. can be 1 of all options of this select

<select name="orderby" onchange="if(this.value != '-') document.reorder.submit();">
                  <option "-">Select one...</option>
                  <option value="FoodID">ID</option>
                  <option value="FoodName">Name</option>
                  <option value="AnimalTypeID">Food Type</option>
                  <option value="Username">User</option></select>

<select name="cid_value" onchange="if(this.value != '-') document.reorder.submit();">
                  <option "-">Select CID...</option>
                  <option value="1">CID is 1</option>
                  <option value="0">CID is other</option></select>

if (isset($_GET['orderby'])) { 
     $orderby = $_GET['orderby']; 
}else{
     $orderby = "FoodID"; 
}

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

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

select is used for when we have more than 2 options.
If only 2 choices we can better use RADIO BUTTONS

<input type="radio" name="cid_value" value="1"> CID = 1
<br>
<input type="radio" name="cid_value" value="0"> CID other

more info here, about all types of <form> elements we can use,
along with examples and html code:
http://www.w3schools.com/html/html_forms.asp

regars 🙂
halojoy

[drax007]

Hi there,

Thanks for reply, thats brilliant, works with both 2nd as drop down and as radio button...Thought radio button perhaps best as there is only need to choose between either record 1 or any other records (but NOT 1)

With that in mind would it be possible to have only 1 radio option that IF checked would search all records for 1 and IF nothing is checked to search all records other than 1??

Thanks very much for your help 🙂