How can I join a dropdown from another table?

[spacepoet]

Hi:

 

Can someone help me understand how to fix my code below so that I can update it properly?

 

The SELECT dropdown does not update, and I am at a loss about how to make this work.

 

I basically trying to make a very simple product catalog to create Categories, and then add items to it:

<?php

include('../include/myConn.php');

$photo_id = $_REQUEST['photo_id'];

if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
$photo_id = mysql_real_escape_string($_POST['photo_id']);
$photo_title = mysql_real_escape_string($_POST['photo_title']);
$photo_price = mysql_real_escape_string($_POST['photo_price']);
$photo_caption = mysql_real_escape_string($_POST['photo_caption']);
$photo_category = mysql_real_escape_string($_POST['photo_category']);

$sql = "

UPDATE gallery_photos
   SET
   	  photo_id = '$photo_id',
      photo_title = '$photo_title',
      photo_price = '$photo_price',
      photo_caption = '$photo_caption',
      photo_category = '$photo_category'
  WHERE photo_id = $photo_id
  ";
  
mysql_query($sql) && mysql_affected_rows()

?>
<?php
}

$query=mysql_query("SELECT photo_id,photo_title,photo_price,photo_caption,photo_category FROM gallery_photos WHERE photo_id=$photo_id") or die("Could not get data from db: ".mysql_error());

while($result=mysql_fetch_array($query))
{
  $photo_id=$result['photo_id'];
  $photo_title=$result['photo_title'];
  $photo_price=$result['photo_price'];
  $photo_caption=$result['photo_caption'];
  $photo_category=$result['photo_category'];
}
?>

....

Product Category:

$result = mysql_query( "SELECT category_id,category_name FROM gallery_category" );
echo "<select name='photo_category' size='1'>";
while( $row = mysql_fetch_array( $result ) )
{
echo "<option value=\"".$row['category_id']."\">".$row['category_name']."</option>";
}
echo "</select>";


Title:

<input type="text" name="photo_title" size="45" maxlength="200" value="<?php echo $photo_title; ?>" />

Price:

<input type="text" name="photo_price" size="45" maxlength="200" value="<?php echo $photo_price; ?>" />

Description:

<textarea cols="107" rows="1" name="photo_caption"><?php echo $photo_caption; ?></textarea>

 

These are the database tables:

-- 
-- Table structure for table `gallery_category`
-- 

CREATE TABLE `gallery_category` (
  `category_id` bigint(20) unsigned NOT NULL auto_increment,
  `category_name` varchar(50) NOT NULL default '0',
  PRIMARY KEY  (`category_id`),
  KEY `category_id` (`category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

-- 
-- Dumping data for table `gallery_category`
-- 

INSERT INTO `gallery_category` VALUES (1, 'My First Gallery');



-- --------------------------------------------------------

-- 
-- Table structure for table `gallery_photos`
-- 

CREATE TABLE `gallery_photos` (
  `photo_id` bigint(20) unsigned NOT NULL auto_increment,
  `listorder` int(11) default NULL,
  `photo_filename` varchar(25) default NULL,
  `photo_title` varchar(255) default NULL,
  `photo_price` varchar(255) default NULL,
  `photo_caption` text,
  `photo_category` bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (`photo_id`),
  KEY `photo_id` (`photo_id`)
) ENGINE=MyISAM AUTO_INCREMENT=105 DEFAULT CHARSET=latin1 AUTO_INCREMENT=105 ;

-- 
-- Dumping data for table `gallery_photos`
-- 

INSERT INTO `gallery_photos` VALUES (97, NULL, '97.jpg', '', 1);
INSERT INTO `gallery_photos` VALUES (99, NULL, '99.jpg', '', 1);
INSERT INTO `gallery_photos` VALUES (100, NULL, '100.jpg', '', 1);
INSERT INTO `gallery_photos` VALUES (101, NULL, '101.jpg', '', 1);
INSERT INTO `gallery_photos` VALUES (102, NULL, '102.jpg', '', 1);
INSERT INTO `gallery_photos` VALUES (103, NULL, '103.jpg', '', 1);
INSERT INTO `gallery_photos` VALUES (104, NULL, '104.jpg', '', 1);

 

Or, if this is not "fixable" the way I am doing it, is there a tutorial or a example someone can post that will show me how to do this?

 

I just want to be able to make Categories, and then add a Photo with title, price, and decsription to it.

And of course be able to edit or delete the Categories and/or Items.

 

The examples I'm finding via GOOGLE are too much - I just want something simple.

 

Anyone?

 

[spiderwell]

the code you have given doesn't add categories at all, only UPDATEs or SELECTs from the gallery table.

to make it easier you should have 2 pages, one for adding categories and one for adding the photos.

on the categories page have a simple for with one field for the name of category, have it post to itself, and insert the name into the gallery category table in the database.

on the other page you have the form like the one you posted, only you don't appear to have an INSERT just UPDATE so you will only be editing existing records, unless you change that script to have an option to add new records also.

[spacepoet]

Hi:

 

Thanks for the reply. I have 3 other pages to make the Categories, Edit the Categories, and a page to add a photo and the data for each photo.

 

I'm having trouble trying to use the code I posted to Edit the page data, (photo is edited on a separate page), specifically trying to edit the Category a photo is assigned to.

 

It doesn't seem to "stick" when I select it - it keeps going back to the first one in the list.

 

Any ideas?

 

Better coding example? I've been stuck on this for awhile and am a bit frustrated.

 

Thanks!

[cyberRobot]

To get your drop-down menu selection to stick, you'll need to utilize the passed selection. In the code that generates the drop down, run a simple to test to see which one is selected add the selected attribute.

 

For example:

 

<?php
...

echo "<select name='photo_category' size='1'>";
while( $row = mysql_fetch_array( $result ) ) {
    echo "<option value=\"".$row['category_id']."\"";
    if($photo_category == $row['category_id']) { echo ' selected="selected"'; }
    echo ">".$row['category_name']."</option>";
}
echo "</select>";

...
?>