...

View Full Version : display database results in a set order using SQL



chris_angell
03-28-2007, 11:52 AM
I am trying to bring records out from the database but in a set order.. for example.. my table below

i have an id set and a name for example

ID NAME
1 chris
2 dave
3 steve
4 john
5 ivor


select * FROM tbl ORBERY BY.... 5 then 3 then 1 then 2 then 4

so it displays

(id 5) ivor
(id 3) steve
(id 1) chris
(id 2) dave
(id 4) john

how can I bring write my sql statement to diplay the results in a certain order ???

cheers chris

markingT
03-28-2007, 02:04 PM
Hi,

Better alter the table and add some sort column (integer) and then put order by sort column..

Hope that helps..

Mark

chris_angell
03-28-2007, 04:08 PM
how do i do this??.. i don't suppose you could write a quick SQL string example..


thanks

Daemonspyre
03-28-2007, 04:20 PM
What you are talking about is doing Nested ordering.

ID should be an auto_increment in your table. You would want to add an `Nesting` INT() to your table. Then you can add the order that you want.

Simple SQL statement? It's not simple, but it's not hard. Here are some simple queries that you need to create a new table, add the data, and your SELECT.



CREATE TABLE `tblChrisAngell` (
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Name` VARCHAR(5) DEFAULT NULL,
`Nesting` INT DEFAULT 0,
PRIMARY KEY(`ID`)
);

INSERT INTO tblChrisAngell (Name,Nesting) VALUES
('chris',3),
('dave',4),
('steve',2),
('john',5),
('ivor',1);

/*
Since ID is auto_increment, adding the fields in this order will give you the same ID as before without the extra typing.
*/

SELECT ID,Name FROM tblChrisAngell ORDER BY Nesting ASC;


HTH!

chris_angell
03-28-2007, 09:29 PM
many thanks for that,,, I understand the logic you were talking about.. BUT

if I have these records in the database already under the names and nesting values.. that is fine

('chris',3),
('dave',4),
('steve',2),
('john',5),
('ivor',1);

but then for example then i want to bring the information out on a web page in a different order for example

SELECT * FROM tblChrisAngell ORDER BY NESTING (order the way i want to bring them out ie i want to show records in this order 2,1,3,5,4)

rst1.move(5)
do until rst.eof

rst("name") & "<br>"

loop

???????????????????

so i am wondering if i can bring them out by an ORDER i specify....

so when i query the database i want to bring out nesting record 2 first, then nesting record 1 and so on...

thanks you.. i hope this makes sense,,, :)

Daemonspyre
03-30-2007, 03:08 PM
I have been thinking about this, and I cannot come up with a solution to your problem.

Would you please provide more info on the page that you are trying to display? It may be that there are other fields that we need to consider rather than just the names in the display order.

FOR EXAMPLE,
We may need to include surnames, not just first names.
If this is a grade book, we need to include grades or GPAs.
We can sort by dates and times, depending on what you need to see.

This may also be a time for code, not just database record ordering.

chris_angell
03-30-2007, 03:22 PM
thanks for the reply....if you look at the linked page..

http://preview.globalwebcity.com/code/EDITawp/

if you look at the link.. you will see x ammount of boxes, and with these you can drag up and down.. so they have a new position...

when you click the link at the top of the page it will now show the running order of the boxes

what I am trying to do , is record the running list so when i bring the boxes out of the database they will come out in that set order.. i was thinking i would have a field saying 1,2,3,5,4,6,7, (ie the order)

then this is the point of my post.. so the database initially brings out the boxes in that order untill the new order is saved..

i hope this makes sense... cheers chris

Daemonspyre
04-02-2007, 07:18 PM
Sorry to be so late on the reply --

I looked at your page.

The JS seems to work fine to me, and ergo, you can make this an easy fix.

So, you want this to record the new order...

Add a field to your form.
<input type="hidden" name="newOrder" size="1" value="">

Whenever the JS updates the order, set the value of the hidden field = new JS order.

<script language="javascript"> document.form[0].newOrder.value = liArray.join();</script>

Now, when you post your form with the new order, set the database field "pgOrder" (or whatever name you choose) to be that value, giving you the new order. This database field can either be in the table above we have already talked about, or in another table used to drive this page.

When you retrieve the record, you will be need to Split() your record, but then you can push the new order out that way.

Does this make sense? Let me know if it doesn't.

HTH!

chris_angell
04-02-2007, 08:09 PM
many thanks for that.. it is making scene and I get the adding liArray.join(); to the database,,, but i dont get the

split() when pulling the info out... is it possible you could show me an example ???

cheers chris

Daemonspyre
04-02-2007, 11:23 PM
Sure...

I will show you how to do this in both ASP and JS, and you can decide which is best for you. Please note, I am a much better ASP coder than JS, but it has been tested and it does work...

I am going to assume that you know how to push and pull records from a database.

For this example, table is "tblChris_Angell", ordered field is "newOrder"

ASP:


<body>
<%
if rs("newOrder") <> "" then
orderMe = rs("newOrder")
arrOrder = Split(orderMe,",")
'
listItem = ""

for x = 0 to ubound(arrOrder)
listItem = listItem & "<li id=""" & x & """>" & vbCrLf
listItem = listItem & "<table border=""0"" width=""100%"" cellspacing=""1""> & vbCrLf
listItem = listItem & "<tr>" & vbCrLf
listItem = listItem & "<td width=""50%"">awp layout " & x & "</td>" & vbCrLf
listItem = listItem & "<td width=""50%"" align=""right"">open</td>" & vbCrLf
listItem = listItem & "</tr>" & vbCrLf
listItem = listItem & "</table>" & vbCrLf
listItem = listItem & "</li>" & vbCrLf
next
end if
%>
...
...
...
<ul id="numeric" class="sortable boxier" style="margin-right: 1em; position:absolute; left:154px; top:116px; width:764px">
<%
if listItem <> "" then
response.write(listItem)
else %>
your previous HTML code goes here...
<% end if %>
</ul>



AND in JS ( a little more difficult, but not hard...)



<% if rs("newOrder") <> "" then
listItem = rs("newOrder")
else
listItem = "1,2,3,4,5,6"
end if %>

<body>
<script language="javascript">
var listItem = '<%= listItem %>';
var items = listItem.split(/,/);
var numericList = document.getElementById('numeric');

window.onload = function() {

for(i=0;i<items.length;i++){
var li = document.createElement('li');
li.id = items[i];

var table = document.createElement('table');
table.style.border = '0';
table.style.width = '100%';

var tr = table.insertRow(-1);
tr.innerHTML = '<td width=\'50%\'>awp layout ' + items[i] + '</td><td width=\'50%\' align=\'right\'>open</td>';

table.appendChild(tr);

li.appendChild(table);
numeric.appendChild(li);
}
return numericList;
}
</script>
<ul id="numeric" class="sortable boxier" style="margin-right: 1em; position:absolute; left:154px; top:116px; width:764px">
</ul>



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum