foreach loop, clever or not? 1

[marinelli82]

Hello again,

I am working on a PHP 5 MySQL 4.1 projekt with several tables. The system later will run at a local server on an intranet.
I am not really experienced in using MySQL.
My problem is that I fetch data from different tables and then for each record I have to use a foreach-loop to send another SQL-request to the server.
But now I don´t know how 'clever' my solution is.
Otherwise my way to split the data on my tables seem to work fine. Saved without redundance, and quite transparent. So I don´t know if it is a good idea to change that structure only to bypass foreach-loops like this.
I would be happy to receive help here again

thanks a lot

bye

Andy


Below a code snippet of my described problem:
Here my main SQL-request

$sql_uebersicht_liste="SELECT sd.datensatzID, 
                            ma.benutzername, 
                            sd.datum, 
                            at.abteilung, 
                            sd.projekt_nr, 
                            sd.kkmID, 
                            sd.ursacheID, 
                            sd.lkmID, 
                            sd.locked
                    FROM stammdaten AS sd, 
                         mitarbeiter AS ma, 
                         abteilungen AS at
                    WHERE sd.erstellerID = ma.mitarbeiterID
                      AND ma.abteilungsID = at.abteilungsID 
                    ORDER BY sd.datensatzID DESC";

And here my froeach-loop

foreach($uebersicht_liste as $uebersicht_data)
{
$sql_kkm="SELECT IF(erledigt IS NULL, 0, erledigt) 
                 AS fertig, rang 
          FROM kkms 
          WHERE kkms.kkmID = $uebersicht_data[kkmID] 
          ORDER BY rang ASC";
        $kkm=$db->query($sql_kkm);    

        while($daten=$kkm->fetchRow())
        {
         $kkms[$uebersicht_data[datensatzID]][$daten [rang]]=$daten[fertig];
        }
        $kkm->free();    
}

 

[r937]

a query inside a loop is incredibly inefficient

why not just do another join?

select sd.datensatzID
     , ma.benutzername
     , sd.datum
     , at.abteilung
     , sd.projekt_nr
     , sd.kkmID
     , sd.ursacheID
     , sd.lkmID
     , sd.locked
     , coalesce(kkms.erledigt,0) as fertig
     , kkms.rang 
  from stammdaten as sd
inner
  join mitarbeiter as ma
    on sd.erstellerID = ma.mitarbeiterID
inner
  join abteilungen as at
    on ma.abteilungsID = at.abteilungsID 
inner
  join kkms 
    on sd.kkmID = kkms.kkmID
order 
    by sd.datensatzid desc

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[marinelli82]

Hi again

First, thanks for your fast answer!
I had no idea how incredible inefficient a query in a loop could be =)
Your advice to use another join to solve my problem sounds good but then I partly receive double data records because the relation between sd.kkmID and kkms.kkmID is 1:n .
So I have to filter them by PHP.
First I save the information 'coalesce(kkms.erledigt,0) as fertig' in an 2D-array (array[sd.datensatzID][rang]) and then I have to filter my double records by sd.datensatzID to receive only data with an unique datensatzID because I want to show a list of my requested data without repeats.
Even if I think that way is more efficient than my query-loop, how efficient are those double-record-filter loops anyway?
And finally, do you think I shall restructure my tables if my SQL-requests are such inefficient? But I think I can´t optimize my tables for every kind of SQL-request I need.
For example the 1:n relation I talked about seemed quite useful for me because first the data are saved in that table (kkms) belong together and second you can receive each record with all fields of "kkmID" and "rang" (both together are the primary key) quite easily, in general.

Maybe you have another advice for me?

Much thanks at all!

Bye

Andy

 

[r937]

ah, a 1:n relationship

there are two solutions: return multiple rows for each sd.datensatzID and detect the groups using an output loop (i guess this is what you were doing with that array?), or else concatenate all the occurrences of kkms data into one column with the GROUP_CONCAT function (available in version 4.1) --

select sd.datensatzID
     , ma.benutzername
     , sd.datum
     , at.abteilung
     , sd.projekt_nr
     , sd.kkmID
     , sd.ursacheID
     , sd.lkmID
     , sd.locked
     , group_concat(
          concat(coalesce(kkms.erledigt,0) 
                ,'-',kkms.rang)
                   )  as fertig 
  from stammdaten as sd
inner
  join mitarbeiter as ma
    on sd.erstellerID = ma.mitarbeiterID
inner
  join abteilungen as at
    on ma.abteilungsID = at.abteilungsID 
inner
  join kkms 
    on sd.kkmID = kkms.kkmID
group
    by sd.datensatzID
     , ma.benutzername
     , sd.datum
     , at.abteilung
     , sd.projekt_nr
     , sd.kkmID
     , sd.ursacheID
     , sd.lkmID
     , sd.locked    
order 
    by sd.datensatzid desc

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[marinelli82]

Hello again

Thanks a lot for your posting, I guess that is exactly the SQL-query I was looking for! =)
I just still don´t get what the "GROUP BY" statement with all those fields as paramters is good for. Also I can´t quite figure out the difference between the "WHERE" and the "INNER JOIN" clause. Which one is faster, why do you prefer the "INNER JOIN" way?
Apart from that, I really have to thank you for your professional help and I hope I may seek you out again when facing other problems that are still to come. =)

Bye

Andy

 

[ericbrunson]

I just still don´t get what the "GROUP BY" statement with all those fields as paramters is good for.

Any decent tutorial on SQL would cover it.

 

[r937]

the GROUP BY is good for grouping

you must do grouping to use an aggregate function

WHERE and INNER JOIN are executed the same way, but INNER JOIN syntax is better for several reasons

one reason is that it's a lot easier to read and understand (remember, the person you are writing for is the person who comes after you, and this could even be you 6 months later)

it's also easier to change a query from INNER JOIN to LEFT OUTER JOIN than if the join conditions were all mixed up with other conditions in the WHERE clause



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)