SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    Level 8 Chinese guy Archbob's Avatar
    Join Date
    Sep 2001
    Location
    Somewhere in this vast universe
    Posts
    3,741
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MYSQL query order problem

    Right now I have this:

    Code:
     mysql_query("SELECT games.*, categories.* FROM games, categories WHERE categories.category_id = games.category_id AND $cat_q games.verified = 1 AND games.added_date <= '" . strtotime(date('Y-m-d')) . "' ORDER BY file_id DESC LIMIT 4");
    It orders the categories by ID and then the files within the categories by file_id.

    How do I changed it so it ordered the categories by a "ordered" field I've created. I want the categories to display in a specific order.

  2. #2
    SitePoint Enthusiast rajeev13's Avatar
    Join Date
    Nov 2012
    Location
    New Delhi,India
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Archbob View Post
    Code:
     mysql_query("SELECT games.*, categories.* FROM games, categories WHERE categories.category_id = games.category_id AND $cat_q games.verified = 1 AND games.added_date <= '" . strtotime(date('Y-m-d')) . "' ORDER BY file_id DESC LIMIT 4");
    It orders the categories by ID and then the files within the categories by file_id.
    How do I changed it so it ordered the categories by a "ordered" field I've created. I want the categories to display in a specific order.
    Code:
     mysql_query("SELECT games.*, categories.* FROM games, categories WHERE categories.category_id = games.category_id AND $cat_q games.verified = 1 AND games.added_date <= '" . strtotime(date('Y-m-d')) . "' ORDER BY ordered ASC ,categoryname ASC ");
    whatever your fileld name in your table

  3. #3
    Level 8 Chinese guy Archbob's Avatar
    Join Date
    Sep 2001
    Location
    Somewhere in this vast universe
    Posts
    3,741
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats not quite what I want. The site is this: http://www.java-gaming.com and I'm modifying the index page. I still want it to show 4 games per category but I want certain categories to show first, thats why I created a new field.

    The whole query looks like this

    Code:
    $result = mysql_query("SELECT games.*, categories.* FROM games, categories  WHERE categories.category_id = games.category_id AND $cat_q games.verified = 1 AND games.added_date <= '" . strtotime(date('Y-m-d')) . "' ORDER BY file_id DESC LIMIT 4");
    
    
          $return = mysql_fetch_rows($result);
    	  return $return;
    I need it to return when it still shows 4 games per category but the categories needs to be "order by ordered DESC". Just sticking that at the end doesn't produce anything I want.

  4. #4
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,235
    Mentioned
    239 Post(s)
    Tagged
    1 Thread(s)
    I assume that this query runs OK and gives no errors... The second part of the query is what has me a bit puzzled, specially this part

    Code:
    $cat_q games.verified = 1
    I don't know what that variable $cat_q, is doing there, all by itself. No comparisions of any kind or anything. Although I confess that I always follow the same approach with my SQL and I try to keep it simpler than simple.

    Also, I'm surprised that you don't use a join (although in this case, it makes no difference). Adding conditions in the WHERE area to create the join is old school but completely valid.

    I'm also assuming that your real SQL query is not using wild cards. Queries run faster if you don't use wild cards.

    So I wonder if the query wouldn't be more efficient if it was something like this
    Code:
    SELECT g.field1, g.field2..., c.field1, c.field2.... FROM (games g JOIN categories c ON c.category_id = g.category_id) WHERE g.verified = 1 AND g.added_date <= '2012/03/01' ORDER BY file_id DESC LIMIT 4;
    I removed the $cat_q as I didn't know which condition you wanted to add with it

    Edit: The use of alias (c for categories and g for games) is because I often get bored of writing the whole table name more than once
    Last edited by molona; Jan 31, 2013 at 02:21. Reason: adding aditional notes

  5. #5
    Level 8 Chinese guy Archbob's Avatar
    Join Date
    Sep 2001
    Location
    Somewhere in this vast universe
    Posts
    3,741
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    They entire function looks like this:

    Code:
     function insert_imedia($a)
      {        
          // Include Sub-Categories
          
          $cat_q = "(games.category_id = '".$a['category_id']."'" ;
          $p_cat = mysql_query("SELECT * FROM categories WHERE parent_id='$a[category_id]' order by ordered desc");
          while ($row = mysql_fetch_array($p_cat))
          {
              $cat_q .= "OR games.category_id = '".$row['category_id']."' ";
          }
          
          $cat_q .= ") AND";
          
         
          
          $result = mysql_query("SELECT games.*, categories.* FROM games, categories  WHERE categories.category_id = games.category_id AND $cat_q games.verified = 1 AND games.added_date <= '" . strtotime(date('Y-m-d')) . "' ORDER BY file_id DESC LIMIT 4");
    
    
          $return = mysql_fetch_rows($result);
          return $return;
      }
    Cat_q just builds a parent categories with its sub-categories.

    I test several times and it seems the first query has no effect on the 2nd no matter how you order it and the 2nd one is the one that controls the display. in the "categories" table, I have a field called "ordered". I need the categories to sort by "ordered desc" while still displaying the latest 4 games from each category(which its doing now but categories are sorted by ID desc by default).

  6. #6
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,235
    Mentioned
    239 Post(s)
    Tagged
    1 Thread(s)
    Maybe you have to indicate the name of the table for the field file_id. You're selecting records from more than one table and I assume that you're using MySQL (Access would guess the table if there was no other field with the same name)

    Your queries, in principle, are OK even if I wouldn't have done them that way (I would use JOINS and all that)

    Try that and see what happens

  7. #7
    Level 8 Chinese guy Archbob's Avatar
    Join Date
    Sep 2001
    Location
    Somewhere in this vast universe
    Posts
    3,741
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's actually just the problem I'm running into. file_id is from the games table and "ordered" is from the categories table. I need firstly the categories to sort by "ordered" and then the games in the categories to sort of file_id, but I just can't get it to work right.

    It throws everything into result, I've tried sorting the results after the query but it doesn't seem to work either.

    I basically need something like this

    Code:
    mysql_query("SELECT games.*, categories.* FROM games order by file_id DESC limit 4, categories order by ordered DESC  WHERE categories.category_id = games.category_id AND $cat_q games.verified = 1 AND games.added_date <= '" . strtotime(date('Y-m-d')) . "' ORDER BY file_id DESC LIMIT 4");

    of course, that doesn't work because its not correct SQL syntax.

  8. #8
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,235
    Mentioned
    239 Post(s)
    Tagged
    1 Thread(s)
    Ok, so you want them ordered by category first and file_id after.

    Then, first don't order your results when you query your categories table (the query for $p_cat) As you can see, they will get disordered later because in the final query you're only ordering by one field. So it is useless.

    It has another advantage too. Because you're not ordering the results, the query will run slightly faster (although you have millions of records this may be a difference )

    Then, your final query should be something like (and I'll try to use your own system to build the query instead of mine)

    Code:
    myslq_query("SELECT games.*, categories.* FROM games, categories
    WHERE categories.category_id=games.category_id AND $cat_q game.verified=1 
    AND games.added_date <='" . strtotime(date('Y-m-d')) . "' 
    ORDER BY categories.ordered, games.file_id DESC LIMIT 4")
    That should order everything all right
    Last edited by molona; Jan 31, 2013 at 15:59. Reason: adding code tags

  9. #9
    Level 8 Chinese guy Archbob's Avatar
    Join Date
    Sep 2001
    Location
    Somewhere in this vast universe
    Posts
    3,741
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It didn't work, it did not change the results display at all. the categories themselves are still order by category_id instead of the "ordered" field. The categories sort does not seem to be obeyed.

    I'm basically trying to find any query that would achieve the result, a join query would be fine too, I'm just not as familiar with those.

  10. #10
    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)
    in my opinion, unless the number of categories is prohibitively large, it's quite practical to have two separate queries, one for the categories (in the order you want them) and then, yes, in a loop, another query to return the top 4 games in each category

    i mean, i could write the join query for you that would do it all in one, but it probably won't perform well at all, as it requires a theta self-join of the games (and if you don't know what that means, don't worry about it)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    Level 8 Chinese guy Archbob's Avatar
    Join Date
    Sep 2001
    Location
    Somewhere in this vast universe
    Posts
    3,741
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would do that, but it feeds the results to a smarty template thing as 1 mysql_fetch_rows() thing. I can't separate it into two files cause I can't figure out where it feeds the variables into smarty.

    I think at the bottom, where it says

    return $return

    $return is what gets fed into smarty. The smarty installation for the newest version doesn't really look right.

    I'm still trying to figure out a better way to do it that can pass the entire results into one array to feed into smarty. The most frustrating thing is that I can't find the file where it actually passes the variables into smarty, otherwise I can just sort the categories there.

  12. #12
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,235
    Mentioned
    239 Post(s)
    Tagged
    1 Thread(s)
    To be honest, it works fine for me in Access so maybe there's something that I'm missing here... I can only think of doing a test with "real" data and a real database structure.

    Before I posted the other day, I did a test with dummy data but maybe I didn't used enough of it.


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
  •