[RESOLVED] displaying data from database

[afrancis]

Wasn't sure if I should put this in the newbie section or this one because I am definately a newbie who is trying to run before he can walk but it is unavoidable.

I have a page where I show a list of achievements using the following code:

<?php
$query = ' SELECT id, achievement, DATE_FORMAT(achievdate," %b %Y")as achievdt FROM achievements order by enterdate desc LIMIT 4';
$result=mysql_query($query) or die ("Invalid Query:".mysql_error());
while ($row = mysql_fetch_array($result)) { 
$entername = $row[entername];
$achievdate = $row[achievdt];
$achievement = $row[achievement];
$id = $row[id];

echo "<UL><h3>","<LI>","$achievement", "</h3></UL>";
    echo "<HR>";
} 

?>

However I would like to split this list of achievements up and show the lastest in one place and say another 3 or 4 in another place. I understand that instead of using while ($row = mysql_fetch_array($result)) I would use a "for" loop but in spite of reading that section in various books several times I still cannot seem to translate their examples to my requirements. Can someone help a non-programmer and put it into words of one sylable

[sfullman]

your code is just fine. I suggest you add, right after your while(){} statement:

$i++;

That way you can know what your index number is and probably do what you're asking for based on the numbers. For example, you could place the 4th through 7th results in an array instead and show them somewhere else.

From my experience it's best to keep your queries simple and use php to enhance, and it's not a bad idea to get used to putting your datasets into an array FIRST before looping through HTML output - at least once it gets complex.

Hope this is helpful

[bradgrafelman]

When posting PHP code, please use the board's

bbcode tags as they make your code much easier to read and analyze. You should go back and edit your post now to add these tags.

@: There is no variable "$i" defined in the script at this point, so $i++ would at least cause coding errors. Plus, you wouldn't want it after the while() loop but rather inside it.

@: What do you mean the "lastes" in one place? Are you talking about taking 4 items returned from MySQL and showing them at different places on the page? If so, I would say loop through the result set and store the data into a PHP variable (array). That way, you could go through that array at your own pace:

// let's pretend $data is the array of rows returned by MySQL

echo '<ul><h3><li>' . $data[0]['achievement'] . '</h3></ul>';

// more HTML... blah blah...

for($i=1; isset($data[$i]); $i++) {
    // starting at i=1 since we've already displayed the 1st row
    echo '<ul><h3><li>' . $data[0]['achievement'] . '</h3></ul>';
}

[afrancis]

I am struggling with my PHP and was unable to store the data in an array so I got round the problem a different way, although you programmers will probably cringe at my method.

In one area of the page I called the last posted achievement from my database and in another area I called items 2 to 4 from the database. It did what I wanted although I need to go to the database twice

[bradgrafelman]

To store the data in an array, you'd simply loop through the result set:

$query = 'SELECT blah blah....';
$exec = mysql_query($query);

$data = array();
while($result = mysql_fetch_assoc($exec))
    $data[] = $result;