复杂SQL查询的默认下拉选项

I'm a bit stumped with this one. I'm putting together my own CMS as a learning exercise. I've created an edit form and one of the fields is a drop down menu for the item's category. What I am struggling to do is set the correct default category for the item.

The logic is, I am trying to match the id of the item to the corresponding row in the table then select the foreign key that links to another table with the categories in it. If the foreign key matches the category id then that must be the default.

The $id variable was defined further up the page and I have tested it to confirm it works correctly.

Any help would be massively appreciated!

(Also I'm aware using mysql is bad practice but this is only for learning purposes, I intend to attack mysqli and PDO next)

<div class="cell"><select name="categoryid"><option value=""></option>
<?php

$result = "SELECT categoryid, classescatid
           FROM classes 
           LEFT JOIN classescat ON classes.categoryid=classescat.classescatid 
           WHERE classesid=$id";
$defcat = mysql_fetch_array($result);
echo $defcat['categoryid'];
echo $defcat['classescatid'];

$catfetch = "SELECT * FROM classescat";
$outcome = mysql_query($catfetch); 
while ($row = mysql_fetch_array($outcome)) {

    echo '<option value="';
    echo $row['classescatid'] . '" ';
    if ($defcat['categoryid'] == $defcat['classescatid']) { echo selected; };
    echo '>' ;
    echo $row['name'];
    echo '</option>';
}
?>
</select>

You are not comparing the current value in the loop, but rather always the values outside of the loop. Try this:

if ($row['classescatid'] == $defcat['classescatid']) { echo ' selected="selected"'; };

Two problems:

First, you should be comparing the default category ID with the category ID from the current row, not with the row from $defcat.

Second, you need to quote selected when you echo it.

However, you can do everything in one query:

SELECT classescatid, name, (classesid IS NOT NULL) isdefault
FROM classescat
LEFT JOIN classes ON classes.categoryid=classescat.classescatid AND classesid = $id

Then your PHP would be:

while ($row = mysql_fetch_assoc($outcome)) {
    echo '<option value="';
    echo $row['classescatid'] . '" ';
    if ($row['isdefault']) { echo "selected"; };
    echo '>' ;
    echo $row['name'];
    echo '</option>';
}

You need to quote out "selected" in your echo...

if ($defcat['categoryid'] == $defcat['classescatid']) { echo 'selected'; };

From one learner to another, I'll just give a different approach that I've recently learned, and added to my "cheese database".

A php query is really just a string that can be broken up and stitched back together.

So I select a country and a cheese classification and pass it to the server. The server does a lookup and creates some variables here:

$countryname = $_POST['country'];
$classification = $_POST['classification'];

if( in_array( $countryname, array( "Holland", "Italy", "France", "Spain", "Basque",
                                   "Switzerland", "Austria", "Australia", "England", "Belgium", "Sardegna", "US" ) ) )
  { $countryname = $countryname; }
  else
  { $countryname = 'All'; }

if( in_array( $classification, array( "Fresh", "Soft", "Semi-Soft", "Hard", "Very Hard" ) ) )
  { $classification = $classification; }
  else
  { $classification = 'All'; }

Then the relevant parts of the query are selected:

if ( $countryname == 'All' && $classification == 'All' )
  { $myselector1 = " uniqueid > 0 " ; }
else if ( $countryname == 'All' && $classification <> 'All' )
  { $myselector1 = " classification = '$classification' "; }  
else if ( $countryname <> 'All' && $classification == 'All' )
  { $myselector1 = " country = '$countryname' " ; }
else if ( $countryname <> 'All' && $classification <> 'All' )
  { $myselector1 = " country = '$countryname' AND classification = '$classification' "; }

Then the query is constructed of a series of strings based on the country name and classification (or "All") for anything else.

$query = " SELECT * " .
         " FROM xcheese " .
         " WHERE " .
         " $myselector1 ". 
         " ORDER BY country ASC, score DESC";
$result = mysqli_query($cxn, $query) or die ("could not connect");

Obviously this is not an exact answer to your question, but perhaps it will give you something to think about.