problems with empty search fiels and row count

nade93

Hi all

really stuck and in desperate need of help!

thanks greensmurf, but thats not what im after

basically, trying to create a results page from a form and need two things that am really stuck on.

If the form value ="" then i need it to return all the results in that table field.
I need to then take those results and count certain values. For example, how many case_open fields have the "yes" value and then return that value as a number

Code for part 1.

$ethnic = $_REQUEST['ethnic'];
$gender = $_REQUEST['gender'];
$referral = $_REQUEST['referral'];
$age = $_REQUEST['age'];
$month = $_REQUEST['month'];
$year = $_REQUEST['year'];

$query  = "SELECT * from tbl WHERE";

if ((!empty($_POST['ethnic']))&&($_POST['ethnic'] != 'all')) { $query .= " ethnic_background like '". addslashes($_POST['ethnic'])."%' "; }

if ((!empty($_POST['gender']))&&($_POST['gender'] != 'all')) { $query .= " and gender like '". addslashes($_POST['gender'])."%' "; }


if ((!empty($_POST['age']))&&($_POST['age'] != 'all')) { $query .= " and age_range like '". addslashes($_POST['age'])."%' "; }

if ((!empty($_POST['month']))&&($_POST['month'] != 'all')) { $query .= " and date_received_month like '". addslashes($_POST['year'])."%' "; }

if ((!empty($_POST['year']))&&($_POST['year'] != 'all')) { $query .= " and date_received_year like '". addslashes($_POST['year'])."%' "; }

$result = mysql_query($query);
$numrows=mysql_num_rows($result);
 if ($numrows == 0)
  {

  echo "<div class=\"heading\">Sorry, your search returned no results. Please go back and try again!</div>";

  }

I then run a loop i.e.

 <? while ($row = mysql_fetch_assoc($result)) {

 ?>

Then i need to return the results below in a table i.e.

<table><tr>
                <td valign="top" height="30" style="border-bottom:1px #ccc solid;" class="bodytext_table"><div align="left"> Number of IMHA cases opened</div></td>
		<td style="border-bottom:1px #ccc solid;"> <div align="right" style="color:#993366; font-size:12px; font-weight:600;">number here</div></td> </tr></table>

can someone shed some light please, its really urgent and need it tonight and have searched high and low for answers!!!

any help appreciated!

thanks

dagon

are the form values from a dropdown select box or text input field or?

nade93

dagon;10927480 wrote:
are the form values from a dropdown select box or text input field or?

hey

they are from a drop down box

so for example (please select would be classed as the empty value)

 <select name="gender" id="gender" style="width:100%;">
              <option value="Male">Male</option>
              <option value="Female">Female</option>
              <option value=" " selected="selected">Please Select</option>
            </select>

dagon

if you make it

<option value="" selected="selected">Please Select</option>

no space, then it will be empty and question 1 is taken care of.

dagon

for 2, in the while loop add something like

if($row['case_open']=='yes'){
$total_case_open++;
}

nade93

its is now producing this error (it should be producing all the results

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /mounted-storage/home78c/sub006/sc43830-XXAV/iant.org.uk/admin/IMHAresults.php on line 40
Results

Sorry, your search returned no results

Please go back and try again!

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /mounted-storage/home78c/sub006/sc43830-XXAV/iant.org.uk/admin/IMHAresults.php on line 55

dagon

well if all the fields are are all empty there will still be a "WHERE" in the search query which will make it invalid.

nade93

yeah but how do i exlude that?

$query  = "SELECT * from forms_IMHA WHERE ";

if ((!empty($_POST['ethnic']))&&($_POST['ethnic'] != 'all')) 
{ $query .= " ethnic_background= '". addslashes($_POST['ethnic'])."%' "; }

if ((!empty($_POST['gender']))&&($_POST['gender'] != 'all')) 
{ $query .= " and gender='". addslashes($_POST['gender'])."%' "; }

if ((!empty($_POST['age']))&&($_POST['age'] != 'all')) 
{ $query .= " and age_range='". addslashes($_POST['age'])."%' "; }

if ((!empty($_POST['month']))&&($_POST['month'] != 'all')) 
{ $query .= " and date_received_month='". addslashes($_POST['year'])."%' "; }

if ((!empty($_POST['year']))&&($_POST['year'] != 'all')) 
{ $query .= " and date_received_year='". addslashes($_POST['year'])."%' "; }

nade93

also, this way, will the empty fields produce just the results searched for from the specific drop down lists or will it just return everything?

nade93

nade93;10927488 wrote:

yeah but how do i exlude that?

$query  = "SELECT * from forms_IMHA WHERE ";

if ((!empty($_POST['ethnic']))&&($_POST['ethnic'] != 'all')) 
{ $query .= " ethnic_background= '". addslashes($_POST['ethnic'])."%' "; }

if ((!empty($_POST['gender']))&&($_POST['gender'] != 'all')) 
{ $query .= " and gender='". addslashes($_POST['gender'])."%' "; }

if ((!empty($_POST['age']))&&($_POST['age'] != 'all')) 
{ $query .= " and age_range='". addslashes($_POST['age'])."%' "; }

if ((!empty($_POST['month']))&&($_POST['month'] != 'all')) 
{ $query .= " and date_received_month='". addslashes($_POST['year'])."%' "; }

if ((!empty($_POST['year']))&&($_POST['year'] != 'all')) 
{ $query .= " and date_received_year='". addslashes($_POST['year'])."%' "; }

also this is producing no results when i search all terms that i know exist in the database

dagon

nade93;10927490 wrote:
also, this way, will the empty fields produce just the results searched for from the specific drop down lists or will it just return everything?

try it and see, you will also need to take care of the "and" if the first is empty the query will be " ... WHERE AND ... "

nade93

dagon;10927492 wrote:
try it and see, you will also need to take care of the "and" if the first is empty the query will be " ... WHERE AND ... "

sorry it just doesnt work, trying it differnet ways and not doing anything

the AND statement produces no results and the OR statement produces all results no matter what i search

just getting no where fast with it