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.
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Coder
    Join Date
    Apr 2013
    Posts
    32
    Thanks
    2
    Thanked 0 Times in 0 Posts

    bit of help to get this 'Sorted'!!

    Good evening everyone, and thanks for having me on board this forum.

    I found a piece of code in the codingforums archives (per below) that allows a table to be sorted in an ascending or descending fashion by clicking on the header.

    Now Im no expert coder, so hold my hands up that stuff like this I have to research on, but I do read through and try to understand all code I find to try and increase my knowledge. However Im struggling to understand how to alter this slightly to allow me to do the following.

    At the moment, The code sorts the data when the header title is clicked. I want to change this so that the sorting takes place by two arrows (ascending and descending arrows) with in the header. Im just struggling to sort according to the href arrow.

    Any help??


    Code:
    Here is my revised code:
    
    This script works ASC but not DESC -
    
    When clicking $sort produces
    sort=CostItem&order="(DESC%20)"
    
    How can I correct this to toggle ASC DESC onClick?
    
    
    // select default
    $Default = 'CostItem';
    
    // select array
    $Columns = array('CostCode','CostItem');
    
    // define sortable query ASC DESC
    $sort = isset($_GET['sort']) && in_array($_GET['sort'], $Columns) ? $_GET['sort'] : $Default;
    $order = (isset($_GET['order']) && strcasecmp($_GET['order'], 'DESC') == 0) ? 'DESC' : 'ASC';
    
    $result = mysql_query("SELECT * FROM costitems ORDER BY $sort $order");
    // clickable header
    ?>
    <table width='350' border='1'>
    <tr>
    Here is my revised code:
    
    This script works ASC but not DESC -
    
    When clicking $sort produces
    sort=CostItem&order="(DESC%20)"
    
    How can I correct this to toggle ASC DESC onClick?
    
    
    // select default
    $Default = 'CostItem';
    
    // select array
    $Columns = array('CostCode','CostItem');
    
    // define sortable query ASC DESC
    $sort = isset($_GET['sort']) && in_array($_GET['sort'], $Columns) ? $_GET['sort'] : $Default;
    $order = (isset($_GET['order']) && strcasecmp($_GET['order'], 'DESC') == 0) ? 'DESC' : 'ASC';
    
    $result = mysql_query("SELECT * FROM costitems ORDER BY $sort $order");
    // clickable header
    ?>
    <table width='350' border='1'>
    <tr>
    <td><a href='?sort=CostCode&order="(<?php echo $order == 'DESC' ? 'ASC' : 'DESC' ?> )" '>CostCode</a></td>
    <td><a href='?sort=CostItem&order="(<?php echo $order == 'DESC' ? 'ASC' : 'DESC' ?> )" '>CostItem</a></td>
    </tr>
    <?php
    // list records
    while ($row = mysql_fetch_assoc($result)) {
    echo "<tr>
    <td>$row[CostCode]</td>
    <td>$row[CostItem]</td>
    </tr>";
    }
    echo "</table>";
    ?>
    </tr>
    <?php
    // list records
    while ($row = mysql_fetch_assoc($result)) {
    echo "<tr>
    <td>$row[CostCode]</td>
    <td>$row[CostItem]</td>
    </tr>";
    }
    echo "</table>";
    ?>

  • #2
    New Coder
    Join Date
    Apr 2013
    Posts
    32
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Sorry, Hadnt realised I had left the original posters questions on the top. So just reposting the code only

    Code:
    // select default
    $Default = 'CostItem';
    
    // select array
    $Columns = array('CostCode','CostItem');
    
    // define sortable query ASC DESC
    $sort = isset($_GET['sort']) && in_array($_GET['sort'], $Columns) ? $_GET['sort'] : $Default;
    $order = (isset($_GET['order']) && strcasecmp($_GET['order'], 'DESC') == 0) ? 'DESC' : 'ASC';
    
    $result = mysql_query("SELECT * FROM costitems ORDER BY $sort $order");
    // clickable header
    ?>
    <table width='350' border='1'>
    <tr>
    <td><a href='?sort=CostCode&order="(<?php echo $order == 'DESC' ? 'ASC' : 'DESC' ?> )" '>CostCode</a></td>
    <td><a href='?sort=CostItem&order="(<?php echo $order == 'DESC' ? 'ASC' : 'DESC' ?> )" '>CostItem</a></td>
    </tr>
    <?php
    // list records
    while ($row = mysql_fetch_assoc($result)) {
    echo "<tr>
    <td>$row[CostCode]</td>
    <td>$row[CostItem]</td>
    </tr>";
    }
    echo "</table>";
    ?>
    </tr>
    <?php
    // list records
    while ($row = mysql_fetch_assoc($result)) {
    echo "<tr>
    <td>$row[CostCode]</td>
    <td>$row[CostItem]</td>
    </tr>";
    }
    echo "</table>";
    ?>

  • #3
    Regular Coder
    Join Date
    Sep 2011
    Posts
    417
    Thanks
    18
    Thanked 26 Times in 26 Posts
    PHP Code:
    <td><a href='?sort=CostCode&order="(<?php echo $order == 'DESC' 'ASC' 'DESC' ?> )" '>CostCode</a></td>
    <td><a href='?sort=CostItem&order="(<?php echo $order == 'DESC' 'ASC' 'DESC' ?> )" '>CostItem</a></td>
    Change this part to this:
    PHP Code:
    <td><a href="?sort=CostItem&order=ASC">CostCode</a></td>
    <
    td><a href="?sort=CostItem&order=DESC">CostItem</a></td
    What it originally prints out is this for the links:
    PHP Code:
    <td><a href='?sort=CostCode&order="(ASC )" '>CostCode</a></td>
    <
    td><a href='?sort=CostItem&order="(ASC )" '>CostItem</a></td
    The parenthesis aren't needed and neither are the quotes. Inserting php code into html code that isn't being echoed or printed will work and shouldn't be escaped with extra quotes, meaning you don't need an extra "" or parethesis.


    If you want it in one clickable link to tggle, use this:
    PHP Code:
    <td><a href="?sort=CostCode&order=<?php echo $order == 'DESC' 'DESC' 'ASC';?>">CostCode</a></td>

  • #4
    New Coder
    Join Date
    Apr 2013
    Posts
    32
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hey Dubz,

    Thanks for you quick reply and for the help

    I was actually in the right ball park in terms of what section I thought needed to be edited, I just couldnt figure it out as I was trying to get this to work

    Code:
    <td><a href='?sort=CostCode&order="(<?php echo $order == 'DESC'?> )" '>CostCode</a></td>
    <td><a href='?sort=CostCode&order="(<?php echo $order ==  'ASC'?> )" '>CostCode</a></td>
    You dont seem to keep the echo $order in your response, just out of learning interest, how come?

    Also in you statement about the "" and parethesis, so am I right in understanding that what your saying is that unless you are echo'ing or printing something via php, you dont need the "" or parenthesis as the html will automatically pick it up?

  • #5
    Regular Coder
    Join Date
    Sep 2011
    Posts
    417
    Thanks
    18
    Thanked 26 Times in 26 Posts
    For starters, the code you posted would only echo 1 if the $order was equal to the string and blank if it wasn't since you're echoing the comparison test.

    Quote Originally Posted by RussH View Post
    You dont seem to keep the echo $order in your response, just out of learning interest, how come?
    Because you have two links that don't change on any condition (static), the php code isn't even needed. If you wanted to have them toggle however, you would compare them to what is already shown and toggle between those options (dynamic based on current listing).

    Quote Originally Posted by RussH View Post
    Also in you statement about the "" and parethesis, so am I right in understanding that what your saying is that unless you are echo'ing or printing something via php, you dont need the "" or parenthesis as the html will automatically pick it up?
    The parentheses and quotes are unneeded because the php will automatically be taken over in the php tags and the results of what happens inside them (in this case the echoing) will take place of the tags and everything between them.

    In basic terms, The first line will become the second in this example:
    PHP Code:
    <p><?php echo 'Hello world';?></p>
    <p>Hello world</p>
    This is what is outputted in the php tags, the php "engine" executes the code and "echo"es the string in its place

    However, if you place the quotes like you have in the example below, the results will be as follows:
    PHP Code:
    <td><a href='?sort=CostCode&order="(<?php echo $order == 'DESC' 'ASC' 'DESC' ?> )" '>CostCode</a></td>
    <td><a href='?sort=CostCode&order="(ASC )" '>CostCode</a></td>
    The second line isn't what you want, what you want is this:
    PHP Code:
    <td><a href='?sort=CostCode&order=ASC'>CostCode</a></td
    This is only assuming that $order == 'DESC';, which also means the code is partially backwards (with the ? comparison, true is first followed by false, having : separate them).
    Last edited by Dubz; 04-07-2013 at 09:24 PM.

  • #6
    New Coder
    Join Date
    Apr 2013
    Posts
    32
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks for all your help Dubz, got this working a treat now.

    I have a couple of other things I wondered if you could give me some pointers on.

    Firstly, i want to create two buttons above the table, which when press/clicked on, will limit the range of results in the table. For example, say you had a set of window sizes, ranging from 500mm to 4000mm, at the top you had free buttons (50 - 1000mm) (1001 to 2000mm) and (2001 to 4000mm), and as such if you clicked on one it would alter the mysql_query statement to only show that range? Can that be done??

    Also, Im thinking of adding a text box with a max and min value that the user can enter a range in, thus showing the results in that range

    I guess both of the above would use very similar code, just different user entry points

  • #7
    Regular Coder
    Join Date
    Sep 2011
    Posts
    417
    Thanks
    18
    Thanked 26 Times in 26 Posts
    You can easily select the rows from the database by giving it a starting row number and a limit to how many. You can use the LIMIT command in your select syntax to do this.

    Example 1:
    This will limit the results to the first 10 rows, starting on row 0 (the first row displayed since arrays start on 0 and not 1)
    Code:
    SELECT * FROM `my_table` ORDER BY id ASC LIMIT 0, 10
    Example 2:
    This will limit the results to 25, starting on row 99 (100th entry)
    Code:
    SELECT * FROM `my_table` ORDER BY id ASC LIMIT 99,25

    Basically the first number in the command is the starting row and the second is how many maximum results to return. If there is only 4 possible results but you limit it to 5, it will still only return the 4 results.



    As for the limiting the number of rows per query, use a variable to pass through the command (I'd check with ctype_digit() to make sure its a whole integer and avoid sqli while defaulting to whatever on failure). If the user isn't defining the number of rows, then you can just place a number there.

  • #8
    New Coder
    Join Date
    Apr 2013
    Posts
    32
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I think I follow, but how would make the "SELECT * FROM `my_table` ORDER BY id ASC LIMIT" statment change depending on the users entry, so it they said they want to limit the size between 50 and 1000mm, how would you apply this gotten variable?

  • #9
    Regular Coder
    Join Date
    Sep 2011
    Posts
    417
    Thanks
    18
    Thanked 26 Times in 26 Posts
    Not too sure on what you mean by mm. Either you're explaining wrong or there's something else out there I haven't learned about this yet.

    What I showed you would limit based on number of rows, not memory or size (guessing that's what that is for) so before I go off and "screw it up by innocently mistaking it for something else", I'll have to leave it for the "experts" to answer you on this one.

  • #10
    New Coder
    Join Date
    Apr 2013
    Posts
    32
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Sorry Dubz, the mm is milimeters. So im looking to allow the user to search for a max and min range within the size data of the size size column, and return the results.

    So say we had the below

    Size (mm - milimeters)
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10


    I want the user to fill in a text box on the webpage, say they enter Min - 2 and max 6. The table would refresh and only show the results that fall in between that range. They might then change their mind and change the range to 2 - 4. So it would only show the data between them

    etc

  • #11
    Regular Coder
    Join Date
    Sep 2011
    Posts
    417
    Thanks
    18
    Thanked 26 Times in 26 Posts
    How are these numbered stored exactly in the database? Is it an integer or a string? Also, are there duplicates (or possibly to be duplicates) of the sizes or are there only one of each?

  • #12
    New Coder
    Join Date
    Apr 2013
    Posts
    32
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Dibz,

    The values are stored as decimals at the moment, but I can change this. There will also be duplicates.

    Cheers

  • #13
    New Coder
    Join Date
    Apr 2013
    Posts
    32
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Actually, I should probably explain this a little better, rather than the simple examples above.

    My sql has a number of field with examples as follows

    Part Number (part_num) - stored as varchar(30)

    Size (size) - stored as Decimal(5,1). The value of which are sizes such as 25.0, 35.8, 100.7, 130.0, 167.3, 190.5, 230.7, 255.0 etc

    Depth (depth) - stored as Decimal(4,1). Values of which are 5.1, 8.0, 10.5 etc

    Weight (weight) - stored as Decimal(5,2). Values of which are 5.8, 7.7, 9.2, 10.6. etc

    So most fo the field I would like to use a search faciltiy on are decimals, with the exception of the part numbers which is a varchar. There will be duplicate values in the field, except for the part numbers.

    Cheers

  • #14
    Regular Coder
    Join Date
    Sep 2011
    Posts
    417
    Thanks
    18
    Thanked 26 Times in 26 Posts
    I'm not familiar with the decimal type column because I have never used it. I know that for selecting items within a specific size like that you would add a comparison in the query.

    For example, if I wanted to get every entry from a table where say a moderation warning percentage was between 40 and 60, I would use the following command:
    PHP Code:
    mysql_query("SELECT * FROM `members` WHERE `mod_warning` > 40 AND `mod_warning` < 60"); //Everything BETWEEN 40 and 60
    mysql_query("SELECT * FROM `members` WHERE `mod_warning`>=40 AND `mod_warning`<=60"); //Everything INCLUDING 40 and 60 
    Using quotes around the value you are searching for will depend on the type of data stored in the column (integers won't need them, strings will). Since you have decimals in your numbers, it's safe to say you would need the quotes no matter what.

    I don't know for sure if this will work because I haven't used the decimal type before so run a test or two in the actual phpmyadmin (or whatever you may use) under the SQL tab and make sure the results are what you want.

  • #15
    New Coder
    Join Date
    Apr 2013
    Posts
    32
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks Dubz, I will do some reading / learning of commands you suggested.

    How would you link this 'WHERE' command to the entry boxes on the website.

    So say on the web page I had to text boxes, Min and Max, where a user could enter their search parameters, and the WHERE statement would take them and display the results.

    So if a user entered on the webpage the 40 min and 60 max you show in your above example. How do I get it from that form to the WHERE statement

    Hope that question makes sense


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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