Sorting data without mysql or sort()

[ballhogjoni]

I am pulling a certain amount of rows from a db via LIMIT in my query and from that query I get the id of the rows and store them in an array. In a foreach loop I run a query that gets other data from another table based off that id in the first query. I have to do it this way because I have to run thru a table of 500K records or more. This is the most efficient way as far as mysql EXPLAIN goes.

 

EXAMPLE:

I pull 10 rows and each row I get the id, name, and some other data. my code creates a table and then loops through the resource creating table rows and columns.

 

When the user clicks on the column heading I need to sort that column, and it doesn't matter if its ASC or DESC because that will need to change each time the user clicks the column heading.

 

So because I have 2 queries that get the info I need to display the table correctly with the correct data, I can't use ASC/DESC in an ORDER clause. If I do then it ORDERs all records in the entire table and not just the 10 records I pulled.

 

So my question is how can I sort/order the 10 rows when a user clicks on the column heading?

 

I researched and tried usort but I must not understand it all that well because I can't get it to work right. Bottom line I have to use php to sort these rows because I can't use mysql to sort them. (Unless someone knows a way I don't)

[ober]

Use MYSQL.  When the user clicks the column name, add a parameter to the URL and check for that parameter when you create the SQL query.  That's the best and most efficient way to do it.

[ballhogjoni]

Use MYSQL.  When the user clicks the column name, add a parameter to the URL and check for that parameter when you create the SQL query.  That's the best and most efficient way to do it.

 

I realize that but its not possible to the best of my knowledge in this instance. Let me explain...I pull 10 specific rows from a database that has 500,000 rows. The user only wants to sort these 10 specific rows, not all 500,000. If I add a ASC/DESC to a query like: select id from table_name order by name ASC LIMIT 10 ... then it will sort all of the rows in the entire table.

[PFMaBiSmAd]

In a foreach loop I run a query that gets other data from another table based off that id

 

Don't loop. Make a single query using the IN() comparison. You can then use ASC/DESC on this single query.

 

Assume your first query returns the id's - 1,5,10,30. Your second query would be -

 

SELECT your_columns FROM your_2nd_table WHERE id IN(1,5,10,30) ORDER BY your_column DESC

 

And if by chance you need to do an arbitrary ORDER BY using the order of the list of id's (for example 5,30,1,10) we can show how to do that as well.

[flyhoney]

Yes, I believe everything you are trying to do can be done using SQL.  If not, than usort will work fine.  Show us the data you are sorting and we can create a workable usort function for you to use.

[ballhogjoni]

$aCompanyIds = "SELECT c.id FROM companies c WHERE approved = 1 AND deleted = 0 AND (c.name LIKE '%".$search."%' OR c.code LIKE '%".$search."%') $sort_order $sort_dir $companies_to_view";
		foreach( $aCompanyIds as $id ){
			$aCompanyInfo[$id['id']] = "SELECT	
						c.id,
						c.code,
						c.name, 
						c.date_started, 
						c.num_employees, 
						c.city, 
						c.state, 
						count(clients.id) as clients
							FROM clients clients
								LEFT JOIN companies c ON c.id = clients.company_id
								LEFT JOIN company_lists cl ON c.id = cl.company_id 
									WHERE approved = 1 AND deleted = 0 AND clients.company_id = ".$id['id']);

			$aCompanyInfo[$id['id']]['sales'] = "SELECT
						sum(cs.sale_count) - sum(cs.nosale_count) as sales 
							FROM company_sales AS cs
								WHERE cs.cid = ".$id['id']);
			$aCompanyInfo[$id['id']]['sales'] = ($aCompanyInfo[$id['id']]['sales'] > 0) ? $aCompanyInfo[$id['id']]['sales'] : "0";

			$aCompanyInfo[$id['id']]['last_year_number_of_sales'] = "SELECT
						sum(cs.sale_count) - sum(cs.nosale_count) as sales 
							FROM company_sales AS cs
								WHERE cs.year = ".$last_year." AND cs.cid = ".$id['id']);
			$aCompanyInfo[$id['id']]['last_year_number_of_sales'] = ($aCompanyInfo[$id['id']]['last_year_number_of_sales'] > 0) ? $aCompanyInfo[$id['id']]['last_year_number_of_sales'] : "0";

			$aCompanyInfo[$id['id']]['this_year_number_of_sales'] = "SELECT
						sum(cs.sale_count) - sum(cs.nosale_count) as sales 
							FROM company_sales AS cs
								WHERE cs.year = ".$current_year." AND cs.cid = ".$id['id']);
			$aCompanyInfo[$id['id']]['this_year_number_of_sales'] = ($aCompanyInfo[$id['id']]['this_year_number_of_sales'] > 0) ? $aCompanyInfo[$id['id']]['this_year_number_of_sales'] : "0";

		}

 

FYI I had to break this into 5 queries because if it were one query it would have to create like 3 views and search each row in each view as well as each table, so it would go through hundreds of thousands of records to get one I need. it was to inefficient so I had to break it up like this and loop through the ids and run the query in the loop, which I know is not what you want to do but I couldn't think of any other way.