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

问题描述：

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?

答

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.

答

I have provided some sample code with some effective error checking. The error you report is due to a problem occurring in the MySQL call, and your code won't detect the real cause of the error without some proper error checking.

Note that I have reversed the order of the values provided to the mysqli_statement, since the order you have them in your code will provide the values to your database in the order section_name, section_id, the reverse of the order you have them in your table. I have also removed the prepare, bind and close calls from the loop.

This code works on my test server and reports errors in the appropriate places. Use this as a template.

<?php

$db = mysqli_connect('dbhost','dbuser','dbpass','dbname'); 
$sections = array('Latest News', 'Commentary', 'Impact', 'Opportunities', 'Policy', 'Events', 'People', 'Market', 'Careers');

if (($stmt = mysqli_prepare($db, "INSERT INTO cdfi_sections VALUES (?, ?)")) === false) {
  die(mysqli_error($db));
}

$idx = 0;
$text = '';

if (mysqli_stmt_bind_param($stmt, 'is', $idx, $text) === false) {
  die(mysqli_stmt_error($stmt));
} 

foreach($sections as $key => $value)
{
  $idx = $key;
  $text = $value;
  echo "idx:$idx, text:$text";
  if (mysqli_stmt_execute($stmt) === false) {
    die(mysqli_stmt_error($stmt));
  }
}

mysqli_stmt_close($stmt);
?>

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

相关推荐