Error in query: . You have an error in your SQL syntax; check the manual that co

tlavelle · August 15, 2007

Any thoughts on why this:

$metric_query = "SELECT metric_desc.met_desc, userdata.median, userdata.best_pract FROM metric_desc, userdata where metric_desc.met_key=userdata.met_key and $useryear=userdata.year";

Causes this

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 '=userdata.year' at line 1

Orio · August 15, 2007

Why you have a dollar sign in your query?

Orio.

php_tom · August 15, 2007

First of all you have

and $useryear=userdata.year

If you havn't defined $useryear, this will be rendered by the PHP engine as

]and =userdata.year

,

obviously the wrong syntax. Try getting PHP to echo the query to the screen and verify that it says what you are expecting.

Also, im not sure if you need parens when doing multistatment conditions, e.g.,

FROM metric_desc, userdata WHERE (metric_desc.met_key=userdata.met_key AND $useryear=userdata.year)";

tlavelle · August 15, 2007

I am not sure how to flow this out. Here is the whole file. I have tried defining $userdata in various places and in this version I still get the sql error

<?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 query
$year_query = "SELECT distinct year FROM userdata";
$industry_query = "SELECT distinct comp_desc FROM comp_desc";




// execute query
$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 or mysql_num_rows($industry_result)){
     // yes
     // print them one after another
if (!isset($_POST['wercbench1'])) {
 /*echo"<form method=\"POST\" action=\"<?php$_SERVER['PHP_SELF']; ?>\" 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>";


     echo" <tr>";
     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=\"wercbench1_next\"></button></td>";
     echo"   <td></td>";
     echo" </tr>";

    echo"</tbody>";
  echo"</table>";
echo"</form>";

}
}

else {
    // no
    // print status message
    echo "No rows found!";


}


echo $_Session['sess_year'];
//Calcualtor variables
$useryear=$_POST['year'];
$_Session['sess_year']=$_POST['year'];
$userindustry=$_POST['industry'];
$_Session['sess_ind']=$_POST['industry'];
$metric_query = "SELECT metric_desc.met_desc, userdata.median, userdata.best_pract FROM metric_desc, userdata where metric_desc.met_key=userdata.met_key and $useryear=userdata.year";
$metric_result = mysql_query($metric_query) or die ("Error in query: $query. ".mysql_error());
if(!isset($_POST['wercbench2']) and (mysql_num_rows($metric_result)) > 0){

    // yes
    // print them one after another
//echo"<form method=\"POST\" action=\"". $_SERVER['PHP_SELF']. "\" name=\"wercbench1\">";
echo "<form action=\"". $_SERVER['PHP_SELF']. "\" method=\"post\" name\"wercbench2\">";
    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=\"submit\" value=\"Compare\"></td></tr></table>";
}
// free result set memory
mysql_free_result($year_result);
mysql_free_result($industry_result);
//mysql_free_result($metric_result);

// close connection
mysql_close($connection);

?>

</body>
</html>

Orio · August 15, 2007

Replace:

$metric_query = "SELECT metric_desc.met_desc, userdata.median, userdata.best_pract FROM metric_desc, userdata where metric_desc.met_key=userdata.met_key and $useryear=userdata.year";

With:

$metric_query = "SELECT metric_desc.met_desc, userdata.median, userdata.best_pract FROM metric_desc, userdata where metric_desc.met_key=userdata.met_key and useryear=userdata.year";

Orio.

tlavelle · August 15, 2007

Orio,

Just curious. C an you explain why I do not need the variable?

tlavelle · August 15, 2007

Also,

the change you suggested gives me this:

Error in query: . Unknown column 'useryear' in 'where clause'

Orio · August 15, 2007

I thought you put it there by mistake =/

So try it this way:

$metric_query = "SELECT metric_desc.met_desc, userdata.median, userdata.best_pract FROM metric_desc, userdata where metric_desc.met_key=userdata.met_key and ".$useryear."=userdata.year";

Orio.

tlavelle · August 15, 2007

Damn

I still get this:

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 '=userdata.year' at line 1

	$useryear=$_POST['year'];
$_Session['sess_year']=$_POST['year'];
$userindustry=$_POST['industry'];
$_Session['sess_ind']=$_POST['industry'];
$metric_query = "SELECT metric_desc.met_desc, userdata.median, userdata.best_pract FROM metric_desc, userdata where metric_desc.met_key=userdata.met_key and ".$useryear."=userdata.year";
$metric_result = mysql_query($metric_query) or die ("Error in query: $query. ".mysql_error());

Orio · August 15, 2007

Try submitting the form. You still get the error?

Orio.

tlavelle · August 15, 2007

The error goes away after form submissioin

Orio · August 15, 2007

Ok so I understand the problem- The page both prints and processes the form, even if you dont submit. Try it this way:

<?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 query
$year_query = "SELECT distinct year FROM userdata";
$industry_query = "SELECT distinct comp_desc FROM comp_desc";




// execute query
$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 or mysql_num_rows($industry_result)){
     // yes
     // print them one after another
if (!isset($_POST['wercbench1'])) {
 \" 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>";


     echo" <tr>";
     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=\"wercbench1_next\"></button></td>";
     echo"   <td></td>";
     echo" </tr>";

    echo"</tbody>";
  echo"</table>";
echo"</form>";

}
}

else {
    // no
    // print status message
    echo "No rows found!";


}


echo $_Session['sess_year'];
//Calcualtor variables
if(isset($_POST['wercbench1_next']))
{
$useryear=$_POST['year'];
$_Session['sess_year']=$_POST['year'];
$userindustry=$_POST['industry'];
$_Session['sess_ind']=$_POST['industry'];
$metric_query = "SELECT metric_desc.met_desc, userdata.median, userdata.best_pract FROM metric_desc, userdata where metric_desc.met_key=userdata.met_key and $useryear=userdata.year";
$metric_result = mysql_query($metric_query) or die ("Error in query: $query. ".mysql_error());
if(!isset($_POST['wercbench2']) and (mysql_num_rows($metric_result)) > 0){

    // yes
    // print them one after another
//echo"<form method=\"POST\" action=\"". $_SERVER['PHP_SELF']. "\" name=\"wercbench1\">";
echo "<form action=\"". $_SERVER['PHP_SELF']. "\" method=\"post\" name\"wercbench2\">";
    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=\"submit\" value=\"Compare\"></td></tr></table>";
}
// free result set memory
mysql_free_result($year_result);
mysql_free_result($industry_result);
//mysql_free_result($metric_result);
}
// close connection
mysql_close($connection);

?>

</body>
</html>

Orio.

tlavelle · August 15, 2007

ORio,

Thanks. I actually posted this question a few days ago and no one answered it and i am hoping that maybe you can answer this for me. I actually had this originally in 3 different pages. The first form in page one, second form in page 2 and ultimately I was going to put the result set in page 3. A couple of general questions.

1. What is the best practice for something like this? Should the code be all in one file? The 3 files I mentioned above? In the spirit of OOP should everything be componentized and separated out?

2. Are session variables the best way to handle the data? Cookies? Database? Whatever the best strategy is, how does it get implemented?

Like I said, I am pretty new to this. I do have a tech background, project management, bus analyissi and some programming, but this is new to me

Orio · August 15, 2007

Well I don't know what the whole thing is, so it's hard to answer question 2. I have no idea what you are doing.

About question 1, its all about your personal style and what you like. I personally think that all in one file is the best for me, but you have to know how to program everything without being messy. I mean, your code is kinda messy, but that improves along the way.

Orio.

tlavelle · August 15, 2007

A couple more questions:

Can you tell me what is messy? How would you recommend cleaning?

also, in your code you changed the name of the form in the if statement. Can you explain why?

I tend to ask an annoying amount of questions..it is how I learn. IF you are done with this topic, let me know

tlavelle · August 15, 2007

This:

if (!isset($_POST['wercbench1'])) {

" name=\"wercbench1\">";*/

Is generating this error

Parse error: syntax error, unexpected '*' in C:\xampp\htdocs\bench2\index3.php on line 40

Orio · August 15, 2007

Messy- mainly not spaced well. In overall- the coding level is not bad at all

Spacing correctly, in my opinion, is mainly adding another level of tabbing in each level of braces. There are other stuff, but thats more of my coding style.

I didn't change the form name, I checked if the submit was pressed (the wercbench1_next is the button's name).

About the error now- I don't see anything like it in the code. Post the current code again.

Orio.

tlavelle · August 15, 2007

The last code you posted contained this:

if (mysql_num_rows($year_result) > 0 or mysql_num_rows($industry_result)){
     // yes
     // print them one after another
if (!isset($_POST['wercbench1'])) {
 " 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>";


     echo" <tr>";
     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=\"wercbench1_next\"></button></td>";
     echo"   <td></td>";
     echo" </tr>";

    echo"</tbody>";
  echo"</table>";
echo"</form>";

}
}

I am assuming you were debuggins so i deleted this line

" name=\"wercbench1\">";*/

leaving this

if (mysql_num_rows($year_result) > 0 or mysql_num_rows($industry_result)){
     // yes
     // print them one after another
if (!isset($_POST['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>";


     echo" <tr>";
     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=\"wercbench1_next\"></button></td>";
     echo"   <td></td>";
     echo" </tr>";

    echo"</tbody>";
  echo"</table>";
echo"</form>";

}
}

Sign In

Error in query: . You have an error in your SQL syntax; check the manual that co

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information