-3

I currently have a query that looks like this:

$sql = "SELECT * FROM Results ORDER BY '$SortBy' '$SortIn'";

$SortBy = $_POST['SortBy']; 
$SortIn = $_POST['SortIn'];

When I run this query, I recieve no errors and it displays all the data in question, however, it does not display it corretly.

SortBy refers to the table fields (e.g. Reference Number) SortIn refers to ascending or descending.

If I run the query without using variables, for example:

$sql = "SELECT * FROM Results ORDER BY RefNo asc"

It works fine and sorts the data as expected. Do you have any idea how I can get it to work using the variables? I have a form where the users can choose to sort the data how they please.

The code to the form:

<form action="#" method="post" id="displayTimeTable">
<table>
<tr>
<td>
Sort table on =
<select name="SortBy">
<option value="RefNo" selected="selected">Reference Number</option>
<option value="Date">Date</option>
<option value="Amount">Amount</option>
</select>
</td>
<td>
Sort in =
<select name="SortIn">
<option value="asc" selected="selected">Ascending order</option>
<option value="desc">Descending order</option>
</select>
</td>
</tr>
</table>
<p>
<input type="submit" name="submit" value="Submit"/>
</p>
</form>

After making the change suggested, I recieved the following error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given on line 37

Below is the code that it is referring to (Line 37 is the while statement):

$result = mysql_query($sql, $connection);
echo "<table>";
echo "<table border=1>";
echo "<TR><TH>Reference Number</TH><TH>Date</TH><TH>Amount</TH></TR>";
while ($row = mysql_fetch_array($result)) { 

    echo "<tr><td>"; 
    echo $row['RefNo']; 
    echo "</td><td>"; 
    echo $row['Date']; 
    echo "</td><td>";
    echo $row['Amount'];
    echo "</td></tr>";
} 

echo "</table>";
Joe
  • 4,691
  • 5
  • 29
  • 48
  • 1
    Do you really think that `ORDER by 'field_name' 'asc'` is a valid syntax? – u_mulder Jan 01 '17 at 19:44
  • If you compare your queries, you'll see that one has quotes and the other doesn't. Also, SQL injection. – shmosel Jan 01 '17 at 19:45
  • Post your full code and the API connection method used for this. After seeing this error below in a comment under an answer *"Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given"* - That suggests mixing different mysql apis, and/or your query failed. What you posted so far still stands as a duplicate that it was closed with. You can ping me back once you've done that and hopefully Mureinik will adjust the answer, or someone else can help you where I might reopen the question. I have been looking at this whole thing from the start and feel I spent enough time here. – Funk Forty Niner Jan 01 '17 at 20:04
  • hi @Fred-ii-, I've added the code that is related to the error I'm recieving. – Joe Jan 01 '17 at 20:15
  • the connection, what is it; mysql_, mysqli_, pdo? `mysql_error()` if you're using mysql_ to connect with, will tell you exactly what's wrong, as will php's error reporting. – Funk Forty Niner Jan 01 '17 at 20:16
  • @Fred-ii- it's `mysql_connect`. Thank you for your help. – Joe Jan 01 '17 at 20:19
  • Did you look at the documentation for `mysql_connect`? Did you see the big red banner at the top of page, telling you not to use it? – Lightness Races in Orbit Jan 01 '17 at 20:21
  • again; add `mysql_error()` http://php.net/manual/en/function.mysql-error.php to your query http://php.net/manual/en/function.mysql-query.php. You should edit your question in the exact way you're running it. Something is failing you here, could be an incorrect column, its type, non-existant etc. I'm really trying to help you out here, yet I feel that Mureinik should also get in on this. I feel like I'm doing all the (leg) work. @thickguru Post your db schema also. – Funk Forty Niner Jan 01 '17 at 20:37
  • btw, if you're not choosing both values from both select dropdowns, that could also trigger/cause that error. – Funk Forty Niner Jan 01 '17 at 20:38
  • ok, I've decided to leave the question; I believe I spent enough time here to help out with this. I sincerely wish you well with this. – Funk Forty Niner Jan 01 '17 at 20:49
  • I've fixed my problem! A very stupid one on my part. I'd put my `$SortBy = $_POST['SortBy'];` and `$SortIn = $_POST['SortIn'];` in the incorrect position and put it after my query. So effectivly I was calling `ORDER BY BLANK BLANK`. Although this question drew a lot of bad feedback (sorry, I guess?), I hope it might helps others in the future. Also, thankyou @Fred-ii- for trying so hard to help me, I appreciate it greatly. I think that is the second time. – Joe Jan 03 '17 at 13:00
  • @john conde did you actually read the entire question and answer that went along side it. It has nothing to do with the issue that is stated in the "duplicate" you have suggested. That duplicate has already been suggested once and removed. – Joe Jan 04 '17 at 13:06
  • @thickguru Their query failed which is what causes this error. This is *exactly* what the canonical question covers. This is an exact dupe and should have been left closed the first time it was marked as a duplicate. – John Conde Jan 04 '17 at 13:08

2 Answers2

1

Single quotes denote string literals. Since your variables hold column names and syntactic elements (such as asc or desc), you need to lose the quotes:

$sql = "SELECT * FROM Results ORDER BY $SortBy $SortIn";
# Here --------------------------------^-----^-^-----^
Mureinik
  • 277,661
  • 50
  • 283
  • 320
0

I've fixed my problem! A very stupid one on my part. All I needed to do was correct the layout, Before I had this:

$sql = "SELECT * FROM Results ORDER BY $SortBy $SortIn";

$SortBy = $_POST['SortBy']; 
$SortIn = $_POST['SortIn'];

$SortBy = $_POST['SortBy']; and $SortIn = $_POST['SortIn']; are in the incorrect position and it needs to go before my query, not after.. So effectively I was calling ORDER BY BLANK BLANK.

So now my code looks like this and works perfectly without issue :)

$SortBy = $_POST['SortBy']; 
$SortIn = $_POST['SortIn'];

$sql = "SELECT * FROM Results ORDER BY $SortBy $SortIn";
Joe
  • 4,691
  • 5
  • 29
  • 48