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 5 of 5
  1. #1
    New Coder
    Join Date
    Mar 2005
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Database output sorting

    Im going to make this one short, sry to bother you all.

    I have a dropdown-list and a button. When an option is selected in the list and I click the button a table with certain data should be presented on the page, depending on what option was selected.

    This has been done correctly. The problem is that I wan't to be able to sort the table using my "headlines" as links.

    Name Weight Creator Store -->
    **** ***** ****** ****

    If I click Name the table should be sorted from the Name column. Same if I click on Weight, the table sorts from Weight then. How is this possibly done?

    Code for my varor.php - the page with dropdownlist and button (and the created table when showed)

    PHP Code:
    <p align="center"></p>
    <form method="POST" action="">
            <p align="center"><select size="1" name="D1">
        <option value="brod">Bröd</option>
        <option value="fruktgront">Frukt & Grönt</option>
        <option value="fardigmat">Färdigmat</option>
        <option value="godiskakor">Godis & Kakor</option>
        <option value="hemkok">Hem & Kök</option>
        <option value="konserver">Konserver</option>
        <option value="kottfagelfisk">Kött, Fågel, Fisk</option>
        <option value="mejerivaror">Mejerivaror</option>
        <option value="mjolgryn ">Mjöl & Gryn</option>
        <option value="pastapotatisris">Pasta, Potatis, Ris</option>
        </select><br>
        <br>
        <input type="submit" value="Skicka" name="B1"><input type="reset" value="Rensa" name="B2"></p>
    </form>
    </body>

    <?php
        
    include './func/functions.php';
        if (isset(
    $_POST['D1'])) {
    produkt_query();
    }
    else {
        echo (
    "<p>Välj ett alternativ innan du söker.</p>");
    }
    ?>
    Function for creating the table and showing it:

    PHP Code:
    function produkt_query() {
        $vilkenknapp = $_POST['D1'];
        $sortera = "namn";
        db_intern();
        echo "<p>Om ej vikt/volym anges är priset ett kg-pris.</p>";
        $asking = "SELECT namn AS Namn, vikt AS Vikt, tillverkare AS Tillverkare, butik AS Butik, pris AS Pris, ursprung AS Ursprung FROM artiklar WHERE grupp = '$vilkenknapp' ORDER BY '$sortera'";
        $resultat = mysql_query($asking);

        $affected = mysql_num_rows($resultat);

        ?>
        <table border="0" cellspacing="3" cellpadding="3" align="center">
            <tr>
                <th>Namn</th>
                <th>Vikt/Volym</th>
                <th>Tillverkare</th>
                <th>Butik</th>
                <th>Pris</th>
                <th>Ursprung</th>
            </tr>
        <?
         $x 
    0;
    while(
    $raden mysql_fetch_row($resultat))
        if(
    $x == 0) {
            echo 
    "<tr bgcolor=\"#f6f1ee\"><td>$raden[0]</td><td>$raden[1]</td><td>$raden[2]</td><td>$raden[3]</td><td>$raden[4]</td><td>$raden[5]</td></tr>"
            
    $x++;
        } else {
            echo 
    "<tr bgcolor=\"#f6f1ee\"><td>$raden[0]</td><td>$raden[1]</td><td>$raden[2]</td><td>$raden[3]</td><td>$raden[4]</td><td>$raden[5]</td></tr>"
            
    $x 0;
        }
    }  
            
        
    ?>
        </table>    
        </td>
      </tr>
    </table>

  • #2
    New Coder
    Join Date
    Mar 2005
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Anyone?

  • #3
    Regular Coder
    Join Date
    Feb 2005
    Location
    West Midlands, UK
    Posts
    623
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You could turn your table headings into links which pass a variable to the url telling php which sort order to use. I think this would work and with very little modification required to your existing code:

    PHP Code:
    function produkt_query() { 
        $vilkenknapp = $_POST['D1']; 
        $sortera = (isset($_GET['sort']) ? $_GET['sort'] : 'namn'); // this will get the sort order if one is specified, if not it will just use the default sort order
        db_intern(); 
        echo "<p>Om ej vikt/volym anges är priset ett kg-pris.</p>"; 
        $asking = "SELECT namn AS Namn, vikt AS Vikt, tillverkare AS Tillverkare, butik AS Butik, pris AS Pris, ursprung AS Ursprung FROM artiklar WHERE grupp = '$vilkenknapp' ORDER BY '$sortera'"; 
        $resultat = mysql_query($asking); 

        $affected = mysql_num_rows($resultat); 

        ?> 
        <table border="0" cellspacing="3" cellpadding="3" align="center"> 
            <tr> 
                    // now create the links that tell php which sort order to use in the query
                <th><a href="<?php echo basename($_SERVER['PHP_SELF']); ?>?sort=namn">Namn</a></th> 
                <th><a href="<?php echo basename($_SERVER['PHP_SELF']); ?>?sort=vikt">Vikt/Volym</a></th> 
                <th><a href="<?php echo basename($_SERVER['PHP_SELF']); ?>?sort=tillverkare">Tillverkare</a></th> 
                <th><a href="<?php echo basename($_SERVER['PHP_SELF']); ?>?sort=butik">Butik</a></th> 
                <th><a href="<?php echo basename($_SERVER['PHP_SELF']); ?>?sort=pris">Pris</a></th> 
                <th><a href="<?php echo basename($_SERVER['PHP_SELF']); ?>?sort=ursprung">Ursprung</a></th> 
            </tr>
    However, you will lose your POST data when you click on a link so you'll need to find a workaround for this. I would suggest you store the post data as sessions then access it via sessions, that way it won't be lost when the page is refreshed. Alternatively you could store the original post data as hidden form variables and change the links in the above code to javascript form submit links, so that the hidden form data is passed correctly from page to page - the downside of that is that it will only work properly in javascript enabled browsers of course.

    Before you try that, though, another method that may work is to pass the contents of D1 in your url along with the sort order. If you make your links as follows:
    PHP Code:
    <th><a href="<?php echo basename($_SERVER['PHP_SELF']); ?>?sort=namn&id=<?php echo $_POST['D1']; ?>">Namn</a></th> 
    <th><a href="<?php echo basename($_SERVER['PHP_SELF']); ?>?sort=vikt&id=<?php echo $_POST['D1']; ?>">Vikt/Volym</a></th> 
    .
    .
    etc
    Then add the following to the top of your code:
    PHP Code:
    // this will check if the form was subsequently posted, if not it will assign the existing variable from the url
    if(isset($_GET['id']) && !isset($_POST['D1'])) {
        
    $_POST['D1'] = $_GET['id'];
    }
    if (isset(
    $_POST['D1'])) { 
        
    produkt_query(); 

    I think that will work the way you want it to.
    Last edited by delinear; 07-09-2005 at 12:43 PM.

  • #4
    New Coder
    Join Date
    Mar 2005
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thats great Got it to work instantly...thou, I can't get the same code (after modification) to work with a textbox and four options. Very irritating.

    Search-form:

    PHP Code:
    <p align="center"></p>
    <form method="POST" action="">
        <p align="center"><input type="text" name="T1" size="45"><br>
        <input type="radio" name="R1" value="namn"> Namn&nbsp; 
        <input type="radio" name="R1" value="vikt"> Vikt&nbsp;
        <input type="radio" name="R1" value="tillverkare"> Tillverkare&nbsp;
        <input type="radio" name="R1" value="pris"> Pris<br>
        <br>
        <input type="submit" value="Skicka" name="B1"><input type="reset" value="Rensa" name="B2"></p>
    </form>
    </body>
    <?php
    include './func/functions.php';
        if (isset(
    $_POST['B1'])) { 
        
    produkt_search();

    }  
        else {
            echo (
    "<p>Välj ett alternativ innan du söker.</p>");
    }
    ?>
    The direct change in the function to show the table will be this one:
    PHP Code:
    $asking "SELECT namn AS Namn, vikt AS Vikt, tillverkare AS Tillverkare, butik AS Butik, pris AS Pris, ursprung AS Ursprung FROM artiklar WHERE $vilkenknapp LIKE '%$sok%' ORDER BY namn"
    Instead of...

    PHP Code:
    $asking "SELECT namn AS Namn, vikt AS Vikt, tillverkare AS Tillverkare, butik AS Butik, pris AS Pris, ursprung AS Ursprung FROM artiklar WHERE grupp = '$vilkenknapp' ORDER BY '$sortera'"
    Both these down here is used to show where to get data from and what to get.

    PHP Code:
    $sok $_POST['T1']; 
    $vilkenknapp $_POST['R1']; 
    Last edited by exzrael; 07-09-2005 at 07:09 PM.

  • #5
    Regular Coder
    Join Date
    May 2005
    Location
    Michigan, USA
    Posts
    566
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there any reason why you do not use JS to do this? I have a quick and dirty one that I use to sort tables. Put this into a JS file called SortTable.js

    PHP Code:
    var SORT_DESC = -1;
    var 
    SORT_ASC 1;

    var 
    SORT_DESC_IMG './images/Move_Up.gif'
    var SORT_ASC_IMG './images/Move_Down.gif'

    var CLASSNAME 'sortabletable'




    function setSortTables() {
        var 
    aTable document.getElementsByTagName('table');
        for ( var 
    aTable.length x++ ) {
            
    //alert(aTable[x]['tablesort']);
            
    if ( aTable[x].className.toLowerCase() == CLASSNAME ) {
                
    createSortTable(aTable[x]);
            }
        }
    }

    function 
    attachEvent(objsEventfun) {
        if (
    obj.addEventListener) {
            
    obj.addEventListener(sEvent.substr(2), funfalse);
            return 
    true;
        } else if (
    obj.attachEvent) {
            var 
    obj.attachEvent(sEventfun);      
            return 
    r;
        } else {
            return 
    false;
        }
    }

    function 
    getParent(objtagName) {
        if ( !
    obj || typeof tagName != 'string' ) return null;
        var 
    tObj obj;
        
    tagName tagName.toUpperCase();
        while ( ! ( 
    tObj == null || tObj.tagName == tagName ) ) {
            
    //alert(tObj.tagName);
            
    tObj tObj.parentNode;
        } 
        return 
    tObj;
    }

    function 
    sortByMe(e) {
        var 
    obj returnEventTarget(e);
        
        if ( 
    obj == null ) return true;
        var 
    oTable getParent(obj'TABLE');
        
        if ( 
    typeof oTable['sortcol'] == 'undefined' oTable['sortcol'] = -10000;
        if ( 
    isNaN(parseInt(oTable['sortcol'])) ) {
            
    oTable['sortcol'] = -10000;
        } else {
            
    oTable['sortcol'] = parseInt(oTable['sortcol']);
        }
        
        
        
        
        
        
        var 
    iSortCol Math.absoTable['sortcol'] )
        var 
    iIndex obj.cellIndex 1;
        var 
    cSort SORT_ASC;
            
        if ( 
    oTable['sortcol'] != -10000 && iSortCol != iIndex  ) {
            var 
    oTR getParent(obj'TR');
            var 
    aTD oTR.getElementsByTagName('td');
            if ( 
    aTD.length == ) {
                var 
    aTD oTR.getElementsByTagName('th');
            }
            var 
    oTD aTD[iSortCol 1];
            
            var 
    aImg oTD.getElementsByTagName('img');
            if ( 
    aImg.length ) {
                
    oTD.removeChild(aImg[aImg.length 1]);
            }
                    
        
        }


        var 
    aImg obj.getElementsByTagName('img');
        if ( 
    aImg.length ) {
            var 
    oImg aImg[aImg.length 1];
        } else {
            var 
    oImg obj.appendChild(document.createElement('img'));
        }
        
        if ( 
    iSortCol == iIndex && oTable['sortcol'] > ) {
            
    cSort SORT_DESC;
            
    oImg.src SORT_DESC_IMG;
        } else {
            
    oImg.src SORT_ASC_IMG;
        }
        

        
        
        
        var 
    aBody oTable.getElementsByTagName('tbody');
        if ( 
    aBody.length == ) return true;
        var 
    oBody aBody[0];
        
        var 
    oTRs oBody.getElementsByTagName('TR');
        
        
        
        
        var 
    arr = new Array();
        
        for (var 
    0oTRs.lengthx++) {
           
    arr[x] = oTRs[x];
        }                    

        
    oTable['sortcol'] = iIndex cSort;

        
    //alert(oTable['sortcol']);

        
    arr.sort(function (ab) { return oTable_SortoTable['sortcol'] , a); });
        for ( var 
    0arr.length x++ ) {
            
    oBody.removeChild(arr[x]);
            
    oBody.appendChild(arr[x]);
        }    
        
        
        

    }


    function 
    oTable_SortiIndexa) {

        var 
    iDesc 1;
        var 
    Index iIndex;
        if ( 
    Index ) { //Decending
            
    iDesc = -1;
            
    Index Math.abs(Index);
        }
        
        var 
    aCell a.getElementsByTagName('td')[ Index ];
        if ( 
    aCell == null ) return (iDesc);
        var 
    bCell b.getElementsByTagName('td')[ Index ];
        if ( 
    bCell == null ) return (-iDesc);
            
        
    //alert(b.getElementsByTagName('td')[ Index - 1 ]);
            
        
    var aVal parseInt(aCell.innerHTML);
        var 
    bVal parseInt(bCell.innerHTML);
        if ( 
    isNaN(aVal) || isNaN(bVal) ) {
            
    aVal aCell.innerHTML;
            
    bVal bCell.innerHTML;
        }
        
    //alert( aVal + ' - ' + bVal);
        
    if ( aVal bVal ) return (-iDesc);
        if ( 
    aVal bVal ) return (iDesc);

        return 
    0;
    }



    function 
    returnEventTarget(e) {
        var 
    obj null;
        if (
    e.targetobj e.target;
        else if (
    e.srcElementobj e.srcElement;
        return 
    obj;
    }

    function 
    createSortTable(oTable) {
        var 
    aHead oTable.getElementsByTagName('thead');
        if ( 
    aHead.length == ) {
            var 
    oHead document.createElement('thead');
            
    oTable.appendChild(oHead);
            
            var 
    oBody oTable.getElementsByTagName('tbody')[0];        
            var 
    oTR oBody.removeChild(oBody.firstChild);
            
    oHead.appendChild(oTR);
            var 
    aHead oTable.getElementsByTagName('thead');
        } else if  ( 
    aHead.length != ) {
            return 
    false;
        }
        var 
    oHead aHead[0];
        
        var 
    aTD oHead.getElementsByTagName('th');
        if ( 
    aTD.length == ) {
            
    aTD oHead.getElementsByTagName('td');
            if ( 
    aTD.length == ) return false;
        }
        
        
        for ( var 
    aTD.length x++ ) {
            
    attachEvent(aTD[x], 'onclick', function ( event ) { sortByMe(event); } );
        }
    }

     var 
    prev_onload window.onload;
     
    window.onload = function() { if (prev_onload) {prev_onload();} setSortTables(); } 
    Then include that JS file in your HTML like so
    Code:
    <script language="javascript" type="text/javascript" src="./SortTable.js"></script>
    Then on any tables you want to have sortable add the classname of 'sortabletable' such as
    Code:
    <table class='sortabletable'>
    Now you can do it on the fly without hitting the server again.


    *EDIT*
    O yes you must change SORT_DESC_IMG and SORT_ASC_IMG to be images on your server. And yes this code can be cleaned up a bit, but I have no time for that!


  •  

    Posting Permissions

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