Solved

Order By variable.

Posted on 2009-05-14
11
1,240 Views
Last Modified: 2013-12-13
Hi guy's

im in dire need of some expert assistance.  i've got a table populated with the data from a mysql database.  i want to be be able to sort the data either by ID, name, or whatever else i chose.
here's the code i am using

<form name='dd' action='repdetailstable.php' method='POST'>
        <select name="sort1" ID="sort1" onChange='this.form.submit()'>
            <option selected value='statVal_id'>Sort By</option>
            <option value='statVal_id'>id</option>
            <option value='repName'>Name</option>
            <option value='repNumber'>Rep #</option></select>
      </form>

$selectQuery = "SELECT * from repDetails
                              WHERE 1
                              ORDER BY $sort1 ";

but whenever i initially run the script i get an error:
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3"

but as the rest of the page appears, i can use my drop down and when i choose an option, the data appears as it should.
can anyone help me? i would like it so that when the page loads, it appears in order of ID (statval_id)

regards

jack lindsay
0
Comment
Question by:jack-lindsay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 24384852
Chances are that $sort1 is blank or contains something which is not a valid name. Change it from

$selectQuery = "SELECT * from repDetails
                              WHERE 1
                              ORDER BY $sort1 ";

to

$selectQuery = "SELECT * from repDetails
                              WHERE 1
                              ORDER BY $sort1 ";
echo "<h1>$selectQuery</h1>";

what does that produce?
0
 
LVL 7

Expert Comment

by:tg_wilk
ID: 24384857
If register_globals is turned off, you need to use $_POST array to access form fields.

It is VERY unwise to use user input without any verification. At least use mysql_real_escape_string. Better would be to verify if your sort field is one of possible ones. Remember that although your field is a select, user can substitute it for anything very easily.

$selectQuery = "SELECT * from repDetails
                              WHERE 1
                              ORDER BY ".mysql_real_escape_string($_POST['sort1']);

Open in new window

0
 

Author Comment

by:jack-lindsay
ID: 24385317
bportlock... when i echo selectQuery, i get:

SELECT * from repDetails ORDER BY $sort1
Unknown column '$sort1' in 'order clause'

tg_wilk...
how would i use the $_POST array ?  this is only for our internal it department, so im not too bothered at the moment about varification, might consider it in the future as development, but thanks for your concern and i didn't know it could cause an issue :s
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:tg_wilk
ID: 24387369
Still keeping some standards is worth thinking through.

if you want to use a variable from form, you need to use $_POST['name_of_the_field']. So in your example it would be
$selectQuery = "SELECT * from repDetails
                              WHERE 1
                              ORDER BY ".$_POST['sort1'];

Open in new window

0
 
LVL 8

Expert Comment

by:Bobaran98
ID: 24390824
Are you sure you're using double-quotes when you set $selectQuery?

Did you copy-paste your code from your project, or did you type it in by hand?  The fact that you're getting a $sort1 in your exported string suggests that your variable name isn't being replaced by a value.  That's what's supposed to happen when you embed a variable in a string that is in single-quotes rather than double quotes.  For example, using single quotes:

$sort1 = "repNumber";
$selectQuery = 'SELECT * from repDetails WHERE 1 ORDER BY $sort1 ';
echo $selectQuery;
will give you:

SELECT * from repDetails WHERE 1 ORDER BY $sort1
Whereas using double-quotes:

$sort1 = "repNumber";
$selectQuery = "SELECT * from repDetails WHERE 1 ORDER BY $sort1 ";
echo $selectQuery;
will give you:

SELECT * from repDetails WHERE 1 ORDER BY repNumber
Since the first example is apparently what you're getting, I suspect you've actually got single-quotes.  Change to double-quotes and try again.
0
 
LVL 8

Expert Comment

by:Bobaran98
ID: 24390853
And tg_wilk is completely right about using mysql_real_escape_string($_POST['sort1']) instead of just $sort1.  It's such an easy measure to take, and the risk you run otherwise-- however remote it may be-- can nevertheless be devastating.
0
 

Author Comment

by:jack-lindsay
ID: 24393409
ok im using the $_POST array now, but still when the page initially loads, im getting an error:
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3"

i have  a feeling its because the javascript event property im using is onChange, but i cant think how else i do it.  the selected value has the id of a column i'd like to sort by.

<form name='dd' action='repdetailstable.php' method='POST'>
        <select name="sort1" ID="sort1" onchange='this.form.submit()'>
            <option selected value='statVal_id'>Sort By</option>
            <option value='statVal_id'>id</option>
            <option value='repName'>Name</option>
            <option value='repNumber'>Rep #</option></select>
      </form>
0
 
LVL 7

Expert Comment

by:tg_wilk
ID: 24393460
Initially, if the form hasn't been submitted yet, you need to provide some default value, because $_POST is empty.
Would something like this work for you?
//if $_POST['sort1'] is empty - provide default value, else use $_POST data
$order = empty($_POST['sort1'])?'statVal_id':$_POST['sort1'];
$selectQuery = "SELECT * from repDetails
                              WHERE 1
                              ORDER BY ".$order;

Open in new window

0
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 125 total points
ID: 24393469
Assuming you have applied the changes above, I suspect you might be getting a "default value" of "Sort By" as that is your first option. I would change the query to define a default sort order and then check for only valid entries like so

(untested code)

$sortOrder = mysql_real_escape_string( $_POST['sort1'] );

switch( $sortOrder ) {
     case "repName":
     case "repNumber":
     case "statVal_id": $clause = " ORDER BY $sortOrder ";
               break;

     default: $clause = "";
               break;
}

$selectQuery = "SELECT * from repDetails WHERE 1 $clause";


This way you can be sure that you have a valid column name or you can change the default clause to contain a default column name, but the query should always work.
0
 

Author Closing Comment

by:jack-lindsay
ID: 31597508
thank you very very much!! works a treat, now i just have to go through that snippit of code to understand what it does, thanks again
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 24393610
I should have commented it for you. So here it is again


// Get the column name to sort on
//
$sortOrder = mysql_real_escape_string( $_POST['sort1'] );


// Check the column name is in my allowed list of column names. If it is then I can use it
//
switch( $sortOrder ) {

     // Allowed names
     //
     case "repName":
     case "repNumber":
     case "statVal_id": $clause = " ORDER BY $sortOrder ";
               break;

      // Anything else goes here. It is either an unknown column or a forbidden column
     //
     default: $clause = "";
               break;
}


// Run the query
//
$selectQuery = "SELECT * from repDetails WHERE 1 $clause";
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question