<? How to use all values by select option from drop-box as ALL

bn1995

I have form contains 4 Fields ( 2 drop-down box ,2 Fields)

1- sex : ALL,MALE,FEMALE
2-agemin --->minimum age
3-agemax--->maximum age
4-country:ALL,USA,UK,FRANCE,ITALY

how to make query that when for example I select
sex : ALL
agemin: 10
agemax:14
country: USA

the result will be all male and female profiles in USA between 10 and 14 (10,11,12,13,14)

the difficult thing is how to setup ALL so when you select ALL will give you everything in that field either sex or country .
if I select ALL in country will give me all people from USA,UK,FRANCE AND ITALY

how can I do that and setup the "all".

thanks in advance

cahva

In your query you can ignore the sex and country if its set to all. Just dont add them to your query:

// This will give you all rows with both genders and all countries with age between 10 and 14
SELECT sex,age,country FROM table WHERE age BETWEEN 10 AND 14;

// This will give you females from usa with age between 10 and 14
SELECT sex,age,country FROM table WHERE country='usa' AND sex='female' AND age BETWEEN 10 AND 14;

Codewise it should go something like this:

$where = array(); // initialize

$genders = array('female','male');
$countries = array('USA','UK','FRANCE','ITALY');

if (in_array($sex,$genders))
{
	$where[] = "sex = '{$sex}'";
}

if (in_array($country,$countries))
{
	$where[] = "country = '{$country}'";
}

$where[] = 'age BETWEEN '.$agemin.' AND '.$agemax;

$sql = "SELECT sex,age,country WHERE ".implode(' AND ',$where);

bn1995

Thank you alot you saved my life ! 4 days working to find sloution for this one .

here my code any one can use it

<html >

<head>

<title>New Page 2</title>
</head>

<body>

<form method="POST" action="zo.php">

<p><input type="submit" value="Submit" name="B1"></p>
<p>&nbsp;</p>
<p><select size="1" name="sex">
<option>-</option>
	<option value="female">female</option>
	<option value="male">male</option>
	</select></p>
	<p><select name="nationality" size="1" maxlength="100" dir="rtl">
	<option>-</option>
				<option value="usa">usa</option>
<option value="uk">uk</option>
<option value="italy">italy</option>
<option value="France">France</option>
<option value="Canada">Canada</option>
			</select></p>
<p><select name="countrystaying" size="1" maxlength="100" dir="rtl">
	<option>-</option>
				<option value="usa">usa</option>
<option value="uk">uk</option>
<option value="italy">italy</option>
<option value="France">France</option>
<option value="Canada">Canada</option>
			</select></p>
<p><select name="countryorgin" size="1" maxlength="100" dir="rtl">
	<option>-</option>
				<option value="usa">usa</option>
<option value="uk">uk</option>
<option value="italy">italy</option>
<option value="France">France</option>
<option value="Canada">Canada</option>
			</select></p>

<p>&nbsp;<font color="#0000FF"><input type="text" name="agemin" size="2" value="0"></font>&nbsp;
</p>
<p>&nbsp;<font color="#FF0000"><input type="text" name="agemax" size="2" value="80"></font></p>
<p>&nbsp;</p>
</form>

</body>

</html>


<?php

include('conit.php');


include("MySQLPagedResults.class.php");// you will find it in db libr. in this site.


$paging_results = new MySQLPagedResults("SELECT count(*) FROM 

users ","page","",5,10,">>>","Previous","Next","<<<"," | ");

$first_nav = $paging_results->getFirstNav();
$prev_nav = $paging_results->getPrevNav();
$next_nav = $paging_results->getNextNav();
$last_nav = $paging_results->getLastNav();
$pages_nav = $paging_results->getPagesNav();
$offset = $paging_results->currentOffset();
$results_per_page = $paging_results->results_per_page;
$current_page = $paging_results->current_page;
$total_pages = $paging_results->totalPages();
$start_number = $paging_results->getResultNumbersStart();
$end_number = $paging_results->getResultNumbersEnd();


$where = array(); 

$agemin=$_POST['agemin'];
$agemax=$_POST['agemax'];

$genders = array('male','female'); 
$countrystayings = array('usa','uk','France','italy','Canada');
$contryorgins = array('usa','uk','France','italy','Canada');
$nationalitys = array('usa','uk','France','italy','Canada');

if (in_array($sex,$genders)){ 
$where[] = "sex = '{$sex}'"; 
} 


if (in_array($countrystaying,$countrystayings)) 
{ 
    $where[] = "countrystaying= '{$countrystaying}'"; 
} 


if (in_array($contryorgin,$contryorgins)) 
{ 
    $where[] = "contryorgin = '{$contryorgin}'"; 
} 






if (in_array($nationality,$nationalitys)) 
{ 
    $where[] = "nationality = '{$nationality}'"; 
} 



$where[] = 'age BETWEEN '.$agemin.' AND '.$agemax; 


$res = "SELECT sex,nationality,age,status,countrystaying,countrystaying,id,username,city,religon  FROM users WHERE ".implode(' AND ',$where); 


$query = mysql_query($res);  

$counter = 1;


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




$id = $row['ID'];
$user =  $row['username'];

echo '<p><b><font size=5 color=#333300> -'.$counter.':<IMG width=50px height=50px SRC=http://localhost/zwaj/reviews/'.$row['piclink'].'> </font></b></p><b><font size=4><a href=profile.php?id='.$id.'>*username :&nbsp'.$user.'</font></b></a>'
;

echo '<b>&nbsp&nbsp, sex :&nbsp&nbsp'.$row['sex'].'&nbsp&nbsp,&nbsp&nbsp&nbsp&nbsp age:&nbsp&nbsp'.$row['age'].' &nbsp,&nbsp&nbsp&nbsp&nbsp nationality:&nbsp&nbsp'.$row['nationality'].'<p></p>&nbsp&nbsp,&nbsp&nbsp&nbsp&nbsp countryorgin :&nbsp&nbsp'.$row['countryorgin'].'&nbsp&nbsp,&nbsp&nbsp&nbsp&nbsp country staying:&nbsp&nbsp'.$row['countrystaying'].'</b>';




print "
<hr>";
$counter++;






}



if($counter>5 OR $pages_nav!=1){
echo "<p align=center><b>Page:</b> $current_page of $total_pages &nbsp;|&nbsp; 

<b>Results:</b> $start_number - $end_number<br>";
echo "$first_nav $prev_nav $pages_nav $next_nav $last_nav</p>";
}

else {

echo "";

}

?>

but I have problem if you can solve it it will be so great wanna to limit no# of resualts per page and order it by my pev. code was like .

$res="SELECT * FROM USERS ORDER BY '$orderby' ASC WHERE 
countrystaying='$countrystaying' AND sex='$sex' AND 
countryorgin='$countryorgin'  AND nationlitay='$nationlitay'  LIMIT 
$offset,$results_per_page";

how can I add these LIMIT $offset,$results_per_page ,,,,,and,,,,,ORDER BY '$orderby' ASC to this one :

$res = "SELECT sex,nationality,age,status,countrystaying,countrystaying,id,username,city,religon  FROM users WHERE ".implode(' AND ',$where);

thank you in advance.

cahva

Its just matter of adding those to the end:

$res = "SELECT sex,nationality,age,status,countrystaying,countrystaying,id,username,city,religon  FROM users WHERE ".implode(' AND ',$where)." ORDER BY $orderby ASC LIMIT $offset,$results_per_page";

bn1995

Hi Cahva,

actually I did that and getting error for that I asked

$Q=implode(' AND ',$where);

$sql = "SELECT * FROM users WHERE '$Q'  LIMIT '$offset','$results_per_page' "; 


$query = mysql_query($sql);

error with this one

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in

when I removed '' '' from $results and $offset

error will be

mysql_num_rows(): supplied argument is not a valid MySQL result resource

is there any way to add it or to fix these errors ,,, I was trying to solve it but no luck .

note :I tried

".implode(' AND ',$where)." ORDER BY $orderby ASC LIMIT $offset,$results_per_page";

too . same error.

cahva

Don use quotes around the limit values.
Print the sql and mysql_error to debug:

$Q=implode(' AND ',$where);

$sql = "SELECT * FROM users WHERE '$Q'  LIMIT $offset,$results_per_page";

echo '<pre>'.$sql.'</pre>';
$query = mysql_query($sql);
echo mysql_error();

That will print the actual sql and the error that it gives.

bn1995

Hi again ,

actually to know what is going on as u can see I put limition start from 0 for 5 records each page ... am using pagining code ,,, this paging code is working fine with another query which doesnt have array things normal query ,but with this one I got the frist page 5 records but when I move to next page I got this error

SELECT * FROM users WHERE age BETWEEN AND LIMIT 5,5

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND LIMIT 5,5' at line 1
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

rest records dont move to next page .

I have another code that code works fine with paging but it has problem that I cannot add age between agemin and agemax .... take a look

function ee( $var )
{
return mysql_real_escape_string( $var );
} 

function add_sql( $field , $var )
{
global $res;
if ( !empty( $_POST["$var"] ) ) {
if ( $_POST["$var"] != "-1" )  //   <=== -1 =ALL 
$res .= $field . "='" . ee( $_POST["$var"] ) . "' AND ";
} 
} 

$res = null;


add_sql( "sex","sex" ); 
add_sql( "nationality", "nationality" );
add_sql( "contryorgin", "contryorgin" );
add_sql( "countrystaying", "countrystaying" );


$fields = array( "username"=>"username",....... etc);



if ( !empty( $res ) ){
$Q = " WHERE " . substr( $res , 0, -4 );

}
else
$Q = "";



$res="SELECT * FROM USERS $Q LIMIT $offset,$results_per_page";

thanks in advance .

bn1995

the last error

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND LIMIT 5,5' at line 1

this error after I press next page frist page working but to move rest resualt to next page

and when I put '' around $Q I will get no error no records .

bn1995

I found solution for this one the issue is solved .
Thank you for cahva.

we have to add this

if(isset($_POST['agemax']) && is_numeric($_POST['agemax']))
$agemax = $_POST['agemax'];
else
$agemax = 80;

if(isset($_POST['agemin']) && is_numeric($_POST['agemin']))
$agemin = $_POST['agemin'];
else
$agemin = 0;