sonofharrold Posted November 9, 2014 Share Posted November 9, 2014 Hi all, The code creates 4 select boxes.The user clicks on one item in each of the boxes and these are entered into the SQL statement.Trouble is I keep getting error after error all referring to line 59(the SQL statement line) can any keen eyed PHP freak see what I am doing wrong(or even if the whole of the code is any good)..Not too experienced with php so any comments suggestions(or laughs) greatly appreciated. <!DOCTYPE HTML> <html> <body> <select name = "author"> <option value="kendavies">ken davies</option> <option value="arthursmith">arthur smith</option> <option value="gillrafferty">gill rafferty</option> <option value="mollybrown">molly brown</option> <option value="gilbert riley">gilbert riley</option> <option value="colinwilson">colin wilson</option> <option value="jamesgreen">james green</option> <option value="arnoldlaing">arnold laing</option> <option value="cathyellis">cathy ellis</option> <option value="carolreed">carol reed</option> </select> <select name = "publisher"> <option value="yonkers">yonkers</option> <option value="blueparrot">blue parrot</option> <option value="zoot">zoot</option> </select> <select name = "yearpublished"> <option value="2003">2003</option> <option value="2004">2004</option> <option value="2005">2005</option> <option value="2006">2006</option> <option value="2007">2007</option> <option value="2008">2008</option> </select> <select name = "genre"> <option value="adventure">adventure</option> <option value="thriller">thriller</option> <option value="crime">crime</option> <option value="biography">biography</option> <option value="romance">romance</option> </select> $aa = "author" $bb = "publisher" $cc = "yearpublished" $dd = "genre" <?php $aa = "author"; $bb = "publisher"; $cc = "yearpublished"; $dd = "genre"; mysql_connect ("localhost","root","") or die(mysql_error()); mysql_select_db ("authors") or die(mysql_error()); $strSQL = SELECT * FROM `books` WHERE author = '".$aa."' AND publisher = '".$bb."' AND yearpublished = '".$cc."' AND genre ='".$dd."'" "; $rs = mysql_query($strSQL;); while($row = mysql_fetch_array($rs) ) { print $row ['ID']."<br/>"; print $row ['author']."<br/>"; print $row ['booktitle']."<br/>"; print $row ['publisher']."<br/>"; print $row ['yearpublished']."<br/>"; print $row ['genre']."<br/>"; print $row ['copiessold']."<br/>"; } mysql_close(); ?> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/292378-is-it-my-sql-select-line/ Share on other sites More sharing options...
ginerjm Posted November 9, 2014 Share Posted November 9, 2014 (edited) Would be real nice IF YOU HAD shown us the error instead of making us digest your entire code. Could it be because you didn't quote it properly? Edited November 9, 2014 by ginerjm Quote Link to comment https://forums.phpfreaks.com/topic/292378-is-it-my-sql-select-line/#findComment-1496173 Share on other sites More sharing options...
NotionCommotion Posted November 9, 2014 Share Posted November 9, 2014 Try running these lines all by themselves. What does the error say? Always try to make your code easy to read. Given all those quotes, I would tend to use "hello '{$name}'" format (and I never put ticks around table/column names, but maybe I should?). Also, offtopic, but look into PDO. <?php $aa = "author"; $bb = "publisher"; $cc = "yearpublished"; $dd = "genre"; $strSQL = "SELECT * FROM `books` WHERE author = '".$aa."' AND publisher = '".$bb."' AND yearpublished = '".$cc."' AND genre ='".$dd."'" "; ?> Quote Link to comment https://forums.phpfreaks.com/topic/292378-is-it-my-sql-select-line/#findComment-1496182 Share on other sites More sharing options...
sonofharrold Posted November 9, 2014 Author Share Posted November 9, 2014 Hi both, Thanks for the replies.Here is the error.Parse error: syntax error, unexpected '`' in C:\xampp\htdocs\boxes.php on line 59 It refrers to the `` around the reference to the books table.So I tried many a variation on that including ' and "" but then I got the same error up just referring to those.I also left the quotes around books out altogether,the I get this Parse error: syntax error, unexpected 'books' (T_STRING) in C:\xampp\htdocs\boxes.php on line 59 So its a bit of a catch 22. I am just trying different variations on the SELECT line to see if I can crack it but so far to no avail. Again many thanks for your replies. Harold Quote Link to comment https://forums.phpfreaks.com/topic/292378-is-it-my-sql-select-line/#findComment-1496187 Share on other sites More sharing options...
ginerjm Posted November 9, 2014 Share Posted November 9, 2014 Show us your latest query statement (in code and properly posted here) and we'll debug it. Quote Link to comment https://forums.phpfreaks.com/topic/292378-is-it-my-sql-select-line/#findComment-1496188 Share on other sites More sharing options...
NotionCommotion Posted November 9, 2014 Share Posted November 9, 2014 Sonofharold, It has nothing to do with SQL, and not even your books table. Look at the the following line. Strings need quotes around them. See http://php.net/manual/en/language.types.string.php. Your string is not well formed. $strSQL = SELECT * FROM `books` WHERE author = '".$aa."' AND publisher = '".$bb."' AND yearpublished = '".$cc."' AND genre ='".$dd."'" "; I would probably do something like the following (provided I didn't want to use PDO): $strSQL = "SELECT * FROM books WHERE author = '{$aa}' AND publisher = '{$bb}' AND yearpublished = '{$cc}' AND genre ='{$dd}'"; Quote Link to comment https://forums.phpfreaks.com/topic/292378-is-it-my-sql-select-line/#findComment-1496191 Share on other sites More sharing options...
ginerjm Posted November 9, 2014 Share Posted November 9, 2014 As I pointed out earlier this statement needed quotes initially. And it can be written easier like this: $strSQL = "select * from books where author = '$aa' and publisher = '$bb' and yearpublished = '$cc' and genre = '$dd' "; PS - it is not recommended to select *. One should only select those fields (columns) that are necessary. Quote Link to comment https://forums.phpfreaks.com/topic/292378-is-it-my-sql-select-line/#findComment-1496193 Share on other sites More sharing options...
sonofharrold Posted November 10, 2014 Author Share Posted November 10, 2014 Hi all, many thanks for your replies I tried all three SELECTS above,but to no avail. The first SELECT came up with the message:Parse Error.syntax error,unexpected '='inC:\xampp\htdocs\harold.php on line 56. The second line resulted in the four drop down boxes being displayed. and the third line came up with the message Parse Error.syntax error,unexpected '$aa=' (T_VARIABLE) inC:\xampp\htdocs\harold.php on line 56. I am wondering if the php can't read the HTML code. Here is the complete code and I left the three mysql SELECTS in to have a look at. <!DOCTYPE HTML> <html> <body> <select name = "author"> <option value="kendavies">ken davies</option> <option value="arthursmith">arthur smith</option> <option value="gillrafferty">gill rafferty</option> <option value="mollybrown">molly brown</option> <option value="gilbert riley">gilbert riley</option> <option value="colinwilson">colin wilson</option> <option value="jamesgreen">james green</option> <option value="arnoldlaing">arnold laing</option> <option value="cathyellis">cathy ellis</option> <option value="carolreed">carol reed</option> </select> <select name = "publisher"> <option value="yonkers">yonkers</option> <option value="blueparrot">blue parrot</option> <option value="zoot">zoot</option> </select> <select name = "yearpublished"> <option value="2003">2003</option> <option value="2004">2004</option> <option value="2005">2005</option> <option value="2006">2006</option> <option value="2007">2007</option> <option value="2008">2008</option> </select> <select name = "genre"> <option value="adventure">adventure</option> <option value="thriller">thriller</option> <option value="crime">crime</option> <option value="biography">biography</option> <option value="romance">romance</option> </select> <?php $aa = "author"; $bb = "publisher"; $cc = "yearpublished"; $dd = "genre"; mysql_connect ("localhost","root","") or die(mysql_error()); mysql_select_db ("authors") or die(mysql_error()); //$strSQL = "SELECT * FROM books WHERE author = {$aa} AND publisher = {$bb} AND yearpublished = {$cc} AND genre ={$dd}"; $strSQL = "SELECT * from books WHERE author = '$aa' AND publisher = '$bb' AND yearpublished = '$cc' AND genre = '$dd' "; //$strSQL = "SELECT * FROM books WHERE author = "$aa" AND publisher = "$bb" AND yearpublished = "$cc" AND genre ="$dd"; $rs = mysql_query($strSQL); while($row = mysql_fetch_array($rs) ) { print $row ['ID']."<br/>"; print $row ['author']."<br/>"; print $row ['booktitle']."<br/>"; print $row ['publisher']."<br/>"; print $row ['yearpublished']."<br/>"; print $row ['genre']."<br/>"; print $row ['copiessold']."<br/>"; } mysql_close(); ?> </body> </html> > Quote Link to comment https://forums.phpfreaks.com/topic/292378-is-it-my-sql-select-line/#findComment-1496245 Share on other sites More sharing options...
ginerjm Posted November 10, 2014 Share Posted November 10, 2014 This code looks fine. Although you are displaying the dropdowns and then doing a query to get some data that you then display below them. Are you getting error messages still from the above EXACT code? Do you have error checking turned on? Quote Link to comment https://forums.phpfreaks.com/topic/292378-is-it-my-sql-select-line/#findComment-1496250 Share on other sites More sharing options...
sonofharrold Posted November 10, 2014 Author Share Posted November 10, 2014 Hello ginerjm Thanks for your reply.No as it is the code isn't working.Are you suggesting that the code needs rearrangeing in some way.Interestingly,when I use the second sql line I just get the drop boxes,it's as if only the html is being read and the php ignored.I am beginning to think that the while loop might be at fault. Kind Regards Harold Quote Link to comment https://forums.phpfreaks.com/topic/292378-is-it-my-sql-select-line/#findComment-1496271 Share on other sites More sharing options...
tryingtolearn Posted November 10, 2014 Share Posted November 10, 2014 The user clicks on one item in each of the boxes and these are entered into the SQL statement Are you trying to get the value of the drop down item they select into your query? If so, I am not sure that it is going to work this way.. $aa = "author"; $bb = "publisher"; $cc = "yearpublished"; $dd = "genre"; is just going to pass the words author, publisher, yearpublished, and genre as the values - not the dropdown values Or am I missing something? Quote Link to comment https://forums.phpfreaks.com/topic/292378-is-it-my-sql-select-line/#findComment-1496272 Share on other sites More sharing options...
sonofharrold Posted November 10, 2014 Author Share Posted November 10, 2014 Hi tryingtolearn, Thanks for your reply.You may well be right.I am quite new to this and trying to make some logical jumps based on my sparse grasp of the language.Learning all the time though Kind regards H. Quote Link to comment https://forums.phpfreaks.com/topic/292378-is-it-my-sql-select-line/#findComment-1496274 Share on other sites More sharing options...
ginerjm Posted November 10, 2014 Share Posted November 10, 2014 You have to realize that FIRST you have to send the html form (with the dropdowns and perhaps a submit) to the client and then wait. When the form and input is submitted back to your script you have to CHECK to see what's going on. Are you just supposed to send the html form again or are you supposed to process some input from the previously sent form? A good way to do that is to test for the existence of your submit button's value in the $_POST array. Once you have the input grab it into your vars and then build your query and run it and then process the results of that and do whatever you want to do with it. I highly recommend you turn on error checking - see my signature. Your code should look like this: - if post array does NOT have submit button value build output form, output it, and exit if post array does have submit button value grab the inputs do your query process the query results build your output output it exit Quote Link to comment https://forums.phpfreaks.com/topic/292378-is-it-my-sql-select-line/#findComment-1496277 Share on other sites More sharing options...
NotionCommotion Posted November 10, 2014 Share Posted November 10, 2014 Forget about the SQL stuff for now. Focus just on sending stuff between the server and client. The client is your web browser. The server is the Apache/IIS/etc sever located somewhere. Client says "server, give me some HTML". Server gets the HTML, and sends it to the client. User clicks "submit" on the client, but wait, nothing happens??? Well, your inputs and submit button need a <form> tag around the inputs to tell it where to send the data. Note that the HTML you showed doesn't have this form. Add the form, and then the data goes whizzing to the server. The server receives the data, does some work, and then sends back the appropriate HTML. But don't do your SQL queries yet. Instead, make sure it is receiving the correct data. I like to use echo('<pre>'.print_r($_POST,1).'</pre>');, but others prefer var_dump($_POST);. As ginerjm indicated, enable error display! When you know everything is working, deal with the SQL part, so the correct HTML is sent back. Client receives the HTML, and user is happy Hi all, many thanks for your replies I tried all three SELECTS above,but to no avail. The first SELECT came up with the message:Parse Error.syntax error,unexpected '='inC:\xampp\htdocs\harold.php on line 56. The second line resulted in the four drop down boxes being displayed. and the third line came up with the message Parse Error.syntax error,unexpected '$aa=' (T_VARIABLE) inC:\xampp\htdocs\harold.php on line 56. I am wondering if the php can't read the HTML code. Here is the complete code and I left the three mysql SELECTS in to have a look at. <!DOCTYPE HTML> <html> <body> <select name = "author"> <option value="kendavies">ken davies</option> <option value="arthursmith">arthur smith</option> <option value="gillrafferty">gill rafferty</option> <option value="mollybrown">molly brown</option> <option value="gilbert riley">gilbert riley</option> <option value="colinwilson">colin wilson</option> <option value="jamesgreen">james green</option> <option value="arnoldlaing">arnold laing</option> <option value="cathyellis">cathy ellis</option> <option value="carolreed">carol reed</option> </select> <select name = "publisher"> <option value="yonkers">yonkers</option> <option value="blueparrot">blue parrot</option> <option value="zoot">zoot</option> </select> <select name = "yearpublished"> <option value="2003">2003</option> <option value="2004">2004</option> <option value="2005">2005</option> <option value="2006">2006</option> <option value="2007">2007</option> <option value="2008">2008</option> </select> <select name = "genre"> <option value="adventure">adventure</option> <option value="thriller">thriller</option> <option value="crime">crime</option> <option value="biography">biography</option> <option value="romance">romance</option> </select> <?php $aa = "author"; $bb = "publisher"; $cc = "yearpublished"; $dd = "genre"; mysql_connect ("localhost","root","") or die(mysql_error()); mysql_select_db ("authors") or die(mysql_error()); //$strSQL = "SELECT * FROM books WHERE author = {$aa} AND publisher = {$bb} AND yearpublished = {$cc} AND genre ={$dd}"; $strSQL = "SELECT * from books WHERE author = '$aa' AND publisher = '$bb' AND yearpublished = '$cc' AND genre = '$dd' "; //$strSQL = "SELECT * FROM books WHERE author = "$aa" AND publisher = "$bb" AND yearpublished = "$cc" AND genre ="$dd"; $rs = mysql_query($strSQL); while($row = mysql_fetch_array($rs) ) { print $row ['ID']."<br/>"; print $row ['author']."<br/>"; print $row ['booktitle']."<br/>"; print $row ['publisher']."<br/>"; print $row ['yearpublished']."<br/>"; print $row ['genre']."<br/>"; print $row ['copiessold']."<br/>"; } mysql_close(); ?> </body> </html> > Quote Link to comment https://forums.phpfreaks.com/topic/292378-is-it-my-sql-select-line/#findComment-1496279 Share on other sites More sharing options...
sonofharrold Posted November 11, 2014 Author Share Posted November 11, 2014 Hmmm !!So should I be using the POST attribute in the HTML Form to send the value from my Listbox back to the a variable that will be placed into the SELECT string Kind Regards H Quote Link to comment https://forums.phpfreaks.com/topic/292378-is-it-my-sql-select-line/#findComment-1496319 Share on other sites More sharing options...
ginerjm Posted November 11, 2014 Share Posted November 11, 2014 NO. Your last statement is fraught with confusion about terms. Have you not done ANY reading to try and get on board with html, php or how client and server communicate? An HTML form can return its input values (from the input tags) via either the $_POST or $_GET array. This array is read in the PHP script and the values are then used in the PHP logic to do what you want to do with them. POST is not an attribute except in the html form tag itself. You php code will grab that input and yes - you can then use it in the select string. Quote Link to comment https://forums.phpfreaks.com/topic/292378-is-it-my-sql-select-line/#findComment-1496320 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.