MYSQL query order problem

[Archbob]

Right now I have this:

 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.

[rajeev13]

 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.

 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

[Archbob]

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

$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.

[molona]

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

$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

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

[Archbob]

They entire function looks like this:

 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).

[molona]

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

[Archbob]

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

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.

[molona]

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)

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

[Archbob]

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.

[r937]

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)

[Archbob]

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.

[molona]

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.