Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder dniwebdesign's Avatar
    Join Date
    Dec 2003
    Location
    Carrot River, Saskatchewan
    Posts
    846
    Thanks
    15
    Thanked 10 Times in 10 Posts

    Question Limit MySQL Query Results....

    Alright, I have the following
    PHP Code:
    $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.
    Dawson Irvine
    CEO - DNI Web Design
    http://www.dniwebdesign.com

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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

    Edit:
    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?
    Last edited by Fou-Lu; 09-08-2005 at 07:00 AM.

  • #3
    Regular Coder dniwebdesign's Avatar
    Join Date
    Dec 2003
    Location
    Carrot River, Saskatchewan
    Posts
    846
    Thanks
    15
    Thanked 10 Times in 10 Posts
    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.
    Dawson Irvine
    CEO - DNI Web Design
    http://www.dniwebdesign.com

  • #4
    Regular Coder dniwebdesign's Avatar
    Join Date
    Dec 2003
    Location
    Carrot River, Saskatchewan
    Posts
    846
    Thanks
    15
    Thanked 10 Times in 10 Posts
    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:
    PHP Code:
        // 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.
    Last edited by dniwebdesign; 09-08-2005 at 10:03 PM.
    Dawson Irvine
    CEO - DNI Web Design
    http://www.dniwebdesign.com

  • #5
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    PHP Code:
    $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($resulter0); // Get game date.

    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #6
    Regular Coder dniwebdesign's Avatar
    Join Date
    Dec 2003
    Location
    Carrot River, Saskatchewan
    Posts
    846
    Thanks
    15
    Thanked 10 Times in 10 Posts
    Well, I never knew about a subselector...

    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:
    PHP Code:
     $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($resulter4); // Get game date. 

    Where the "4" is the array number of where my date is? Same as using $row[4];?
    Dawson Irvine
    CEO - DNI Web Design
    http://www.dniwebdesign.com

  • #7
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by dniwebdesign
    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
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •