1

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

Sami
  • 1,473
  • 1
  • 17
  • 37
  • 2
    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 Apr 07 '14 at 17:32
  • 1
    @Ohgodwhy, thanks for your comment, sure, I'll be more careful, cheers, – Sami Apr 07 '14 at 17:34
  • 1
    can you show your second table structure and same sample data ? – echo_Me Apr 07 '14 at 17:35
  • 2
    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](http://us1.php.net/mysqli_error#60669) – Ohgodwhy Apr 07 '14 at 17:35
  • I guess, any of your values in `$field1`, or `$field2` or `$field3` is empty and not set. And because of this reason your query fails. Turn on your error reporting to check. – Sarvap Praharanayuthan Apr 07 '14 at 17:39
  • @Ohgodwhy, I removed the `die()`, and the query inserted 12 rows, but still 8 rows are not inserted, – Sami Apr 07 '14 at 17:45
  • @echo_Me, this is the table structure, `CREATE TABLE IF NOT EXISTS `hello` ( `id` int(20) NOT NULL AUTO_INCREMENT, `field1` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, `field2` text CHARACTER SET utf8 COLLATE utf8_unicode_ci, `field3` mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=400 ; ` – Sami Apr 07 '14 at 17:46
  • @Fred-ii- thanks for your comment, is there something I'm missing in coding? – Sami Apr 07 '14 at 17:48
  • 4
    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 Apr 07 '14 at 17:50
  • where is `dob` column in your table structure ? – echo_Me Apr 07 '14 at 17:55
  • you didnt show hello table structure which i asked for. – echo_Me Apr 07 '14 at 17:58
  • When using `mysqli` you should be using parameterized queries and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation to accomplish this because you will probably create severe [SQL injection bugs](http://bobby-tables.com/). `stripslashes` is not the thing you want to be using here. That's to counteract [magic quotes](http://www.php.net/manual/en/security.magicquotes.php) which should be off. – tadman Apr 07 '14 at 18:08
  • @echo_Me, I added to your edit, thanks, – Sami Apr 07 '14 at 18:16
  • @tadman, could you please edit the post to change the `stripslashes` function? – Sami Apr 07 '14 at 18:17
  • @Apiah have you some indexes/unique_keys ? in hello table ? – echo_Me Apr 07 '14 at 18:20
  • @echo_Me, not really, in fact, in order to find the problem, I've created a test table with same fields name and structure like `names` table, but still I'm not successful to insert all queries, – Sami Apr 07 '14 at 18:24
  • @Apiah It's not a case of changing it. That shouldn't be used in the first place. Use placeholders to be sure your data is going in correctly. See the `bind_param` link in my comment for examples. – tadman Apr 07 '14 at 18:26
  • can you run this `DESCRIBE hello;` and post the result in your question ? – echo_Me Apr 07 '14 at 18:27
  • @Apiah can you run this also `SHOW INDEX FROM hello` and post the results please ? – echo_Me Apr 07 '14 at 19:05
  • @echo_Me, please see the result – Sami Apr 07 '14 at 19:14
  • @Apiah i really just guessing that you are inserting duplicate entries and then the insert didnt enter the duplicates. so you got just 12 rows and 8 are duplicated. – echo_Me Apr 07 '14 at 19:20
  • @echo_Me, I get this error for the queries that can not be inserted `Warning: mysqli_error() expects parameter 1 to be mysqli, boolean given in...` – Sami Apr 07 '14 at 20:58
  • possible duplicate of [mysql\_fetch\_array() expects parameter 1 to be resource, boolean given in select](http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-boolean-given-in-select) – John Conde Apr 08 '14 at 02:54

2 Answers2

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.

Mike Brant
  • 70,514
  • 10
  • 99
  • 103
  • 1
    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, – Sami Apr 08 '14 at 00:50
  • 1
    @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? – Mike Brant Apr 08 '14 at 19:59
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']);
Sami
  • 1,473
  • 1
  • 17
  • 37