CODEWITHSUNDEEP
phpmysqlsql-server
user4351337
user4351337

Reputation:

Show certain code depending on mysql query? (php)

I need a little kickstart here.

We're just learning php in school and we have this project where we're making a website for movie browsing. So we wanna be able to select genre, and show all movies from that genre using a MySQL database. We're all clear on the SQL queries and such, my question is rather how I make the browser show movies depending on SQL query?

Let me explain. Say we're movies.com

So on movies.com/genre is where you select the genre right, and on movies.com/display is where you're supposed to see the movies from the genre selected. So, clicking on "Comedy" should take you to movies.com/display and show you only the comedy movies. Selecting "Drama" should take you to the same site (movies.com/display) and show you only the drama movies.

Problem here is that we just don't know where to begin, it became a problem when switching page to show certain sql queries depending on what you selected in a previous page. I am not sure how to Google it, but just a link or a suggestion will help. I'm sure it can't really be too hard.

Thanks in advance.

Upvotes: 0

Views: 273

Answers (2)

deacs
deacs

Reputation: 4309

Start with the html to choose a genre:

<form action="display.php" method="POST">
    <select name="genre">
        <option value="drama">Drama</option>
        <option value="comedy">Comedy</option>
        <option value="thriller">Thriller</option>
        <option value="horror">Horror</option>
    </select> 
    <input type="submit" value="Submit"/>
</form>

Here we have a form with a dropdown menu to select the genre. The forms action goes to display.php

Create display.php where we can get the submitted value and put that into our query:

// get the submitted value
$genre = $_POST['genre']; 

// set a default genre if the POST genre is empty
if(empty($genre)) {
    $genre = 'comedy';
}

// connect to database
$link = mysqli_connect("localhost", "my_user", "my_password", "db_name");

// check connection
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: (" . mysqli_connect_errno() . ") ";
}

// build the query
$query = "SELECT * FROM movie WHERE genre = '".$genre."'"; // unsafe !!

$query = "SELECT * FROM movie WHERE genre = '".mysqli_real_escape_string($link, $genre)."'"; // safer

// execute query
$result = mysqli_query($link, $query);

// check result
if (!$result) {
    echo 'Query unsuccessfull!';
}

// show values
while($row = mysqli_fetch_assoc($result)) {
    echo $row['title']; 
    echo $row['description'];
    echo "<br/>";
}

// close connection
mysqli_close($link);

Upvotes: 1

Kickstart
Kickstart

Reputation: 21513

I would suggest having a table of genres. That way you can display the list of genres easily for a user to select from (ie, you could populate an HTML forms select list from that table). The list of genres would have a unique id (an integer) and a text description, so when the list of produced you display the description but have a value that is the id.

Then have a table of movies (I presume you already have this). Each movie would be identified by a unique integer id field/

Lastly a table of movies to genres. This would just link the id of the movie to the id of the genre(s) for the movie. This way you can have a movie linked to several genres if required.

Doing it this way also means that the values you return from the HTML forms can just be integers, which are easily made safe (just use the php intval() function).

In use you would provide a list of genres as a SELECT list. When the user selects an item and submits the form the id of the selected genre is returned to the script. The script can then do a query that joins the tables together, checking the genre is the selected genre that has been returned to the script.

For example.

tbl_genres
id
genre_name

tbl_movies
id
movie_name

tbl_genre_movie
genre_id
movie_id

Then to get the details for a returned genre id:-

$sql = "SELECT *
FROM tbl_genres 
INNER JOIN tbl_genre_movie
ON tbl_genres.id = tbl_genre_movie.genre_id
INNER JOIN tbl_movies
ON tbl_genre_movie.movie_id = tbl_movies.id
WHERE tbl_genres.id = ".intval($_POST['genre_id']);

Upvotes: 0

Related Questions