...

View Full Version : Limit MySQL Query Results....



dniwebdesign
09-08-2005, 06:26 AM
Alright, I have the following
$result = mysql_query("SELECT * FROM hstats_".$season."_schedule WHERE gameday < '$date' ORDER BY gamenumber") or print mysql_error();

Which is alright on one of my websites because there is only 1 game per night...

However I am also using my script on a league website, and there are multiple games on 1 night. However I only want to show the last game(s).

This also goes for upcoming games, I want to show only the games from the next date there is a game on (not all the ones in the future). How would I go about doing this? If you need me to clarify please, let me know.

Fou-Lu
09-08-2005, 06:38 AM
Hey DNI,
LIMIT baby. Assuming that `game_number` is an autoincrementing number, order it DESC with a LIMIT of 1 (Either LIMIT 1 OR LIMIT 0, 1 should work just fine). As well, an alternative would be to use mysql_result, though this isn't as easy to control especially if you decide you want to later output more rows.
Is this what it is your looking for?
Hey, one of these days we should meet up and talk about website design/dev. It would be sweet to know someone else IRL who does this ;)


Sorry mate, didn't pay attention to your last part there.
Alright, so instead of using a LIMIT, perhaps it would be best to evalute it instead. First, to select your current is fine for previous days, but you'll need to also add for the next day as well correct? You could always add 86400 to your date which is 24 hours, or use an strtotime if you just want the next day. From this, you can display all future games for up to one day in advance, and use php to limit it to one game already played.
Did you need any code for an example, or is this going to be enough to get you going?

dniwebdesign
09-08-2005, 02:30 PM
I believe I understand what you are trying to tell me however instead of showing what games are "tomorrow" (or the 1 day in advance) I would like to show the next game(s). So if there isn't a game "tomorrow" it would show the games on the day 2 days from now. (If that made any sense at all), this would be the same with the last games.
However if I am just rambling let me know... and if there isn't a way also let me know.

dniwebdesign
09-08-2005, 10:01 PM
Alright, I actually thought of an answer that is working well, by querying the database twice.

The first query is to get some info on the last game, limited in the query to 1 (because I only want the date the last game was played on). Then I saved the date from the game into a variable.

I then queried the database again for all games played on the date of the last game which in turn gives me all the games last played.

Example:

// First Query
$resulter = mysql_query("SELECT * FROM hstats_".$season."_schedule WHERE gameday < '$date' ORDER BY gamenumber DESC LIMIT 1") or print mysql_error();
$count=0;

if(mysql_num_rows($resulter))
{
while($row=mysql_fetch_array($resulter))
{
$thedate = $row["gameday"]; // Get game date.
}
}
// Query 2
$result = mysql_query("SELECT * FROM hstats_".$season."_schedule WHERE gameday = '$thedate'") or print mysql_error();
$count=0;
if(mysql_num_rows($result))
{
while($row=mysql_fetch_array($result))
{ // Echo out the games...
Example of output can be found on http://www.nsjhl.com.

raf
09-08-2005, 10:24 PM
i don't realy get it.

can't you use a subselect (requires mysql version 4.1 or up)? like

"SELECT * FROM hstats_". $season ."_schedule WHERE gameday = (SELECT gameday from hstats_". $season ."_schedule WHERE gameday < '". $date ."' ORDER BY gamenumber DESC LIMIT 1")

in any case, your first query can be shortened to


$resulter = mysql_query("SELECT gameday FROM hstats_".$season."_schedule WHERE gameday < '$date' ORDER BY gamenumber DESC LIMIT 1") or print mysql_error();
if(mysql_num_rows($resulter)){
$thedate = mysql_result($resulter, 0); // Get game date.
}

dniwebdesign
09-08-2005, 11:21 PM
Well, I never knew about a subselector... :D

As far as the shortened version, how does it know which coloumn to get it out of? So I get the date instead of the id, or hometeam, etc...? Do I just change it like:


$resulter = mysql_query("SELECT gameday FROM hstats_".$season."_schedule WHERE gameday < '$date' ORDER BY gamenumber DESC LIMIT 1") or print mysql_error();
if(mysql_num_rows($resulter)){
$thedate = mysql_result($resulter, 4); // Get game date.
}

Where the "4" is the array number of where my date is? Same as using $row[4];?

raf
09-08-2005, 11:48 PM
As far as the shortened version, how does it know which coloumn to get it out of?
Well, your query is "SELECT gameday FROM..." so your resultset will only have 1 variable-value pair for each record. Because the resultset is a zero-based array, the value for this variable will be stored as resultset[row0][column0]
If you would select two columns and 2 records, then our recordset would be
resultset[row0][column0]
resultset[row0][column1]
resultset[row1][column0]
resultset[row1][column1]

now, mysql_result() takes the result-id as first parameter, the rownumber as second, and the columnnumber as third parameter.
Since we only have 1 record in the recordset, we don't need to specify the rownumber.
mysql_result($resulter, 0) is the same as mysql_result($resulter, 0, 0)
and means: give me the value for the first column of the first record.

if you change it to mysql_result($resulter, 4), then your resultset should contain at least 5 columns, and your select should look like "SELECT column1, column2, column3, column4, gameday"

more info --> http://uk.php.net/mysql_result



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum