Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    New Coder
    Join Date
    Feb 2010
    Posts
    19
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Searching a MySQL database

    Hi...

    I have created a form which consists of check boxes, radio buttons etc and these can be used to make a selection and search for an item in the database.

    the form i have created looks as follows:
    PHP Code:
    <html>
        <head>
            <title>Advanced Search</title>
        </head>
        
        <div align="center">

        <?php
            
    include ('includes/header.html')
        
    ?>
        
        <body>
        <p> &nbsp </p>
        <h1>Advanced Search</h1></br>
        <p>Narrow your search using the following options</p>
        <p> &nbsp </p>
        
        <form action="asearch.php" method="post">
        <name="asearch" />
        <table table border='1' table width = 50%>    
        <tr><th COLSPAN=4>Is this player still playing?</th></tr>
        <tr><td>
            <p> &nbsp </p>
        </td><td>
            <input type="radio" name="playing" value="Retired" /> Retired
        </td><td>
            <input type="radio" name="playing" value="Still Playing" /> Still Playing
        </td><td>
            <p> &nbsp </p>    
        </td></tr>
        <tr><th COLSPAN=4>What Nationality is the player?</th></tr>
        <tr><td>
            <p> &nbsp </p>
        </td><td>    
            <select name="nationality" align="center">
            <option value="all">Any</option>
            <option value="african">African</option>
            <option value="aisian">Asian</option>
            <option value="european">European</option>
            <option value="namerican">North American</option>
            <option value="samerican">South American</option>    
            <option value="oceanian">Oceanian</option>
            <option value="uncapped">*Uncapped</option>            
            </select>
        </td><td>
            (select "any" if unsure)
        </td><td>
            <p> &nbsp </p>
        </td></tr>
        <tr><th COLSPAN=4>Position?</th></tr>
        <tr><td>
            Goalkeeper
            <input type="checkbox" name="position" value="goalkeeper" />
        </td><td>
            Defener
            <input type="checkbox" name="position" value="defender" />
        </td><td>
            Midfielder
            <input type="checkbox" name="position" value="midfielder" />
        </td><td>
            Striker
            <input type="checkbox" name="position" value="striker" />
        </td></tr>
        <tr><th COLSPAN=4>Club Shirt Number</th></tr>
        <tr><td>
            <input type="radio" name="clubno" value="01" /> #1 - #11
        </td><td>
            <input type="radio" name="clubno" value="1230" /> #12 - #30
        </td><td>
            <input type="radio" name="clubno" value="3159" /> #31- #59
        </td><td>
            <input type="radio" name="clubno" value="6099" /> #60 - #99
        </td></tr>
        </table>
        
        <p> &nbsp </p>
        <input type="submit" name="submit" value="Search" />
        <input type="hidden" name="submitted" value="TRUE" />
        <p> &nbsp </p>
        <p>* select Uncapped in the player has not yet been called up by the national team</p>
        <p> &nbsp </p>
        
        </form>
            <p> &nbsp </p>
        </body>
        
            <?php
            
    include ('includes/footer.html')
        
    ?>
        
        </div>
        
    </html>
    and the file asearch.php looks like:
    PHP Code:
    <?php
    ini_set
    ('display_errors''1');
    error_reporting(E_ALL);
    ?>

    <html>

        <head>
            <title>Advanced Search</title>
        </head>

    <div align="center">

    <body>

    <p>&nbsp </p>

    <?php
        
    include ('includes\header.html');
    ?>    

    <?php
          
    // Get the search variable from URL

      
    $var = @$_POST['asearch'] ;
    ?>

    <?php
    //connect to your database
    mysql_connect("*****","*****","*****"); //(host, username, password)

    //specify database
    mysql_select_db("******") or die("Unable to select database"); //select which database we're using

    $the_query "SELECT * FROM players WHERE Number like '".$_POST['clubno']."'";

    if (!empty(
    $_POST['asearch']))
     {
       
    $the_query $the_query "active_player = " $_POST['asearch'];
      } 
    if (!empty(
    $_POST['clubno']))
     {
        
    $the_query $the_query " clubno = " $_POST['clubno'];
     }
     if (!empty(
    $_POST['playing']))
     {
        
    $the_query $the_query " playing = " $_POST['playing'];
     }
     if (!empty(
    $_POST['nationality']))
     {
        
    $the_query $the_query " nationality = " $_POST['nationality'];
     }
      if (!empty(
    $_POST['position']))
     {
        
    $the_query $the_query " position = " $_POST['position'];
     }

     
    $numresults=mysql_query($the_query);
     
    //$numrows=mysql_num_rows($numresults);
     
     // next determine if s has been passed to script, if not use 0
      
    if (empty($s)) {
      
    $s=0;
      }

    // get results
      //$the_query .= " limit $s,$limit";
      
    echo "<p>&nbsp </p>";
      
    $result mysql_query($the_query) or die("Couldn't execute query");

    // begin to show results set
    echo "Results";
    $count $s ;


        echo 
    "<table border='1'>";
        echo 
    "<tr> <th>Name</th> <th>DOB</th> <th>Club</th> <th>Number</th> <th>Cost</th> <th>Position</th> <th>National Team</th> </tr>";
       
    // keeps getting the next row until there are no more to get
        
    while($row mysql_fetch_array$result )) {
        
    // Print out the contents of each row into a table
        
    echo "<tr><td>"
        echo 
    $row['Name'];
        echo 
    "</td><td>"
        echo 
    $row['DOB'];
        echo 
    "</td><td>"
        echo 
    $row['Club'];
        echo 
    "</td><td>"
        echo 
    $row['Number'];
        echo 
    "</td><td>"
        echo 
    $row['Cost'];
        echo 
    "</td><td>"
        echo 
    $row['Position'];
        echo 
    "</td><td>"
        echo 
    $row['NationalTeam'];    
        echo 
    "</td></tr>"


    echo 
    "</table>";
    ?>
     

    </body>

    <div align="center">

    <?php
        
    include ('includes\footer.html');
    ?>    

    </html>
    the error i am getting is: "Couldn't execute query"

    currently i am just testing the query by only looking at one of the values selected on the form.

    Help?

    el nino

  • #2
    New Coder
    Join Date
    Jun 2009
    Posts
    14
    Thanks
    0
    Thanked 1 Time in 1 Post
    Change this line
    Code:
    $result = mysql_query($the_query) or die("Couldn't execute query");
    to this:

    Code:
    $result = mysql_query($the_query) or die("Couldn't execute query. MySQL Said: ".mysql_error());
    then let us know the output.

  • #3
    New Coder
    Join Date
    Feb 2010
    Posts
    19
    Thanks
    4
    Thanked 0 Times in 0 Posts
    ok, i now get the following error

    Couldn't execute query. MySQL Said: 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 'clubno = 1 nationality = all' at line 1
    I am not looking at the nationality just yet as i have not yet defined the values for the different options in my form

  • #4
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    1) You have several conditions in the WHERE clause not separated with AND or OR. This gives the error.

    Just in case: while you are debugging locally (not at the Production system of course because it would be not safe), it could be often useful to echo the query before using it in mysql_query(). Often it helps to see an error at once.

    2) You are using POST variables right in your query. This makes your query vulnerable for SQL injection attack. Never do it like this. Always escape any potential user input. Please see the mysql_real_escape_string() function for reference. If you find this brief description not clear or not sufficient, please ask questions. Security is a very important aspect in web-programming.

  • Users who have thanked SKDevelopment for this post:

    el_nino (02-01-2010)

  • #5
    New Coder
    Join Date
    Feb 2010
    Posts
    19
    Thanks
    4
    Thanked 0 Times in 0 Posts
    thanks for the reply

    i understand the second point you made about security, as i am doing for a project i wanted to first get the query working, then show the effects of sql injection and then provide a solution for making it more secure.

    however i do not quite understand the first point you made:
    You have several conditions in the WHERE clause not separated with AND or OR. This gives the error
    could you please elaborate on that? i was under the assumption that if the user clicked on the box that stated #1-#11 on the form, the value would always be set to 1 as is defined in the form.

  • #6
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    Yes, of course. Initially the query is formed by the line:
    PHP Code:
    $the_query "SELECT * FROM players WHERE Number like '".$_POST['clubno']."'"
    Then in if-conditions you are adding some parts to this query like
    PHP Code:
    $the_query $the_query "active_player = " $_POST['asearch']; 
    If you did it like this:
    PHP Code:
    $the_query $the_query " AND active_player = " $_POST['asearch']; 
    you would not get the SQL syntax error. But since AND (or maybe OR) is missing, you are getting the error.

  • #7
    New Coder
    Join Date
    Feb 2010
    Posts
    19
    Thanks
    4
    Thanked 0 Times in 0 Posts
    i'm not quite understanding the function of:
    $the_query = $the_query . "active_player = " . $_POST['asearch'];

    $the_query = $the_query . " AND active_player = " . $_POST['asearch'];
    thanks

  • #8
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    If you try to echo the query, the query without and should give you something like
    Code:
    SELECT * FROM players WHERE Number like 'something here' active_player = 1
    It is necessary to have something like
    Code:
    SELECT * FROM players WHERE Number like 'something here' AND active_player = 1
    The error you have shown says that the conditions are joined like "clubno = 1 nationality = all". This is wrong. Conditions must be joined via AND or OR.

    You could try to echo the query
    PHP Code:
    echo $the_query
    right before using it in mysql_query(). It would allow you to see the problems with the query syntax too.

    Also please notice that all string values must be surrounded with single quotes. So "nationality = all" must be "nationality = 'all'".

  • Users who have thanked SKDevelopment for this post:

    el_nino (02-01-2010)

  • #9
    New Coder
    Join Date
    Feb 2010
    Posts
    19
    Thanks
    4
    Thanked 0 Times in 0 Posts
    hi, sorry about all the questions but i really don't seem to be with it today, i now see why the nationality was appearing from apparently nowhere, i took that out as i didnt want it to feature in this "test" query.

    when i echo out the query it looks like:
    SELECT * FROM players WHERE Number like '1' AND active_player =
    and the accompanying error messages look like:

    Couldn't execute query. MySQL Said: Unknown column 'active_player' in 'where clause'
    and

    Notice: Undefined index: asearch in asearch.php on line 41
    line 41 being

    $the_query = $the_query . " AND active_player = " . $_POST['asearch'];
    Last edited by el_nino; 02-01-2010 at 06:05 PM.

  • #10
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    1) The notice
    Code:
    Notice: Undefined index: asearch in asearch.php on line 41
    means the index "asearch" is not present in the array $_POST.

    The only element with the name "asearch" which I see in the HTML code you have posted is:
    Code:
    <name="asearch" />
    It is not an HTML element. And it would not be submitted by POST. Maybe you meant some hidden field instead ? Then the HTML code for it would be like this
    Code:
    <input type="hidden" name="asearch" value="some_value_here" />
    2) The error
    Code:
     Couldn't execute query. MySQL Said: Unknown column 'active_player' in 'where clause'
    means that the field `active_player` is absent in the table `players`. I mean the table `players` contains no field with the name `active_player`. Maybe this field is called a little bit differently in the database table `players` ?

  • #11
    New Coder
    Join Date
    Mar 2009
    Posts
    25
    Thanks
    2
    Thanked 3 Times in 3 Posts
    Like SKDevelopment said,
    Code:
    <name="asearch" />
    vvv Change To vvv
    Code:
    <input type="hidden" name="asearch" value="some_value_here" />
    Does have to happen because right now you are asking mySQL to find something
    where active player = nothing. Impossible, right? Also I believe you will need to change your mySQL line from:
    Code:
    $the_query = $the_query . " AND active_player = " . $_POST['asearch'];
    To:
    PHP Code:
    $the_query $the_query " AND active_player = ' " $_POST['asearch']." ' "
    (I added spacing around the apostrophes to make it easier to read...these aren't necessary)...

    Sorry if that didn't make sense, feel free to ask more questions.

  • #12
    New Coder
    Join Date
    Feb 2010
    Posts
    19
    Thanks
    4
    Thanked 0 Times in 0 Posts
    thanks for the help

    i've removed the active_players as i feel its not needed. i have the file a search.php which looks like:

    Code:
    <?php
    ini_set('display_errors', '1');
    error_reporting(E_ALL);
    ?>
    
    <html>
    
    	<head>
    		<title>Advanced Search</title>
    	</head>
    
    <div align="center">
    
    <body>
    
    <p>&nbsp </p>
    
    <?php
    	include ('includes\header.html');
    ?>	
    
    <?php
    	  // POST the search variable from URL
    
      $var = @$_POST['asearch'] ;
    ?>
    
    <?php
    //connect to your database
    mysql_connect("localhost","root",""); //(host, username, password)
    
    //specify database
    mysql_select_db("vasim") or die("Unable to select database"); //select which database we're using
    
    //$the_query = "SELECT * FROM players WHERE Number like '".$_POST['clubno']."'";
    
    $the_query = "SELECT * FROM players WHERE Number like '".$_POST['clubno']."'";
    
    //$the_query = $the_query . "active_player = " . $_POST['asearch'];
    
    //$the_query = $the_query . " AND active_player = " . $_POST['asearch'];
    
    //$the_query = $the_query . " AND active_player = ' " . $_POST['asearch']." ' "; 
    
        echo "<p>&nbsp </p>";
      echo $the_query; 
      
    
    if (!empty($_POST['asearch']))
     {
       $the_query = $_POST['asearch'];
      } 
    if (!empty($_POST['clubno']))
     {
        $the_query = $the_query . " clubno = " . $_POST['clubno'];
     }
     if (!empty($_POST['playing']))
     {
        $the_query = $the_query . " playing = " . $_POST['playing'];
     }
     if (!empty($_POST['nationality']))
     {
        $the_query = $the_query . " nationality = " . $_POST['nationality'];
     }
      if (!empty($_POST['position']))
     {
        $the_query = $the_query . " position = " . $_POST['position'];
     }
    
     $numresults=mysql_query($the_query);
     //$numrows=mysql_num_rows($numresults);
     
     // next determine if s has been passed to script, if not use 0
      if (empty($s)) {
      $s=0;
      }
    
    // POST results
      //$the_query .= " limit $s,$limit";
      echo "<p>&nbsp </p>";
      //$result = mysql_query($the_query) or die("Couldn't execute query");
    
      $result = mysql_query($the_query) or die("Couldn't execute query. MySQL Said: ".mysql_error());
        echo "<p>&nbsp </p>";
      echo $the_query; 
      
    // begin to show results set
    echo "Results";
    $count = 1 + $s ;
    
    
    	echo "<table border='1'>";
    	echo "<tr> <th>Name</th> <th>DOB</th> <th>Club</th> <th>Number</th> <th>Cost</th> <th>Position</th> <th>National Team</th> </tr>";
       // keeps POSTting the next row until there are no more to POST
    	while($row = mysql_fetch_array( $result )) {
    	// Print out the contents of each row into a table
    	echo "<tr><td>"; 
    	echo $row['Name'];
    	echo "</td><td>"; 
    	echo $row['DOB'];
    	echo "</td><td>"; 
    	echo $row['Club'];
    	echo "</td><td>"; 
    	echo $row['Number'];
    	echo "</td><td>"; 
    	echo $row['Cost'];
    	echo "</td><td>"; 
    	echo $row['Position'];
    	echo "</td><td>"; 
    	echo $row['NationalTeam'];	
    	echo "</td></tr>"; 
    } 
    
    echo "</table>";
    ?>
     
    
    </body>
    
    <div align="center">
    
    <?php
    	include ('includes\footer.html');
    ?>	
    
    </html>
    and the query which i have echoed looks like:

    SELECT * FROM players WHERE Number like '1'
    this is the query i was hoping to get out for this particular radio button, i have used 'like' because i will be adding more value to that particular radio button but for now it only has one value, that being '1'

    i would have expected this to now work but still get:

    Couldn't execute query. MySQL Said: 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 'clubno = 1' at line 1
    SKDevelopment - you mentioned SQL Injection earlier, i wanted to ask, in this form the user is unable enter their own specific value into a text field, but instead asked to select options using radio buttons/ check boxes. am i correct in assuming that i would not need to worry about sql injection here, but would need to consider it in the form which allowed the user to enter their own search term?

    thanks
    el nino

  • #13
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    You have echoed your query when it was only partially formed. Please echo it right before the line:
    PHP Code:
    $result mysql_query($the_query) or die("Couldn't execute query. MySQL Said: ".mysql_error()); 
    Then you would see the problem at once. It is the same problem as before. AND or OR is missing between conditions in the WHERE clause.

    Edit: Sorry for the error in the word "missing" (corrected).
    Last edited by SKDevelopment; 02-02-2010 at 02:25 PM. Reason: corrected grammatical error

  • #14
    New Coder
    Join Date
    Feb 2010
    Posts
    19
    Thanks
    4
    Thanked 0 Times in 0 Posts
    when i attempt to echo the query where you have suggested it doesn't seem to be printed :S

  • #15
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    Do you mean when you echo the query exactly here:
    PHP Code:
    echo $the_query '<br />';
    $result mysql_query($the_query) or die("Couldn't execute query. MySQL Said: ".mysql_error()); 
    the query is not printed ?


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •