updating records

[shenagsandnags]

 

 

again my tables are

 

movies| ID(PK) - Titles - Category(FK) - URL

            0        Name        1              www.xyz.com

 

 

categories| ID(PK) - Category

                  1          Comedy

 

 

say i create a query  SELECT * FROM movies WHERE Category="1"

 

and it gave me all the movies from my table that is in the comedy category which should

look like this:

 

ID - Title - Category - URL

0    Name  Comedy  www.xyz

1    xyz    Comedy  www.abc

            etc..

 

  How can i add a drop down menu beside each movie listed in that result that would let me

change the category for it and save it ? like this,

 

ID - Title - Category - URL

0    Name  Comedy  www.xyz      [Dropdown]

1    xyz    Comedy  www.abc      [Dropdown]

 

            [save Button]

 

 

being a newbie im going to guess that i will need a INSERT in here somewhere and not really sure how i would write my result echos. i have been trying all kinds of "update records" forms and cant get them right so i figured it may be easier to just try and create my own. if i could get some example or if you have more simple suggestions

[Psycho]

The "results" you display are invalid. As I told you in a post just yesterday, your "Category" field in the movie table holds the Category ID - not the name. So, your results would look like this:

ID - Title - Category - URL
0    Name    1          www.xyz
1    xyz     1          www.abc

 

Because the field is named "Category" you continue to misinterpret what that field holds. I suggested that you should change the name to CategoryID to avoid the same mistake in the future, and here we are just one day later and you have made the same mistake again. If I (or someone else) was to look at this post and did not know that earlier information, the proposed solution would be entirely different.

 

Before you generate the list of movies from the result you should get a list of the Categories (ID and Name). You can then use that to create the select list next to each movie entry and show the currently selected. Value. Then, when the page is submitted you would use an UPDATE to change the calues for each movie - not an INSERT

 

I'll psot some sample code in a few minutes.

[Psycho]

<?php

//Create function to create select list of categories for a movie
function categorySelect($movieID, $categories, $selectedCatID=false)
{
    $selectHTML = "<select name=\"category[{$movieID}]\">\n";
    foreach($categories as $catID => $catName)
    {
        $selected = ($catID===$selectedCatID) ? ' selected="selected"' : '';
        $selectHTML .= "<option value=\"{$catID}\"{$selected}>{$catName}</option>\n";
    }
    $selectHTML .= "<select name=\"category[{$movieID}]\">\n";
    return $selectHTML;
}

//Get list of categories and create array for use in above function
$query = "SELECT ID, Name from Categories ORDER BY Name";
$result = mysql_query($query);
$categoryAry = array();
while($cat = mysql_fetch_assoc($result))
{
    $categoryAry[$cat['ID']] = $cat['Name'];
}

//Get list of movies to display
$categoryID = (int) $_GET['category_id'];
$query = "SELECT ID, Title, CategoryID, URL from Movies WHERE CategoryID = $categoryID";
$result = mysql_query($query);
//Create output for the movies
$movieOutput = '';
while($movie = mysql_fetch_assoc($result))
{
    $movieOutput .= "  <tr>\n";
    $movieOutput .= "    <td>{$movie['ID']}</td>\n";
    $movieOutput .= "    <td>{$movie['Title']}</td>\n";
    $movieOutput .= "    <td>" . categorySelect($movie['ID'], $categories, CategoryID) . "</td>\n";
    $movieOutput .= "    <td>{$movie['URL']}</td>\n";
    $movieOutput .= "  </tr>\n";
}

?>
<table>
  <tr>
    <th>ID</th>
    <th>Title</th>
    <th>Category</th>
    <th>YRL</th>
  </tr>
  <?php echo $movieOutput; ?>
</table>