Problems with ORDER BY

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
derchris
Forum Commoner
Posts: 44
Joined: Sat Jun 10, 2006 6:14 pm

Problems with ORDER BY

Post by derchris »

Yes, I have some issues with ORDER BY.

First my code:

Code: Select all

if(isset($_POST['viewlist'])) {

$orderby = $_POST['orderby'];
$ascdesc = $_POST['ascdesc'];


  	@mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASS) OR die(mysql_error());
  	mysql_select_db(MYSQL_DATABASE) OR die(mysql_error());
  	$sql = "SELECT * FROM `sfx` ORDER BY '$orderby' '$ascdesc'";
  	$result = mysql_query($sql) OR die(mysql_error());
  	$numrows = mysql_num_rows($result);
  	echo "Gamercards in Database:<br /><br />\n";
  	echo "<table border='1' width='500' cellspacing='0' cellpadding='2'>\n";
	echo "<tr><td>id</td><td>Gamertag</td><td>Squad</td><td>Views</td></tr>\n";
  	if(mysql_num_rows($result)) {
  	echo "<form method=\"post\" action=\"gc_admin.php\">";

  		while($row = mysql_fetch_assoc($result)) {
  			$id = $row['id'];
			$gt = $row['gt'];
			$squad = $row['squad'];
			$views = $row['views'];
			$row_color = ($row_count % 2) ? $color1 : $color2;
			echo "<tr>
			<td bgcolor='$row_color'>
			$id</td>
			<td bgcolor='$row_color'>
			$gt</td>
			<td bgcolor='$row_color'>
			$squad</td>
			<td bgcolor='$row_color'>
			$views</td>
			</tr>";
			$row_count++;
		}
 	 } else {
  		echo "Nothing in DB<br />\n";
  	}

	echo "<tr><td>Sort :</td>
	<td><select name='orderby' size='1'>
	<option>id</option>
	<option>gt</option>
	<option>squad</option>
	<option>views</option>
    </select></td>
    <td><select name='ascdesc' size='1'>
	<option>asc</option>
	<option>desc</option>
	</select></td>
	<td><input type='submit' name='viewlist' value='GO'></td>
  	</tr></form></table><br />";
    echo "Gamertags eingetragen: ", $numrows;
    echo "<br />\n";
    echo $orderby;
    echo "<br />\n";
    echo $ascdesc;
}
I have a form, which includes two hidden fields, one orderby - default value gt, and ascdesc - default value asc.
When I do something from the form, which would use this script, everything is ok.
Now as you see, I made a little table for the presentation of the data. Then come another form, where you can select different kind of
sorting options. If I select one of the combinations, for ex. orderby = gt, ascdesc = desc,
he is reloading the page, but not with the right sorting options.
But he has recognized them, which I can see at the end of the page because I added two echos for the values.
What I'm doing wrong ?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Perhaps try removing the single quotes from around the $orderby (and replace them with backticks) and $ascdesc variables in the SQL, so:

Code: Select all

$sql = "SELECT * FROM `sfx` ORDER BY `$orderby` $ascdesc";
instead of

Code: Select all

$sql = "SELECT * FROM `sfx` ORDER BY '$orderby' '$ascdesc'";
Mac
derchris
Forum Commoner
Posts: 44
Joined: Sat Jun 10, 2006 6:14 pm

Post by derchris »

Will try that.

Thanks.
derchris
Forum Commoner
Posts: 44
Joined: Sat Jun 10, 2006 6:14 pm

Post by derchris »

Perfect, now he is sorting it right.

Thanks :D
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Cool :)

Mac
Post Reply