• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 138
  • Last Modified:

How can I INSERT an ARRAY into MySQL?

Hi Guys,
They say "breaking up is hard to do." But it shouldn't be!

I'm trying to break up a gigantic table into smaller tables.
I have the SELECT query right (I think).
But the INSERT query (as I suspected) only inserts one row.

So, how should I write the INSERT query?
Or is my entire effort screwy?
Thanks,
Sas
PS: I must use mysql_query not mysqli

$N_zip = "R_zip ='10001' OR R_zip ='10006' OR R_zip ='10012'" ;

$get="SELECT * from TABLE_NewYorkState WHERE $N_zip ";
$got=mysql_query($get)  or die("Could not get your list <P>". mysql_error());  //    );//
while ($row=mysql_fetch_array($got)) {
$R_name = $row ['R_name'];
$R_zip = $row ['R_zip'];
$R_eMail = $row ['R_eMail'];
}

mysql_query("INSERT INTO TABLE_Manhattan (R_name,R_zip,R_eMail) 
VALUES('$R_name','$R_zip','$R_eMail')");

Open in new window

0
sasnaktiv
Asked:
sasnaktiv
  • 5
  • 4
1 Solution
 
NARANTHIRANCommented:
0
 
Ray PaseurCommented:
You need to read this, (the light at the end of the tunnel may be a train) because PHP is doing away with MySQL support.  I don't understand why you think you can't use MySQLi.  Why do you think you can't use it?
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

In the instant case, the script makes a loop retrieving the data from the SELECT query results set, but the INSERT query is outside the loop.  Therefore, the INSERT query only gets run once, after the loop is finished.  See if this design doesn't make more sense.  I added some comments to help clarify what the PHP logic is doing.

<?php // demp/temp_sasnaktiv.php
error_reporting(E_ALL);

// SEE http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28475792.html
// ASSUMES VALID DATA BASE SELECTION AND CONNECTION

// SET UP A WHERE CLAUSE
$N_zip = "R_zip ='10001' OR R_zip ='10006' OR R_zip ='10012'" ;

// SET UP A QUERY STRING
$get = "SELECT * from TABLE_NewYorkState WHERE $N_zip ";

// RUN THE QUERY OR FAIL ON ERROR
$got = mysql_query($get) or trigger_error("QUERY FAIL: $get BECAUSE ". mysql_error(), E_USER_ERROR);

// IF WE GOT HERE WE MAVE A RESULTS SET IN $got
while ($row = mysql_fetch_object($got)) 
{
    // ESCAPE THE DATA FOR SAFE USE IN THE NEXT QUERY
    $R_name  = mysql_real_escape_string($row->R_name);
    $R_zip   = mysql_real_escape_string($row->R_zip);
    $R_eMail = mysql_real_escape_string($row->R_eMail);
    
    // INSERT THE DATA INTO THE TABLE
    mysql_query("INSERT INTO TABLE_Manhattan (R_name,R_zip,R_eMail) VALUES ('$R_name','$R_zip','$R_eMail')");
}

Open in new window

0
 
sasnaktivAuthor Commented:
Thanks for all the help Guys.
Ray, your explanation and execution were incredibly easy to understand and follow.
It functioned very well. And I could see what I was doing wrong. However when executing the INSERT query I got this warning:
Fatal error: Maximum execution time of 30 seconds exceeded in line 25.

Now, the reason I'm sticking with "mysql_query" and shy of "mysqli_query" is because I would have to rewrite HUNDREDS of php documents. The chances of missing something are extremely high, and would be fatal to the project.
It's much more reliable for me to set my server so that it only uses the former version of PHP.

Now, can I pass these values ('$R_name','$R_zip','$R_eMail') on to another php document using a GET or POST method, so I don't have to perform a SELECT in subsequent documents?
Thanks a lot,
Sas
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ray PaseurCommented:
Fatal error: Maximum execution time of 30 seconds exceeded in line 25.
How many rows of data are in play here?
Now, the reason I'm sticking with "mysql_query" and shy of "mysqli_query" is because I would have to rewrite HUNDREDS of php documents. The chances of missing something are extremely high, and would be fatal to the project.
That is factually untrue.  You can make two connections to the data base server, one for MySQL just like you're doing now.  And one for MySQLi.  Then you can convert the queries one at a time.  Any text editor can look for "mysql_query" to help you find the queries.  There is nothing wrong with using both extensions simultaneously.  There is everything very wrong with ignoring a deprecation notice!
can I pass these values ('$R_name','$R_zip','$R_eMail') on to another php document using a GET or POST
Yes, I am sure you can.  But I don't get where you're going with the part about "don't have to perform a SELECT in subsequent documents?"
0
 
sasnaktivAuthor Commented:
Thanks for the quick reply, Ray.
The TABLE_NewYorkState table contains well over 6 MILLION rows!
That's why I'm breaking the table up into counties. Some counties are under, some over 1 MILLION rows.

With populations that huge I do not want to have to execute a SELECT query with the next document in line, nor the one following that one. Because the wait times will be far too much for the visitor to tolerate, and I will lose them. Not to mention that I'll get a negative reputation. Remember Obama's nightmare?

Odd thing though, even though I got that error message, the INSERT did execute all the rows. I'd like to solve that because I want to DELETE all the rows  from  TABLE_NewYorkState that I INSERT into  TABLE_Manhattan.

Now, maybe we should address the "mysqli" issue later. If I can run both PHP versions, your solution seems quite logical. I don't know if the server will allow two versions, and that opens up an entire new chapter which I'd like to put aside for the near future.
0
 
Ray PaseurCommented:
I don't know if the server will allow two versions... Easy enough to try it!
Can you please post the CREATE TABLE statements for TABLE_NewYorkState and TABLE_Manhattan?  I would like to see the indexed columns.

Also, you can increase the execution time for your script with PHP set_time_limit().  It sounds like the time spent by MySQL is being added to the execution time for your PHP script.  So you call MySQL to do a big INSERT and MySQL does the INSERTing, but when control gets back to PHP, the script has timed out.  Not sure about that, but without any more information than we have now, it might be.
0
 
sasnaktivAuthor Commented:
Hi Ray,
I put the set_time_limit(0);
I think that will allow it to function until MySQL query has been completed.
I'm not getting the time out error any longer.

As far as providing the CREATE-TABLE statements go -- they don't exist. I created the tables manually with phpMyAdmin.

Now, should I open a new question in regard to passing the array values to subsequent documents?
Thanks for all the help,
Sas
0
 
Ray PaseurCommented:
set_time_limit(0);
Yikes -- never do that!  If your script loops, it will go on forever.  Instead go into your script near where the INSERT query is done and do set_time_limit(2); before each INSERT.  After the INSERT loop ends, do set_time_limit(30) for the rest of the script.

The CREATE TABLE statements do exist.  Use SHOW CREATE TABLE to get them listed out.

I think the part about passing array values in the request variables is a completely different question, so you might want to open a new one for that part of things.
0
 
sasnaktivAuthor Commented:
Thanks for your help and patience Ray.
It's performing properly.
And although I don't quite understand how & where (you say "near" the INSERT query) to place the set_time_limit(2); and set_time_limit(30); the queries are doing the job.
Thanks again,
Sas
0
 
Ray PaseurCommented:
Thanks for the points and thanks for using E-E.  If you read the post carefully, you will see that I write this:
set_time_limit(2); before each INSERT
and this
After the INSERT loop ends, do set_time_limit(30) for the rest of the script
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now