Hi there.

I have this script, it creates a temporary table (table1) in a database when it loads up and populates it with data from a csv file using a LOAD DATA INFILE query, then it loops through each of the records comparing them with records in another table (table2) in the database, if the record is present in table2 it updates it with the new record from table1, if it is not in table2 it writes the new record to the table.

The whole process works fine as I have tried it locally on my test server, however it requires about 600 seconds to execute the script so I had to edit the timeout for the server and max_execution_time in the php.ini file, however I am not going to be able to make these changes on the webspace where it will be used. And I would rather do it another way anyway.

Does anyone know a better way to do it or have any suggestions?

I have tried using "set_time_limit(0);" but it didnt make any difference.

I would, personally, make the adjustments locally, after synchronizing data from development server and live server, output the updated data into a file. Upload that file into the live server into a table other than the live one. In chunks if need be (each file should probably be no more than a MB or so - at least that's been my experience). And after all is uploaded, drop the old table and rename the new table to the name of the old one.

I can't really see any ISP adjusting the max execution time, I mean its there for a reason, namely to keep server resources from being exploited by poorly designed scripts.

If this is a busy table, constantly being updated by users, I would then plan downtime during low traffic hours and take the service offline temporarily while the information was being updated. Which would amount to what? A few minutes?

But that's just my plan!

: )
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::

I have split my upload by limiting the sql query so now it processes 1000 records at a time, then refreshes the page and does the next 1000 untill it goes through all the records, and this has taken care of timout issue. However I am not quite happy with it yet because it still takes ome time, so I think it may be possible to speed the process up by using less sql queries, at the moment I use

$sql_check="SELECT * FROM phone_main WHERE work_no = '" . $row_tmp['work_no'] . "'";

To check if there is already a record there with the number

   If there is I run an update query

   $sql_update="UPDATE phone_main SET
   name = '" . addslashes($row_tmp['name']) . "',
   department = '" .addslashes($row_tmp['department']) . "',
   location = '" . addslashes($row_tmp['location']) . "'
   WHERE work_no = '" . addslashes($row_tmp['work_no']) . "'";

   else I run an insert query

Is it possible to do it using less queriesperhaps with join or something? I'm open to suggestions cos Im not to hot with sql joins.

Thanks

You're right -- you're loops are horribly inefficient. You select ALL The rows of your temp table, then loop through each individual row and perform two queries per row (one SELECT to see if it exists in phone_main, then either an UPDATE or INSERT based on existence or lack thereof).

You should be able to do everything in just two queries and let MySQL figure out which sets are to be manipulated.

First, the update query:

UPDATE phone_main, TempTable
   SET phone_main.name = TempTable.name,
       phone_main.department = TempTable.department,
       phone_main.location = TempTable.location,
 WHERE phone_main.work_no = TempTable.work_no


Now, the insert query... Unfortunately, MySQL doesn't allow subselects (with most versions/table types). There's not an easy way to select the difference between two tables without it.

For example, this is an illegal query in MySQL:
  SELECT * FROM Table1
   WHERE Table1.Col1 NOT IN
         SELECT DISTINCT Col1 FROM Table2

The trick is to use a LEFT JOIN on two corresponding columns, and check where the right column is NULL. (For more information why this works, read up on JOINs -- there's LOTS of tutorials and documentation out there!)

SELECT Table1.Col1
  FROM Table1
       LEFT JOIN Table2 ON Table1.Col1 = Table2.Col1
 WHERE Table2.Col1 IS NULL;

This will return all the rows from Table1 where the value of Col1 does NOT appear in Table2.

Using this knowledge, we attempt to write the INSERT query you need.

INSERT INTO phone_main
       SELECT * FROM TempTable
                LEFT JOIN phone_main ON
                     TempTable.work_no = phone_main.work_no
       WHERE phone_main.work_no IS NULL


I can't guarantee that this will work! I haven't tested it myself. The gist of the query is that it inserts into phone_main all the rows from TempTable where the work_no does not already exist in phone_main.


As always, the FIRST place you should look for answers is the manual!
  http://www.mysql.com/doc/en/UPDATE.html
  http://www.mysql.com/doc/en/INSERT.html
  http://www.mysql.com/doc/en/INSERT_SELECT.html


Finally, if you have a primary key in your phone_main table, then you should consider a MUCH simpler method:

  INSERT ... ON DUPLICATE KEY UPDATE

I'll leave it to you to read up on this in the MySQL manual.



Take care,

Nik
http://www.bigaction.org/

Thanks for the tips. I wonder if its possible to get a couple more, im having a few problems and have been looking around on the internet but cannot find answers.

With this query-

INSERT INTO phone_main (name, location, department)
       SELECT sort_name, address, building FROM tmp_phone_main
                LEFT JOIN phone_main ON
                     tmp_phone_main.int_extension = phone_main.work_no
       WHERE phone_main.work_no IS NULL

I get this error msg- Not unique table/alias: 'phone_main'

The select part works fine cos if I run just that I get the results so it is the select part that is tripping it up. My searching says the it is not possible to run such a query if same table name appears in the INSERT and SELECT parts, but its in the JOIN part should that still cause it to fail?


When I run the other query I just keep getting errors but none specific it just says-

You have an error in your SQL syntax near ' tmp_phone_main SET phone_main.name = tmp_phone_main.sort_name, phone_main.locat' at line 1

I am using it how it is supposed to -

UPDATE phone_main, tmp_phone_main
   SET phone_main.name = tmp_phone_main.sort_name,
       phone_main.location = tmp_phone_main.address,
       phone_main.department = tmp_phone_main.building
 WHERE phone_main.work_no = tmp_phone_main.int_extension

So ive been fiddling about with it but have been having little success, I have been looking at the manual and my book and it looks correct, Im stuck.

Any help would be appreciated.

I just noticed that the table "phone_main" appears twice in the query. The SQL parser is complaining that the table name, 'phone_main', is not unique, so when you have something like "phone_main.work_no", it doesn't know which phone_main table result set to look at work_no for. We need to use aliases:

INSERT INTO phone_main
       SELECT * FROM TempTable
                LEFT JOIN phone_main AS pm ON
                     TempTable.work_no = pm.work_no
       WHERE pm.work_no IS NULL


Well, not technically... your query is invalid. You can't insert into multiple tables, you can only insert into ONE table. Your query is attempting to insert into two tables.

To insert values into one table from another, you need to use that INSERT...SELECT query I showed you (or something like it).


Take care,

Nik
http://www.bigaction.org/

Im confused now.

This one is for inserting the records which are not already in the table?-

INSERT INTO phone_main
       SELECT * FROM TempTable
                LEFT JOIN phone_main AS pm ON
                     TempTable.work_no = pm.work_no
       WHERE pm.work_no IS NULL


And I though this one would update the ones that were already in the table?-

UPDATE phone_main, tmp_phone_main
   SET phone_main.name = tmp_phone_main.sort_name,
       phone_main.location = tmp_phone_main.address,
       phone_main.department = tmp_phone_main.building
 WHERE phone_main.work_no = tmp_phone_main.int_extension

Im just trying to update phone_main is that not what this says? Update Phone_main with tmp_phone_main? Or am I just missing it completely?

Thanks

Okay, the top query inserts all the records from TempTable into phone_main that don't already exist.

The way this works is because of the left join. In any join, you create rows based on relationships between corresponsing columns in two separate tables.

In a left join, if there is a row in the left table (TempTable) that doesn't have a corresponding row in the right table (pm), the row is still created but the right column value is set to NULL.

In an inner join, rows are only created when the column values from both tables match. Therefore, an inner join is equivalent to a "WHERE right.a = left.a" statement.


On to the second query:

The UPDATE query updates all the rows in phone main that have a work_no column that matches the int_extension column of a row in tmp_phone_main. The new values from phone_main are retrieved from their corresponding columns in tmp_phone_main.


I realize this can be confusing, and bear in mind that I don't have the time to create temporary test tables to verify my queries. That should always be left as an exersize to you, since you're the one that needs the problem solved.

I suggest you create some simple temporary tables and play with joins and queries until you get the hang of them.

TableA
ColA ColB ColC
Hello Nik 1234
World Frank 2345
How John 3456

TableB
ColA ColB ColC
Hello Jimmy 9876
Heya John 8765
Ciao Max 1234


SELECT * FROM TableA
  INNER JOIN TableB ON
        TableA.ColA = TableB.ColA

SELECT * FROM TableA
  LEFT JOIN TableB ON
        TableA.ColA = TableB.ColA

SELECT * FROM TableA
  RIGHT JOIN TableB ON
        TableA.ColA = TableB.ColA



etc...




Take care,

Nik
http://www.bigaction.org/

Thankyou for your help nik, I am now understand JOINs clearer.

I was getting this error-
"INSERT TABLE 'phone_main' isn't allowed in FROM table list"

However I feel this may be something that is beyond MySQL
from the manual-

"The target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query. (The problem is that the SELECT possibly would find records that were inserted earlier during the same run. When using subquery clauses, the situation could easily be very confusing.)"

The other things ive found have said something of the same thing.

Thanks

Ah crap. Damn mysql! See, this is where having a more robust database engine really helps. Nested subselects and temporary tables come in really handy for things like this.

Okay, here's my suggestion. Use a new temporary table. You can populate the new table from the result rowset of a select query. This way, MySQL automatically knows all the column data types and names.


CREATE TEMPORARY TABLE new_temp_table
    SELECT * FROM TempTable
        LEFT JOIN phone_main ON
            TempTable.work_no = phone_main.work_no
    WHERE phone_main.work_no IS NULL

INSERT INTO phone_main
    SELECT * FROM new_temp_table



MySQL will automatically delete the temporary table when PHP closes the MySQL connection. If you're using a persistent connection, the table will not be deleted since PHP maintains the database connection across sessions. If this is the case, I'd explicitly drop the new temp table after the insert query.



Take care,

Nik
http://www.bigaction.org/