tlavelle Posted August 15, 2007 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Orio Posted August 15, 2007 Share Posted August 15, 2007 Why you have a dollar sign in your query? Orio. Quote Link to comment Share on other sites More sharing options...
php_tom Posted August 15, 2007 Share Posted 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)"; Quote Link to comment Share on other sites More sharing options...
tlavelle Posted August 15, 2007 Author Share Posted 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> Quote Link to comment Share on other sites More sharing options...
Orio Posted August 15, 2007 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
tlavelle Posted August 15, 2007 Author Share Posted August 15, 2007 Orio, Just curious. C an you explain why I do not need the variable? Quote Link to comment Share on other sites More sharing options...
tlavelle Posted August 15, 2007 Author Share Posted August 15, 2007 Also, the change you suggested gives me this: Error in query: . Unknown column 'useryear' in 'where clause' Quote Link to comment Share on other sites More sharing options...
Orio Posted August 15, 2007 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
tlavelle Posted August 15, 2007 Author Share Posted 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()); Quote Link to comment Share on other sites More sharing options...
Orio Posted August 15, 2007 Share Posted August 15, 2007 Try submitting the form. You still get the error? Orio. Quote Link to comment Share on other sites More sharing options...
tlavelle Posted August 15, 2007 Author Share Posted August 15, 2007 The error goes away after form submissioin Quote Link to comment Share on other sites More sharing options...
Orio Posted August 15, 2007 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
tlavelle Posted August 15, 2007 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Orio Posted August 15, 2007 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
tlavelle Posted August 15, 2007 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
tlavelle Posted August 15, 2007 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Orio Posted August 15, 2007 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
tlavelle Posted August 15, 2007 Author Share Posted 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>"; } } Quote Link to comment 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.