Why does my PHP foreach loop only insert one value to SQL?

Question

I am trying to populate an SQL table with strings from an array called $title.
Here is my PHP code:

// To store the values to insert into the database tables.
$cid = 0; // Primary key.
$dates = array();
$number = array();
$title = array("aaa", "bbb", "ccc");
$description = array();

// For connecting to database.
$user = 'root';
$pass = '';
$db = 'contractsdb';

// Assign HTML file contents to $content.
$content = file_get_contents('C:\xampp\htdocs\HTMLParser/tester.html');

// Load HTML page into Document Object Model for parsing HTML.
$dom = new DOMDocument;    
$dom->loadHTML($content);

// Connect to DB and check connection.
$con=mysqli_connect("localhost", "$user", "$pass", "$db");
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// Insert the values into the database contracts table.
foreach ($dom->getElementsByTagName('a') as $link) {
    $sql = "INSERT INTO contracts (CID, TITLE)
                           VALUES ('$cid', '$title[$cid]')";
    echo "1 record added<BR>";
    $cid++; 
} 

// Error message if fail.
if (!mysqli_query($con, $sql)) {
  die('Error: ' . mysqli_error($con));
}

// Close connection to database.
mysqli_close($con);

When I run this code through XAMPP, and look at the table, I get this:
enter image description here
As you can see, only one row is added. What am I missing?
Is there something wrong with the foreach syntax?

The HTML page prints out 3 lots of "1 record added", why doesn't the table contain 3 rows?

pavel · Accepted Answer · 2014-10-20 10:56:47Z

2

You overwrite $sql variable in each loop.

$sql = 'INSERT INTO contracts (CID, TITLE) VALUES ';

foreach ($dom->getElementsByTagName('a') as $link) {
    $sql .= "($cid, '$title[$cid]'),"; // $cid is INT, so don't use quotes, in the second case you should you real_escape_string.
    $cid++; 
} 

if (!mysqli_query($con, rtrim($sql, ','))) {...}

answered Oct 20, 2014 at 10:56

pavel

27.3k11 gold badges47 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

trzyeM- · Accepted Answer · 2014-10-20 10:59:41Z

2

Everything is ok.

As u can see, You have echo in foreach loop but mysqli_query outside loop. So You execute only one statement:

INSERT INTO contracts (CID, TITLE) VALUES (2, 'ccc')

answered Oct 20, 2014 at 10:59

trzyeM-

9638 silver badges10 bronze badges

Collectives™ on Stack Overflow

Why does my PHP foreach loop only insert one value to SQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related