Solved

Order By variable.

Posted on 2009-05-14
11
1,235 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
  • 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

948 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now