[SOLVED]:Grabbing data from a mysql database in a loop

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
pmbasehore
Forum Newbie
Posts: 4
Joined: Tue Feb 24, 2009 10:55 am
Location: Peachtree City, Georgia

[SOLVED]:Grabbing data from a mysql database in a loop

Post by pmbasehore »

Hello all...I apologize if this has already been answered, but I couldn't find anything relevant in a quick search.

I am trying to build a page that is entirely database driven. My database is already set up, my mysql code seems to work (as best as I can tell, anyway) but PHP throws up all over itself when I run my script. Let me give you guys some more details.

I want my page to look like this (http://www.sunnywoodstudios.com/woods1.php), which is a stop-gap I coded to be static, not database-driven. I want the pictures and descriptions to come from the database, so when the site admin needs to add a new product, all he needs to do is add a row to the database and the page would update itself. The code I posted below shows how I tried to do this using a FOR loop.

Code: Select all

<?php
    $numrows = mysql_num_rows($result);
    For ($i=0; $i <= $numrows; $i++)
    {
        If ($numrows % 2 == 0)
        {
            echo"<td width='20%' height='77'>";
            echo"<a href='woodinfo.php?type={mysql_result($result,$i,'type')}'><img     src='images/{mysql_result($result,$i,'SmallImage')}' width='172' height='77' border='0'>";
            echo"</a>";
            echo"</td>";
            echo"</tr>";
            echo"<tr>";
        }else
        {
            echo"<td width='20%' height='77'>";
            echo"<a href='woodinfo.php?type={mysql_result($result,$i,'type')}'><img src='images/{mysql_result($result,$i,'SmallImage')}' width='172' height='77' border='0'>";
            echo"</a>";
            echo"</td>";
        }
    }
?>
I can give you a copy of the entire page code if you want, but it seems like my problems are in here. I hope I've made sense with my request, please let me know if you need anything else.

Thanks so much in advance!
Last edited by pmbasehore on Tue Feb 24, 2009 2:42 pm, edited 1 time in total.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: Grabbing data from a mysql database in a loop

Post by Bill H »

For one thing, you are going to attempt one more output than the resultset contains. $numrows is the number of results, then your for loop starts at zero and goes <=$numrows. It should either start at one, or it should end at <$numrows.

I find that for loops and resultsets don't generally get along very well. I would stay with a while loop and count the columns to trigger a new row.

Code: Select all

 
$colcnt = 0;
while ($row = mysql_fetch_array($result))
{
     if (!$colcnt)
     {     // start a new row for your table
           $colcnt = 5;    // or how many ever columns
     }
     // output the colomn from this data row
    $colcnt--;
}
 
Note that if that if the row you are outputting is not the first one you need to not only start a new row, you need to terminate the existing one, so you need to count the rows as well as columns. You also need to close the last row after exiting the loop. I left those parts out, but...
pmbasehore
Forum Newbie
Posts: 4
Joined: Tue Feb 24, 2009 10:55 am
Location: Peachtree City, Georgia

Re: Grabbing data from a mysql database in a loop

Post by pmbasehore »

Thanks Bill H, that was a big help. Just to make sure I understand you correctly though...

I put the HTML portion after I set $colcnt to 5 and before I subtract one and loop; correct?

Also, I would have to add in a little bit more logic, saying that if $colcnt = 0, then do the </td></tr> etc before I create the row information. Yes?

Again, thanks so much for your help!
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: Grabbing data from a mysql database in a loop

Post by Bill H »

Well, yeah, I was somewhat less than crystal there wasn't I. The comment about starting a new row wasn't just "hey we need to start a new row" it was intended to mean, "insert html here to start the new row." If this is not the first row, then you also need to insert html th finish the existing row. So in addition to the $colcnt var you would need a $rownct var, set at zero initially and then incermented whenever a row is created.

When the loop runs out of records and exits, you'll need to do the appropriate number of blank columns and terminate the row.

Yes, test for the $colcnt value before outputting the data from the database extraction.
pmbasehore
Forum Newbie
Posts: 4
Joined: Tue Feb 24, 2009 10:55 am
Location: Peachtree City, Georgia

Re: Grabbing data from a mysql database in a loop

Post by pmbasehore »

Ahh, that works 90% perfectly! The only problem I have now is that the first entry in my database is not shown. My code is below, or you can compare the pages at dynamic:http://www.sunnywoodstudios.com/woods.php and static:http://www.sunnywoodstudios.com/woods1.php

My SQL block is here:

Code: Select all

mysql_connect($server,$user, $pswd) or die ("Couldn't Connect to Server!");
mysql_select_db($database);
$query = "SELECT EngName, SmallImage, type FROM woods ORDER BY EngName";
$result = mysql_query($query);
if (!$result)
{
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}
$row = mysql_fetch_assoc($result) ?>
and my table block is here:

Code: Select all

$colcnt = 0;
$rowcnt = 1;
while ($row = mysql_fetch_array($result))
{
    if (!$colcnt)
    {     // start a new row for your table
        if ($rowcnt > 1)
        {
            echo"</tr>";
        }
        echo"<tr>";
        $colcnt = 5;    // or how many ever columns
    }
    echo"<td>";
    echo"<a href='woodinfo.php?type={$row['type']}'><img src='images/{$row['SmallImage']}' width='172' height='77' border='0'></a>";
    echo"</td>";
    // output the colomn from this data row
    $colcnt--;
    $rowcnt++;
}
Any ideas what could be causing it to do these things? I don't know why it would ignore the first entry in my table.

EDIT: The entry that is missing "Ash" is not the first entry in my table, but the second. It is, however, the first entry with my "ORDER BY".
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: Grabbing data from a mysql database in a loop

Post by Bill H »

Line 11 in your SQL block is extracting the first row of data.
pmbasehore
Forum Newbie
Posts: 4
Joined: Tue Feb 24, 2009 10:55 am
Location: Peachtree City, Georgia

Re: Grabbing data from a mysql database in a loop

Post by pmbasehore »

That did it. Thanks so much for your help!
Post Reply