Display mysql data in html form?

PHP
1

hi, I have a table of movies and it has nearly 1000 entries.

CREATE TABLE movies (
movie_id INT,
movie_name VARCHAR(255),
type_id INT,
movie_region CHAR(3),
release_date DATE
) DEFAULT CHARACTER SET utf8’;

and I want to be able to display them (depending on what pre-search criteria they select such as genre) as options for the user to select in a html form such as this:

	<form method="post">
		<label for="movies"><strong>Movies</strong></Label><br/>
			<select name="movies">
				<option value=""></option>
				<option value=""></option>
				<option value=""></option>
				<option value=""></option>
				<option value=""></option>
				<option value=""></option>
				<option value=""></option>
				<option value=""></option>
			</select>
	</form>

how do I go about doing this?

if this should be in the MySQL section, my apologies.

2

Which bit are you having trouble with? Have you built the pre select form? Does it work? Have you written any sql?

3

nah that’s kinda all I have: table full of movies and the form. just after the basics to point me in the right direction, so I can figure it out. like, do you just echo it somehow?

4
  1. Create a form that has options like genre etc
  2. Submit this form and make sure it sends what it is supposed to to the processing script
  3. Create a query (in phpmyadmin or mysql) that does what you want and works
  4. Copy that working query into php and replace the dynamic bits with the form data
  5. Execute query and process results into table/list/whatever
5

thanks, I did it with some array stuff :slight_smile:

6

bump, havin’ some issues. here is my code.


<?php
require_once $_SERVER['DOCUMENT_ROOT'] . '/includes/connect.php';
require_once $_SERVER['DOCUMENT_ROOT'] . '/includes/functions.php';
$result = mysqli_query($link, 'SELECT genre_name FROM genres');
	if (!$result)
		{
			$error = 'Error fetching info from database!';
			include 'error.php';
			exit();
		}
	while ($row = mysqli_fetch_array($result))
		{
			$genres[] = array('genre_name' => $row['genre_name']);
		}
$result = mysqli_query($link, "SELECT movie_name FROM movies");
	if (!$result)
		{
			$error = 'Error fetching info from database!';
			include 'error.php';
			exit();
		}
	while ($row = mysqli_fetch_array($result))
		{
			$movies[] = array('movie_name' => $row['movie_name']);
		}
?>

<?php echo '<form action="addmovie.php?user=' . $user . '" method="post">' ?>
	<label for="region"><strong>Region</strong></label><br/>
	<input type="radio" name="region" value="NA"/> NA
	<input type="radio" name="region" value="JP"/> JP
	<input type="radio" name="region" value="AU"/> AU
</form> 

<form action="" method="post">
	<label for="genre"><strong>Genre</strong></Label><br/>
	<select name="genre" id="genre">
		<option value="">Select Genre</option>
		<?php foreach ($genres as $genre): ?>
		<option value=""><?php htmlout($genre['genre_name']); ?></option>
		<?php endforeach; ?>
		</select>
</form>

<form action="" method="post">
	<label for="movie"><strong>Movies</strong></Label><br/>
	<select name="movie" id="movie">
		<option value="">Select Movie</option>
		<?php foreach ($movies as $movie): ?>
		<option value=""><?php htmlout($movie['movie_name']); ?></option>
		<?php endforeach; ?>
	</select>
</form>

<input type="submit" name="add_movie" value="Add Movie"/>
</body>
</html>

it all works fine but I’m just wondering, how do I get the results in the last form to be based off the selections in the first 2 forms? for example if I select a different region and/or genre, the list of movies in the final form would be different.

any tips as to how I can handle that?

7

I’m guessing I need to define region and genre somehow so that I can use them in the ‘SELECT movie_name FROM movies’ query - but how? This is driving me nuts, been trying all day.

8

This part of your code creates the $movies array. It doesn’t do anything with the region or genre data.
You need to build the SQL query dynamically, so that it will filter the results based on existing POST data


$result = mysqli_query($link, "SELECT movie_name FROM movies");
if (!$result)
{
    $error = 'Error fetching info from database!';
    include 'error.php';
    exit();
}
while ($row = mysqli_fetch_array($result))
{
    $movies[] = array('movie_name' => $row['movie_name']);
}

Try modifying it like this:


$query = "SELECT movie_name FROM movies WHERE 1=1 ";  //note trailing space

//Region
if(isset($_POST['region']) AND in_array($_POST['region'], array('NA', 'JP', 'AU'))) {
    $query .= "AND movie_region='{$_POST['region']}' ";
}

//Debug
# echo $query;

$result = mysqli_query($link, $query);
if(!$result) {
    // ....
}

So now $query is a string, by default it will select ALL movies. If a region was selected (and is a safe known value) the query will change

I’m not sure how to do the same with genre because I can’t see where your DB identifies a genre for each movie. Is that what type_id is for?

9

oh awesome thank you, I really just had no idea how to do that (still learning heh) - makes sense to me now I think. will play some. one part I don’t understand - what does 1=1 mean?

and for genres, I have the genre table with ‘id’ and ‘genre_name’

in my movie table I have ‘genre_id’ to link to that table

10

this is what I changed it to, but it’s loading the full list of movies and then not changing when I select different regions…


<?php
require_once $_SERVER['DOCUMENT_ROOT'] . '/includes/connect.php';
require_once $_SERVER['DOCUMENT_ROOT'] . '/includes/functions.php';
$query = "SELECT movie_name FROM movies WHERE 1=1 ";
if(isset($_POST['region']) AND in_array($_POST['region'], array('US', 'JP', 'AU'))) {
		$query .= "AND movie_region='{$_POST['region']}' ";
}
$result = mysqli_query($link, $query);
if (!$result)
		{
			$error = 'Error fetching info from database!';
			include 'error.php';
			exit();
		}
	while ($row = mysqli_fetch_array($result))
		{
			$movies[] = array('movie_name' => $row['movie_name']);
		}
?>

all the forms are the same as they were, underneath that PHP code there. not sure what I’m doing wrong. probably something to do with the order of things… hm.

I’m guessing, maybe, that upon selecting a different region, the list would have to re-list itself somehow, because as it is now it’s loading all the movies and leaving it at that, regardless of what I select afterwoods… due to the code already being finished… I think? hehe

11

what does 1=1 mean?

1=1 is a WHERE condition that will always be true (1 is always equal to 1). The reason I included it was so that the query would not break when there is no region or genre selected.

It’s a bit like a placeholder so the query always has a WHERE clause. If the region is specified the “AND movie_region…” part gets added.
If nothing is added the query will still work, and select all rows.
The code below it can add additional parts to the WHERE clause without knowing if there were any others before it.

it’s loading the full list of movies and then not changing when I select different regions

Selecting a radio button doesn’t affect PHP at all. You need to SUBMIT the form (i.e POST it back to the server) for the $_POST varialbes to exist, and your movie list to get limited.

You’ll probably need region and genre to share a form. When that form is submitted you build the final one with a list of relevant movies.

As for the genres you need to three things:

  1. Select the ID as well as the genre name from your database to build the <select> field
  2. Use that ID as the <option> value (e.g <option value=“6”>Horror</option>)
    use zero as the value if you want to have an ‘Any Genre’ <option>
  3. Update the code that builds $query in a similar way, so that if there is a non-zero genre value you limit by it.
    For example:

$query = "SELECT movie_name FROM movies WHERE 1=1 ";

//Region
if(isset($_POST['region']) AND in_array($_POST['region'], array('US', 'JP', 'AU'))) {
        $query .= "AND movie_region='{$_POST['region']}' ";
}

//Genre
if(isset($_POST['genre']) AND $_POST['genre'] != 0) {
    $query .= "AND genre_id = " . (int)$_POST['genre'] . ' ';
}

This is why step #2 above is important. $_POST[‘genre’] needs to be the genre ID, not the text name.

12

Thanks for your reply, it’s making a lot of sense to me now.

I’m playing around with it and have gotten it working to the point where each form is working individually (region form, genre form) to refresh the movie list, I’m just not sure how to implement region AND genre in the same form… so that I can update the movie list with BOTH choices and not just one or the other. so trying to figure that out… then I think I’m set.

13

did it, just changed the form to this:

		&lt;form action="addmovie.php" method="post"&gt;

		&lt;label for="region"&gt;Region&lt;/label&gt;&lt;br/&gt;
		  &lt;input type="radio" name="region" value="US"/&gt; US
		  &lt;input type="radio" name="region" value="JP"/&gt; JP
		  &lt;input type="radio" name="region" value="AU"/&gt; AU&lt;br/&gt;

		&lt;label for="platform"&gt;Genre&lt;/Label&gt;&lt;br/&gt;
		&lt;select name="genre"&gt;
		  &lt;option value=""&gt;Genre&lt;/option&gt;
		  &lt;?php foreach ($genres as $genre): ?&gt;
		  &lt;option value="&lt;?php htmlout($genre['id']); ?&gt;"&gt;&lt;?php htmlout($genre['genre_name']); ?&gt;&lt;/option&gt;
		  &lt;?php endforeach; ?&gt;
	        &lt;/select&gt;

		&lt;input type="submit" value="submit"/&gt;
	&lt;/form&gt;

now the only thing to do with this that I’m wondering about is the fact that when I submit that form, yes it refreshes the movie list appropriately, awesome - but the options selected are cleared when the page is reloaded after submitting the form. does anyone know if there any way to have the form show the options that were selected after the form is submitted? hope that makes sense. lol

14

ugh or not, it’s only taking the region value when I submit that form… playing more.

15

but the options selected are cleared when the page is reloaded after submitting the form

You have to print them out in the value=“” attribute

16

oknow - to preselect a radio it needs a checked=“checked” attribute and for the <option> tags it’s selected=“selected”.

Also, there is no point having one label refer to all 3 radios. That really defeats the purpose and is bad for accessibility. Each radio should have its own label tag that refers to the radio ID

Similarly the label for genre points to ‘platform’ which doesn’t exist.

e.g


<input type="radio" name="region" id="regionUS" value="US" /><label for="regionUS"> US</label>
<input type="radio" name="region" id="regionJP" value="JP" checked="checked" /><label for="regionJP"> JP</label>
<input type="radio" name="region" id="regionAU" value="AU" /><label for="regionAU"> AU</label>

<label for="genre">Genre</label>
<select name="genre" id="genre">
    <option value="1">Comedy</option>
    <option value="2">Action</option>
    <option value="3" selected="selected">Documentary</option>
    <option value="4">Horror</option>
</select>

So to preselect you need to echo the attributes I mentioned if their is a POST value and it matches the current item.

For the genre it will look like this. The $selected will be the attribute or an empty string if not selected:


<label for="genre">Genre</genre><br/>
<select name="genre" id="genre">
<option value="0">Genre</option>
<?php
foreach ($genres as $genre) {

    $id = $genre['id'];
    $name = htmlout($genre['genre_name']);
    
    //Reselect genre
    if(isset($_POST['genre']) AND $_POST['genre'] == $id) {
        $selected = 'selected="selected"';
    } 
    else $selected = ''; //not selected
    
    echo "<option value='$id' $selected>$name</option>\
";
}
?>
</select>

Do the same with region. If you make an array of regions to loop over your code will be a bit cleaner as you won’t have to do the if/else multiple times for each hard-coded region.

17

edit: solved that myself, had to update a database column :slight_smile:

thanks very much for the help, going to work on adding the ‘remember selection’ stuff now, seeing the code it makes sense to me. really, really appreciate the help!

one more step to learn after this and my practice site is about done.