Looping through an array

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
chriso
Forum Commoner
Posts: 31
Joined: Fri Aug 01, 2003 11:52 am

Looping through an array

Post by chriso »

I have two tables.
Table 1: certs.
contains certs.ID and certs.CERTS. The certs.CERTS column is a list of various certifications such as Microsoft, Novell, A+, etc.

Table 2: faccerts.
contains faccerts.ID - primary key
faccerts.FID - corresponds to the faculty ID in another table. This information is passed to the function.
faccerts.CID - corresponds to certs.ID above.

I am trying to print out a listing of certifications an individual faculty member. I've created a function to do this:

Code: Select all

function listfaccerts($fid) {

$sql_query = @mysql_query("SELECT certs.CERTS FROM certs, faccerts WHERE faccerts.FID='$fid' AND certs.ID=faccerts.CID");
if (!$sql_query) {
echo ("Error in sql query: " . mysql_error());
}
$allcerts = mysql_fetch_array($sql_query);
foreach ($allcerts as $eachcert) {
echo ('<p align="center">'.$eachcert.' <br>');
}
} // End List of Faculty Certifications
The problem I'm having is that only one certification is being printed out, and that certification is being printed out twice. Say for example, an instructor is a CIW and CNE. The CNE cert has lower cert.ID (3) than cert.CIW (14). When I click the link to list the certs for that instructor, the CNE cert gets printed twice and the CIW cert doesn't get printed at all. Will you help me get the code correct for this? Thanks.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Do this instead:

Code: Select all

while ($allcerts = mysql_fetch_array($sql_query)) { 
 echo ('<p align="center">'.$eachcert[0].' <br>'); 
}
Hope you see and understand the difference.
chriso
Forum Commoner
Posts: 31
Joined: Fri Aug 01, 2003 11:52 am

Post by chriso »

JAM wrote:Do this instead:

Code: Select all

while ($allcerts = mysql_fetch_array($sql_query)) { 
 echo ('<p align="center">'.$allcerts[0].' <br>'); 
}
Hope you see and understand the difference.
That worked fine. Thanks. However, I'm afraid I don't understand as I'm still in the beginning stages of learning php. I thought the foreach loop was designed to step through each element of an array. Why use a while loop instead? Would you explain your reasoning?

Thank you.
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post by scorphus »

mysql_fetch_array() returns one row each call, and false when there are no more data to be retrived.

We use a while loop to 'walk' thru the table rows. See:

sql:

Code: Select all

Select * from Cliente;
result:

Code: Select all

+---------+----------+-------------+
| Cod_cli | Nome_cli | Gerente_cli |
+---------+----------+-------------+
|       1 | Verbatim | John Bueri  |
|       2 | Sony     | John Bueri  |
|       3 | TDK      | John Bueri  |
|       4 | Maxell   | John Bueri  |
+---------+----------+-------------+
First time mysql_fetch_array is called returns:

Code: Select all

Array
(
    &#1111;0] => 1
    &#1111;Cod_cli] => 1
    &#1111;1] => Verbatim
    &#1111;Nome_cli] => Verbatim
    &#1111;2] => John Bueri
    &#1111;Gerente_cli] => John Bueri
)
The second call:

Code: Select all

Array
(
    &#1111;0] => 2
    &#1111;Cod_cli] => 2
    &#1111;1] => Sony
    &#1111;Nome_cli] => Sony
    &#1111;2] => John Bueri
    &#1111;Gerente_cli] => John Bueri
)
And so on, until there is no more data to get from the DB.

If you do a foreach using mysql_fetch_array returned data you'll walk through the row fileds, not through the table rows.

Hope it helps a bit...

Regards,
Scorphus.

ps.: please escuse my English
Last edited by scorphus on Wed Sep 17, 2003 5:51 pm, edited 2 times in total.
chriso
Forum Commoner
Posts: 31
Joined: Fri Aug 01, 2003 11:52 am

Post by chriso »

Helps a lot. If its not too much to ask, what do you mean by "walking through the row fields" instead of the table rows? Could you use the same code above and show me how the fetch function would handle the foreach statement?

Many thanks for the time to respond.
User avatar
Heavy
Forum Contributor
Posts: 478
Joined: Sun Sep 22, 2002 7:36 am
Location: Viksjöfors, Hälsingland, Sweden
Contact:

Post by Heavy »

mysql_fetch_array() returns an array with one table row in it.

Code: Select all

<?php
while ($allcerts = mysql_fetch_array($sql_query)) {
echo ('<p align="center">'.$allcerts[0].' <br>');
}
?>
The while loop takes whatever is in it's parentheses and checks whether it is TRUE or FALSE. In PHP anything that is not zero is TRUE. That means that when $allcerts = mysql_fetch_array($sql_query) returns a row and puts it in $allcerts, the boolean expression in the while loop is evaluated to TRUE.

The result is that the while statement loops through all rows returned by the SELECT query. One at a time.

At this point I should tell you that $allcerts is a bad name for its mission. It should be called $certRow or whatever, but not "all certs", because it will only get one cert for each mysql_fetch_array() call.

Then you take a look at the echo statement and see that the $allcerts is an array and used as such; $allcerts[0]

This means that your "certs.CERTS" is accessible withing the while loop using $allcerts[0].

IF you had more columns in your query, IE:

Code: Select all

<?php
$sql_query = @mysql_query("SELECT certs.ID, certs.CERTS FROM certs, faccerts WHERE faccerts.FID='$fid' AND certs.ID=faccerts.CID"); 
?>
then certs.ID for the cert would be accessible with $allcerts[0] and certs.CERTS for the cert would be accessible with $allcerts[1].

That [0] and [1] refers to the "columns" or "row fields" as scorphus wrote.

so:
Loop through the result. Every iteration handles one row returned from mysql.

*********************************
Here's another way to get the data you want in an array so that you can do the foreach as you tried the first time:

Code: Select all

<?php
$result = mysql_query("SELECT CERTS FROM certs t1 left join faccerts t2 on t1.ID=t2.CID WHERE t2.FID='$fid'");
if ($result !== FALSE){
    while ($arrRow = mysql_fetch_array($result )){
        $allcerts[] = $arrRow ;
    }
}
// Now it is the way you wanted so here comes the rest of your code:
foreach ($allcerts as $eachcert) {
    echo ('<p align="center">'.$eachcert.' <br>');
}
?>
Is it easier to grasp now? :wink:
chriso
Forum Commoner
Posts: 31
Joined: Fri Aug 01, 2003 11:52 am

Post by chriso »

Makes alot more sense. Thanks for taking the time to explain it. :D
Post Reply