MYSQL statement

[vang163]

hi,

if i have 100 records in a table, how can i display data at row 11~20 in the descending order?

eg.

row 20 data

row 19 data

row 18 dtata

...

...

 

What is the sql stement like?

$sql = "select * from table order by desc limit 10,10"; ???

 

[.josh]

you need to specify which column to order by

 

select * from table order by column desc limit 10,10

[vang163]

$sql = "select * from table order by column desc limit 10,10";

i try this but it give me data of row 80~90 in descending order, not row 11~20... :'(

[wildteen88]

desc should be asc

[vang163]

i have 100 records in a table, how can i display data at row 11~20 in the descending order?

eg.

row 20 data

row 19 data

row 18 dtata

...

...

[.josh]

ah yeah, forgot about that.  The desc makes the list overall 100, 99, 98, etc.. so 100 is position 0, so offset 10 is 91 or whatever.  If you change it from desc to asc then the results will be

 

11

12

13

14

...

 

It will be the result set you want, but not the right order. So you can

 

a) remove the desc and reverse the order of the results through php, or

b) change your offset to what 80, 10?

[vang163]

is there a formulae or logic so that i can view the result in any way i want but with only 1 sql statement?

eg.

view row 30~40 in ascending & descending order?

view row 60~90 in ascending & descending order?

....

 

[vang163]

i do not want to go back to the script and start editing each time i want to view different set of data in different order.

appreciate if anyone could show me or point me the direction on how this can be done with the correct sql statement or logic, thanks.

 

[.josh]

well if you're talking about getting different results depending on your mood/situation, then that's done by having options in a form to check/select, and building a dynamic query string based on that info... I thought you were wanting help on writing a very specific query string (which is why I moved it to mysql forum).  Are you wanting help on building a dynamic query string in general?

[vang163]

yes, please.

I tried for last 3 days, but failed. only managed to sort it out for table column id, but not for other column which is in varchar field.

[revraz]

You need to use a Variable for the SORT BY and variables for the Start, Number values.

[vang163]

my initial question written on the other day was:

 

hi,

i've a table with 100 records.

i displayed the data into 10 records per page, therefore i've 10 pages of data.

 

using this query - "select * from table order by $column $direction limit $set_limit $limit_size"

where $column is the table column name

$direction - asc or desc

 

Assuming i'm at page 1, I can view the display data (1,2,3,4..10) in ascending order by default. But when i want to display the data in descending order for page 1, I'm getting descending order data from page 10.

 

I want to be able to see the page 1 data in ascending and in descending order. This applies to all other pages. (2,3,4...10)

Can someone show me how this can be done, thanks.

 

But i did not know what the other helpers are referring to.

That is why i thought the problem lies with the sql statement.

Your assistance in this matter is greatly appreciated, thanks.

[vang163]

this is my code, i use a function to display the data.

$limit - refers to the number of data rows display. eg. display 10 records per page

$page - refers to the current page number.

 

function displayData($tableName, $column, $direction, $limit, $page)
{
$set_limit = ($page * $limit) - $limit;

$sql = "SELECT * FROM $tableName ORDER BY $column $direction LIMIT $set_limit, $limit";
$result = mysql_query($sql) or die('Query Failed.');
$count = mysql_num_rows($result);	
if ($count <= 0)
{	echo 'No match.';
}
else if ($count > 0)
{	while($row = mysql_fetch_assoc($result))	
	{	echo "   <tr>";
		echo "    <td>" . $row['id'] . "</td>";
		echo "    <td>" . $row['tel'] . "</td>";
		echo "    <td>" . $row['fax'] . "</td>";
		echo "    <td>" . $row['Owner'] . "</td>";
		echo "    <td>" . $row['countryCode'] . "</td>";
		echo "    <td>" . $row['country'] . "</td>";
		echo "   </tr>";
	}
}
}

[revraz]

Have you looked at the Pagination tutorials?

[vang163]

there is nothing wrong with my pagination. All is ok.

I'm only having problem with the data display for sorting order (asc/desc) in a single page.