Accessing specific records from a query

[Ewan]

Hi all,

 

I've got a MySQL database which stores monthly records from a weather station.  I want to display info from this on a web page.  Some data is already displayed as graphs but I want to add some trivia based on the current month.

 

eg The wettest <current_month> was in <year1> when <Rain1>mm fell, followed by <year2> (<Rain2>mm) and <year3> (<rain3>mm).

 

The query to get the data is

$query= mysql_query ("SELECT Year, Rain FROM historical_data WHERE Month = '$current_month' ORDER By Rain DESC"); 
$data = mysql_fetch_array ($query);

 

I want to show the top 3 records, laid out as shown above, and not as a table, which would have been easier of course!

 

How do I access data from the returned array to do this?

 

MySQL version is 4.1.22.

 

Thanks.

[svivian]

Here's the code I use:

 

$result = mysql_query( "..." );

while ( ($row=mysql_fetch_assoc( $result )) != false )
{
  echo $row['field'], '<br />';
}

 

mysql_fetch_assoc returns an 'associative' array so you can reference your fields with, in your case, $row['Year'] and $row['Rain']. Inside the while loop you'd put your code to output whatever HTML you need.

[fenway]

You already have the order by clause; just add LIMIT 3.  You can output it however you desire... also, why have the year/date in separate columns?

[Ewan]

Thanks for your quick replies, but I'm still unclear how this would work in practise.

 

Surely using a while loop will simply show one record followed by the next and I couldn't insert records from different rows into different places in a block of text as I want to?

 

eg The wettest October was in 1967 when 136mm fell, followed by 2004 (115mm) and 1979 (98mm).  (different colours here representing different returned records).  Is there a way to reference specific records in this way?

 

@fenway - Thanks.  I've used LIMIT, just forgot to mention it in the thread.  I've got year and month in different fields because that's how the data is supplied.  The fields in the db are: Year, Month, MaxTemp, MinTemp, Rain.

You can output it however you desire...

 

Outputting it all as I desire is what I'm struggling with

[fenway]

The problem is that you need to store the 3 rows first, and *then* deal with the output -- that's why you're struggling.

[Ewan]

Thanks.  I thought that they were stored using mysql_fetch_assoc.  Can you - or anyone - please suggest a method for accessing the array that would achieve the desired result?

[fenway]

No, just retrieved -- just push each row onto your own array.

[Ewan]

Aah, I think I see what you mean.  So I might use a while loop to build the array and then just pick the data from that.  I'll look into it, thanks.