having trouble with this query won't insert in the specified field

[co.ador]

This is the query

 

$sql = mysql_query("INSERT INTO products (product_name, price, details, subcategory,  city, state, country, category_id, date_added) 
	VALUES('$productname','$price','$details','$subcategory','$city','$state','$country','$category_id', now())") or die (mysql_error());
	// Get the inserted ID here to use in the activation email
	$id = mysql_insert_id();
?>

 

 

This is the form with a name of category_id this field has the value of the id only, I also want to send the name value to the query.

 

<select name="category_id" size="1"><br />';


$sql = "SELECT id, name FROM categories ORDER BY name";

$rs = mysql_query($sql);

while($row = mysql_fetch_array($rs))
{
  echo "<option value=\"".$row['id']."\">".$row['name']."</option>\n  ";
}

echo'
</select>


SQL structure

`product_id` int(11) NOT NULL AUTO_INCREMENT,

  `product_name` varchar(225) NOT NULL,
  `price` varchar(16) NOT NULL,
  `details` text NOT NULL,
  `category` varchar(255) NOT NULL,
  `subcategory` varchar(225) NOT NULL,
  `date_added` datetime NOT NULL,
  `location` varchar(255) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(255) NOT NULL,
  `country` varchar(255) NOT NULL,
  `category_id` int(11) NOT NULL,

[teynon]

I haven't done a lot with sub queries in inserts, but i'm pretty sure you could use it here. Although I don't know why you would, it seems like a wasted table to me.

 

INSERT INTO products (product_name, price, details, subcategory, city, state, country, category_id, category_name, date_added) VALUES (SELECT '{$productname}', '{$price}', '{$details}', '{$subcategory}', '{$city}', '{$state}', '{$country}', '{$category_id}', name, now() FROM categories WHERE id = '{$category_id}');

[jcbones]

What is the question?

[co.ador]

Thank you taynon,

 

I wanted it to be a single selection that returns both the ID and the name? id and name the fields in the query

 

someone has suggested to send concatenate the fields name and id at the option tag like

 

echo "<option value=\"".$row['name']."$".$row['id']."\">".$row['name']."</option>\n  ";

 

then retrieve the data you can explode it as so

 

$option = explode("$", $_POST['category_id']);

echo $option[0]; // Name

echo $option[1]; /

 

 

That solved the name