Solved

Order By variable.

Posted on 2009-05-14
11
1,234 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Consider the following scenario: You are working on a website and make something great - something that lets the server work with information submitted by your users. This could be anything, from a simple guestbook to a e-Money solution. But what…
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…
The viewer will learn how to count occurrences of each item in an array.

760 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

24 Experts available now in Live!

Get 1:1 Help Now