passing select list variable from page1 to insert statement as table name on page2

I have a page with a select list that will contain as many as 20 options, allowing only one choice, not multiple. I have a second page that is loaded on submit that wants that select list variable for the table name in an insert statement. On the second page is a form for entering data into the table, chosen from the select list on the first page. I can echo the variable on the second page, so I know it is being sent from page1 to page2 appropriately. I see in the url that it is being send appropriately, and upon filling in the form, again the url shows the data that is to be sent to the database. All appears to be okay, except I get a syntax error message. Below is copied messages I have been exchanging with another user on this subject, it contains a complete history, and the code for the two pages, at the bottom. I hope someone can help solve this perplexing problem. -- Chip W www.wiegand.org chip@wiegand.org Begin previous posts -- ========================================================================= Well, I think we're getting closer, in that the correct data is being sent, as seen here - http://192.168.1.53/test2.php?listbox=leglifts&exerciselist= Then when I enter some data and press submit the correct data is sent - http://192.168.1.53/test2.php?exercise=80&reps=12&comments=test&submit=Send+Data But at the same time I get this error - You have an error in your SQL syntax near '(date,exercise,reps,comments) values ('08-05','80','12','test')' at line 1 My entire sql statement looks like this - <? if(isset($submit)): $db = mysql_connect("localhost","----","----"); if(!$db) error_message(sql_error()); mysql_select_db("workout",$db) or die (mysql_error()); $date = date("m-d"); $sql = "insert into $listbox (date,exercise,reps,comments) values ('$date','$exercise','$reps','$comments')"; mysql_query($sql) or die (mysql_error()); endif; echo $listbox; ?> As for the variable $listbox in the insert statement, I have tried to quote it - ".$listbox." - and also like this - "$listbox" - both of which result in the same error as above, and then this version - '$listbox' - causing a T-Variable error which crashes the page. I don't see any syntax error in there. I will be posting this on the lists also. -- Chip W www.wiegand.org chip@wiegand.org On Mon, 2002-08-05 at 20:18, Rich Hutchins wrote: > Chip, > > I'm going to take one more shot here, because, frankly, I'm stumped too. > > If you look at your URL, you see: > http://192.168.1.53/workout-absflexor.php?exerciselist=%24listbox&exercise=1 > 20 > &reps=12&date=1-1&comments=test&submit=Send+Data > > The part that reads ?exerciselist=%24listbox is what worries me. > exerciselist should have the value from the listbox in it and exerciselist > is what you should be referencing in your sql. So this part of the URL > should look something like: > > ...?exerciselist=crunches&... > > Then you'd use a reference to $exerciselist in your SQL, not $listbox. > > Forget what the book says for a moment. They're prone to errors just like > you and me. > > Variables are passed in name/value pairs. Everything to the left of the = is > a name and everything to the right is a value. Pretty simple. Your URL does > not have a valid name/value pair for the variable $exerciselist. > $exerciselist is valid, but %24listbox is not. Fix that and you're home > free. > > My hunch (can't you just hear Velma saying this to Fred?) is that there's > something wrong with the way you are attempting to assign the value of > listbox to exerciselist on your first page. I have never worked with passing > <select> values, so you're one up on me there. But if you look closely, > there has to be something funky going on there. > > If you can't figure it out, maybe post to this list or the php-general list > a question about how to pass the values of <select>s from page to page. You > might also want to check this out too: > > http://www.php.net/manual/en/faq.html.php > > If you figure it out, please post the answer because I want to learn too. > > Wait...A rock just hit me... > > Look at this: > > > > echo "<input type='hidden' name='exerciselist' > > > value='$listbox'>"; > > Know what you're saying here? The value of exerciselist is the _STRING_ > $listbox. Not the VALUE of the $listbox, the _LITERAL STRING_. Try saying it > this way: > > echo "<input type='hidden' name='exerciselist' > value='".$listbox."'>"; > > I don't know if this grabs the value of $listbox at submit time, but it > should escape you from the HTML being echoed and substitute the value of the > variable $listbox. > > Now I'm really finished ;^) > > Good luck, > Rich > > -----Original Message----- > From: Chip Wiegand [mailto:chip@wiegand.org] > Sent: Monday, August 05, 2002 12:53 AM > To: Rich Hutchins > Subject: RE: [PHP-DB] problem getting form input field to become > partofquery statement > > > On Sun, 2002-08-04 at 09:41, Rich Hutchins wrote: > > Chip, > > > > Could you post the result of your echo statement please? > > Here's the error returned: > > You have an error in your SQL syntax near '(date, comments, reps, > exercise) values('1-1','test','12','120')' at line > 1 > > And this is the url that matches the above error: > > http://192.168.1.53/workout-absflexor.php?exerciselist=%24listbox&exercise=1 > 20 > &reps=12&date=1-1&comments=test&submit=Send+Data > > The error has to be the table name, it's wrong. The variable $listbox > isn't being used. > I can echo $listbox (as seen below, last line) and it returns the one > value I want, and selected in the list box on the first page. > Why the same variable doesn't work in the insert statement is what's > bugging me. > > <? > if(isset($submit)): > $db = mysql_connect("localhost","----","-----"); > if(!$db) error_message(sql_error()); > mysql_select_db("workout",$db) or die (mysql_error()); > //$date = date("m-d"); > $sql = "insert into $listbox (date, comments, reps, exercise) > values('$date','$comments','$reps','$exercise')"; > mysql_query($sql) or die(mysql_error()); > endif; > echo $listbox; > ?> > > > Judging from your code, it looks like your $exercises array contains three > > table names in your db. When your user selects a value from the listbox, > you > > assign that variable to the hidden field exerciselist. > > Eventually, the select list will contain aprox. 20 selections, the user > would pick one only, which is passed to the next page and becomes the > table name in the insert statement. If there's a better way to do this > than what I'm trying, I'm open to suggestions. > > > Then when the user hits submit, the value in exercise list gets sent to > the > > receiving page where the user should be able to enter additional info > > regarding that particular exercise. > > > > I'm curious then, why are you attempting to use the variable $listbox as a > > source for the table name in your SQL statement (I'm assuming that's what > > $listbox is supposed to be doing for you) instead of the single value that > > gets passed via the exerciselist hidden field? > > I'm just following the examples is the book Beginning PHP4, by > Choi/Kent/Lea. The show a select list with a name 'listbox' on the first > page, and the same name echoed on the second page. Simple enough. > > > Seems to me that $listbox would have the three values from the array > stored > > in it but that $exerciselist would have only the one chosen from the > > listbox. I could see why your query would bomb since MySQL wouldn't know > > which of the three tables you're trying to insert into. > > When I replace the variable $listbox with $exerciselist I get the > following error: > > Table 'workout.$exerciselist' doesn't exist > > > Hope I'm not torturing you here. I certainly don't mean to. Just trying to > > help. > > No torturing, I need the help, thanks. > > > Rich > > -- > Chip W > www.wiegand.org > chip@wiegand.org > > > > > -----Original Message----- > > From: Chip Wiegand [mailto:chip@wiegand.org] > > Sent: Sunday, August 04, 2002 1:33 AM > > To: Rich Hutchins > > Cc: phpdb > > Subject: RE: [PHP-DB] problem getting form input field to become part > > ofquery statement > > > > > > On Sat, 2002-08-03 at 06:05, Rich Hutchins wrote: > > > Try referencing the $listbox variable in you SQL statement like this: > > > > > > $sql = "insert into ".$listbox." > > > > > > values(NULL,'$date','$exercise','$reps','$comments')"; > > > > > > I'm guessing that it might also work like this: > > > > > > $sql = "insert into '$listbox' > > > > > > values(NULL,'$date','$exercise','$reps','$comments')"; > > > > > > I think your core problem is that the $listbox variable is not being > > > evaluated properly in the SQL statement. Once you solve that, you're > good > > to > > > go. > > > > > > Hope this helps. > > > > > > Rich > > > > I tried both suggestions and neither are working. I am using the get > > format for the form so I can see what is being sent, and I am getting > > this: > > > http://192.168.1.53/workout-absflexor.php?exerciselist=%24listbox&exercise=8 > > 0 > > &reps=12&comments=&submit=Send+Data > > > > Notice that the $listbox variable is still not being sent. Just above > > this is an echo statement which shows what's in that variable, and it > > displays the expected result (see code below). I am at a loss as to why > > this is not working. > > > > If I replace the $listbox variable with the table name shown in the echo > > statement, a connection is made and the query is completed. > > > > -- > > Chip W > > www.wiegand.org > > chip@wiegand.org > > > > > > > -----Original Message----- > > > From: Chip Wiegand [mailto:chip@wiegand.org] > > > Sent: Saturday, August 03, 2002 12:51 AM > > > To: phpdb > > > Subject: [PHP-DB] problem getting form input field to become part of > > > query statement > > > > > > > > > I have a form with a select list and a hidden field to save the selected > > > item. On submit another page is loaded with a few fields to be filled in > > > and submitted to a table. Using get I see the data is being passed from > > > the first page to the second properly, and the second page sends its > > > data properly. The hidden field from the first page is to be used by the > > > second page as the name of the table in the query. I have a place where > > > I echo the contents of the hidden field just to be sure it is correct, > > > and that does indeed show what I expect. I then make the query statement > > > point to the variable but it always responds that it cannot find the > > > table. The table does exist, the variable does contain the appropriate > > > table name, but is not being replaced by the name. What am I doing > > > wrong? > > > > > > -- > > > Chip W > > > www.wiegand.org > > > chip@wiegand.org > > > > > > Below is the code for the first page -- > > > > > > <html> > > > <head> > > > <title></title> > > > </head> > > > <body> > > > <div align="center"> > > > <? > > > $exercises=array("absflexor","absmachine","leglifts"); > > > > > > echo "<form action='workout-absflexor.php' > > > method='get'>"; > > > echo "<table width='70%' border='0' > > > align='center'>"; > > > echo "<tr><th align='center'><h2>Exercise Data Input</h2></th></tr>"; > > > echo "<tr><th align='center'><select > > > name='listbox'>"; > > > echo "<option>$exercises[0]</option>"; > > > echo "<option>$exercises[1]</option>"; > > > echo "<option>$exercises[2]</option>"; > > > echo "</select><br><br>"; > > > echo "</th></tr></table>"; > > > echo "<input type='hidden' name='exerciselist' > > > value='$listbox'>"; > > > echo "<input type='submit'>"; > > > echo "</form>"; > > > ?> > > > </div> > > > </body> > > > </html> > > > > > > And below is the code for the second page -- > > > > > > <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" > > > > > > "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> > > > <html > > > xmlns="http://www.w3.org/1999/xhtml"> > > > <head> > > > <title>Workout Data Entry Form</title> > > > <style type="text/css"> > > > body { background-color: aqua; } > > > div.c1 {text-align: center} > > > </style> > > > </head> > > > <body> > > > <div class="c1"> > > > <h2>Work-Out Data Entry Screen</h2> > > > <form action="<? PHP_SELF ?>" method="get"> > > > <table summary="" width="60%" border="1" > > > align="center" > > > bgcolor="green"> > > > <tr> > > > <th>Weight</th> > > > <td align="left"><input type="text" > > > name="exercise" > > > maxlength="4"></td> > > > </tr> > > > <tr> > > > <th>Reps</th> > > > <td align="left"><input type="text" > > > name="reps" > > > maxlength="4"></td> > > > </tr> > > > <tr> > > > <th>Comments</th> > > > <td colspan="2"><textarea cols="50" > > > rows="3" > > > name="comments"> > > > </textarea></td> > > > </tr> > > > <tr><td><? echo $listbox; ?></td></tr> > > > </table> > > > <br /> > > > <input type="submit" name="submit" value="Send > > > Data" /> <input > > > type="reset" /> > > > </form> > > > </div> > > > <? > > > if(isset($submit)): > > > $db = mysql_connect("localhost","----","------"); > > > if(!$db) error_message(sql_error()); > > > mysql_select_db("workout",$db) or die ("Ack! Where's the database?"); > > > $date = date("m-d"); > > > $sql = "insert into $listbox > > > > > > values(NULL,'$date','$exercise','$reps','$comments')"; > > > mysql_query($sql) or die ("Ack! No response when I queried the > > > server!"); > > > endif; > > > ?> > > > </body> > > > </html>