ORDER BY `title` ....doesnt work?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
pgolovko
Forum Commoner
Posts: 38
Joined: Sun Sep 17, 2006 9:13 am

ORDER BY `title` ....doesnt work?

Post 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?
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post 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) {
:wink:
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post 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:

Code: Select all

LIMIT 0 , 1000000
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post by Oren »

Mordred wrote:Explain the purpose of this:

Code: Select all

LIMIT 0 , 1000000
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 :P ) then just do:

Code: Select all

LIMIT 1000000
User avatar
pgolovko
Forum Commoner
Posts: 38
Joined: Sun Sep 17, 2006 9:13 am

Post 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[]. :?
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post 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.
User avatar
pgolovko
Forum Commoner
Posts: 38
Joined: Sun Sep 17, 2006 9:13 am

Post 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.
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post 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 :wink:
User avatar
pgolovko
Forum Commoner
Posts: 38
Joined: Sun Sep 17, 2006 9:13 am

Post 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.
Post Reply