PHP: Fastest way to loop through a array / stringbuiler or something else

Question

We have a PHP script that loops through many XML / CSV files from different websites. Right now we manage to build a good XML / CSV parser script.

The PHP script we wrote is looping though some BIG XML or CSV files. In these XML or CVS files contains Barcodes from different products.

Right now before the script starts I fill an array with the Product ID + Barcode from the MySQL like this:

    function Barcodes_Array() { 

    $sql = "SELECT ProductId, Barcode FROM Products WHERE (Barcode <> '') ";

    $res = mysql_query($sql);

    while ($rijen = mysql_fetch_assoc($res)) {      
        $GLOBALS['arrBarcodes'][] = $rijen;
    }

    }

Each time we loop through the XML (or CSV) files we have to check if the Barcode exists in the array and return the Product ID.

For searching in the function:

$ProductId = SearchBarcodeProduct($EanNr, 'Barcode');

And yet the function:

    function SearchBarcodeProduct($elem, $field)
{
    $top = sizeof($GLOBALS['arrBarcodes']) - 1;
    $bottom = 0;

    $ProductId = 0;

    while($bottom <= $top)
    {
        if($GLOBALS['arrBarcodes'][$bottom][$field] == $elem) {         
            return $GLOBALS['arrBarcodes'][$bottom]['ProductId'];
        }
        else {

            if (is_array($GLOBALS['arrBarcodes'][$bottom][$field])) {
                if (in_multiarray($elem, ($GLOBALS['arrBarcodes'][$bottom][$field]))) {
                    return $GLOBALS['arrBarcodes'][$bottom]['ProductId'];
                }
            }

        }

        $bottom++;
    }        

    return $ProductId;
}

We fill in the array because it took forever each time we ask the MySQL Products Table.

My Question is now:

It still takes a VERY long time each time looping through the array of the barcodes. Is there a faster way for any other solutions maybe a different way then a array? Can someone help please i am working like weeks on this stupid :) thing!

Have you considered building up a list of multiple barcodes you want to look up and then when the list reaches a certain size querying for all of them at once? That would avoid the overhead of executing a new query every time but you would still benefit from MySQL's indexes. — Chris
– Chris, Commented May 16, 2016 at 22:34
You'll probably want to look into sorting the lookup array when it is initially loaded, and then how to perform a logarithmic search on it. (Actually, you can do the sorting part just by adding ORDER BY barcode to the query.) — Uueerdo
– Uueerdo, Commented May 16, 2016 at 22:52
WARNING: If you're just learning PHP, please, do not learn the obsolete mysql_query interface. It's awful and has been removed in PHP 7. A replacement like PDO is not hard to learn and a guide like PHP The Right Way helps explain best practices. Make sure your user parameters are properly escaped or you will end up with severe SQL injection bugs. — tadman
– tadman, Commented May 17, 2016 at 0:06

Alex · Accepted Answer · 2016-05-17 00:01:00Z

0

Why do you need 2 functions?

Try just one

function itemBarcode($id) { 
    $id = intval($id);
    $sql = "SELECT ProductId, Barcode FROM Products WHERE ProductId = $id Barcode <> '') ";

    $res = mysql_query($sql);

    if ($row = mysql_fetch_assoc($res)) {      
        return $row['barcode'];
    } else {
       return 0;
    }
}

Update if you need to search by barcode you can create another function:

function itemProduct($barcode) { 
    $sql = "SELECT ProductId, Barcode FROM Products WHERE Barcode = $barcode ";
    $res = mysql_query($sql);

    if ($row = mysql_fetch_assoc($res)) {      
        return $row['ProductId'];
    } else {
       return 0;
    }
}

edited May 17, 2016 at 0:01

answered May 16, 2016 at 23:39

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Faith Over a year ago

We dont look for the ProductID! We always look for the Barcode thats what is universal ;)

Alex Over a year ago

check update. I still can't get why you need 2 functions.

Faith Over a year ago

The first function fills a Array so the script will lookup in the array and not 150.000 times to the database table.

Alex Over a year ago

that is exactly what you did: you are looping 150.000 twice. when create an array and when search in array. in my case you just get what you really need at the moment.

Faith Over a year ago

not really. The function Barcodes_Array() only runs at windows startup.

|

Arth · Accepted Answer · 2016-05-17 00:10:41Z

0

Sounds like you are missing an index on your Barcode column in your database.. A single row lookup using a presumably unique single indexed column should be blisteringly fast.

CREATE INDEX Barcode_Index ON Products (Barcode)

Then simply:

SELECT ProductId FROM Products WHERE Barcode = *INPUT*

You could also make the index UNIQUE if you NULL the Barcode where they currently = '' if there are more than one of these.

Another option is keying the array you have with the Barcode:

while ($rijen = mysql_fetch_assoc($res)) {      
     $GLOBALS['arrBarcodes'][$rijen['Barcode']] = $rijen;
}

or even just:

while ($rijen = mysql_fetch_assoc($res)) {      
     $GLOBALS['arrBarcodes'][$rijen['Barcode']] = $rijen['ProductId'];
}

Then you can do a straight look up:

$ProductId = isset($GLOBALS['arrBarcodes'][$Barcode])
    ?$GLOBALS['arrBarcodes'][$Barcode]['ProductId']
    :0;

or:

 $ProductId = isset($GLOBALS['arrBarcodes'][$Barcode])
    ?$GLOBALS['arrBarcodes'][$Barcode]
    :0;

N.B Please read the warnings in the comments about use of $GLOBALS and mysql_query.

If you need it, store the barcodes array in an object or variable instead.
PDO is pretty handy, and I think it can also key your returned array for you on fetch.

edited May 17, 2016 at 0:10

answered May 17, 2016 at 0:03

Arth

13.1k5 gold badges43 silver badges72 bronze badges

5 Comments

Faith Over a year ago

Very strange try the both way but searching in the Table of the MySQL results more rows than the array search: while ($rijen = mysql_fetch_assoc($res)) { $GLOBALS['arrBarcodes'][$rijen['Barcode']] = $rijen['ProductId']; }

Arth Over a year ago

Sorry I don't understand what you mean.. I thought the Barcode should be unique in the database table. What is the problem? Both these options should be faster, particularly a keyed array in memory.

Faith Over a year ago

Just like i say. I already had the Barcode column with a Index. When i use the search for the barcode in the Products table i got more results then the $ProductId = isset($GLOBALS['arrBarcodes'][$Barcode]) ?$GLOBALS['arrBarcodes'][$Barcode]['ProductId']:0; *** When searching for barcodes in the array ***

Arth Over a year ago

Hmm, strange.. is Barcode a string? You may have to normalise it to lower case with lower() when assigning the key to the array and searching.. but you would have had the same problem while loop searching. Is your array size and returned row count from the original query the same?

Arth Over a year ago

Maybe find and compare a few examples where the Barcode is found in the table but not the array..

Collectives™ on Stack Overflow

PHP: Fastest way to loop through a array / stringbuiler or something else

2 Answers 2

6 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related