[SOLVED] Over 100 Records = Constant Loop....???

PHPNewbie55 · January 8, 2008

I am working on a little project for myself.. nothing pressing or urgent at all...

Just trying to learn more about PHP and creating different projects to help myself along...

Here is what I have so far.. The code below is the CSV IMPORT process..

the MySql Database has two tables that I need to populate with data from the CSV file..

1. Products Table

2. Categories Table

So I put all the info into a temporary table so that I can do some calculations and populate certain fields with data.

The problem I am having is while importing a TEST.CSV file with only 100 records in it.. the script works fine.. zips right through all the stuff I have with no problems.

IF the CSV file contains 10,000 records it just goes into a constant loop and never completes...

So I can't figure out why with 100 records everything is fine yet with 10,000 records it never completes...

Here is the CODE... now don't laugh too hard...

it's a NOOB code and I have broken it down step by step so as not confuse my Noob Brain too much...

<?php
if ($action == "import") {
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
  $csv_file = ($_POST["csv_file"]);
  $retailer = ($_POST["retailer_name"]);
  $category = ($_POST["maincat"]);
  $cat = ($_POST["cat"]);
  $sub = ($_POST["sub"]);
  $group = ($_POST["group"]);
  $name = ($_POST["pname"]);
  $description = ($_POST["description"]);
  $keywords = ($_POST["keywords"]);
  $sku = ($_POST["sku"]);
  $brand = ($_POST["brand"]);
  $retail_price = ($_POST["retail_price"]);
  $sale_price = ($_POST["sale_price"]);
  $imageurl = ($_POST["imageurl"]);
  $thumburl = ($_POST["thumburl"]);
  $buyurl = ($_POST["buyurl"]);
  $replace_this = ($_POST["replacethis"]);
  $with_this = ($_POST["withthis"]);

$checktemp = mysql_fetch_array(mysql_query("SELECT * FROM temp"));
if (!$checktemp){  
mysql_query("
CREATE TABLE `temp` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `category` int(10) unsigned NOT NULL default '0',
  `cat` varchar(255) NOT NULL,
  `sub` varchar(255) NOT NULL,
  `group` varchar(255) NOT NULL,
  `cat2` varchar(255) NOT NULL,
  `sub2` varchar(255) NOT NULL,
  `group2` varchar(255) NOT NULL,
  `featured` set('yes','no') NOT NULL default 'no',
  `name` tinytext NOT NULL,
  `description` text NOT NULL,
  `keywords` text NOT NULL,
  `sku` varchar(50) NOT NULL,
  `brand` varchar(255) NOT NULL,
  `retail_price` decimal(65,2) NOT NULL,
  `sale_price` decimal(65,2) NOT NULL,
  `on_sale` varchar(10) NOT NULL,
  `difference` decimal(65,2) NOT NULL,
  `percent_off` decimal(65,2) NOT NULL,
  `imageurl` tinytext NOT NULL,
  `thumburl` tinytext NOT NULL,
  `buyurl` tinytext NOT NULL,
  `retailer` tinytext NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `category` (`category`,`featured`))")
  	or die("<b>Invalid query:</b><hr>" . mysql_error());
} else { /* do nothing */ }
			// Opening the Feed

        $feed = fopen($csv_file, 'r');  
        
   while ($data = fgetcsv ($feed, 10000, ",")) {
$retail = "".$data[$retail_price]."";
$sale = "".$data[$sale_price]."";
$difference = $retail-$sale;
if ($difference > 0) {
$percent_off = $difference/$retail;
$salekeys = ", on sale, discounted, discount, bargains, bargain";
$onsale = "Y";
} else {
$percent_off = "";
$salekeys = "";
$onsale = "";
}

$query = "

INSERT INTO `temp` (
`category`, 
`cat2`, 
`sub2`, 
`group2`, 
`featured`, 
`name`, 
`description`, 
`keywords`, 
`sku`, 
`brand`, 
`retail_price`, 
`sale_price`, 
`on_sale`, 
`difference`, 
`percent_off`, 
`imageurl`, 
`thumburl`, 
`buyurl`, 
`retailer`

) VALUES (

'".$category."', 
'".mysql_real_escape_string(str_replace("/", " ", str_replace("'", "", $data[$cat])))."', 
'".mysql_real_escape_string(str_replace("/", " ", str_replace("'", "", $data[$sub])))."', 
'".mysql_real_escape_string(str_replace("/", " ", str_replace("'", "", $data[$group])))."', 
'no', 
'".mysql_real_escape_string($data[$name])."', 
'".mysql_real_escape_string($data[$description])."', 
'".mysql_real_escape_string($data[$keywords])."".$salekeys."', 
'".$data[$sku]."', 
'".mysql_real_escape_string(addslashes($data[$brand]))."', 
'".$data[$retail_price]."', 
'".$data[$sale_price]."', 
'".$onsale."', 
'".$difference."', 
'".$percent_off."', 
'".$data[$imageurl]."', 
'".$data[$thumburl]."', 
'".str_replace("".$replace_this."", "".$with_this."", $data[$buyurl])."', 
'".$retailer."'
)

";


mysql_query($query) or die("<b>QUERY 1 -- Invalid query:</b><hr>" . mysql_error());
}// end while//////////

mysql_query("DELETE FROM temp WHERE id='1'");

///   OK now all the data is in the temp table... 

///   start creating categories...............................
///.................................................................

//////  CATEGORY  //////////..........//////.....///
$catquery = mysql_query("SELECT * from temp group by cat2");
while ($ccat = mysql_fetch_array($catquery)) {
$checkcatn = mysql_fetch_array(mysql_query("SELECT * FROM dcms_categories WHERE name='".$ccat['cat2']."'"));
$chid = "".$checkcatn['name']."";
if (!$chid) { 
mysql_query("INSERT INTO `dcms_categories` (`parent`, `name`) VALUES ('".$ccat['category']."', '".$ccat['cat2']."')");
} else {
// do nothing
}
}// end while..


//////  SUBCATEGORY   /////////////..../......////////........
$catquery = mysql_query("SELECT * from temp group by sub2");
while ($ccat = mysql_fetch_array($catquery)) {
$checksubn = mysql_fetch_array(mysql_query("SELECT * FROM dcms_categories WHERE name='".$ccat['sub2']."'"));
$chid = "".$checksubn['name']."";
if (!$chid) {
mysql_query("INSERT INTO `dcms_categories` (`parent`, `name`) VALUES ('', '".$ccat['sub2']."')");
} else {
// do nothing
}
}// end while..


//////  PRODUCTGROUP   /////////////..../......////////........
$catquery = mysql_query("SELECT * from temp group by group2");
while ($ccat = mysql_fetch_array($catquery)) {
$checkgn = mysql_fetch_array(mysql_query("SELECT * FROM dcms_categories WHERE name='".$ccat['group2']."'"));
$chid = "".$checkgn['name']."";
if (!$chid) {
mysql_query("INSERT INTO `dcms_categories` (`parent`, `name`) VALUES ('', '".$ccat['group2']."')");
} else {
// do nothing
}
}// end while..




////  now insert the category id's into the temp table....................
////..................................................................................
$gettempinfo = mysql_query("SELECT * FROM temp");
while ($tempdat = mysql_fetch_array($gettempinfo)) {
// Category

//*******PROBLEM AREA MAYBE*******//

$catid = mysql_fetch_array(mysql_query("SELECT * FROM dcms_categories WHERE name='".$tempdat['cat2']."'"))or die("<b>Invalid query:</b><hr>" . mysql_error());
mysql_query("UPDATE `temp` SET `cat` = '".$catid['id']."' WHERE `cat2` ='".$tempdat['cat2']."'"or die("<b>Invalid query:</b><hr>" . mysql_error()));
}// end while......

$gettempinfo = mysql_query("SELECT * FROM temp");
while ($tempdat = mysql_fetch_array($gettempinfo)) {
// Subcategory
$subid = mysql_fetch_array(mysql_query("SELECT * FROM dcms_categories WHERE name='".$tempdat['sub2']."'"))or die("<b>Invalid query:</b><hr>" . mysql_error());
mysql_query("UPDATE `temp` SET `sub` = '".$subid['id']."' WHERE `sub2` ='".$tempdat['sub2']."'")or die("<b>Invalid query:</b><hr>" . mysql_error());
}// end while......

$gettempinfo = mysql_query("SELECT * FROM temp");
while ($tempdat = mysql_fetch_array($gettempinfo)) {
// ProductGroup
$groupid = mysql_fetch_array(mysql_query("SELECT * FROM dcms_categories WHERE name='".$tempdat['group2']."'"))or die("<b>Invalid query:</b><hr>" . mysql_error());
mysql_query("UPDATE `temp` SET `group` = '".$groupid['id']."' WHERE `group2` ='".$tempdat['group2']."'")or die("<b>Invalid query:</b><hr>" . mysql_error());
mysql_query("UPDATE `temp` SET `category` = '".$groupid['id']."' WHERE `group2` ='".$tempdat['group2']."'")or die("<b>Invalid query:</b><hr>" . mysql_error());
}// end while......


////  now insert the category id's into the categories table....................
////..................................................................................
$gettempinfo = mysql_query("SELECT * FROM temp");
while ($tempdat = mysql_fetch_array($gettempinfo)) {
// SubCategory
$catid = mysql_fetch_array(mysql_query("SELECT * FROM dcms_categories WHERE name='".$tempdat['cat2']."'"))or die("<b>Invalid query:</b><hr>" . mysql_error());
mysql_query("UPDATE `dcms_categories` SET `parent` = '".$catid['id']."' WHERE `name` ='".$tempdat['sub2']."'")or die("<b>Invalid query:</b><hr>" . mysql_error());
}// end while......

$gettempinfo = mysql_query("SELECT * FROM temp");
while ($tempdat = mysql_fetch_array($gettempinfo)) {
// ProductGroup
$subid = mysql_fetch_array(mysql_query("SELECT * FROM dcms_categories WHERE name='".$tempdat['sub2']."'"))or die("<b>Invalid query:</b><hr>" . mysql_error());
mysql_query("UPDATE `dcms_categories` SET `parent` = '".$subid['id']."' WHERE `name` ='".$tempdat['group2']."'")or die("<b>Invalid query:</b><hr>" . mysql_error());
}// end while......


///   start transferring the data to the dcms_products table   ......
///.................................................................

$temptr = mysql_query("SELECT * from temp");
while ($dat = mysql_fetch_array($temptr)) {
$checksku = mysql_fetch_array(mysql_query("SELECT * FROM dcms_products WHERE sku='".$dat['sku']."'"));
if (!$checksku){
mysql_query("INSERT INTO `dcms_products` (
`category`, 
`cat`, 
`sub`, 
`group`, 
`featured`, 
`name`, 
`description`, 
`keywords`, 
`sku`, 
`brand`, 
`retail_price`, 
`sale_price`, 
`on_sale`, 
`difference`, 
`percent_off`, 
`imageurl`, 
`thumburl`, 
`buyurl`, 
`retailer`
) VALUES (
'".$dat['group']."', 
'".$dat['cat']."', 
'".$dat['sub']."', 
'".$dat['group']."', 
'".$dat['featured']."', 
'".$dat['name']."', 
'".$dat['description']."', 
'".$dat['keywords']."', 
'".$dat['sku']."', 
'".$dat['brand']."', 
'".$dat['retail_price']."', 
'".$dat['sale_price']."', 
'".$dat['on_sale']."', 
'".$dat['difference']."', 
'".$dat['percent_off']."', 
'".$dat['imageurl']."', 
'".$dat['thumburl']."', 
'".$dat['buyurl']."', 
'".$dat['retailer']."'
)")or die("<b>QUERY 2 -- Invalid query:</b><hr>" . mysql_error());

} else { 
mysql_query("UPDATE `dcms_products` SET `name` = '".addslashes($dat['name'])."' WHERE `sku` ='".$dat['sku']."'");
mysql_query("UPDATE `dcms_products` SET `description` = '".addslashes($dat['description'])."' WHERE `sku` ='".$dat['sku']."'");
mysql_query("UPDATE `dcms_products` SET `keywords` = '".addslashes($dat['keywords'])."' WHERE `sku` ='".$dat['sku']."'");
mysql_query("UPDATE `dcms_products` SET `retail_price` = '".$dat['retail_price']."' WHERE `sku` ='".$dat['sku']."'");
mysql_query("UPDATE `dcms_products` SET `sale_price` = '".$dat['sale_price']."' WHERE `sku` ='".$dat['sku']."'");
mysql_query("UPDATE `dcms_products` SET `on_sale` = '".$dat['on_sale']."' WHERE `sku` ='".$dat['sku']."'");
mysql_query("UPDATE `dcms_products` SET `difference` = '".$dat['difference']."' WHERE `sku` ='".$dat['sku']."'");
mysql_query("UPDATE `dcms_products` SET `percent_off` = '".$dat['percent_off']."' WHERE `sku` ='".$dat['sku']."'");
mysql_query("UPDATE `dcms_products` SET `imageurl` = '".$dat['imageurl']."' WHERE `sku` ='".$dat['sku']."'");
mysql_query("UPDATE `dcms_products` SET `thumburl` = '".$dat['thumburl']."' WHERE `sku` ='".$dat['sku']."'");
mysql_query("UPDATE `dcms_products` SET `buyurl` = '".$dat['buyurl']."' WHERE `sku` ='".$dat['sku']."'");
}// end if else....


}// end while....


  mysql_query("drop table temp");
  mysql_query("REPAIR TABLE dcms_products");
  mysql_query("OPTIMIZE TABLE dcms_products");
////  not needed......
$rowNum = mysql_num_rows(mysql_query("SELECT * FROM dcms_products"));
print "<b>Import Process Complete</b><br>$rowNum Records Imported Into Products - Under Category: <b>$category</b>";
////  not needed......
/////////////
////////////////
///////////////////
/////////////////////
}// end if import

?>

Now I thought that I may have too much stuff in a single WHILE LOOP so I made each one it's own WHILE LOOP...

I also included a lot of or die("<b>Invalid query:</b>" . mysql_error()); for almost every single query..

But the script is not dieing.. it is just in a constant loop that never completes...

I can't really pinpoint where the constant loop starts... I think it is around the area where I have //*******PROBLEM AREA MAYBE*******//

I know this is a pretty big code.. but if someone would like to take a look at it and give me something to go on it would be greatly appreciated...

(I have been working on this for three days and I have tried to complete it without asking.. but I can't resolve this constant loop problem...)

PHPNewbie55 · January 8, 2008

I guess this is just waaay too much code for someone to sift through... LOL...

Sorry about that... but since I don't really know where the problem is I had to post the entire code....

If you want to take a look at it... keep in mind that the POST variables are the CSV Column Numbers....

And at the top of that code I have error_reporting(E_ALL);

And it is not giving me any errors.. just a constant loop...

Weird because if it was some mistake in the code you would think it would have a constant loop no matter how many records the CSV file had.. but with 100 records everything is fine.. 10,000 records never completes...

I guess I will go back to the drawing board and try some things a little differently to see if I can pinpoint the problem with the loop.....

Sorry if that is way too much code.....

nikefido · January 8, 2008

my first guess is that anything over 100 queries happens to be taking too long and the query is stopped by php -

check out this handy tool:

http://us.php.net/set_time_limit

rhodesa · January 8, 2008

my first guess is that anything over 100 queries happens to be taking too long and the query is stopped by php -

check out this handy tool:

http://us.php.net/set_time_limit

I agree with nikefido

And unfortunately you are right. That is too much code for me to sift through right now. But I will suggest that if it works on 100, but not 10,000, that it is most likely an unexpected character in your data. Just start using the half method:

Try it with 5,000. Doesn't work.

Try it with 2,500. Does work.

Try it with 3,750. etc,etc, etc.

This will help you narrow down to what exact record is causing the issue.

PHPNewbie55 · January 8, 2008

Thanks!!

My PHP Time Limit is MAXXED OUT... It will loop for an hour without dieing....

So that is not the problem....

I tend to agree that it may be some unexpected character in the CSV file in one of the records past 100...

BUT the weird thing is that ALL of the records get inserted into the TEMP table... all 10,557 records are in the TEMP TABLE when I exit....

So if it was a problem with the data in the csv file you would think it would DIE before it completed the import into the temp table...

What it does now is just sit there like it IS doing something when it is not....

I have pinpointed the area where the problem might be... based on what is done in the MySQL Databse when I exit...

Here is a shorter version of the code with everything cut out that IS being done...

<?php
////  now insert the category id's into the temp table....................
////..................................................................................
$gettempinfo = mysql_query("SELECT * FROM temp");
while ($tempdat = mysql_fetch_array($gettempinfo)) {
// Category
$catid = mysql_fetch_array(mysql_query("SELECT * FROM dcms_categories WHERE name='".$tempdat['cat2']."'"))or die("<b>Invalid query:</b><hr>" . mysql_error());
mysql_query("UPDATE `temp` SET `cat` = '".$catid['id']."' WHERE `cat2` ='".$tempdat['cat2']."'"or die("<b>Invalid query:</b><hr>" . mysql_error()));
}// end while......

$gettempinfo = mysql_query("SELECT * FROM temp");
while ($tempdat = mysql_fetch_array($gettempinfo)) {
// Subcategory
$subid = mysql_fetch_array(mysql_query("SELECT * FROM dcms_categories WHERE name='".$tempdat['sub2']."'"))or die("<b>Invalid query:</b><hr>" . mysql_error());
mysql_query("UPDATE `temp` SET `sub` = '".$subid['id']."' WHERE `sub2` ='".$tempdat['sub2']."'")or die("<b>Invalid query:</b><hr>" . mysql_error());
}// end while......

$gettempinfo = mysql_query("SELECT * FROM temp");
while ($tempdat = mysql_fetch_array($gettempinfo)) {
// ProductGroup
$groupid = mysql_fetch_array(mysql_query("SELECT * FROM dcms_categories WHERE name='".$tempdat['group2']."'"))or die("<b>Invalid query:</b><hr>" . mysql_error());
mysql_query("UPDATE `temp` SET `group` = '".$groupid['id']."' WHERE `group2` ='".$tempdat['group2']."'")or die("<b>Invalid query:</b><hr>" . mysql_error());
mysql_query("UPDATE `temp` SET `category` = '".$groupid['id']."' WHERE `group2` ='".$tempdat['group2']."'")or die("<b>Invalid query:</b><hr>" . mysql_error());
}// end while......

?>

So it comes down to that set of WHILE LOOPS........

For some reason ONE of them is constantly looping.

So I have to ask because I don't really know... LOL

Does each while loop get completed before the next while loop starts..??

if each while loop does not get completed before the next while loop starts... that would cause problems...

Since each while loop depends on the previous loop being completed...

I hope that makes sense...

PHPNewbie55 · January 8, 2008

I think I will just take out one WHILE LOOP at a time and see if I can determine which one is stalling the script.....

Ken2k7 · January 8, 2008

Find:

mysql_query("UPDATE `temp` SET `cat` = '".$catid['id']."' WHERE `cat2` ='".$tempdat['cat2']."'"or die("<b>Invalid query:</b><hr>" . mysql_error()));

Replace with:

mysql_query("UPDATE `temp` SET `cat` = '".$catid['id']."' WHERE `cat2` ='".$tempdat['cat2']."'") or die("<b>Invalid query:</b><hr>" . mysql_error());

GingerRobot · January 8, 2008

How long does it take for the code to complete with 100 records? I personally think its much more likely to be an issue with the sheer number of records you're trying to use. You've a lot of while loops - each one is having to loop through 100x as much data with 10,000 records. One of the things that isn't going to be helping you is the sheer number of mysql queries.

You do realise that you can update more than one field at a time?

UPDATE tbl SET col1 = 'some value', col2 = 'something else'

Looks to me like doing that would save you an awful lot of queries.

PHPNewbie55 · January 8, 2008

Ken2k7 - Your EXACTLY right... I guess I just looked at it so long that I couldn't really see anything.. THANK YOU...

That fixed THAT issue... but now it has caused some more of the same...

Now what I am going to do is rewrite some of the code and combine some queries as "GingerRobot" has suggested.. Thank you...

It seems that I maybe missing some ends () and why I didn't get an error baffles me... instead of just going into a constant loop it should have given me some kind of error for not having the correct syntax or something...

I'll keep hammering away at this until it works...

I'll post this as RESOLVED even though it really isn't...

THANK YOU for looking at this!!!!!!!!!!

::)

roopurt18 · January 8, 2008

Don't take this as anything more than constructive criticism.

I took a small look at your code in my editor and I have to say this is some of the most inefficient stuff I have ever seen in my life. I see you doing a SELECT * FROM temp 5 times in a row and each time looping over the results; that's a lot of work on 10k records. I'll play with it a little and see what I can come up with.

Also, if you want a temporary table, just do:

CREATE TEMPORARY TABLE ...

It's temporary, which means it goes away automatically when the connection closes, so you don't have to remember to drop it. Does this temporary table happen to mirror one of your existing table's structure exactly?

roopurt18 · January 8, 2008

I'm going to go through this a piece at a time and give you lots of feedback. Hopefully it will be helpful.

line 2

if ($action == "import") {

By convention, we indent the bodies of our if statements, which you have done. However, scrolling to the to the end of the file, I see that no action is taken if the condition is false. So why not save yourself some indenting and write your code like this:

  if($action != 'import'){
    return; // nothing to do!, could also exit()
  }
  // rest of your code only has to indent two spaces, nothing major
  // this just prevents code creep, which is where many nested levels of indentation
  // cause the right margin to become crowded

lines 3 - 20

  $csv_file = ($_POST["csv_file"]);
  $retailer = ($_POST["retailer_name"]);
  $category = ($_POST["maincat"]);
  $cat = ($_POST["cat"]);
  $sub = ($_POST["sub"]);
  $group = ($_POST["group"]);
  $name = ($_POST["pname"]);
  $description = ($_POST["description"]);
  $keywords = ($_POST["keywords"]);
  $sku = ($_POST["sku"]);
  $brand = ($_POST["brand"]);
  $retail_price = ($_POST["retail_price"]);
  $sale_price = ($_POST["sale_price"]);
  $imageurl = ($_POST["imageurl"]);
  $thumburl = ($_POST["thumburl"]);
  $buyurl = ($_POST["buyurl"]);
  $replace_this = ($_POST["replacethis"]);
  $with_this = ($_POST["withthis"]);

You could easily create all those variables with this code here, which names the variables after the $_POST keys.

  foreach($_POST as $key => $val){
    ${$key} = $val;
  }

lines 22 - 23

  $checktemp = mysql_fetch_array(mysql_query("SELECT * FROM temp"));
if (!$checktemp){

This is a poor way of checking if a table exists. You are selecting every thing in the table. If there are 100k rows with 40 columns, that's a lot of data just to determine if it exists. Use a query like this:

SELECT pick_one_column FROM table WHERE 1 LIMIT 1

lines 24 - 50

mysql_query("
CREATE TABLE `temp` (
...
  KEY `category` (`category`,`featured`))")

Instead of creating a permanent table to be used as a temporary table, just create a temporary table to begin with. While you're at it, add an IF NOT EXISTS and you can eliminate that previous select statement to determine if the table exists. Temporary tables automatically disappear when the connection closes, so you won't have to remember to drop it later.

  CREATE TEMPORARY TABLE IF NOT EXISTS `temp` (
    ...
  )

line 52

} else { /* do nothing */ }

Useless statement; just remove the else part if it's not going to do anything.

lines 55 - 57

        $feed = fopen($csv_file, 'r');  
        
   while ($data = fgetcsv ($feed, 10000, ",")) {

You didn't check if fopen() was successful. Always error check and recover gracefully.

lines 57 - 121

  /*
  You have a while loop that reads lines from the csv file and inserts them one at a time into the DB.
  It is very inefficient to make frequent calls to the DB.
  At the very least, you should read a handful of lines at a time and prepare a statement that will
  insert multiple records at once.  i.e. making one SQL call that inserts 10 rows is more efficient than
  making 10 individual calls.
  */

You should toss out all of the code that reads the CSV file into the DB. MySQL has a built in ability to read CSV files, here is the link to the documentation:

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

This will be much faster than opening the file and inserting via PHP.

But roopurt, I have to perform calculations on each row!

No, no you don't.

Let's say your CSV file represents orders, with the following columns:

cost, quantity, product

And your final table should look like:

cost, quantity, total, product

Here's what I'd do:

// First create temp table so that it matches the CSV file EXACTLY
CREATE TEMPORARY TABLE IF NOT EXISTS `temp` (
  cost ...,
  quantity ...,
  product ...
)

// Perform our LOAD DATA
LOAD DATA INFILE ...

// We want a `total` column - add it with an alter table
// This is incorrect MySQL syntax, check the documentation for the correct syntax
ALTER TABLE `temp` ADD COLUMN `total`

// Update the `total` column with mysql
UPDATE `temp` SET `total`=`cost`*`quantity` WHERE 1

What I'm saying is, create a temp table in MySQL that matches your layout of the CSV file exactly. Then use the LOAD DATA INFILE to populate it. Then use alter statements to add any additional columns. Finally, use MySQL queries with embedded logic to perform your data manipulation.

//////  CATEGORY  //////////..........//////.....///
$catquery = mysql_query("SELECT * from temp group by cat2");
while ($ccat = mysql_fetch_array($catquery)) {
$checkcatn = mysql_fetch_array(mysql_query("SELECT * FROM dcms_categories WHERE name='".$ccat['cat2']."'"));
$chid = "".$checkcatn['name']."";
if (!$chid) { 
mysql_query("INSERT INTO `dcms_categories` (`parent`, `name`) VALUES ('".$ccat['category']."', '".$ccat['cat2']."')");
} else {
// do nothing
}
}// end while..

This is very inefficient as well. Did you know you can directly insert the results of a SELECT statement into a table?

INSERT INTO `table` (`col1`, `col2` )
SELECT `a_column`, `another_column` FROM
  `source_table`
WHERE
  conditions

That's all I'm going to point out for now. Try tackling some of my suggestions one at a time and post as you run into trouble and I'll assist you as time allows. You are using way too much PHP to accomplish your goals here. MySQL already natively supports much of what you're doing so just use what it offers.

If done correctly, you will find that your import routine will go from 300 lines of PHP code and logic to probably 100 or less lines of code that mostly just make query calls. It's much easier to manage and executes more quickly as well (since you're pushing the work into a highly optimized compiled program).

PHPNewbie55 · January 9, 2008

roopurt18

THANK YOU... I really appreciate you taking the time to break it all down for me...!!

I know that this script has Waaay too much PHP and waaay too many queries...

I just kind of started on it and it grew out of control... LOL...

I had no idea that there were the options to create a TEMP TABLE... so that is nice...

Really I had no idea about a lot of the stuff you pointed out and like I said I really appreciate you taking the time..

There is alot more to this code than what I posted...

Let me explain...

The first page asks what the retailer name is and where the CSV file is located.

The second page requires that you select a MySQL Category to import the CSV File into AND..

Allows you to MAP the CSV Columns to the MySQL Table Columns by CSV Column Number...

Then once you have all of that info POSTED.. the IMPORT PROCESS starts...

( the import process is what I was having trouble with )

I need to reread what you posted... make some notes and do some research on some of the things you pointed out..

And don't worry I would never get offended by someone giving me constructive critisism.. that is what I want...

Everyone who posted here gave me some good advice... so what I'll do is soak it all in and rewrite some code...

VERY MUCH APPRECIATED!!!!!!!!!

PHPNewbie55 · January 9, 2008

Just an update I actually got it to work...

It takes about five minutes to complete

It WAS working... But what I had done was to NOT group some of those queries.. so instead of just getting the info for the "group" I wanted it was looping through everything...

Which was making it take forever...

<?php
$gettempinfo = mysql_query("SELECT * FROM temp GROUP BY cat2");
?>

Just doing the grouping and combining some queries made it a lot faster...

I am still working on combining some things to make it faster... so who knows by tomorrow it could work in seconds.. LOL...

Sign In

[SOLVED] Over 100 Records = Constant Loop....???

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information