Query With Checkbox

[frsome]

Hi,

I'm new to php and trying to make a form query that uses check boxes. What I want is to show some names like dog, cat, fish with checkboxes by them. if the check boxes are marked, any place in the table that has data in those fields the corresponding name will be returned.

 

So if I marked fish and dog, both bill and joe would be returned.

 

 

I have a test db and table set up with mysql.

I've tried watching some demos and I always come up with nothing.

 

I have a form sorta like this

 

 

<form method="post" action="">

 

dog:

<input type="checkbox" name="dog" />

 

cat:

<input type="checkbox" name="cat" />

fIsh:

<input type="checkbox" name=fish" /><br />

 

<input type="submit" value="Search" />

 

</form>

 

Thanks!

[Jessica]

You haven't posted any code that you've tried that doesn't work.

It's a simple select, so show us what you tried and we can help.

[frsome]

really I don't know where to start I've messed around with if.. else statments and tried this below, but I'm not sure if I am even on the right track. Thanks

 

 

<?php

function sort()

{

$Name = ('');

$Dog = ('');

$Cat = ('');

$Fish = ('');

 

$con = mysql_connect('host','username','password');

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

}

mysql_select_db("test_db", $con);

$query="Select * From test_table WHERE Name='$Name' Dog='$dog' cat='$cat' fish='$fish')";

mysql_query($query) or die ('error updating database');

echo "result " .$Name. " ".$dog." ".$cat." ".$fish ;

 

 

?>

[Jessica]

1. $Name = ('');

Why are you doing that???

 

2. Variables are case sensitive, so $dog and $Dog are not the same.

 

3. Your SQL is missing AND/OR between the columns. Look up the syntax.

 

4. What is the datatype in the DB?

 

5. See the link in my signature about debugging SQL, and use that technique instead of just die() with a useless message.

 

6. Use :codetags: on this forum.

 

7. I just realized you have all of that in a function, without any evidence that you're even calling the function. You probably don't need a function for this.

 

8. When you post a form, the values will be in the $_POST array.

[Jessica]

I forgot 9, use mysqli

 

So. You should change the table structure to have one table for types of pets (like pet_types with a PK like pet_type_id), one table for users, and a third table for users_pets, linking the pet_type_id and the user_id. Then your code could work as follows:

 

<?php
//Get the pet types.
$sql = "SELECT pet_type_id, pet_type FROM pet_types";
$pet_types = array();
$result = mysqli_query($conn, $sql) or trigger_error("Query Failed! SQL: $sql - Error: ".mysqli_error(), E_USER_ERROR);
while($row = mysqli_fetch_assoc($result)){
$pet_types[$row['pet_type_id']] = $row['pet_type'];
}

//If the array was sent via POST
if(isset($_POST['selected_pet_types'])){
//Sanitize the user input
$pet_type_ids = array_filter($_POST['selected_pet_types'], "intval");
//Build the SQL to join all 3 tables.
$sql = "SELECT users.*, pet_types.pet_type 
FROM users
INNER JOIN users_pets 
ON users.user_id = users_pets.user_id
INNER JOIN pet_types
ON users_pets.pet_type_id = pet_types.pet_type_id
WHERE pet_type_id IN (".implode(', ', $pet_type_ids.")";
$result = mysqli_query($conn, $sql) or trigger_error("Query Failed! SQL: $sql - Error: ".mysqli_error(), E_USER_ERROR);
//Here, do whatever you want with the results.
echo '<pre>';
while($row = mysqli_fetch_assoc($result)){
	print_r($row);
}
}else{	
//Display the form
echo '<form method="post" action="">';
//Display each pet type with a checkbox.
foreach($pet_types AS $pet_type_id=>$pet_type){
	echo "<p>$pet_type <input type=\"checkbox\" name=\"selected_pet_types[]\" value=\"$pet_type_id\"></p>";
}
echo '<p><input type="submit" value="Search" /></p>
</form>';
}

[frsome]

Not sure about the $name =(''); thing. do I need that or what do I need to put in there?

 

datatype is varchar, I would rather something like true or false, yes or no ect

 

like I said really new to most of this and don't understand what I need to do based on your last 3 points

[Jessica]

Change the data type to a boolean. Then read the code I provided.

[frsome]

Ok I've been trying to make the tables as suggested. I'm abit more familiar with those, but i'm not sure how to link them together. Or at least not sure how the third table should be designed. Are they linked with the INNER JOIN part of the query or do I create them linked?