Is it my sql SELECT line?

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

ginerjm · November 9, 2014

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

NotionCommotion · 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."'" ";         
?>

sonofharrold · 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 :confused: but so far to no avail.

Again many thanks for your replies.

Harold

ginerjm · November 9, 2014

Show us your latest query statement (in code and properly posted here) and we'll debug it.

NotionCommotion · 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}'";

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

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

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

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

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

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

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

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

sonofharrold · 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 :confused:

Kind Regards

H

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

Sign In

Is it my sql SELECT line?

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

Join the conversation

Important Information