Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Regular Coder
    Join Date
    Jul 2002
    Location
    Brighton, UK
    Posts
    282
    Thanks
    1
    Thanked 0 Times in 0 Posts

    display database results in a set order using SQL

    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
    <marquee>thanks</marquee>

  • #2
    New to the CF scene
    Join Date
    Mar 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

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

    Hope that helps..

    Mark

  • #3
    Regular Coder
    Join Date
    Jul 2002
    Location
    Brighton, UK
    Posts
    282
    Thanks
    1
    Thanked 0 Times in 0 Posts
    how do i do this??.. i don't suppose you could write a quick SQL string example..


    thanks
    <marquee>thanks</marquee>

  • #4
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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.

    Code:
    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!

  • #5
    Regular Coder
    Join Date
    Jul 2002
    Location
    Brighton, UK
    Posts
    282
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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,,,
    <marquee>thanks</marquee>

  • #6
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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.

  • #7
    Regular Coder
    Join Date
    Jul 2002
    Location
    Brighton, UK
    Posts
    282
    Thanks
    1
    Thanked 0 Times in 0 Posts

    have a look at the link...

    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
    <marquee>thanks</marquee>

  • #8
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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!

  • #9
    Regular Coder
    Join Date
    Jul 2002
    Location
    Brighton, UK
    Posts
    282
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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
    <marquee>thanks</marquee>

  • #10
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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:
    Code:
    <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...)

    Code:
    <% 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>


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •