SitePoint Sponsor

User Tag List

Results 1 to 24 of 24
  1. #1
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query is ignoring 6 rows

    I'm trying to select the last 10 rows from a database, no problem right? Well it's not working. I inserted 6 rows last night, and it was working fine at the time.

    This morning, it's just ignoring them, pretending they don't even exist.

    http://vertex-games.com/index.php
    If you look there, you will indeed see 10 games. But all of the dates are August 6th, there should be 6 games from August 27th.

    The query I have is like this
    Code:
    "SELECT DATE_FORMAT(DATE, '%M %D %Y') AS NewDate, G.TITLE, G.description, G.URL, G.CategoryID, G.ID, GC.CLICKS, GC.monthclicks
    FROM games  G INNER JOIN game_clicks GC ON
        GC.GAME_ID = G.ID
    ORDER BY G.ID DESC
    LIMIT 10"
    And I've tried this in phpmyadmin
    SELECT* FROM games
    ORDER BY ID DESC
    LIMIT 10

    And it still ignores those 6 rows I inserted last night!

    Any help is much appreciated.
    Josh

  2. #2
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Try a SELECT * to see what data is actually in the table (the dates could be wrong inside of the table).

  3. #3
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, the dates are right in the table. Atleast in phpmyadmin.

    Thanks though.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    if the games rows have no related rows in the game_clicks table, then they drop out of the join

    try it with a left outer join:
    Code:
    SELECT DATE_FORMAT(DATE, '%M %D %Y') AS NewDate
         , G.TITLE
         , G.description
         , G.URL
         , G.CategoryID
         , G.ID
         , coalesce(GC.CLICKS,0)    as clicks
         , coalesce(GC.monthclicks,0) as monthclicks
      FROM games  G 
    left outer
      JOIN game_clicks GC 
        ON GC.GAME_ID = G.ID
    ORDER 
        BY G.ID DESC
    LIMIT 10
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    May I ask what I'm doing before I try this?

    Your query looks really weird to me, I don't understand what the coalesce does. And I don't know what you mean by "if it has no related rows"

    Thanks

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    what you are doing is trying a left outer join instead of an inner join

    in the outer join, all rows from the games table will be returned, together with matching game_clicks rows, if any

    in the inner join, only rows from the games table which have matching game_clicks rows will be returned

    coalesce handles the unmatched cases, and displays 0 instead of null

    helps?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh I see, but every row in the games table has a corresponding row in the game_clicks table.

    Would it help to see my table structure for both?

  8. #8
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Did you test what Rudy gave you to see the results? Perhaps your data may have been entered missing something. Try his solution and then post the table structure.

  9. #9
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Heh, well wouldn't you know it, that did it!

    But do you have any idea why this was working last night and then stopped working today? Last night it was even recording clicks!

    It works though, thanks alot.

  10. #10
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, that's working, but my page that updates the number of clicks and then redirects is not working. It updates the clicks fine, but does not redirect.

    Here are the queries there
    Code:
    $sql2 = "SELECT G.*, GC.CLICKS, GC.GAME_ID
    FROM games  G INNER JOIN game_clicks GC ON
        GC.GAME_ID = G.ID
    WHERE G.ID = '$id'";
    It's supposed to redirect and it doesn't.
    In order to redirect it uses the URL row from the games table and php's header function. However, it doesn't seem to be getting that right.

    Table structure for games table:
    Code:
    CREATE TABLE `games` (
      `ID` int(11) NOT NULL auto_increment,
      `CategoryID` int(11) NOT NULL default '0',
      `URL` varchar(100) default NULL,
      `description` varchar(150) default NULL,
      `DATE` date default NULL,
      `TITLE` varchar(30) default NULL,
      PRIMARY KEY  (`ID`),
      FULLTEXT KEY `TITLE` (`TITLE`,`description`),
      FULLTEXT KEY `description` (`description`,`TITLE`)
    ) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=251 ;
    Edit:


    Oh, i forgot to mention, it only happens to those 6 rows

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Josh_
    Edit:

    Oh, i forgot to mention, it only happens to those 6 rows
    i'd try a left outer join there too then
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, so would it look like this?
    Code:
    SELECT G.*
         , coalesce(GC.CLICKS,0)    as clicks
         , coalesce(GC.monthclicks,0) as monthclicks
      FROM games  G 
    left outer
      JOIN game_clicks GC 
        ON GC.GAME_ID = G.ID
    WHERE G.ID = '$id'
    So no idea why it just started doing this all of a suddeN?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you mentioned that every row in games had matching rows in game_clicks

    when you added the 6 rows last night, somehow they musta got added without matching rows

    could be any number of ways that could have happened

    why don't you SELECT * FROM table and inspect the rows manually, to confirm
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    They all do having matching game_clicks rows...that's the thing. Last night they were even succesfully recording clicks.

    And I have inspected the rows manually, they look like every other working row.

    So that query I posted will work?

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    are you asking me if it will work, before you test it?

    i don't know

    i still don't understand why
    - the inner join doesn't return them
    - the left outer join shows them missing
    - and yet you say they're there

    so, like, it could be some other reason

    could you, maybe, please test it and see?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tested it in phpmyadmin and it just redisplayed the query.

  17. #17
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is interesting.

    This query works
    Code:
    SELECT G. * , coalesce( GC.CLICKS, 0 ) AS clicks, GC.GAME_ID
    FROM games G
    LEFT OUTER
    JOIN game_clicks GC ON GC.GAME_ID = G.ID
    WHERE G.ID =2 LIMIT 0 , 30
    as long as the G.ID part equals somethign that isn't those last 6 rows.

  18. #18
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I've been playing around, but still have not been able to come up with something.

    However, I did insert another row to experiment.
    And in phpmyadmin I have tried this as a query
    SELECT * FROM games WHERE ID = 2
    that of course works,
    I have also tried makign ID =245 through 250, and those do not work, they don't come up with anything.
    Now the row I just inserted is number 251, and I can successfully select that one.

    But the page that records the clicks and redirects does not work.

    This is incredibly confusing.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    use phpmyadmin, dump all the rows, zip them up, and post them

    if we can load the same tables onto our system, we can investigate it a lot easier
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I have added two zips, they have the games and game_clicks table respectively.
    Thanks
    Attached Files Attached Files

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    here's what i get when i run the query in post #4 --
    Code:
    NewDate           TITLE           description          
    August 28th 2004  MV-017          Get the robot to the 
    August 27th 2004  Something Fish  Circle fish that are 
    August 27th 2004  Herculympics    Complete all of the e
    August 27th 2004  Caramba         Make the ball hit two
    August 27th 2004  Lightning Pool  Get all of the balls 
    August 27th 2004  Nebulus         Climb the tower.     
    August 27th 2004  Airwolf         Destroy your opponent
    August 6th 2004   Inuyasha Demon  Choose the right card
    August 6th 2004   Hold the Button How long will you hol
    August 6th 2004   Gladiator       Choose the appropriat
    looks like yesterday's 6 entries are there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yah, I know, that works.

    But this query doesnt:
    Code:
    SELECT G. * , coalesce( GC.CLICKS, 0 ) AS clicks, GC.GAME_ID
    FROM games G
    LEFT OUTER
    JOIN game_clicks GC ON GC.GAME_ID = G.ID
    WHERE G.ID =247
    And I need this to work so I can use the rows selected.

    Thanks for taking the time to do this though

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    well, it works for me, it gives this --

    ID,CategoryID,URL,description,DATE,TITLE,clicks,GAME_ID
    247,6,http://www.playaholics.com/play_game..._name_full,Get all of the balls in while collecting the bonuses.,2004-08-27,Lightning Pool,6,247

    perhaps your database is corrupt?

    maybe drop and recreate from thje same scripts you dumped?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That did the trick, works great now.

    Thanks alot for your help rudy.


Bookmarks

Posting Permissions

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