-1

I'm trying to create a search form to search through my mysql database.

I succeded to to create the form with one text field and a submit buttom. BUT.. When I leave the text field blank and hit the submit button all results appear from the specific database table on my search.php site.

When i write anything in the text field (text that is included in the names from the database) and hit the submit button no search results appear at all.

As you can see in the code I've been trying to list the search results in two diferent ways. The first one reacts as described above. The last one does not list any search results at all.

Do anybody know why? Do I have a typo somewhere or do I miss anything ind the code?

Thanks in advance.

This is my index.php

    <! DOCTYPE html>
    <html>
    <head>
    <meta charset ="UTF-8">
    <title> Townin </title>
    <link rel="stylesheet" type="text/css" href="Townin/style.css">
    <style>
    body {background-color: white;}

    header{background-color:#6ab47b;
        border-color:#599a68;
    }


    header h1, h2 {margin-left: 20px;
        color: white;
        font-family: "Helvetica Neue", Helvetica, Arial, sans-serif;
    }
    h1, h1 {margin-left: 20px;
        color: gray;
        font-family: "Helvetica Neue", Helvetica, Arial, sans-serif;
    }
    p {margin-left: 20px;
        color: gray;
        font-family: "Helvetica Neue", Helvetica, Arial, sans-serif;
    }


    nav a, nav a:visited {
       color: white;
       background-color: #599a68;}

    nav a.selected, nav a:hover{color: black;
    background-color:#6ab47b;}

    nav {
      text-align:center;
      padding: 0 0;
      margin:20px 0 0;
      height: 50 px;
      background-color:#599a68;
    }

    nav ul {
        list-style:none;
        margin: 0px 10px;
        padding: 0px;
    }

    nav ul li {
        display: inline-block;
        border-right: 1px solid #6ab47b;
        text-align: center;
        width: 250px;
        padding:0px 0px;
    }  

    nav ul li a {
        display: block;
        height: 40px; 
        width: 100%;
        line-height:50px;
        background-color: #599a68;
    }
    li a:hover {
     background-color: #6ab47b;  

    }
    a:link {
        text-decoration: none;
        font-family: "Helvetica Neue", Helvetica, Arial, sans-serif; 
        color:gray;
    }
   a:visited {
       color:gray;
   }
   .button {
        background-color:#6ab47b;
        color: #fff;
        text-align: center;
        text-decoration: none;
        display: inline-block;
        font-size: 16px;
        font-weight: bold;
        margin: 0px 20px;
        padding:20px 20px;
        border-radius: 100 %;
   }
   .button:visited {
       color:white;
   }
   #bar-knap {
       display: inline-block;
       max-width: 100%;
       border-radius:10%;
       height:40%;
       width:20%;
       margin: 10px 10% 10px 10%;
       border-style:solid;
       border-color:gray;
   }
   #spise-knap {
       display: inline-block;
       max-width: 100%;
       border-radius:10%;
       height:40%;
       width:20%;
       margin:10px 10% 10px 10%;
       border-style:solid;
       border-color:gray;
   } 


    footer {
        background-color: #6ab47b;
        color: white;
        height: 70px;
        font-family: "Helvetica Neue", Helvetica, Arial, sans-serif;
        text-align: center;
        margin: 20px auto 0px auto;
    }
 </style>
 </head>   
 <body>

 <?php include ("Header2.php"); ?>


<a href="barer.php" >Barer
     <img src="img/barknap.jpg" alt="Barer" id="bar-knap">
</a>

<a href="spisesteder.php" >Spisesteder
     <img src="img/spiseknap.png" alt="Spisesteder" id="spise-knap">
</a>





<!--Search form-->
<p style="text-align:center;"> Søg mellem barer </p>


<br />
<br />
 <form method="post" action="search.php">    
 <input type="text" name="search" />     
 <input type="submit" name="submit" value="  Search  ">  
 </form>
<br />
<br />

<footer>
Natalie
</footer>


</body>   

</html>

And this is the search.php

    <?php
    //Connection to phpmyadmin
    //Step1
     $db = mysql_connect("host","username","password"); 
     if (!$db) {
     die("Database connection failed miserably: " . mysql_error());
     }

    //Step2
     $db_select = mysql_select_db("databasename",$db);
     if (!$db_select) {
     die("Database selection also failed miserably: " . mysql_error());
     }

     $search = $_POST[search];

    //SQL statement to select what to search

    $sql="SELECT * FROM Brugerdatabase
    WHERE 'Navn' like '%$search%' OR
    'Mad genre' like '%$search%' OR
    'Beliggenhed' like '%$search%'
    ORDER BY Navn ASC";


    // Run sql statement
    $result = mysql_query($sql, $db) or die(mysql_error());

    //Find out how many matches
    $number= mysql_num_rows($result);
    $pagetitle ="Search Results";



    ?>


    <!doctype html>
    <html lang="da">
    <head>
    <meta charset ="UTF-8">
    <title>Søge resultater</title>
    <link rel="stylesheet" type="text/css" href="style.css">
    <style>


table {
border-collapse: collapse;
width: 80%;
margin:0 5%;
}
table th, td {
    border-bottom:1px solid gray;
    height: 50px;
    vertical-align: center;
    padding: 15px;
    text-align: left;
 }
tr:hover{background-color:#f5f5f5}
fieldset {
    width: 60%;
    margin:0 20%; 
    box-align: center;
}

    </style>


    </head>
    <body>
  <?php include ("Header2.php"); ?>

    <!--Advanceret søge funktion-->
    <p style="text-align:center;"> Søge resultater</p>


    <?php
    //------------------------------------
    //This code inside the lines list the results when nothing is typed in the search field.
    //Creates a loop to loop through results

    while($row = mysql_fetch_array($result)){   

    echo "<table><tr><td>"
    . $row['1'] .
    "</td><td>"

    . $row['4'] .
    "</td><td>"

    . $row['5'] .
    "</td><td>"

    . $row['6'] .
    "</td><td>"

    . $row['7'] .
    "</td><td>"

    . $row['8'] .
    "</td></tr></table>";
    }
    //-----------------------------------

    //-------------------------------
    //This code inside the lines does not list anything at all..
    // loop through results and get variables

    while ($row=mysql_fetch_array($result)){
        $navn =$row["Navn"];
        $genre =$row["Mad genre"];
        $beliggenhed =$row["Beliggenhed"];
        }


    // Tabel with search results

    print " <tr>     
<td>     
    <form method=\"post\" action=\"confirmdelete.php\">  
    <input type=\"hidden\" name=\"sel_record\" value=\"$id\">    
    <input type=\"submit\" name=\"delete\" value=\"  Delete  \">
    <form>   

    <form method=\"post\" action=\"updateform.php\">
    <input type=\"hidden\" name=\"sel_record\" value=\"$id\">    
    <input type=\"submit\" name=\"update\" value=\"    Edit    \">
    </form>  
</td>    

        <td><strong>$navn</strong><br />     
            Mad genre: $genre<br />  
            Beliggenhed: $beliggenhed</td>
</tr>";

    print "</tr></table></body></html>";
   //----------------------------

    mysqli_close($db);

    ?>

    </body>
    </html>
Natalie
  • 43
  • 2
  • 8
  • 2
    `mysql` is deprecated, use `mysqli` – Kevin Kopf Feb 28 '16 at 22:43
  • A) I suggest STOP using mysql_ functions. Instead use PDO or mysqli B) I don't know the rest of your code but quick looking and $search = $_POST["search"]; use quotes around the POST variable name. – smozgur Feb 28 '16 at 22:43
  • Try echoing out your generated query, and run it directly in the database; does it work? As an aside - bear in mind that anyone searching for something with an apostrophe in it will break your search; you should look at using prepared statements instead of putting user-entered data right into the query – andrewsi Feb 28 '16 at 22:45
  • Internal CSS, `
    `, mysql, it's like taking a glimpse to the past. Before continuing with your script, I suggest you research a bit more, like people above suggested: mysqli or PDO, use prepared statements so you don't get hacked; and watch out for standards in HTML, you're not supposed to use `
    ` at all, and your CSS should be in an external file so browsers can download the information in parallel. Don't think `I'll just make this work and learn to do it properly later`, it's a bad mistake I also made, just do it right from the beginning.     – Cârnăciov Feb 28 '16 at 22:55
  • 1
    @aron9forever can you link me anything RE: not supposed to use `
    `? First I've heard of that!     – Martin Feb 28 '16 at 23:01
  • @Martin sure http://stackoverflow.com/questions/1726073/is-it-sometimes-bad-to-use-br – Cârnăciov Feb 28 '16 at 23:07
  • @aron9forever hah thanks, while that links' answers do make sense, I was hoping for something official from the W3C or suchlike . . . – Martin Feb 28 '16 at 23:13
  • Did my anwser solve your issue, Natalie? – Martin Mar 01 '16 at 13:54
  • Hi.. Thanks so much for the help and all the comments. Martin your help did solve my problems, wich I have been struggled with for a long time. I really appreciate it as a newbee. (: And I will try to take a look at mysqli. – Natalie Mar 02 '16 at 20:00

1 Answers1

0

  • You need to encase your array keys -in this example with single quotes- so:

    $search = $_POST['search'];

  • You should also real_escape string your key with:

    $search = mysql_real_escape_string($_POST[search]);

    ...As a minimum (read below)

  • Your search SQL you should not encase your column names, so rewrite them as: 

    $sql="SELECT * FROM Brugerdatabase
WHERE Navn like '%$search%' OR
`Mad genre` like '%$search%' OR
Beliggenhed like '%$search%'
ORDER BY Navn ASC";

You SHOULD encase the middle column within backticks (`) because the column name contains a space. quotes should only go around values, not column or table names.

  • Now, read again the line LIKE '%$search%' OR ... if $search is empty then this will return a string of <anyvalue><null><anyvalue> ==> %%, so this will return any of the columns that are not NULL because they contain something.

  • Your code detailed below does not output anything to the browser to display so you will never have anything to show for your query:

    //This code inside the lines does not list anything at all..
// loop through results and get variables
while ($row=mysql_fetch_array($result)){
    $navn =$row["Navn"];
    $genre =$row["Mad genre"];
    $beliggenhed =$row["Beliggenhed"];
    }

  • Finally, you really, REALLY should be looking into using MySQLi rather than the standard MySQL as that has been Deprecated and removed from current/future versions of PHP. It's use is really not a good idea and it's riddled with flaws and holes.

Martin
  • 22,212
  • 11
  • 70
  • 132