SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    153
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    display mysql data in html form?

    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. #2
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Which bit are you having trouble with? Have you built the pre select form? Does it work? Have you written any sql?

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    153
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    153
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    thanks, I did it with some array stuff

  6. #6
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    153
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    bump, havin' some issues. here is my code.

    PHP 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. #7
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    153
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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. #8
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    PHP Code:
    $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:
    PHP Code:
    $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. #9
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    153
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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. #10
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    153
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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 Code:
    <?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. #11
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    PHP Code:
    $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. #12
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    153
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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. #13
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    153
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    did it, just changed the form to this:

    <form action="addmovie.php" method="post">

    <label for="region">Region</label><br/>
    <input type="radio" name="region" value="US"/> US
    <input type="radio" name="region" value="JP"/> JP
    <input type="radio" name="region" value="AU"/> AU<br/>

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

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

    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. #14
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    153
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    ugh or not, it's only taking the region value when I submit that form.. playing more.

  15. #15
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #16
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Code HTML:
    <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:
    PHP Code:
    <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>\n";
    }
    ?>
    </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. #17
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    153
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    edit: solved that myself, had to update a database column

    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •