tlavelle Posted August 21, 2007 Share Posted August 21, 2007 I am getting this error: Error in query: . 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 '=bench_data.year' at line 1 Related to this snippet if(isset($_POST['wercbench2']) || ($_POST['year']<>'2005')) { $useryear=$_POST['year']; $_SESSION['sess_year']=$_POST['year']; $userindustry=$_POST['industry']; $_SESSION['sess_ind']=$_POST['industry']; $metric_query = "SELECT metric_desc.met_desc, bench_data.median, bench_data.best_pract FROM metric_desc, bench_data where metric_desc.met_key=bench_data.met_key and $useryear=bench_data.year"; $metric_result = mysql_query($metric_query) or die ("Error in query: $query. ".mysql_error()); In this file <?php //initialize session session_start(); ?> <html> <head> <basefont face="Arial"> </head> <body> <?php // set server access variables $host = "localhost"; $user = "root"; $pass = ""; $db = "wercbench"; // open connection $connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!"); // select database mysql_select_db($db) or die ("Unable to select database!"); // create form 1 queries $year_query = "SELECT distinct year FROM bench_data"; // execute form 1 queries $year_result = mysql_query($year_query) or die ("Error in query: $query. ".mysql_error()); //$industry_result = mysql_query($industry_query) or die ("Error in query: $query. ".mysql_error()); // see if any rows were returned if (mysql_num_rows($year_result) > 0){ // yes // print them one after another if (!isset($_POST['wercbench1']) and !isset($_POST['year'])) { /*" name=\"wercbench1\">";*/ echo"<form method=\"POST\" action=\"". $_SERVER['PHP_SELF']. "\" name=\"wercbench1\">"; echo"<table style=\"width: 100%;\" border=\"1\" cellpadding=\"2\" cellspacing=\"2\">"; echo"<tbody>"; echo" <tr>"; echo" <td>Select the comparison year and group then click next"; echo" </td>"; echo" <td></td>"; echo" </tr>"; echo" <tr>"; echo" <td>"; echo" <select name=\"year\">"; while($row = mysql_fetch_row($year_result)) { echo"<option value=\"" .$row[0]. "\">".$row[0]."</option>"; } echo" </select>"; echo" </td>"; echo" <td></td>"; echo" </tr><tr>"; echo" <td><input type=\"Submit\" value=\"Next\" name=\"wercbench1_next\"></button></td>"; echo" </tr>"; echo" </form>"; } if(isset($_POST['wercbench1_next']) && !empty($_POST['year'])){ if ($_POST['year']<>'2005'){ $industry_result = mysql_query("SELECT distinct comp_desc FROM comp_desc") or die(mysql_error()); echo" <tr>"; echo"<form method=\"POST\" action=\"". $_SERVER['PHP_SELF']. "\" name=\"wercbench2\">"; echo" <td>"; echo" <select name=\"industry\">"; while($row = mysql_fetch_row($industry_result)) { echo"<option value=\"" .$row[0]. "\">".$row[0]."</option>"; } echo" </select>"; echo"</td>"; echo" <td></td>"; echo" </tr>"; echo" <tr>"; echo" <td><input type=\"Submit\" value=\"Next\" name=\"wercbench2_next\"></button></td>"; echo" <td></td>"; echo" </tr>"; echo"</tbody>"; echo"</table>"; echo"</form>"; } } } else { // no // print status message echo "No rows found!"; } //Calcualtor variables if(isset($_POST['wercbench2']) || ($_POST['year']<>'2005')) { $useryear=$_POST['year']; $_SESSION['sess_year']=$_POST['year']; $userindustry=$_POST['industry']; $_SESSION['sess_ind']=$_POST['industry']; $metric_query = "SELECT metric_desc.met_desc, bench_data.median, bench_data.best_pract FROM metric_desc, bench_data where metric_desc.met_key=bench_data.met_key and $useryear=bench_data.year"; $metric_result = mysql_query($metric_query) or die ("Error in query: $query. ".mysql_error()); if(isset($_POST['wercbench2_next']) and (mysql_num_rows($metric_result)) > 0){ // yes // print them one after another //echo "<form action=\"results.php\" method=\"post\" name\"wercbench2\">"; echo "<form action=\"results.php\" method=\"post\" name\"wercbench3\">"; echo "<table cellpadding=10 border=1>"; echo "<tr>"; echo "<td>Metric Description</td>"; echo "<td>Average</td>"; echo "<td>Best Practice</td>"; echo "<td>Enter your value here</td>"; echo "</tr>"; while($row = mysql_fetch_row($metric_result)) { echo "<tr>"; echo "<td>".$row[0]."</td>"; echo "<td>" . $row[1]."</td>"; echo "<td>".$row[2]."</td>"; echo "<td><input type=\"text\" name=\"myvalue[]\" value=\"\" /></td>"; echo "</tr>"; } echo "<tr><td colspan=\"3\"> </td><td align=\"center\"><input type=\"submit\" name=\"wercbench3\" value=\"Compare\"></td></tr></table>"; } // free result set memory mysql_free_result($year_result); mysql_free_result($industry_result); mysql_free_result($metric_result); //mysql_free_result($metric_result); } // close connection mysql_close($connection); ?> </body> </html> I dont see how the query is even being triggered upon initial load of this page to cause the error. Quote Link to comment https://forums.phpfreaks.com/topic/66001-solved-why-or-how-is-this-sql-error-happening/ Share on other sites More sharing options...
jcanker Posted August 21, 2007 Share Posted August 21, 2007 you have the last elements of your query reversed where you're searching using the value instead of the column name: $useryear=bench_data.year try: bench_data.year = $useryear Quote Link to comment https://forums.phpfreaks.com/topic/66001-solved-why-or-how-is-this-sql-error-happening/#findComment-330025 Share on other sites More sharing options...
akitchin Posted August 21, 2007 Share Posted August 21, 2007 the order shouldn't matter too much. if it was a column issue, it would tell you "unknown column x in where clause." the issue is most likely that the $useryear variable is empty, leading to a where clause that includes simply =bench_data.year. try echoing $useryear to make sure it isn't empty, and go from there. Quote Link to comment https://forums.phpfreaks.com/topic/66001-solved-why-or-how-is-this-sql-error-happening/#findComment-330028 Share on other sites More sharing options...
tlavelle Posted August 21, 2007 Author Share Posted August 21, 2007 But it should always be empty when the page loads. That variable should not be populated until the user posts form 1. I guess I don't understand why that code is being triggered at all. Should the conditional if insulate that code from being triggered until the a form is posted? Quote Link to comment https://forums.phpfreaks.com/topic/66001-solved-why-or-how-is-this-sql-error-happening/#findComment-330032 Share on other sites More sharing options...
lemmin Posted August 21, 2007 Share Posted August 21, 2007 echo out the criteria for that if statement inside it and trace it back to see why it is executing. Quote Link to comment https://forums.phpfreaks.com/topic/66001-solved-why-or-how-is-this-sql-error-happening/#findComment-330036 Share on other sites More sharing options...
akitchin Posted August 21, 2007 Share Posted August 21, 2007 the problem is your conditional, you're right: if(isset($_POST['wercbench2']) || ($_POST['year']<>'2005')) this will execute the statement if wercbench2 is set, OR if year doesn't equal 2005. since $_POST['year'] likely isn't set on its first run anyway, it will definitely not equal 2005. you'll need to add a condition into that second sub-condition that $_POST['year'] is set as well. Quote Link to comment https://forums.phpfreaks.com/topic/66001-solved-why-or-how-is-this-sql-error-happening/#findComment-330055 Share on other sites More sharing options...
tlavelle Posted August 21, 2007 Author Share Posted August 21, 2007 How do you guys do this without smashing your computer and burning all code in a huge effigy? I am ready to give up on this forever!! Curse you PHP and your endless taunting of me!! Quote Link to comment https://forums.phpfreaks.com/topic/66001-solved-why-or-how-is-this-sql-error-happening/#findComment-330065 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.