How to Query Records

janey

I've been playing with PHP for a couple fo weeks now. My primary interest has been managing a mysql db with php via html forms.

I've been able to add data, delete data and modify from my db with no problem. However, the one area I' m having problem with is querying so only certain records are displayed.

For instance, I have a database (dogs) that contains information about dogs up for adoption. One column in the db is "breed". Using "SELECT * FROM dogs", I am able to display all the records from the db. What I would like to do is have a dropdown where the user could limit the view to only dogs of a certain breed.

Now, I know I could create a new php page and use "SELECT * FROM dogs WHERE breed= 'collie'", but is that the most efficient way to do it since I would have to create a php doc for each breed? Isn't there some way to use a variable (in the url?) to limit the display?

Thanks!!

yelvington

Of course there is; it's done every time you look at a db-generated page on this site.

<?

$query = 'select * from dogs';
if (isset($HTTP_GET_VARS['breed']))
$query = " where breed='" . $HTTP_GET_VARS['breed'] . "'";

echo $query;
?>
Make a web page out of that code and call it with/without ?breed=dachsund as an argument.

Now some "advanced" suggestions.

You should populate your dropdown selector with a database query, rather than hard-coding the breed options in PHP. You've apparently designed your database as a single table, so can do that with SELECT UNIQUE. However ...

Your table design reflects a common mistake. Ideally you should have a table of dogs, and a table of breeds, with breed-<dog as a one-to-many relationship.

Here's why. What if you wanted to print out information about the breed -- how big does it get, how much food does it typically consume, what's the latin name, is it good with children, does it shed, et cetera? For that matter, what happens when people start misspelling "dachshund" when the data is entered into your tables? A separate breed table, linked to the dog table with a relational reference (foreign key) will prevent that.

Think about a query like this: Find me all the dogs that are good with children, have been here more than 14 days, are female, and grow to be 20-30 pounds in weight. Common breed characteristics belong in the common breed table; specific dog characteristics belong in the dog table.

janey

Okay, I've got the dropdown working (it's amazing what a cup of coffee will do for you!), but I have another question:

I may decide to just list all the breeds as links rather than using dropdowns. How would I write the link?

Let's say the page I use to display all the records is called dogs.php with a select statement of "SELECT * FROM dogs WHERE breed= '$breed' ORDER BY color ASC". I thought I could use a link like <a href="dogs.php?collie=$breed, but I don't know exactly how to set it up.

If anyone has an idea or knows of a good article on the subject, that would be great!

Thanks!!

janey

Thanks, yelvington! It works perfectly!

janey

If I am using two dropdowns to query, let's say "breed" and "age", how would I make one of the options "all"?

For instance, I could then select the breed "collie" but leave the age selection at "all" in case age doesn't matter.

On my result page, I have been using "SELECT * FROM dogs WHERE breed= '$breed' AND age='$age'

This works fine as long as I select both parameters, but if one of the parameters doesn't matter to me, how do I ignore it?

mrmufin

Originally posted by janey
If I am using two dropdowns to query, let's say "breed" and "age", how would I make one of the options "all"?

For instance, I could then select the breed "collie" but leave the age selection at "all" in case age doesn't matter.

On my result page, I have been using "SELECT * FROM dogs WHERE breed= '$breed' AND age='$age'

This works fine as long as I select both parameters, but if one of the parameters doesn't matter to me, how do I ignore it?


// Create an array to store conditionals in:
$wheres = array();

// Start building yer query:
$q = "SELECT * FROM dogs";

// Check your HTTP post vars for a breed:
if(strlen($HTTP_POST_VARS["breed"]) > 0) {
	// stick the breed into your conditions array:
	$wheres["breed"] = $HTTP_POST_VARS["breed"];
}

// Check the HTTP post vars for an age:
if(strlen($HTTP_POST_VARS["age"]) > 0) {
	// stick the age into your conditionals array:	
	$wheres["age"] = $HTTP_POST_VARS["age"];
}


// count the conditions
$n = count($wheres);

if($n > 0) {
	// start appending your query to accomodate the conditions:
	$q .= " WHERE";
	$i = 1;
	while(list($k,$v)=each($wheres)) {
		$q .= " $k=\"$v\"";
		if($i < $n) {
			$q .= " AND";
		}
		$i++;		
	}
	// echo the query to make sure its what
	// you want:
	echo $q;
}

This might be overkill, but's one step closer to being able to accomodate more complex queries....