MySQL inside for loop. Insert only runs on first loop

Question

I created a for loop that loops through an array or words exploded from a string. The for loop works and has been tested by echo statements. When running this code it only inserts 1 record into the database rather than the number of strings in the array.

$item=explode(" ", $items);
for ($i = 0; $i < count($item); ++$i) {
    // Create connection
$conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "INSERT INTO invlog (itemid, qty)
VALUES ('".$item[$i]."', '-1')";

if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);    
    }

sidenote: why not take out that connection outside before the loop. no need to connect/close every iteration — Kevin
– Kevin, Commented Nov 22, 2014 at 2:11
Are you getting an error message or does it seem to be failing silently? — paxdiablo
– paxdiablo, Commented Nov 22, 2014 at 2:11
Connecting and closing for each iteration like this is absurd. — Ryan
– Ryan, Commented Nov 22, 2014 at 2:16
no errors. also I have tried moving both the close and conn outside the for many times, still has the same issues — chuck beyor
– chuck beyor, Commented Nov 22, 2014 at 2:19
@chuckbeyor what does $items contain? you should post it in the question too. if one of those words are quoted, then your query will be screwed. you should bind them instead. — Kevin
– Kevin, Commented Nov 22, 2014 at 2:23

Kylie · Accepted Answer · 2014-11-22 02:11:39Z

2

For efficiancy, and to avoid multiple database calls, you should really only execute the statemnt once.

So loop through, create the query, then execute it.

Like so..

$item=explode(" ", $items);
$query="";
 for ($i = 0; $i < count($item); ++$i) {
  // Build Query 
  $query.="(".$item[$i].",-1),";
 }
 $query = rtrim($query); // Remove last trailing comma from the right hand side of query string

 //Connect to database, and do DB stuff outside of loop in one call
 $conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
 // Check connection
 if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
 }
 $sql = "INSERT INTO invlog (itemid, qty)
 VALUES ".$query;

  if (mysqli_query($conn, $sql)) {
    echo "New records created successfully";
  } else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
  }

  mysqli_close($conn);

answered Nov 22, 2014 at 2:11

Kylie

11.8k11 gold badges51 silver badges81 bronze badges

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

6 Comments

chuck beyor Over a year ago

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 '4321 9876 6543,-1),'

chuck beyor Over a year ago

trialling comma is still there

Kevin Over a year ago

@chuckbeyor well if you look at that, it wasn't really exploded. '4321 9876 6543,-1),' the numbers are still joined with a space. you should put var_dump($item) in your question as well. and just use rtrim($query, ',') for that

chuck beyor Over a year ago

This is the query it makes (itemid, qty) VALUES (1234 4321 9876 6543,-1)

chuck beyor Over a year ago

I got a question. If these were submited in a text area and each one was on a new line might that be a issue?

|

chuck beyor · Accepted Answer · 2014-11-22 02:56:34Z

0

$replacer  = array("\r\n", "\n", "\r", "\t", "  ");
$items = str_replace($replacer, " ", $items);
$item=explode(" ", $items);
// Create connection
$conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}


for ($i = 0; $i < count($item); ++$i) {

$sql = "INSERT INTO invlog (itemid, qty)
VALUES ('".$item[$i]."', '-1')";

if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}


    }
    mysqli_close($conn);

answered Nov 22, 2014 at 2:56

chuck beyor

476 bronze badges

Collectives™ on Stack Overflow

MySQL inside for loop. Insert only runs on first loop

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related