[SOLVED] Why or how is this sql error happening?

tlavelle · 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.

jcanker · 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

akitchin · 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.

tlavelle · 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?

lemmin · August 21, 2007

echo out the criteria for that if statement inside it and trace it back to see why it is executing.

akitchin · 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.

tlavelle · 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!!

Sign In

[SOLVED] Why or how is this sql error happening?

Recommended Posts

tlavelle

Link to comment

Share on other sites

jcanker

Link to comment

Share on other sites

akitchin

Link to comment

Share on other sites

tlavelle

Link to comment

Share on other sites

lemmin

Link to comment

Share on other sites

akitchin

Link to comment

Share on other sites

tlavelle

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information