如何从下拉列表中将mysql语句中的变量设置为用户选择

Question

I am creating an onlineshop. The user add the details of a new product using a text-based fields for Title,Price,Description but it chooses where to upload the product using a drop down list with all the tables from the database.

The problem is, how do I set his selection to be the statement in my insert.php file, in order for the uploading of a new file to depend on his selection??

insert.php

<?php
    $con=mysqli_connect('localhost','root', '',"onlineshop");
    // Check connection
    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }


    $sql="INSERT INTO **--SELECTION OF THE USER FROM DROPDOWN--** (title, description, price)
    VALUES
    ('$_POST[title]','$_POST[description]','$_POST[price]')";

    if (!mysqli_query($con,$sql))
    {
        die('Error: ' . mysqli_error($con));
    }
    echo "1 record added";

    mysqli_close($con);
?>

dropdown.php

<?php
    $dbname = 'onlineshop';

    if (!mysql_connect('localhost', 'root', '')) {
        echo 'Could not connect to mysql';
        exit;
    }

    $sql = "SHOW TABLES FROM $dbname";
    $result = mysql_query($sql);

    if (!$result) {
        echo "No tables exist! 
";
        echo 'MySQL Error: ' . mysql_error();
        exit;
    }
    $tables = '';
    while ($row = mysql_fetch_row($result)) {


   $tables .="<option value='$row[0]'>$row[0]</option>"; 

    }

    mysql_free_result($result);
?>

index.html (form for the dropdown list)

<?php 
    include_once 'dropdown.php'; 
?> 
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
    <select id = "form3" name="Tables" id="ddTables">
<?php 

    echo $tables;

?>
    </select>
    <input type="submit" id="tableSubmit" value="Submit"/>
</form>

Please if anyone can suggest anything I will really aprrieciate this. I don't think is something too hard, but for me it is!

Thanks!

connect.php

<?php
// Try to connect to MySQL
$connect = mysql_connect('localhost','root', '') or die('Sorry could not connect to database');
// Check connect and return error if failed
$use_db = mysql_select_db('onlineshop');

$create_db = "CREATE DATABASE onlineshop";
if(!$use_db) {
    echo mysql_error();
    mysql_query($create_db);
    mysql_select_db('onlineshop');
}

$con=mysqli_connect('localhost','root', '');
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

// Create database
$sql="CREATE DATABASE onlineshop";
if (mysqli_query($con,$sql))
  {
  echo "Database my_db created successfully";
  }
else
  {
  echo "Error creating database: " . mysqli_error($con);
  }

//main table
$sql = 'CREATE TABLE mens( '.


  'id INT NOT NULL AUTO_INCREMENT, '.
   'title VARCHAR(20) NOT NULL, '.
   'description  VARCHAR(45) NOT NULL, '.
   'price   FLOAT NOT NULL, '.
   'image varchar(200),'.
   'image_small varchar(200),'.
   'primary key ( id ))';

//copy attributes of the main table
$sql2= 'CREATE TABLE women AS ( SELECT * FROM mens where 1=2)';
$sql3= 'CREATE TABLE kids AS ( SELECT * FROM mens where 1=2)';
$sql4= 'CREATE TABLE infants AS ( SELECT * FROM mens where 1=2)';
$sql5= 'CREATE TABLE baby_books AS ( SELECT * FROM mens where 1=2)';
$sql6= 'CREATE TABLE garden AS ( SELECT * FROM mens where 1=2)';
$sql7= 'CREATE TABLE comics AS ( SELECT * FROM mens where 1=2)';
$sql8= 'CREATE TABLE cooking AS ( SELECT * FROM mens where 1=2)';
$sql9= 'CREATE TABLE moviestv AS ( SELECT * FROM mens where 1=2)';
$sql10= 'CREATE TABLE music AS ( SELECT * FROM mens where 1=2)';
$sql11= 'CREATE TABLE games AS ( SELECT * FROM mens where 1=2)';



$retval = mysql_query( $sql, $connect );
$retval2 = mysql_query($sql2, $connect);
$retval3 = mysql_query($sql3, $connect);
$retval4 = mysql_query($sql4, $connect);
$retval5 = mysql_query($sql5, $connect);
$retval6 = mysql_query($sql6, $connect);
$retval7 = mysql_query($sql7, $connect);
$retval8 = mysql_query($sql8, $connect);
$retval9 = mysql_query($sql9, $connect);
$retval10 = mysql_query($sql10, $connect);
$retval11 = mysql_query($sql11, $connect);

//this checks only for table1, check for all of them
if(! $retval)
{
  die('Could not create table: ' . mysql_error());
}
echo "Tables created successfully
";
?>

Answer 1

Tested code that does as asked. It uses 'mysqli' as object. The code escapes input. and the tablename is validated (not any more).

Note: all form field names are assumed to be lowercase.

PHP 5.3.18, MySQL 5.5.16.

<?php session_start();

    $mysqli = new mysqli('localhost', 'test', 'test',"testmysql");

    // Check connection
    if ($mysqli->connect_error)
    {
        echo "Failed to connect to MySQL: " . $mysqli->error;
    }

    // removed table validation check...
    // $validTableNames = array('my_table_1', 'my_table_2', 'another_table_3');
    $tablename = isset($_POST['tablename']) ? $mysqli->real_escape_string($_POST['tablename']) : '';

    // $tableNameOk = in_array($tablename, $validTableNames);
    // if (!$tableNameOk) {
    //     die('Error: Invalid table name:' . $tablename);
    // }

    $title       =  !empty($_POST['title'])       ? $mysqli->real_escape_string($_POST['title']) : null;
    $description =  !empty($_POST['description']) ? $mysqli->real_escape_string($_POST['description']) : null;
    $price       =  !empty($_POST['price'])       ? $mysqli->real_escape_string($_POST['price']) : null;


    $sql = "INSERT INTO `{$tablename}` (title, description, price) VALUES (?, ?, ?)";

    $stmt = $mysqli->prepare($sql);

    // We need to 'bind' the three input variables to the there '?' in the query.
    // 'sss' indicates that the parameters are 'strings'.
    // the order must match the order of the column names.

    $stmt->bind_param("sss", $title, $description, $price);
    $allOk = $stmt->execute();

    if (!$allOk)
    {
        die('Error: ' . $mysqli->error);
    }
    echo "1 record added";

    $mysqli->close();
?>