Generating table based on form input order by bug...

[elm]

What I'm trying to do here is generate a table based on a form in which the user selects two options. The first option tells the script which database entries to put in the table, the second option tells it how to arrange them. The first works perfectly, the second not at all - it doesn't produce an error, it just doesn't do anything. I've found a number of tutorials that seem to suggest that the problem is somewhere in my punctuation around

ORDER BY '$POST[sort]'

but I've been unable to find a solution that actually works. Any help would be much appreciated, my code is below.

 

Thank you!

 

mysql_select_db($database, $con);

$result = mysql_query("SELECT * FROM main WHERE state='$_POST[state]' ORDER BY '$POST[sort]'");
if(mysql_num_rows($result)==0){
echo "<p align='left'>View Pantries by State</p><div id='stateform'>
	<form action='../admin/viewstate.php' method='post'>	<select name='state' />
		<option value='AL'>Alabama</option>
		<option value='AK'>Alaska</option>
		<option value='AZ'>Arizona</option>
	</select>
	<select name='sort' />
		<option value='name'>Name</option>
		<option value='id'>Id</option>
		<option value='city'>City</option>
		<option value='zip'>Zip</option>
		<option value='timestamp'>Timestamp</option>
	</select>
		<input type='submit' value='Go'></input></form>
	</div>";
}
else{
echo "<p align='left'>View Pantries by State</p>	<div id='stateform'>
		<form action='../admin/viewstate.php' method='post'>	<select name='state' />
			<option value='AL'>Alabama</option>
			<option value='AK'>Alaska</option>
			<option value='AZ'>Arizona</option>
			</select>
			<select name='sort' />
				<option value='name'>Name</option>
				<option value='id'>Id</option>
				<option value='city'>City</option>
				<option value='zip'>Zip</option>
				<option value='timestamp'>Timestamp</option>
			</select>
			<input type='submit' value='Go'></input></form>
		</div>";	
echo "<div id='fptext'><span class='h1'>Food Pantries for " . $_POST['state'] . "</span><br><br></div>"; }
echo "<table border='1' align='center' cellpadding='3' width='900px'>
<tr>
<th>ID</th>
<th>Name</th>
<th>Type</th>
<th>Address</th>
<th>State</th>
<th>Phone</th>
<th>E-mail</th>
<th>Website</th>
<th>Hours</th>
<th>Requirements</th>
<th>Additional Information</th>
<th>Lat</th>
<th>Lng</th>
<th>Update</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . $row['name'] . "</td>";
  echo "<td>" . $row['type'] . "</td>";
  echo "<td>" . $row['address'] . "</td>";
  echo "<td>" . $row['state'] . "</td>";
  echo "<td>" . $row['phone'] . "</td>";
  echo "<td>";
  echo "<a href=mailto:".$row['email'].">".$row['email']."</a>";
  echo "</td>";
  echo "<td>";
  echo "<a href=".$row['website']."\>".$row['website']."</a>";
  echo "</td>";
  echo "<td>" . $row['hours'] . "</td>";
  echo "<td>" . $row['requirements'] . "</td>";
  echo "<td>" . $row['additional'] . "</td>";
  echo "<td>" . $row['lat'] . "</td>";
  echo "<td>" . $row['lng'] . "</td>";
  echo "<td><a href='../public/updatepage.php?id=".$row['id']."'>Update Pantry</a></td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>

 

[Pikachu2000]

$_POST['sort'] != $POST['sort']

[MadTechie]

Before elm asks where to put that code, let me just explain what Pikachu2000 means by

$_POST['sort'] != $POST['sort']

 

elm you are using $POST['sort'] which is NOT the same as $_POST['sort']; and from your code it looks like you mean $_POST['sort']

[elm]

Before elm asks where to put that code, let me just explain what Pikachu2000 means by

$_POST['sort'] != $POST['sort']

 

elm you are using $POST['sort'] which is NOT the same as $_POST['sort']; and from your code it looks like you mean $_POST['sort']

 

Thanks for the correction! Unfortunately, that might have been a problem, but it doesn't seem to have been the problem.

 

I changed

 

$result = mysql_query("SELECT * FROM main WHERE state='$_POST[state]' ORDER BY '$POST[sort]'");

 

to

 

$result = mysql_query("SELECT * FROM main WHERE state='$_POST[state]' ORDER BY '$_POST[sort]'");

 

Still no errors, but the sorting function still doesn't work. Any other ideas?

 

Thanks!

[Pikachu2000]

Have you echoed the query string to make sure the variables contain the values you'd expect them to contain?

[elm]

I hate to admit it, but I'm not sure how to do that in this context, and a quick look around the internet hasn't helped me any in that regard. However, I know from page behavior that the first variable does contain the values I'd expect, and the page responds properly if I put the values of the options from the sort variable into the query in the place of the '$_POST[sort]'.

 

If you can point me to a quick and dirty way to echo the query string, I'd be happy to pass on the results.

 

Have you echoed the query string to make sure the variables contain the values you'd expect them to contain?

[elm]

Having inserted the code below into the page, I can see that the form does produce the correct results. So, the variable is making it as far as $_POST['sort'] - but somehow when that appears in the query, the results aren't having the appropriate impact on its behavior...

 

  echo "<tr><td colspan='7'>". $_POST['state'] . "</td>";
  echo "<td colspan='7'>" . $_POST['sort'] . "<td></tr>";

 

[Pikachu2000]

I just had a thought. Change the single quotes around $_POST['sort'] in the query string to `backticks` since that's a field name, not a string value.

[litebearer]

Grrrr Pik beat me again

[Pikachu2000]

Sorry. Me go sleepy now . . .

[elm]

Works brilliantly - thank you! Now I just need to fix the error I now get when $_POST doesn't have a value...

 

I just had a thought. Change the single quotes around $_POST['sort'] in the query string to `backticks` since that's a field name, not a string value.

[Pikachu2000]

That's easy enough. It's also a good time to set a default value to use in the event that someone arrives at the page without submitting the form. Just make sure you put this after your mysql_connect(), since mysql_real_escape_string() needs a db connection to work.

 

$_POST['state'] = isset($_POST['state']) ? mysql_real_escape_string($_POST['state']) : 'enter your default value here';
$_POST['state'] = isset($_POST['sort']) ? mysql_real_escape_string($_POST['sort']) : 'enter your default value here';