PHP inserting only 10 rows to Mysql via a foreach loop

Question

I am stuck while inserting data to my mysql table using a foreach loop to insert multiple rows at a time.

foreach($event as $booking){
    
    $startDate = $booking[DTSTART];
    $checkIn = strtotime($startDate[value]);
    
    $check_in = date("Y-m-d", $checkIn);
    
    $endDate = $booking[DTEND];
    $checkOut = strtotime($endDate[value]);
    
    $check_out = date("Y-m-d", $checkOut);
    
    $booking_source = "Airbnb";
    
    $source_id = "2";
    
    $summary = $booking[SUMMARY];
    
    $description = $booking[DESCRIPTION];
    
    $uid = $booking[UID];
    
    $sql = "INSERT INTO test_ics (uid, check_in, check_out, summary, description, source, source_id) VALUES (?,?,?,?,?,?,?)";
            
    $result = $conn->prepare($sql);
    $result->bind_param('sssssss', $uid, $check_in, $check_out, $summary, $description, $booking_source, $source_id);
    $result->execute();
    echo "<br>";
    echo "$check_in";
}

The code is working fine and inserting all the rows when I remove description and summary fields from the table where both of them are VARCHAR(2550) but when I am trying to run the code to insert rows with description and summary (like it is in the code above) its inserting only 10 rows.

Thanks

Notice: Use of undefined constant DTSTART - assumed 'DTSTART' in /home/fadipro/public_html/admin/get-airbnb-bookings.php on line 40

Notice: Use of undefined constant value - assumed 'value' in /home/fadipro/public_html/admin/get-airbnb-bookings.php on line 41

Notice: Use of undefined constant DTEND - assumed 'DTEND' in /home/fadipro/public_html/admin/get-airbnb-bookings.php on line 45

Notice: Use of undefined constant value - assumed 'value' in /home/fadipro/public_html/admin/get-airbnb-bookings.php on line 46

Notice: Use of undefined constant SUMMARY - assumed 'SUMMARY' in /home/fadipro/public_html/admin/get-airbnb-bookings.php on line 54

Notice: Use of undefined constant DESCRIPTION - assumed 'DESCRIPTION' in /home/fadipro/public_html/admin/get-airbnb-bookings.php on line 56

Notice: Use of undefined constant UID - assumed 'UID' in /home/fadipro/public_html/admin/get-airbnb-bookings.php on line 58

Best guess is that there is some character in description or summary which is causing the query to fail. To monitor that in the foreach check the status of the sql insertion and if it fails in an array push the error message and print that array after the foreach. You can then check the exact cause and then fix it accordingly... — Lalbhusan Yadav
– Lalbhusan Yadav, Commented Mar 12, 2018 at 12:10
Hard to know really unless we can see the value of $event. How many records are you expecting to be inserted? — Rob Forrest
– Rob Forrest, Commented Mar 12, 2018 at 12:11
Add a var_dump($event); to check the values. Maybe the summary/desc is bigger than you table field? — ZeissS
– ZeissS, Commented Mar 12, 2018 at 12:13
$result = $conn->prepare($sql); $result->bind_param('sssssss', $uid, $check_in, $check_out, $summary, $description, $booking_source, $source_id); $result->execute();...any one of these three statements could fail and return false (or throw an exception, depending on your config) but you don't seem to be testing any of them (and then checking the value of $conn->error), either that or you're suppressing exceptions somewhere — ADyson
– ADyson, Commented Mar 12, 2018 at 12:23

Adder · Accepted Answer · 2018-03-12 12:52:05Z

1

You can remove the notices by putting the string indexes in quotes like e.g. this $booking['DTSTART'].

To catch the database errors, I assume you are using exceptions to report them. This code catches and prints the exception:

    try {
        $result = $conn->prepare($sql);
        $result->bind_param('sssssss', $uid, $check_in, $check_out, $summary, $description, $booking_source, $source_id);
        $result->execute();
    }
    catch (Exception $e)
    {
        echo $e->getMessage();
    }

My guess is it will reveal data that is too long for the column.

answered Mar 12, 2018 at 12:52

Adder

5,8982 gold badges35 silver badges60 bronze badges

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

Comments

ADyson · Accepted Answer · 2018-03-12 13:18:03Z

1

If your fields name is not constant then you have to write it with single quotes

foreach($event as $booking){
  $startDate = $booking['DTSTART'];
  $checkIn = strtotime($startDate['value']);

  $check_in = date("Y-m-d", $checkIn);

  $endDate = $booking['DTEND'];
  $checkOut = strtotime($endDate['value']);

  $check_out = date("Y-m-d", $checkOut);

  $booking_source = "Airbnb";

  $source_id = "2";

  $summary = $booking['SUMMARY'];

  $description = $booking['DESCRIPTION'];

  $uid = $booking['UID'];

  $sql = "INSERT INTO test_ics (uid, check_in, check_out, summary, description, source, source_id) VALUES (?,?,?,?,?,?,?)";

  $result = $conn->prepare($sql);
  $result->bind_param('sssssss', $uid, $check_in, $check_out, $summary, $description, $booking_source, $source_id);
  $result->execute();
  echo "<br>";
  echo "$check_in";
}

edited Mar 12, 2018 at 13:18

ADyson

62.9k16 gold badges86 silver badges99 bronze badges

answered Mar 12, 2018 at 13:06

Gondaliya Darshan

416 bronze badges

4 Comments

ADyson Over a year ago

perhaps worth highlighting exactly what you changed? It may not be immediately obvious to the reader. Also you missed some (value, UID) so I've altered that, and corrected your formatting error. And actually double or single quotes would be fine here, the important thing is to turn it from a constant into a string.

Rajan Benipuri Over a year ago

Hi @ADyson thanks for your help but the issue was in the file I am getting data from. Actually, DESCRIPTION was not available in all the rows and also it was NULL in some of the rows that's why I was getting the errors. I have added NULL as default value in my DB and also added if(isset) in my PHP to fix the issue. Thanks anyways for your help.

ADyson Over a year ago

@RajanBenipuri No problem. But my advice was relevant. If you'd been checking SQL errors you'd have spotted that earlier because SQL would have told you that you couldn't insert a NULL value into that field. And it seems I was correct in assuming that a particular row (or rows) was at fault, hence why it added 10 and then stopped.

Rajan Benipuri Over a year ago

Indeed your advice was relevant and useful at the same time. Thanks

Collectives™ on Stack Overflow

PHP inserting only 10 rows to Mysql via a foreach loop

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related