Order By variable.

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
jack-lindsayAsked:
Who is Participating?
 
Beverley PortlockConnect With a Mentor Commented:
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
 
Beverley PortlockCommented:
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
 
tg_wilkCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jack-lindsayAuthor Commented:
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
 
tg_wilkCommented:
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
 
Bobaran98Commented:
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
 
Bobaran98Commented:
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
 
jack-lindsayAuthor Commented:
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
 
tg_wilkCommented:
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
 
jack-lindsayAuthor Commented:
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
 
Beverley PortlockCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.