PHP mysqli_fetch_array() does not insert all select queries into a table in a while loop

Question

I have a piece of PHP code as below:

$DB_FIRST_NAME = 'NYC';
$DB_SECOND_NAME = 'SFBAY';
$DB_HOST = 'localhost';
$DB_USER = '***';
$DB_PASS = '***';

$db_connect_first = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_FIRST_NAME);   
$select_query_first = "SELECT field1, field2, field3 FROM names WHERE date > '2014-04-04 00:00:00'";
$query_result_first = $db_connect_first->query($select_query_first);

$db_connect_second = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_SECOND_NAME, true);   
while($row = $query_result_first->fetch_assoc()){
    $field1 = $row['field1'];
    $field2 = $row['field2'];
    $field3 = $row['field3'];
    $insert_query_second = "INSERT INTO hello (firstname, lastname, dob) VALUES ('$field1', '$field2', '$field3')";
    $insert_en_result = $db_connect_second->query($insert_query_second);
}

The SELECT query returns 20 rows; however, the INSERT query only inserts twelve rows into the hello table. I get this error for the queries which are not inserted into the table mysqli_error() expects parameter 1 to be mysqli, boolean given in.... Would you please take a look and let me know the problem?

Note: Select query has no error, and the code perfectly echos the $field1, $field2 and $field3 inside the while loop. But, Insert query not working for all rows. Also, table structures are similar but they have different field names.

  CREATE TABLE IF NOT EXISTS names (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `field1` text NOT NULL,
  `field2` text NOT NULL,
  `field3` text NOT NULL,
  PRIMARY KEY (`id`),
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;

  CREATE TABLE IF NOT EXISTS hello ( 
     id int(11) NOT NULL AUTO_INCREMENT, 
     firstname text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
     lastname text CHARACTER SET utf8 COLLATE utf8_unicode_ci, 
     dob mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci, 
     PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=400 ;

below is the picture from Describe hello query in database. enter image description here

also, below is the result from SHOW INDEX FROM hello enter image description here

Please don't post credentials to your database in your code when posting to SO. Irregardless of if we know your site address or not, malicious people are everywhere. — Ohgodwhy
– Ohgodwhy, Commented Apr 7, 2014 at 17:32
@Ohgodwhy, thanks for your comment, sure, I'll be more careful, cheers, — Sami
– Sami, Commented Apr 7, 2014 at 17:34
can you show your second table structure and same sample data ? — echo_Me
– echo_Me, Commented Apr 7, 2014 at 17:35
Furthermore, when you're using the die() within the while loop, the script is going to close and will not complete execution. The issue is likely due to the 4th row that you're retrieving. Please have a look at this try/catch method, instead — Ohgodwhy
– Ohgodwhy, Commented Apr 7, 2014 at 17:35
You are leaving your code wide open to SQL injection attacks. Also, if any of the fields has an apostrophe in it, your query will blow. Please learn about how to use parametrized queries. — Andy Lester
– Andy Lester, Commented Apr 7, 2014 at 17:50

Mike Brant · Accepted Answer · 2014-04-07 18:32:37Z

3

You should just do this with a single query:

INSERT INTO SFBAY.hello
    (firstname, lastname, dob)
SELECT field1, field2, field3
FROM NYC.names
WHERE date > '2014-04-04 00:00:00'

There is no need whatsoever to first query the result set from one DB and table and then individually loop through the result set making inserts into another DB and table.

As long as the user you are using has property permissions on both databases, which seem to be the case in your example, you can work across multiple databases in a single query.

answered Apr 7, 2014 at 18:32

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

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

2 Comments

Sami Over a year ago

thanks for your answer. In fact my real problem is doing a lot more comparisons between different fields in multiple tables. I would be appreciative if you could post your single query in a while loop that I can put other conditions before the final insertion of data,

Mike Brant Over a year ago

@Apiah Can you explain what these conditions might be and is there any reason why they wouldn't be able to be incorporated into the WHERE clause?

Sami · Accepted Answer · 2014-04-08 15:54:21Z

1

thanks all for your comments and answers. The problem was much simpler that I thought! It was because there was some HTML data in field1 and field3 columns, so I simply passed the field1, field2 and field3 inside the while loop through the mysqli_real_escape_string() function as follows:

$field1 = mysqli_real_escape_string($db_connect_second, $row['field1']);
$field2 = mysqli_real_escape_string($db_connect_second, $row['field2']);
$field3 = mysqli_real_escape_string($db_connect_second, $row['field3']);

edited Apr 8, 2014 at 15:54

answered Apr 8, 2014 at 15:46

Sami

1,4912 gold badges17 silver badges39 bronze badges

Collectives™ on Stack Overflow

PHP mysqli_fetch_array() does not insert all select queries into a table in a while loop

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related