mysqli插入语句问题

Question

am getting the following error from my code:

Binding parameters failed: (1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? (Name, Address, Location, Phone, Email, Time, Website, Photo1, Rating, Date_Pu' at line 1

Can anyone help me out please? Here is my code:

include("mysqli.php");
$search_tbl = mysql_query("SELECT * from listing_title where listing_title_ID = '$main_id'");
$tbl_name = $search_tbl['tbl_name'];

                        $stmt = $db->stmt_init();
                        global $tbl_name;
                        if($stmt->prepare("INSERT INTO ? (Name, Address, Location, Phone, Email, Time, Website, Photo1, Rating, Date_Published, categories_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")) 
                        {
                                      $stmt->bind_param('sssssssssisi',$tbl_name,$title,$address,$location,$phone,$email,$time,$website,$name,$rating,$date,$sub_cat);
                $title = $_POST['name'];
                $email = $_POST['email'];
                $address = $_POST['address'];
                $location = $_POST['location'];
                $phone = $_POST['phone'];
                $time = $_POST['time'];
                $rating = $_POST['rating'];
                $main = $_POST['main'];
                $website = $_POST['website'];
                $date = date('Y-m-d');
                                    $stmt->execute();
                                    $stmt->close();

                            }
                            else
                            {
                              echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;

                            }


                    }
                    else
                    {

                    echo 'a';

                    }

Answer 1

your script appears to be incomplete, but doing the best i could with what you had this is what you need. first of all, ditch whatever mysqli wrapper crap you are using. it is teaching you bad principles.

first file, your db info. call it config.php or whatever the hell you want. use require once instead of include. also, ditch the parenthesis around the requires these are not necessary at all, and use single quotes instead of double quotes. single quotes are treated as strings while double quotes php will search for variables inside, thus spending more resources from the cpu/cache.

config.php

$host = 'localhost';//your db host
$user = 'someuser'; //your db user
$pass = 'somepass'; //your db password
$name = 'somedb'; //the name of your db
$mysqli = new mysqli($host,$user,$pass,$name);

if(mysqli_connect_errno()) { 
    echo "Connection Failed: " . mysqli_connect_errno(); 
    exit; 
}else{
    global $mysqli;//make your db connection available globally
}

Now for your script

script.php

require_once 'config.php';

//keep your post variables up here. you still need to santize and trim these
$title = $_POST['name'];
$email = $_POST['email'];
$address = $_POST['address'];
$location = $_POST['location'];
$phone = $_POST['phone'];
$time = $_POST['time'];
$rating = $_POST['rating'];
$main = $_POST['main'];
$website = $_POST['website'];
$date = date('Y-m-d');

global $mysqli;//fetch your db connection


$stmt = $mysqli->prepare("SELECT tbl_name from listing_title where listing_title_ID = ? ");
$stmt->bind_param('i',$main_id);
if($stmt->execute()) {
    $stmt->bind_result($tbl_name);
    $stmt->close();
    $stmt = $mysqli->prepare("INSERT INTO ".$tbl_name." 
    (Name, Address, Location, Phone, Email, Time, Website, Photo1, Rating, Date_Published, categories_ID) 
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
    $stmt->bind_param('ssssssssisi',$title,$address,$location,$phone,$email,$time,$website,$name,$rating,$date,$sub_cat);
    if($stmt->execute()) {
        $stmt->close();
    }else{
        $stmt->close();
        //catch the error
    }
}else{
    $stmt->close();
    //throw an exception or handle the error here.
}

Please note, this still needs work. you need to sanitize and trim your variables. here's an example function. to include funcs, just add a require_once to the config.php file, and it will be included in any file you include config.php in.

example of this:

require_once 'funcs.php';

example sanitize function:

funcs.php

function security($value) {
   if(is_array($value)) {
      $value = array_map('security', $value);
   } else {
      if(!get_magic_quotes_gpc()) {
         $value = htmlspecialchars($value, ENT_QUOTES, 'UTF-8');
      } else {
         $value = htmlspecialchars(stripslashes($value), ENT_QUOTES, 'UTF-8');
      }
      $value = str_replace("\\", "\\\\", $value);
   }
   return $value;
}

to call the function

$title = security(trim($_POST['name']));

I leave the sanitizing to you. its a valuable exercise and you have an example that will sanitize anything, whether it be integers, arrays, objects, or strings.

you should only use trims on strings though. if you want to sanitize an entire array, just use the security function.

good luck.