foreach loop with sql query in foreach loop

Question

I need help with this loop... I have table with address data:

konto_odbiorcy   nazwa     miasto     zip_code    ulica
6                 firm 1    Warsaw     02-174      street 1
23                firm 2    Krakow     05-400      street 2
435               firm 3    Warsaw      26-143      street 3
534               firm 4    Kielce     14-171      street 4
643               firm 5    Krakow     08-199      street 5
...something about 5000 entries

and mysql query:

$sql = 'SELECT
    konto_odbiorcy,nazwa,miasto,zip_code,ulica
FROM
    klienci_ax_all
WHERE
    sales_group IN ("IN","KD","TK","SG","TD")
ORDER BY
    miasto
ASC';

$res = mysql_query ($sql, $link ) or die ('request "Could not execute SQL query" '.$sql);

$cities = array();

while ($row = mysql_fetch_array($res)){
    $cities[$row['miasto']][] = $row;
}

foreach ($cities as $miasto => $_cities){
    echo $miasto;

    foreach ($_cities as $data){
        $sql1 = "SELECT date 
                 FROM 3ce_event
                 WHERE number=".$data['konto_odbiorcy']."
                 AND date <= CURDATE())
                 ORDER BY date
                 DESC LIMIT 0,1";
        $sql_result1 = mysql_query ($sql1, $link ) or die ('request "Could not execute SQL query" '.$sql1);

        $sql2 = "SELECT date FROM 3ce_event
                 WHERE number=".$data['konto_odbiorcy']."
                 AND date >= CURDATE()) ORDER BY date ASC LIMIT 0,1";
        $sql_result2 = mysql_query ($sql2, $link ) or die ('request "Could not execute SQL query" '.$sql2);


        while ($row1 = mysql_fetch_assoc($sql_result1)) {
            if($row1["date"] <> $obecna_data) {
                $data_ostatniej_wizyty = $row1["date"];
              }
        }

        while ($row2 = mysql_fetch_assoc($sql_result2)) {
            if( $row2["date"] == $obecna_data) {
                $data_nastepnej_wizyty = 'dzis';
            } else {
                $data_nastepnej_wizyty = $row2["date"];
            }
        }
    }
}

result:

Kielce
    - firm 4
Krakow
    - firm 2
    - firm 5
Warsaw
    - firm 1
    - firm 3

now it works but awesome slow, when first i search whole table and search first "city", then search again for company ("nazwa")that is located in the particular city.

my question is how to improve this to work faster?

EDIT

I pasted my second mysql query into second foreach loop.

If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Aug 27 '15 at 21:34
@Triple_6: It's not clear what you try to achieve and why you want to "search" when you read and echo the whole table — hellcode, Aug 27 '15 at 21:41
Check your indices on the `klienci_ax_all` table - Are all the relevant fields set as keys? Do you have a `covering index` that covers the columns in question? Effective covering indexes should speed things up a bit — Professor Abronsius, Aug 27 '15 at 21:54
@hellcode: i need sort the whole table in the cities and assign individual companies to these cities — Triple_6, Aug 27 '15 at 23:00
@Triple_6: Do you have speed problems with the code you provided here (I guess not) or with the things you wanna do (search, sort and assign). Then you should describe it in detail or provide the slow code. — hellcode, Aug 28 '15 at 08:04
@hellcode: i paste sql query on second foreach loop. first i run this witchout that and this page render also slow that i only show `echo` as an example. — Triple_6, Aug 28 '15 at 19:27

Marcovecchio · Answer 1 · 2015-08-27T21:44:49.037

First, check to see if your table has indexes, mainly on "sales_group" and "miasto" fields, because you use them to filter and order. If you apply other filters, make sure the table has indexes on them.

If "sales_group" is char or varchar, consider making it a foreign key or a ENUM field.

Last (and harder) suggestion: consider using pagination. That way, instead of dumping the whole table to the screen, you would be showing, for example, 20 records at a time.

score 0 · Accepted Answer · answered Aug 28 '15 at 21:00

Don't do those SQL queries in the loop. Combine them in one SQL statement:

$sql = 'SELECT a.konto_odbiorcy, a.nazwa, a.miasto, a.zip_code, a.ulica
, max(e1.date) as date_le_curdate
, min(e2.date) as date_ge_curdate
FROM klienci_ax_all as a
LEFT JOIN 3ce_event as e1 ON (a.konto_odbiorcy=e1.number AND e1.date <= CURDATE())
LEFT JOIN 3ce_event as e2 ON (a.konto_odbiorcy=e2.number AND e2.date >= CURDATE())
WHERE a.sales_group IN ("IN","KD","TK","SG","TD")
GROUP BY a.konto_odbiorcy
ORDER BY a.miasto ASC';

The table konto_odbiorcy should have an index on sales_group - maybe combined with miasto (assuming that konto_odbiorcy is the primary key or at least a unique key). 3ce_event should have an index on number. But fine tuning depends on which data your tables contain.

foreach loop with sql query in foreach loop

2 Answers2