Inserting Data from dropdown into database with PH

Question

First I needed a dropdown list that I could update easily so I created a database called manufacturers where I list manufacturers to be selected in a form.

I finally accomplished this with this code:

<?php
 // Connect to the test datbase on localhost
 // That's where we created the countries table above
 mysql_connect('localhost','##user##','##pass##');  mysql_select_db('wordpress');

 // Query the countries table and load all of the records
 // into an array.
 $sql = 'select * FROM manufacturers';
 $res = mysql_query($sql) or die(mysql_error());
 while ($rec = mysql_fetch_assoc($res))
 $manufacturers[] = $rec;
 ?>
<form action="select.php" method="post">
<?php
 echo '<select name="dropdown">';
 foreach ($manufacturers as $c)
{
  if ($c['id'] == $_GET['id'])
   echo "<option value=\"{$c['meta_id']}\" selected=\"selected\">{$c['meta_value']}              </option>\n";
 else
  echo "<option value=\"{$c['meta_id']}\">{$c['meta_value']}</option>\n";
 }
echo '</select>';
?>
 <input type="submit" value="Submit" name="submit"/>
 </form>

This worked out great I now have a dropdown list that is populated from my database manufacturers.

Now I need to send this to an existing database call post_meta so that from there I can display the users selection permanently.

I have tried a couple of different options but I am trying to use the following code to send this to my post_meta database.

<?php
$con = mysql_connect("localhost","##user##","##pass##");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("wordpress", $con);

$sql="INSERT INTO wp_postmeta (meta_id, post_id, meta_key, meta_value)
VALUES
('$_POST['meta_id']}','$_POST[post_id]','$_POST[meta_key]','$_POST[meta_value]')";

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "1 record added";
?>

This actually inserts into the database but doesn't record any values.

Please help me figure out what I'm doing wrong.

Answer 1

Do none of your values show up? It looks like you're missing quotes around your key values. For example, shouldn't it be :

$_POST['post_id']  

To do a sanity check, just echo your $_POST variables as opposed to doing the insert right away. This will help you figure out if you've got some syntax wrong. Also I'd read Brad's comment and keep it in mind for the future.

Answer 2

The proper way to do this is to A: escape all those $_POST superglobals.
and B. Write a query as shown below.

Here's the tabledef for wp_postmeta:
http://codex.wordpress.org/Database_Description#Table:_wp_postmeta

Because meta_id is an auto_increment primary key, you do not provide it, MySQL does.

//$meta_id = mysql_real_escape_string($_POST['meta_id']);  <<-- not needed.
$post_id = mysql_real_escape_string($_POST['post_id']);
$meta_key = mysql_real_escape_string($_POST['meta_key']);
$meta_value = mysql_real_escape_string($_POST['meta_value']);
$sql=" INSERT INTO wp_postmeta
       (post_id, meta_key, meta_value)
       VALUES
       ('$post_id','$meta_key','$meta_value') ";  //<<-- don't forget the quotes!
if ($result = mysql_query($sql)) {
  //You can get the new meta_id using:
   $new_meta_id = mysql_insert_id($result);
} else { 
   die ("could not insert ".mysql_error());
}

Answer 3

Try this query:

$sql="
INSERT INTO wp_postmeta
(meta_id, post_id, meta_key, meta_value)
VALUES
(
    '{$_POST['meta_id']}',
    '{$_POST['post_id']}',
    '{$_POST['meta_key']}',
    '{$_POST['meta_value']}'
)
";

And, as people say in comments, this code is very vulnerable, please consider to find better option to pass variables into query.