Arrays, Checkboxes and a Database

[oblivion2010]

Hi, I'm attempting to create a Dog Rehoming website.

The user needs to select their preferred Breed of dog and location of shelter.

They will select these prefrecnes via checkboxes.

 

The website I'm working on:

http://hermes.hud.ac.uk/u0656983/Dog%20Search/Dog%20Website/

 

On my database I have three tables, Dogs, Breeds, and Shelters.

I want my Results.php page to show a list of dogs that match the users Breed and Shelter prefrences, whats the best way to do this?

 

I've also attached the two php file I'm using, thanks for any help.

 

 

[attachment deleted by admin]

[Infinitive]

OK if you only have those 3 tables, you need a way to join them.  It seems to me the best way to do that would be right in the Dogs table.  Let's assume your date looks something like this:

 

SHELTERS
ShelterID    ShelterName
------------------------------------
1 Workingham
2 Jersey
3 London
4 Scotland

BREEDS
BreedID  BreedName
-------------------------------------
1 Labrador
2 Spaniel
3 Shepherd
4 Terrier

DOGS
DogID    DogName     BreedID   ShelterID
---------------------------------------------------
1     Rover    3    4
2     Fido       2    2
3    Benji      4    4
4    King       1    3

And so on.  Noe that the "BreedID" and "ShelterID" columns were re-used in the DOGS table.  They are what's known as "primary keys", and are used to uniquely tell which row is which in a table.  When you use them in a different table, they are "foreign keys" because they link back.

 

So in this case, I can see that dog 4 "King" is breed 1 (Labrador) at shelter 3 (London).

 

(And of course you can have as much other information in those tables as you want.  Shelter address, dog's weight / age / arrival date, whatever you need.  This is just the essential stuff.

 

In your case, what you want to do is take the numbers associated with the breed and shelter they pick.  And then look in the Dogs table:

 

SELECT DogID, DogName

FROM DOGS

WHERE BreedID = 2 AND Shelter = 4;

 

Hope that helps.

[Q695]

add a "shelter_id" and a "dog_id" to do a mysql join like:

 

SELECT * WHERE table.shelter_id=shelters.shelterid AND table.dog_id=breeds.breedid

[oblivion2010]

Thanks for the replies. I already have a Dog ID and Shelter ID in my dogs table.

My problem is not having the knowledge to link up the checkboxes to the database.

 

I have my varibles checking to see which checkboxes are selected:

 
$chosenShelters=$_POST["shelter"];
$chosenBreeds=$_POST["breed"]; 

 

and then I have the PHP coding

 

$query= "SELECT * WHERE dogs.shelter=shelters.shelterid AND table.dog_id=breeds.dog_breed"

$result=mysql_query($query);
$row = mysql_fetch_array($result);

foreach($chosenShelters as $chosenShelter){
 echo $row['dog_name']."<br />";
}

mysql_close();

 

This code is attemping to show the name of the dog which has the same shelter ID and dog ID as the user selected. Though I don't know how to link the two pieces of code up.  Thanks again for the help.

[Q695]

Do a mysql join.

http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

[dave_2008]

Well, here is some SQL that works. If you get a mistake let me know.

(Read the comments, you'll figure out ...)

 

 

DROP TABLE IF EXISTS dogs;
DROP TABLE IF EXISTS breeds;
DROP TABLE IF EXISTS shelters;

create table breeds(
id INT NOT NULL AUTO_INCREMENT,
breedname VARCHAR (50),
PRIMARY KEY(id)
);

create table shelters(
id INT NOT NULL AUTO_INCREMENT,
sheltername VARCHAR (50),
PRIMARY KEY(id)
);

create table dogs(
id INT NOT NULL AUTO_INCREMENT,
dogname VARCHAR (50),
breedid SMALLINT NOT NULL REFERENCES breeds(id),
shelterid SMALLINT NOT NULL REFERENCES shelters(id),
PRIMARY KEY(id)
);

INSERT INTO breeds (id, breedname) VALUES (1, 'Labrador Retriever');
INSERT INTO breeds (id, breedname) VALUES (2, 'Cocker Spaniel');
INSERT INTO breeds (id, breedname) VALUES (3, 'Shepherd Dog');
INSERT INTO breeds (id, breedname) VALUES (4, 'Staffordshire Bull Terrier');
INSERT INTO breeds (id, breedname) VALUES (5, 'Golden Retriever');
INSERT INTO breeds (id, breedname) VALUES (6, 'Boxer');
INSERT INTO breeds (id, breedname) VALUES (7, 'Poodle');

INSERT INTO shelters (id, sheltername) VALUES (1, 'Binfield Dog Rescue (Wokingham)');
INSERT INTO shelters (id, sheltername) VALUES (2, 'Jersey SPCA Animals Shelter');
INSERT INTO shelters (id, sheltername) VALUES (3, 'Four Legged Friends - (London)');
INSERT INTO shelters (id, sheltername) VALUES (4, 'Bandeath Stray Dog Shelter (Scotland)');
INSERT INTO shelters (id, sheltername) VALUES (5, 'Animals In Need');
INSERT INTO shelters (id, sheltername) VALUES (6, 'RSPCA York Animal Home');
INSERT INTO shelters (id, sheltername) VALUES (7, 'RSPCA South Yorkshire Animal Centre');

INSERT INTO dogs (id, dogname, breedid, shelterid) VALUES (1, 'Tommy', 6,1);
INSERT INTO dogs (id, dogname, breedid, shelterid) VALUES (2, 'Timmy', 6,2);
INSERT INTO dogs (id, dogname, breedid, shelterid) VALUES (3, 'Jewel', 7,3);
INSERT INTO dogs (id, dogname, breedid, shelterid) VALUES (4, 'Pearl', 6,4);
INSERT INTO dogs (id, dogname, breedid, shelterid) VALUES (5, 'Roger', 7,6);
INSERT INTO dogs (id, dogname, breedid, shelterid) VALUES (6, 'Jonny', 6,7);
INSERT INTO dogs (id, dogname, breedid, shelterid) VALUES (7, 'Jacky', 7,1);
INSERT INTO dogs (id, dogname, breedid, shelterid) VALUES (8, 'Cooky', 1,2);
INSERT INTO dogs (id, dogname, breedid, shelterid) VALUES (9, 'Puddly', 2,3);
INSERT INTO dogs (id, dogname, breedid, shelterid) VALUES (10, 'King', 2,3);
INSERT INTO dogs (id, dogname, breedid, shelterid) VALUES (11, 'Rover', 3,4);
INSERT INTO dogs (id, dogname, breedid, shelterid) VALUES (12, 'Bobby', 1,5);

--------------------------------
/***** Query *****/
SELECT dogs.dogname, dogs.id, breeds.breedname, shelters.sheltername
FROM dogs, breeds, shelters 

WHERE 1=1  /* to make adding conditions starting with "AND" easier */

/* -begin relating- (just to ensure that table relations are specified) */ 
AND dogs.breedid = breeds.id
AND dogs.shelterid = shelters.id
/* -end relating- */ /* wait! not finished yet ... */

/* -- start of sub condition for breeds -- */
AND ( 0=1  /* to make adding conditions starting with "OR" easier */
OR dogs.breedid = 3
OR dogs.breedid = 6
)
/* -- end of sub condition for breeds -- */

/* -- start of sub condition for shelters -- */
AND ( 0=1  /* to make adding conditions starting with "OR" easier */
OR dogs.shelterid = 1
OR dogs.shelterid = 4
)
/* -- end of sub condition for shelters -- */

;  /* -- end of query -- */

 

It's probably not elegant (like a join) but anyway...

[dave_2008]

And here's the PHP code to clarify:

/* -- start of query -- */

$sql = "SELECT dogs.name, dogs.id, breeds.name, shelters.name
FROM dogs, breeds, shelters 

WHERE 1=1 /* to make adding 'AND' conditions easy */

/* -begin relating- (just to ensure that table relations are specified) */ 
AND dogs.breedid = breeds.id
AND dogs.shelterid = shelters.id
/* -end relating- */  /* wait! not finished yet ... */ " ;

/* -- start of sub condition for breeds -- */
$sql .= " AND ( 0=1 "; /* to make adding 'OR' conditions easy */
$temp = "";
foreach ($_POST['breeds'] as $chosen_breed_id) {
  $temp = " OR dogs.breedid = '".$chosen_breed_id ."' \n "
}
$sql .= $temp;
$sql .= " ) \n ";
/* -- end of sub condition for breeds -- */

/* -- start of sub condition for shelters -- */
$sql .= " AND ( 0=1 "; /* to make adding 'OR' conditions easy */
$temp = "";
foreach ($_POST['shelters'] as $chosen_shelter_id) {
  $temp = " OR dogs.shelterid = '".$chosen_shelter_id ."' \n "
}
$sql .= $temp;
$sql .= " ) \n ";
/* -- end of sub condition for shelters -- */
$temp = "";

$sql .= " ;"
/* -- end of query -- */

 

HTH

( I hope I got your question right )

[oblivion2010]

Wow, thanks Dave!

 

I appreciate your effort, however I'm getting a parse error:

 

'Parse error: parse error, unexpected '}' in /spare/apache2/htdocs/stud/u0656983/Dog Website/results.php on line 49'

 

I can't understand why it claims it's unexpected, any ideas?

 

[oblivion2010]

$sql .= " AND ( 0=1 "; /* to make adding 'OR' conditions easy */
$temp = "";
foreach ($_POST['breed'] as $chosen_breed_id) {
  $temp = " OR dogs.breedid = '".$chosen_breed_id ."' \n "
  } 

 

The last line is line 41.

[Q695]

run each breed individually after the breed type is loaded.

 

You have an extra } somewhere, or are lacking a {