I'm having trouble with php while loop inserting data in to mysql database

Question

All the php code is below.

The script works fine up until the insert in to cinema database table part. I've echo'd the contents of the variables each run through the loop to ensure that they have all the correct data inside them. But for some reason some of the data is not being inserted in to the database even though it is contained in the variables just before the insert statement. There are 18 loops all together, but only 12 rows of data are being inserted in to the database. I thought maybe its too much data for one insert statement so tried limiting it to ten, then I planned on selecting the rest using the from id desc query, but of the first 10 only 7 inserted telling me that it must be the insert statement, not the database table. I have searched for a reason and tried lots of different things but still no joy. This is literally a last resort for me, as I am very much a DIY person and don't like asking for help. Thanks in advance for any help I get!

<?php

require('../php/connect.php');

$table = "CREATE TABLE IF NOT EXISTS cinema (
    id int(20) NOT NULL AUTO_INCREMENT,
    tags VARCHAR(200) NOT NULL,
    title VARCHAR(200) NOT NULL,
    description VARCHAR(300) NOT NULL,
    image VARCHAR(100) NOT NULL,
    link VARCHAR (200) NOT NULL,
    PRIMARY KEY(id))";

$result = mysqli_query ($dbc, $table);


$select = "SELECT * FROM search";

$r = mysqli_query ($dbc, $select);

while ( $row = mysqli_fetch_array( $r , MYSQLI_ASSOC ))
{
    $tags = $row['tags'];
    $title = $row['title'];
    $description = $row['description'];
    $image = $row['image'];
    $link = $row['link'];

    echo $tags;
    echo $title;
    echo $description;
    echo $image;
    echo "$link <br />";
    echo "one <br /><br />";

    $insert = "INSERT INTO cinema (tags, title, description, image, link)
                VALUES ('$tags', '$title', '$description', '$image', '$link')";

    $run = mysqli_query ($dbc, $insert);                        
}


if($result) {
    echo 'success';
}

else {
    echo 'failed';
}

Is it possible that any of those variables contain a '? Try prepared statements, they work perfectly for this use case. — John V.
– John V., Commented Jul 20, 2013 at 14:53
revoua thanks for your reply. No its not saying there is any errors. just not inserting all the the data. — Bobby Longstock
– Bobby Longstock, Commented Jul 20, 2013 at 17:29
John thanks for your reply. I am not too familiar with prepared statements. and it is possible that some of the variables could contain a ' would that cause a problem? — Bobby Longstock
– Bobby Longstock, Commented Jul 20, 2013 at 17:31

Paul G · Accepted Answer · 2014-11-04 18:07:42Z

1

Use the LOW PRIORITY keyword in your insert statement to avoid redundancy when the data is large.

INSERT LOW PRIORITY INTO cinema('a','b','c') VALUES('x','y','c')

edited Nov 4, 2014 at 18:07

Paul G

2,7424 gold badges37 silver badges54 bronze badges

answered Nov 4, 2014 at 17:20

Misheck Ithagu

111 bronze badge

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

Comments

Maximus2012 · Accepted Answer · 2013-07-20 15:01:21Z

0

Try this:

$r = mysqli_query ($dbc, $select);

while ( $row = mysqli_fetch_array( $r , MYSQLI_ASSOC ))
{  
    $tags = $row['tags'];
    $title = $row['title'];
    $description = $row['description'];
    $image = $row['image'];
    $link = $row['link'];

    echo $tags;
    echo $title;
    echo $description;
    echo $image;
    echo "$link <br />";
    echo "one <br /><br />";

    $insert = "INSERT INTO cinema (tags, title, description, image, link)
            VALUES ('$tags', '$title', '$description',               
   '$image', '$link')";

    $run = mysqli_query ($dbc, $insert);                        

    if($run) {
        echo 'success';
    }
    else {
       echo 'failed';
    }       
}

answered Jul 20, 2013 at 15:01

Maximus2012

1,8392 gold badges12 silver badges15 bronze badges

Comments

Vivek Sadh · Accepted Answer · 2013-07-20 15:07:16Z

0

Regarding your code not inserting all data, it might be possible that the data which you are inserting might contain duplicate ID which is a primary key so that particular insert statement is failing.

Suppose:- You are executing loop 10 times(10 rows), then it might be possible that out of 10 rows 3 rows contain duplicate id(a primary key) so 3 insert statements are not getting executed.

edited Jul 20, 2013 at 15:07

answered Jul 20, 2013 at 14:55

Vivek Sadh

4,2683 gold badges35 silver badges55 bronze badges

15 Comments

Maximus2012 Over a year ago

He is using mysqli_ functions.

Vivek Sadh Over a year ago

Oh... I didn't see it :P

Maximus2012 Over a year ago

Also, the duplicate logic will not kick in since cinema table has a primary key with auto-increment and OP is not inserting primary key values in the insert query. The PK duplicate error comes in when you explicitly try to insert a value for a primary key when it already exists.

Vivek Sadh Over a year ago

@Maximus2012 I think he edited the question. Initially he was trying to insert ID.

Bobby Longstock Over a year ago

thanks for the replies. The ID is auto increment on the cinema table. The data is selected from another database table called search which has 18 entries and the id for that is also auto increment.

|

Collectives™ on Stack Overflow

I'm having trouble with php while loop inserting data in to mysql database

3 Answers 3

Comments

Comments

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related