PHP and MySQL Problem

Status
Not open for further replies.
Enigmabomb

Enigmabomb

New member
Feb 26, 2007
2,035
66
0
Than Franthithco
I have a problem where PHP is not Echoing things until it's gotten through a ton, it not all of the queries in a loop. Shouldn't it echo "inline"? Anyone had a weird problem like this?

I have an ungodly slow program that times out and is making my life miserable. I've included some code below. I've tried to utilize the LOW_PRIORITY command in an effort to speed things up by minimizing writes.

$IDS is a 6000 element array.

Somehow I'm Fucking this up. I need insight.

PHP: 
foreach($IDS as $ID)
//for($i=0;$i<10;$i++)
{
    $query = "SELECT products_id FROM ch_products WHERE xref1 = '$ID' LIMIT 1;";
    //echo $query;
    $res = mysql_query($query) or die(mysql_error());
    if(!$idarray = mysql_fetch_array($res))
    {
        echo "$ID Not in CH Database<br>";
        mysql_query("DELETE LOW_PRIORITY FROM Horizon.Products WHERE ID = '".$ID."' LIMIT 1;") or die(mysql_error());
        continue;
    }
    echo "Nuking..".$idarray['products_id']."<br>";
    if (!is_object($db)) {
     //PHP 5.2.0 bug workaround ... 
        $db = new queryFactory();
        $db->connect("localhost", "", "", "cart", USE_PCONNECT, false);
    }

    zen_remove_product($idarray['products_id']);
    mysql_query("DELETE LOW_PRIORITY FROM Horizon.Products WHERE ID = '".$ID."' LIMIT 1;") or die(mysql_error());
}
 


Apache will not send the page until PHP has finished running. I think it's doable with ob_cache or something. I once tried getting it to send echo's as they were executed, but never got it working. Didn't try for long, though :)
 
Have you tried without the "LOW_PRIORITY"?

Update: Just read it all. It seems like you have.
 
Also. This line:
Code: 
        if(!$idarray = mysql_fetch_array($res))
        {
            echo "$ID Not in CH Database<br>";
            mysql_query("DELETE LOW_PRIORITY FROM Horizon.Products WHERE ID = '".$ID."' LIMIT 1;") or die(mysql_error());
            continue;
        }
Isn't that code always skipped? You're testing if $idarray isn't assigned the value of mysql_fetch_array($res), which it always will be. Even if mysql_fetch_array($res) == false.
 
Awesome. I just had to restart the server because I had a load over 10 and mysql was eating 99% CPU. It was brutal. I gotta slim this up somehow.

Thanks for the heads up on the pear.
 
I consulted the manual, and thats how they show it in an example.

Code: 
     [B]Example #3 [B]mysql_fetch_array()[/B] with [B]MYSQL_ASSOC[/B][/B]
      [COLOR=#000000] [COLOR=#0000BB]<?php
mysql_connect[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"localhost"[/COLOR][COLOR=#007700], [/COLOR][COLOR=#DD0000]"mysql_user"[/COLOR][COLOR=#007700], [/COLOR][COLOR=#DD0000]"mysql_password"[/COLOR][COLOR=#007700]) or
    die([/COLOR][COLOR=#DD0000]"Could not connect: " [/COLOR][COLOR=#007700]. [/COLOR][COLOR=#0000BB]mysql_error[/COLOR][COLOR=#007700]());
[/COLOR][COLOR=#0000BB]mysql_select_db[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"mydb"[/COLOR][COLOR=#007700]);

[/COLOR][COLOR=#0000BB]$result [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]mysql_query[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"SELECT id, name FROM mytable"[/COLOR][COLOR=#007700]);

while ([/COLOR][COLOR=#0000BB]$row [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]mysql_fetch_array[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]$result[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]MYSQL_ASSOC[/COLOR][COLOR=#007700])) {
    [/COLOR][COLOR=#0000BB]printf[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"ID: %s  Name: %s"[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]$row[/COLOR][COLOR=#007700][[/COLOR][COLOR=#DD0000]"id"[/COLOR][COLOR=#007700]], [/COLOR][COLOR=#0000BB]$row[/COLOR][COLOR=#007700][[/COLOR][COLOR=#DD0000]"name"[/COLOR][COLOR=#007700]]);
}

[/COLOR][COLOR=#0000BB]mysql_free_result[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]$result[/COLOR][COLOR=#007700]);
[/COLOR][COLOR=#0000BB]?>[/COLOR] [/COLOR]
 
Yeah. But you have a "!" in front of it. Assigning af value to a var will always return true. I think... !'s give me a headache sometimes :)
 
Put the line: ob_end_flush(); before your loop and on the last line inside of your loop, put ob_flush(); . That will stop buffering and allow you to see the output as it is generated.
 
That's some of the worst code I've seen.

You don't do 6000 queries you do 1 and loop through the results.

Either create a query with all of the ids you're checking (look up "IN" in the mySQL docs) or just select all of the ids in the table then loop through them in an array

Same with deleting, create one query then execute it.
 
  • Like
Reactions: Enigmabomb
I tried making select statement that was HUGE and included all the IDS. However, It didn't return results. Im wondering if it was a syntax problem in the query now because it didnt return any results.

This code was my last resort.
 
how many products are in the ch_products table?

I also noticed that you are doing at least 18,000 queries (not sure what all zen_remove_product does) not 6000 and it looks like you're using pconnect, you shouldn't unless you know why you would need to use persistant connections.

It's no wonder mysql was choking.

some other errors I see are including the semicolon in the string passed to mysql_query() and using $idarray as an associative array when its an indexed aray.

You use mysql_fetch_array($res) which returns an indexed array i.e. $idarray[0] but you use $idarray['products_id'] which would require mysql_fetch_assoc($res)


You need to either fetch all of the ids

SELECT xref1, products_id FROM ch_products

or create a SELECT to filter them

SELECT products_id FROM ch_products WHERE xref1 IN ('id1','id2','id3')

Just implode the $IDS array to create the query

$query = "SELECT products_id FROM ch_products WHERE xref1 IN ('" . implode( "','", $IDS ) . "')";

then compare the results against the original array to find the ids that weren't returned

$not_in_chproducts = array_diff($IDS,$res);

$query = "DELETE FROM Horizon.Products WHERE id IN ('" . implode( "','", $not_in_chproducts ) . "')";


I don't have any experience with zen cart (I assume that's where zen_remove_product() comes from), it would probably be easier to work dorectly with the db rather than through zencart

That's just some ideas to get you pointed in the right direction, I didn't test any of that and there may be typos as I'm a little Dyslexic
 
Here's what I want to accomplish efficiently:

<array of 6k ids>

Check to see if the IDs are contained in one database.
If they are, delete Them.
Check to see if the IDs are in a second database
If they are, delete them.

Each database is comprised of 60k+ entries. I dont know if their is a fast way to do this.
 
Enigmabomb said:
Here's what I want to accomplish efficiently:

<array of 6k ids>

Check to see if the IDs are contained in one database.
If they are, delete Them.
Check to see if the IDs are in a second database
If they are, delete them.

Each database is comprised of 60k+ entries. I dont know if their is a fast way to do this.
Click to expand...

in that case no need for a loop just run a DELETE on them, no need to see if they're there, if they aren't nothing will be deleted

just create the query

$query = "DELETE FROM Horizon.Products WHERE id IN ('" . implode( "','", $IDS ) . "')";

connect to the db then

mysql_query($query);

no need to see if they are there first unless you need info from another column other than id
 
Status
Not open for further replies.
Top Bottom