mysqli在foreach循环中向表中插入多行[关闭]

Question

Ok, something is wrong here with my code:

$db = new mysqli("localhost", "db_username", "db_password", "db_name");

if (mysqli_connect_errno())
{
    printf("Connect failed: %s
", mysqli_connect_error());
    exit();
}

$cdfi_articles = "CREATE TABLE cdfi_articles (
  id_article int(10) unsigned NOT NULL auto_increment,
  title varchar(255) NOT NULL default '',
  body text NOT NULL,
  section smallint(5) unsigned NOT NULL default '0',
  time int(10) unsigned NOT NULL default '0',
  views int(10) unsigned NOT NULL default '0',
  visible tinyint(3) NOT NULL default '1',
  PRIMARY KEY (id_article),
  KEY section (section),
  KEY visible (visible)
) ENGINE=MyISAM;";

$cdfi_sections = "CREATE TABLE cdfi_sections (
  id_section int(10) unsigned NOT NULL default '0',
  section_name varchar(255) NOT NULL default '',
  section_order smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY (id_section),
  KEY section_order (section_order)
) ENGINE=MyISAM;";

if (!mysqli_query($db, $cdfi_articles))
    printf("Error: %s
", mysqli_error($db));

if (!mysqli_query($db, $cdfi_sections))
    printf("Error: %s
", mysqli_error($db));

$sections = array('Latest News', 'Commentary', 'Impact', 'Opportunities', 'Policy', 'Events', 'People', 'Market', 'Careers');

foreach($sections as $key => $value)
{
    var_dump(mysqli_error($db));
    $stmt = mysqli_prepare($db, "INSERT INTO cdfi_sections VALUES (?, ?)");
    mysqli_stmt_bind_param($stmt, 'si', $value, $key);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);

}

mysqli_close($db);

I get this error:

Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given in filepath.php on line 44

I am using php.net page here as an example:
var_dump(mysqli_error($db)); returns string(0) "" for the 1st loop, and than every loop after that it says: string(47) "Column count doesn't match value count at row 1"

Where is the problem at?

Answer 1

mysqli_prepare is returning false because of an error. Use var_dump(mysqli_error($db)); to identify what that particular error is. I suspect your $db is the problem, or the table doesn't exist.

Also, with prepared statements you should run the prepare outside of the loop, you can then execute it with different parameters on each iteration which will give you a performance improvement.