Help with code that I inherited

[pjungers]

Hey, this is my first time using this forum, so I apologize if I miss something and I'd like to thank you in advance for your help.

 

I inherited some code from a different php programmer and it's not quite working right.

 

The basics:  We have a form for users to fill out where they select a Township from a drop down menu, then based on the township they choose there is a 2nd drop down populated with Range values.

 

I can get the Township drop down to populate from the mysql database, but once you select the Township, the 2nd drop down does not populate as expected.  This worked on the old web server (which I regrettably no longer have access to), but it's not working on the new web server.  I hope that the problem is just a version issue where some syntax changed, but I'm not sure.

 

Here's the function:

 

<?php
   echo "<form method='post' name='tsform' id='tsform' action='".$_SERVER['PHP_SELF']."'><input type=hidden name='page' id='page' value='viewfntown'>";
   echo "<table><tr VALIGN=baseline><td></td>";
   echo "<td>Township: <select name='ts' id='ts'><option value=''></option>";
   $sql="SELECT distinct(township) from `linksurv_field_notes` WHERE `township`!='' order by `township`;";
   $result=mysql_query($sql) or die("ts sel:".mysql_error());
   $NumResults=mysql_num_rows($result);

   for ($q=1;$q<=$NumResults;$q++) {
    	$row=mysql_fetch_row($result);
    	$township=$row[0];
        echo "<option value='".$township."'>".$township."</option>";
   }
   echo "</select></td>";
   echo "<td>Range: <select name='rg' id='rg'><option value=''></option>";
   $sql="SELECT distinct(range) from `linksurv_field_notes` WHERE `range`!='' order by `range`;";
   $result=mysql_query($sql) or die("rg sel:".mysql_error());
   $NumResults=mysql_num_rows($result);

   for ($q=1;$q<=$NumResults;$q++) {
    	$row=mysql_fetch_row($result);
    	$range=$row[0];
        echo "<option value='".$range."'>".$range."</option>";
   }
   echo "</select></td>";
   echo "<td><button type=button onClick=\"return submitCorner('NA','NA');\">Show All Corners</button></td>";
   echo "</tr>";
   echo "</table>";
   ?>

 

Also, here is the error I see in Firebug:

 

rg sel: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 'range) from `linksurv_field_notes` WHERE `range`!='' order by `range`' at line 1

 

I've been stumped on this for a few weeks and any help you can give is appreciated.  If I left some details out, just ask.  Thanks.

[AyKay47]

i notice that you have an unwanted extra semi-colon inside of your sql statement.. remove it.. should be

 

 $sql="SELECT distinct(township) from `linksurv_field_notes` WHERE `township`!='' ORDER BY `township` DESC";

[pjungers]

Thanks for your help.  The 2 SQL statements now read:

 

$sql="SELECT distinct(township) from `linksurv_field_notes` WHERE `township`!='' ORDER BY `township` DESC";

 

and

 

$sql="SELECT distinct(range) from `linksurv_field_notes` WHERE `range`!='' ORDER BY `range` DESC";

 

I'm still seeing the same result though on the website. 

[AyKay47]

remove the parenthesis around your distinct fields...

 

$sql="SELECT distinct township  from linksurv_field_notes WHERE township !='' ORDER BY township DESC";

[pjungers]

Just tried that and same result:

 

$sql="SELECT distinct township from `linksurv_field_notes` WHERE `township`!='' ORDER BY `township` DESC";
$sql="SELECT distinct range from `linksurv_field_notes` WHERE `range`!='' ORDER BY `range` DESC";

 

I'm also seeing the same error as mentioned in my top post.

 

The funny thing is that the first query works no problem.  It's the second query that receives the error.  As i understand it, once I select a township, the page is then supposed to run the 2nd query and refresh that drop down box.  I don't see this behavior at all.  I'd be happy to include the code that handles that if I could narrow it down.  It appears that the "onClick=return submitCorner" portion is the start of this code though.

[jcbones]

If you use 'range' as a column name you must surround it in backticks, as it is a reserved word in mysql.  You have done that in the second query's WHERE clause, but NOT in the select clause.

 

$sql="SELECT distinct `range` from `linksurv_field_notes` WHERE `range`!='' ORDER BY `range` DESC";

 

When searching for MySQL errors, look at the exact point that the output starts.  You will notice that it started with the word range in the second query.

rg sel: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 'range) from `linksurv_field_notes` WHERE `range`!='' order by `range`' at line 1

[pjungers]

That did the trick!

 

A million thank yous to both of jcbones and AyKay47!  I really appreciate the help.

[jcbones]

Glad to help, please click the solved button over -> there!

[AyKay47]

If you use 'range' as a column name you must surround it in backticks, as it is a reserved word in mysql.  You have done that in the second query's WHERE clause, but NOT in the select clause.

 

$sql="SELECT distinct `range` from `linksurv_field_notes` WHERE `range`!='' ORDER BY `range` DESC";

 

When searching for MySQL errors, look at the exact point that the output starts.  You will notice that it started with the word range in the second query.

rg sel: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 'range) from `linksurv_field_notes` WHERE `range`!='' order by `range`' at line 1

ah yes, "range" is a mysql reserved word and is a field name that must be encased in backticks as jcbones stated.. just for more information.. here is a list of reserved words to watch out for..

 

http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html