Showing Only 1 Results Per Group In A List

[sintax63]

Hello PHP Freaks -

 

I'm querying a large database of events and would like to change the query behavior to enable a more user friendly experience.

 

I'm currently using the following code:

$query="SELECT * FROM schedule ORDER BY short_title ASC LIMIT $limitvalue, $limit";

 

Which produces something like:

 

• Event A on 01/01/12
  
• Event A on 01/04/12
  
• Event A on 01/08/12
  
• Event B on 01/01/12
  
• Event B on 01/04/12
  
• Event C on 01/08/12
  
• Event C on 01/01/12
  
• Event C on 01/04/12
  
• Event D on 01/08/12
  

 

... and so on. This is producing like 30 pages of results for people to wade through. What I would like to do is have it where it shows just one entry for Event A, one entry for Event B, and so on, and from that link I'll take them to a full page of all the date options.

 

I hope I'm explaining it clearly. Any help would be greatly appreciated!

 

Thanks.

[fenway]

Then use a GROUP BY with MIN/MAX and the event.

[sintax63]

I apologize, fenway. It has been a long while since I posted in here so I should have re-familiarized myself with the rules. 

 

your MySQL server version: 5.1.49

the raw MySQL statement in question: See Below

any errors that MySQL returns to the client: None

the table structure & column indexes of the relevant tables: [see Below]

the EXPLAIN output for your query, if applicable: N/A

 

a clear and concise description of what you want this statement to achieve: See Original Post

a description of what it's currently doing that's not to your liking: See Original Post

a brief listing of the types of things you've attempted so far: See Original Post

 

$query="SELECT * FROM schedule ORDER BY short_title ASC;
$result=mysql_query($query);

while( $row = mysql_fetch_assoc( $result ) ) {

$short_title	= $row["short_title"];
$date		= $row["date"];
$time		= $row["time"];
$location		= $row["location"];

echo "<tr> \n";
echo "<td><a href=\"out.php\">$short_title</a></td> \n";
echo "<td>$date</td> \n";
echo "<td>$time</td> \n";
echo "<td>$location</td> \n";
echo "</tr> \n\n";

}

[fenway]

Which "one" would you want?

[sintax63]

Which "one" would you want?

 

short_title, so:

$query="SELECT * FROM schedule GROUP BY short_title ASC;

?

[fenway]

That will get you the event -- though you'd probably want the eventUID -- but which date?

[sintax63]

That will get you the event -- though you'd probably want the eventUID -- but which date?

 

I'm going to leave all that stuff off and then link to a new page that will display all dates and times for that event.

[sintax63]

Thanks again, Fenway. And I'm sorry about not including that info in my original post.