Trouble selecting entries from MySQL database - PHP

theanteater · August 13, 2010

Hello. Many thanks for your help.

I am writing a PHP/MySQL dating-site and have hit a programming impass.

I have a database full of members and a search form consisting of checkboxes.

So to search, a member ticks say...gender: female; age: 21,22,23,24,25,26; height: 5'4",5'5",5'6",5'7"; county: cornwall,devon,somerset

How can a run a check on the database selecting all entries that fall into the selected criteria. For example a 23 year old female of 5'5" living in Cornwall and a 26 year old female of 5'4" living in Somerset?

The key index of my database is 'id' and the fields are: age,height,county

The names of the form checkboxes are:

Gender: male, female; Age: 21,22,23,24 etc; Height: 5_4,5_5,5_6 etc; county: cornwall,devon etc

JonnoTheDev · August 13, 2010

SELECT * FROM members WHERE age='21' AND gender='male' AND height='5'

etc

Simple as testing what the values of the text boxes are or whether checkboxes are ticked and then build up a query similar to the above

kickstart · August 13, 2010

Hi

How are the databases tables structured? Do you have a numeric field for the age, or do you have a numeric field that contains and ID field that points to their age?

Assuming you have an age, pass the check boxes back with values gives the ages requested. Loop through these and save them to an array. So you might land up with an array called $AgesRequested = array(22,23,24,25,26).

Then use:-

$sql = 'SELECT *

FROM People

WHERE Age IN ('.explode(',',$AgesRequested).')';

You can do similar coding to give the line to check the areas in the same SQL.

All the best

Keith

theanteater · August 13, 2010

Thanks Keith. I think that is the way of doing it.

How would I build the array from the checkbox data? Using isset?

$array_ages = array();

if (isset($22){$array_ages["22"];}

if (isset($23){$array_ages["23"];} etc etc

Do the same with the height and county etc then:

$sql = 'SELECT *

FROM People

WHERE age IN ('.explode(',',$array_ages).' AND WHERE height IN '.explode(',',$array_heights).' AND WHERE county IN '.explode(',',$array_countys).')';

Is that looking right?

Cheers, Nigel

kickstart · August 13, 2010

Hi

Assuming your form is something like this

<html>
<head>
</head>
<body>
	<form>
		Ages<br />
		20 <input type='checkbox' name='age_20' value='20' /><br />
		21 <input type='checkbox' name='age_21' value='21' /><br />
		22 <input type='checkbox' name='age_22' value='22' /><br />
		23 <input type='checkbox' name='age_23' value='23' /><br />
		24 <input type='checkbox' name='age_24' value='24' /><br />
		25 <input type='checkbox' name='age_25' value='25' /><br />
		26 <input type='checkbox' name='age_26' value='26' /><br />
		27 <input type='checkbox' name='age_27' value='27' /><br />
		28 <input type='checkbox' name='age_28' value='28' /><br />
		29 <input type='checkbox' name='age_29' value='29' /><br />
		30 <input type='checkbox' name='age_30' value='30' /><br />
		Area<br />
		North <input type='checkbox' name='area_1' value='north' /><br />
		South <input type='checkbox' name='area_2' value='south' /><br />
		East <input type='checkbox' name='area_3' value='east' /><br />
		West <input type='checkbox' name='area_4' value='west' /><br />
		Height<br />
		5'4" <input type='checkbox' name='height_1' value='1' /><br />
		5'5" <input type='checkbox' name='height_2' value='2' /><br />
		5'6" <input type='checkbox' name='height_3' value='3' /><br />
		5'7" <input type='checkbox' name='height_4' value='4' /><br />
		<input type='submit' />
	</form>
</body>
</html>

Then use something like this (not tested so probably some typos)

<?php

$AllowableCounties = array('Somerset','Dorset','Wiltshire','Sussex');
$AllowableHeights = array(0=>"5'4\"",1=>"5'5\"",2=>"5'6\"",3=>"5'7\"",);

$AgeArray = array();
$HeightArray = array();
$CountyArray = array();

foreach($_REQUEST AS $Field=>$Value)
{
$FieldSplitName = explode('_',$Field);
switch ($FieldSplitName[0])
{
	case 'age' :
		if (is_numeric($Value))
		{
			$AgeArray[] = intval($Value);
		}
		break;
	case 'county' :
		if (in_array($Value,$AllowableCounties)
		{
			$AreaArray[] = mysql_real_escape_string($Value);
		}
		break;
	case 'height' :
		if (array_key_exists($Value,$AllowableHeights)
		{
			$HeightArray[] = $Value;
		}
		break;
	default:
		// Who cares
		break;
	}
}

$sql = "SELECT *
FROM People
WHERE age IN (".implode(',',$AgeArray)." )
AND  height IN (".implode(',',$HeightArray).") 
AND county IN ('".implode("','",$CountyArray)."')";

?>

This is assuming that you are storing the height as a reference rather than directly. If I were doing this I would probably do that for both county and height, having a separate table for each, with each person being linked to a row on each of these.

All the best

Keith

theanteater · August 14, 2010

Hi Keith (or who sees this),

Ok, thanks. Here is what I have; one set of checkboxes to start:

</tr>

<td>Female: </td>

</tr>

</table>

Then:

$AllowableGenders = array('male','female');

$GenderArray = array();

foreach($_REQUEST AS $Field=>$Value){

$FieldSplitName = explode('_',$Field);

switch ($FieldSplitName[0]) {

case 'gender' :

if (in_array('$Value',$AllowableGenders)) {

$GenderArray[] = mysql_real_escape_string($Value); }

break;

default:

// Who cares

break; }}

$check_gender = implode(',',$GenderArray);

print_r($GenderArray);

$qry = "SELECT * FROM register WHERE gender IN ('$check_gender') ORDER BY age";

$qrys = mysql_query($qry)or die ("Error Finding Members: ". mysql_error());

$how_many = mysql_num_rows($qrys);

Unfortunately this produces zero results even when both male and female checkboxes are ticked. (print_r shows an empty array) It should find every member as each is either a male or female.

What have I done wrong please???

Thanks Nigel

kickstart · August 14, 2010

Hi

Think you just have a minor typo. You have single quotes around $value on the following line which will cause the problem you are getting (ie, this is going to look for something in the gender array that is equal to $Value rather than equal to the value of the variable $Value).

if (in_array('$Value',$AllowableGenders))

Can't see anything else for now.

All the best

Keith

theanteater · August 14, 2010

Thanks again Keith, I didn't spot that,

Correcting that produces the following results:

print_r($GenderArray); // Array ( [0] => male [1] => female )

echo $check_gender; // male,female

Results: 0

Still no results even though the array looks correct now. Checked the field name and case in the database and all looks well. ??

wildteen88 · August 14, 2010

You're not quiet imploding the $genderArray correctly

$check_gender = implode(',',$GenderArray);

The above will implode the array to "male,female"

You'll want to implode the array so it produces the string "'male','female'". The MySQL IN() clause requires each value to be wrapped in quotes. Otherwise it'll be trying to match the gender 'male,female'. Not the gender being either 'male' or 'female'.

So change the above line to

$check_gender = sprintf('\'%s\'', implode('\',\'',$GenderArray));

Now change your query to

$qry = "SELECT * FROM register WHERE gender IN ($check_gender)  ORDER BY age";

theanteater · August 14, 2010

Solved! Many thanks, and Keith, all your help much appreciated.

Sign In

Trouble selecting entries from MySQL database - PHP

Recommended Posts

theanteater

Link to comment

Share on other sites

JonnoTheDev

Link to comment

Share on other sites

kickstart

Link to comment

Share on other sites

theanteater

Link to comment

Share on other sites

kickstart

Link to comment

Share on other sites

theanteater

Link to comment

Share on other sites

kickstart

Link to comment

Share on other sites

theanteater

Link to comment

Share on other sites

wildteen88

Link to comment

Share on other sites

theanteater

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information