Page 1 of 1
ORDER BY `title` ....doesnt work?
Posted: Fri Oct 13, 2006 2:28 am
by pgolovko
Code: Select all
$result = mysql_query("SELECT * FROM `categories` ORDER BY `title` ASC LIMIT 0 , 1000000");
echo mysql_error();
if (mysql_num_rows($result) <> '') {
$categories_total = mysql_num_rows($result);
while ($row = mysql_fetch_array($result)) {
if($row['parent'] == $id){
$row["links_num"] = mysql_num_rows(mysql_query("SELECT * FROM links WHERE `visible` = '1' AND `category_id` = '".$row['id']."' LIMIT 0 , 3000000"));
$categories[] = $row;
$query_subs = "SELECT * FROM `categories` WHERE `parent` = ".$row['id']." ORDER BY `title` LIMIT 0 , 4";
$result_subs = mysql_query($query_subs);
echo mysql_error();
while ($row_subs = mysql_fetch_array($result_subs)) {
$sub_cats[] = $row_subs;
}
}
}
}
The problem is
$categories[] array doesnt get rows ordered by `title`
as I need them to, instead they are ordered the same way as in MySQL database. While
$sub_cats[] do get ordered by `title`and not how they are ordered in the database. What could be the problem here?
Posted: Fri Oct 13, 2006 3:27 am
by Oren
I only checked the first query, but nothing else since the code is too messy in my opinion. The query looked fine for me, but I notice other things that are even more important in my opinion:
First, you have nested loops in there. Chances are that you need to use a JOIN there instead of all these ugly loops, so check it out.
Second, take a look at this part of the code:
Code: Select all
if (mysql_num_rows($result) <> '') {
$categories_total = mysql_num_rows($result);
What's the point of calling mysql_num_rows() twice with the same result set?
Here is a better practice:
Code: Select all
if (($categories_total = mysql_num_rows($result)) > 0) {

Posted: Fri Oct 13, 2006 3:28 am
by Mordred
Hmm, nothing looks particularly wrong to me, but try specifying ASC or DESC for both orders and see what happens. Also, are you sure the categories are not already sorted by title in the database?
Explain the purpose of this:
Posted: Fri Oct 13, 2006 3:36 am
by Oren
Mordred wrote:Explain the purpose of this:
Good point
Mordred.
pgolovko, if you want to select all the records in the table from record 0 till the end, don't use LIMIT at all. If you want to select records 1 - 1,000,000 (and I'm sure you aren't, but only if

) then just do:
Posted: Fri Oct 13, 2006 3:53 am
by pgolovko
Hello guys! Following your suggestions:
Code: Select all
$result = mysql_query("SELECT * FROM `categories` ORDER BY `title`");
echo mysql_error();
if (($categories_total = mysql_num_rows($result)) > 0) {
while ($row = mysql_fetch_array($result)) {
if($row['parent'] == $id){
$row["links_num"] = mysql_num_rows(mysql_query("SELECT * FROM links WHERE `visible` = '1' AND `category_id` = '".$row['id']."' LIMIT 0 , 3000000"));
$categories[] = $row;
$query_subs = "SELECT * FROM `categories` WHERE `parent` = ".$row['id']." ORDER BY `title` LIMIT 0 , 4";
$result_subs = mysql_query($query_subs);
echo mysql_error();
while ($row_subs = mysql_fetch_array($result_subs)) {
$sub_cats[] = $row_subs;
}
}
}
}
$smarty->assign('categories', $categories);
$smarty->assign('sub_cats', $sub_cats);
In the database `categories` is ordered by id, and I need to reorder them by `title` in the above code. It worked with
$sub_cats[], but didnt with
$categories[].

Posted: Fri Oct 13, 2006 4:16 am
by Oren
Well, this isn't going to work, I can point on too many problems with your code. It'd be better if you tell us what exactly you are trying to do and what is the structure of the related tables in the database and we'll show you the right way to do it.
Posted: Fri Oct 13, 2006 5:05 am
by pgolovko
`categories` table has few fields: id, title, parent, etc.
id = INT(11) auto increment.
title = text
parent = id of a parent category.
I need to assign all rows to $categories where parent = $id. Say the $id = 5, then $categories will be all rows from `categories` table where `parent` = 5.
Code: Select all
if($row['parent'] == $id){
$categories[] = $row;
Then I need to assign 4 subcategories along with the categories I assign above:
Code: Select all
$query_subs = "SELECT * FROM `categories` WHERE `parent` = ".$row['id']." ORDER BY `title` LIMIT 0 , 4";
$result_subs = mysql_query($query_subs);
echo mysql_error();
while ($row_subs = mysql_fetch_array($result_subs)) {
$sub_cats[] = $row_subs;
}
For example in the database I have the following:
Code: Select all
id|title|parent
1|MainCat1|0
2|MainCat2|0
3|MainCat3|0
4|SubCat1|1
5|SubCat2|1
6|SubCat3|1
7|SubCat4|3
8|SubCat5|7
9|SubCat6|2
10|SubCat7|5
11|SubCat8|1
12|SubCat9|2
13|SubCat10|11
If $id = 0, then the output of the above would be:
Code: Select all
MainCat1
-SubCat1 -SubCat2 -SubCat3 -SubCat8
MainCat2
-SubCat6 -SubCat9
MainCat3
-SubCat4
If $id = 1, then the output would be:
Code: Select all
SubCat1
SubCat2
-SubCat7
SubCat3
SubCat8
-SubCat10
My code assigns the categories nad sub categories correctly. $sub_cats[] gets ordered by `title`, but $categories[] do not get sorted by `title`. Would would be the reason? I need them both be sorted by title.
Posted: Fri Oct 13, 2006 5:35 am
by Oren
Your table structure is very confusing and it doesn't not make sense at all - to me at least, but I'm pretty sure everybody else here would agree with me

Have you considered to split it into 2 tables:
Categories and
Sub Categories? This is much more common and better approach

Posted: Fri Oct 13, 2006 7:09 pm
by pgolovko
I think I found what was the problem with "ORDER BY". I didnt specify how to order ASC or DESC. The following works just perfect:
Code: Select all
$result = mysql_query("SELECT * FROM `categories` ORDER BY `title` ASC");
echo mysql_error();
if (($categories_total = mysql_num_rows($result)) > 0) {
while ($row = mysql_fetch_array($result)) {
if($row['parent'] == $id){
$row["links_num"] = mysql_num_rows(mysql_query("SELECT * FROM links WHERE `visible` = '1' AND `category_id` = '".$row['id']."'"));
$categories[] = $row;
$query_subs = "SELECT * FROM `categories` WHERE `parent` = ".$row['id']." ORDER BY `title` ASC LIMIT 0 , 4";
$result_subs = mysql_query($query_subs);
echo mysql_error();
while ($row_subs = mysql_fetch_array($result_subs)) {
$sub_cats[] = $row_subs;
}
}
}
}
$smarty->assign('categories_total', $categories_total);
$smarty->assign('categories', $categories);
$smarty->assign('sub_cats', $sub_cats);
I know you guys find a lot of mistakes in my coding, PHP is not my "native" language, but I learn fast

If you think something else in my code needs an extra touch, then please let me know what and how. Though I dont need just the code, I'd also need to see some
explanations why this and that is better than what I have.
Thank you in advance.