PHP & MYSQL help please

[jaytblack]

hello to those reading this, im new to coding and have a lot to learn but right now what im trying to do is set up a database that can be searched through the use of check box options. when i try my best to code it i get a blank screen(if it's not a error message) here's an idea of what i have so far......

Database

table name: DVD's
columns: D_id|Name |Type |Rating
Row 1: 1 |super bad|Movie|R

HTML
<form action="searchresults.php" method="post">
<input type="checkbox" name="Type[]" value="series" />Series
<input type="checkbox" name="Type[]" value="movie" />Movie
<input type="checkbox" name="Rating[]" value="R" />R
<input type="checkbox" name="Rating[]" value="PG 13" />PG 13
<input type="submit" value="Submit" />
</form>

PHP
<?php
$con=mysql_connect("localhost","username","password");
mysql_select_db("database", $con);

if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("database", $con);

$Type = array("Series", "movie");
$result = mysql_query("SELECT * FROM DVD's");
?>

this is where im stuck or confused or just lack experience to figure it out, i tired different methods to finish off that php code with no luck but what i want to happen is the ability to use the checkbox options to find all movies that match your choices. i kno im doing something wrong just cant figure it out, i even tried the mysql_fetch_array() but i get back error message saying it's not a valid MYSQL result. can anybody please help me(if you need a better idea of what i want to do, it would be just like the search page on animenewsnetwork website for their search by genre option using checkbox to find the list of anime based on your choices.

i need to set it up so that when a value from a checkbox is clicked, the database will be search for records(or rows) containing the values and pull up the row as a result.

p.s sorry for the long post just wanted to make sure i got everything in so it's less confusing please help me out or let me know anything else i'd have to tell you so you can help me out

 

[Kate]

Hi,

First of all, I don't think DVD's is a valid name. If you created the table through phpmyadmin, avoid special characters or reserved names. Remove the single quote.
In phpmyadmin you can test your SQL queries too.

Then you add a WHERE clause to your SQL statement to filter the results.
NB: if no checkbox has been ticked, you have two empty arrays, so you need to check this.

Something like this:

<?php
// declare variables
$arr_type=array();
$arr_rating=array();
$sql='';

// get ticked checkboxes
$arr_type = $_POST['Type'];
$arr_rating = $_POST['Rating'];

$sql = 'SELECT * FROM DVD';
$sql .= ' WHERE 1=1'; // dummy where in case of no further criteria

if (count($arr_type)) > 0 { // array is not empty
	$sql .= ' AND type IN (' implode(",", $arr_type) . ')';
}

if (count($arr_rating)) > 0 { // array is not empty
	$sql .= ' AND rating IN (' implode(",", $arr_rating ) . ')';
}

$result = mysql_query($sql);
?>

So you could end up with something like
SELECT * FROM DVD
WHERE 1=1'
AND type IN ('series','movies')
AND rating IN ('PG 13')

...depending on which checkboxes were selected

NB: code not immune against SQL injection attacks.

 

[jaytblack]

Thank you sdsinc I went over your code and it opened my eyes to more than expected, guess i got a better idea of what went wrong and how much i still have to learn. it also brought up interesting questions with your WHERE clause, is there any certain way i need to have my MYSQL database table set up as far as column or row(record) in order to be compatible with the php code? because your code is making me think i set up the table wrong to begin with and would i only need one table of two using a foreign key to match the values with the row or record, i ask this because im going to be using an genre array set up just like the type and rating so each value(genre) would be able to pull up multiple records(movies) so i want to make sure i have the correct set up with the database