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 4 of 4
  1. #1
    New Coder
    Join Date
    Apr 2007
    Posts
    35
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Post MySQL results order by " " how ??

    Am doing a website to sell cars, you can see it here:
    http://www.autosromo.com

    And i would like to be able to order the results based on price (ascending for starters), miles, year, etc.,

    The code its already done am just trying to improve it the results are shown via this function in the index.php file:

    PHP Code:
                <!--SEARCH-->
                <?php
                     
    foreach (Search () as $row) {
                
    ?>
    and in a file here it is the search function:

    PHP Code:
       function Search () {
           global 
    $rows_limit;
           
    $results = array();
           
    // CONDICIONES
           
    $query "SELECT au.*, mk.name AS marca, mo.name AS modelo FROM autos AS au, make AS mk, model AS mo ";
           
    $query .= (!empty($_GET['make']) && $_GET['make']>1) ? 'WHERE make = \''.$_GET['make'].'\'' '';
           
    $query.= (!empty($_GET['model']) && $_GET['model']>0) ?" AND model ="$_GET['model'] : '';
           
    $query.= ($_GET['year'] > && $_GET['make']=="1" && $_GET['model'] == "0" && $_GET['cilinders'] >= "0" && $_GET['transmision'] >= "0" && $_GET['traccion'] >= "0") ? " WHERE year LIKE '".$_GET['year']."'" "";       
           
    $query.= ($_GET['year'] > 0) ? " AND year LIKE '".$_GET['year']."'" "";
           
    // QUERY CILINDROS
           
    $query.= ($_GET['cilinders'] > and $_GET['make']=="1" and $_GET['model'] == "0" and $_GET['year'] == "0" and $_GET['transmision'] >= "0" and $_GET['traccion'] >= "0") ? " WHERE cilinders ="$_GET['cilinders'] : "";
           
    $query.= ($_GET['cilinders'] > 0) ? " AND cilinders ="$_GET['cilinders'] : "";
          
    // QUERY TRANSMISION
          
    $query.= ($_GET['transmision'] > and $_GET['make']=="1" and $_GET['model'] == "0" and $_GET['year'] == "0" and $_GET['cilinders'] == "0" and $_GET['traccion'] >= "0") ? " WHERE transmision ="$_GET['transmision'] : "";
          
    $query.= ($_GET['transmision'] > 0) ? " AND transmision ="$_GET['transmision'] : "";
          
    // QUERY TRACCION
          
    $query.= ($_GET['traccion'] > and $_GET['make']=="1" and $_GET['model'] == "0" and $_GET['year'] == "0" and $_GET['cilinders'] == "0" and $_GET['transmision'] == "0") ? " WHERE traccion ="$_GET['traccion'] : "";
         
    $query.= ($_GET['traccion'] > 0) ? " AND traccion ="$_GET['traccion'] : "";
         
    $query.= (!empty($_GET['make']) && $_GET['make']>1) ? "    AND au.make=mk.id AND au.model=mo.id" "WHERE au.make=mk.id AND au.model=mo.id";

         
    // LIMIT
          
    $per_page $rows_limit;
          
    $start = (empty($_GET['page'])) ? : ($_GET['page']-1)*$per_page;
          
    $limit " LIMIT $start,$per_page";
         
          
    $sqlmysql_query($query.$limit);            
          while (
    $row mysql_fetch_array ($sql)) {
              
    $results[] = $row;
          }
          return 
    $results;
       } 
    What and where should i made the modifications on the code to order the results by price for example... thanks a lot for the help.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Since your code is building the query one piece at a time, you can easily add an ORDER BY piece to the query. Add an element to your query string so you get a $_GET['sortcolumn'] coming into your script, and then add $query .= "ORDER BY {$_GET['sortcolumn']}"; to the query, after the WHERE clause and before the LIMIT clause.

  • #3
    New Coder
    Join Date
    Apr 2007
    Posts
    35
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fumigator View Post
    Since your code is building the query one piece at a time, you can easily add an ORDER BY piece to the query. Add an element to your query string so you get a $_GET['sortcolumn'] coming into your script, and then add $query .= "ORDER BY {$_GET['sortcolumn']}"; to the query, after the WHERE clause and before the LIMIT clause.
    I added the //order part in the script but it still shows an error this change i made to the script (only the //order part i change):

    PHP Code:
          // QUERY TRACCION
          
    $query.= ($_GET['traccion'] > and $_GET['make']=="1" and $_GET['model'] == "0" and $_GET['year'] == "0" and $_GET['cilinders'] == "0" and $_GET['transmision'] == "0") ? " WHERE traccion ="$_GET['traccion'] : "";
         
    $query.= ($_GET['traccion'] > 0) ? " AND traccion ="$_GET['traccion'] : "";
         
    $query.= (!empty($_GET['make']) && $_GET['make']>1) ? "    AND au.make=mk.id AND au.model=mo.id" "WHERE au.make=mk.id AND au.model=mo.id";

    // ORDER 
    $order = (empty($_GET['price'])) ? : ($_GET['price']); 
    $query.= " ORDER BY $order";


         
    // LIMIT
          
    $per_page $rows_limit;
          
    $start = (empty($_GET['page'])) ? : ($_GET['page']-1)*$per_page;
          
    $limit " LIMIT $start,$per_page";
         
          
    $sqlmysql_query($query.$limit);
          while (
    $row mysql_fetch_array ($sql)) {
              
    $results[] = $row;
          }
          return 
    $results;
       } 
    It shows this error:

    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/autosrom/public_html/funcionesp.php on line 69

    where line 69 is:
    while ($row = mysql_fetch_array ($sql)) {

  • #4
    Regular Coder
    Join Date
    Apr 2005
    Location
    Ohio
    Posts
    254
    Thanks
    1
    Thanked 63 Times in 63 Posts
    On line 68 or so,
    PHP Code:
    echo $query.$limit;
    exit; 
    and verify that the query is properly formed.


  •  

    Posting Permissions

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