MySQL making search function won't work

Question

I am trying to build a search function into my site, which lets you find users based on their name or surname and then it will print all found names. Problem is, that I won't work at all. Can you guys help me?

EDIT: if I try to search something, it will always give me the answer that nothing was found.

The php code:

<?php
    $search = $_POST["search"];
    if(isset($search)) {
        $findname = "SELECT `name` FROM Account WHERE $search = `name` 
                    OR SELECT `name` FROM `Account` WHERE $search = `surname` 
                    OR SELECT `name` FROM `Account` 
                    WHERE $search = `name` . `surname` 
                    OR SELECT `name` FROM `Account` WHERE $search = `name` . `surnameprefix` . `surname`";

        $findsurname = "SELECT `surname` from Account WHERE $search = `name` 
                        OR SELECT `surname` FROM `Account` WHERE $search = `surname` 
                        OR SELECT `surname` FROM `Account` WHERE $search = `name` . `surname` 
                        OR SELECT `surname` FROM `Account` WHERE $search = `name` . `surnameprefix` . `surname`";

        $result2 = mysql_query($findsurname);
        $result1 = mysql_query($findname);
            if($result1 == false && $result2 == false) {
                echo '<div id="searchresult"><h1>People found:</h1> <p>No one was found...</p>';
            } else {
                echo '<div id="searchresult">
                        <h1>People found:</h1>
                        <table id="searchtable">
                            while($result = mysql_fetch_array($result1)){
                                <tr id="searchtablerow">
                                    <td> $findname ' . '$findsurname</td>
                                    <td><img src="standard-profile-square.jpg" id="searchpicture"></td>
                                </tr>
                            }
                        </table>
                    </div>';
            }
        }
?>

The html form code:

    <div id="searchform">
        <h1>Search friends:</h1>
        <form name="searchform" method="post" action ="searchlink.php">
            <input type="text" name="search" id="search" autofocus placeholder="e.g. John Smith..."></input> <br>
            <input type="submit" name="submitsearch" value="Search" id="searchbutton"></input>
        </form>
    </div>

Thank you guys in advance for helping me!

Yours sincerely, Unheil

Answer 1

Your last echo is too long. The php instructions for outputting your result are not interpreted but outputted.

<?php
} else {
  echo '<div id="searchresult">
  <h1>People found:</h1>
  <table id="searchtable">';
  while($result = mysql_fetch_array($result1)){
    echo '<tr id="searchtablerow">
        <td> '.$findname.' '.$findsurname.'</td>
        <td><img src="standard-profile-square.jpg" id="searchpicture"></td>
      </tr>';
  }
  echo '</table></div>';
}
?>

And if you use ' for Strings, you have to concat your variables into it.

Answer 2

There are a lot of errors in your code. The SQL statements are wrong. The two statements basically do the same thing except they return a different column.

The SELECTs in the where clause are nonsense. Instead, in the where clause you should just add more qualifiers, not select statements.

In a SQL statement, you use PHP concatenation, which doesn't work in SQL. The PHP code has many syntax and logic errors. You are using the mysql functions instead of the mysqli functions. You never open a connection to the database.

The list of problems in the code go on and on.

Here's some code that should work and get you closer to what you want.

<?php
    $host = 'your host name';
    $user = 'your db user name';
    $password = 'your db user name';
    $db_name = 'your db name';
    $search = $_POST["search"];
    if(isset($search)) {
        $db = mysqli_connect($host, $user, $password, $db_name);
        $wild_search = "%".$search."%";
        $findname = "SELECT `name`,`surname`
        FROM `Account` 
        WHERE `name` LIKE '".$wild_search."'
            OR `surname` LIKE '".$wild_search."'
            OR CONCAT(`name`, `surname`) LIKE '".$wild_search."'
            OR CONCAT(`name`, `surnameprefix`, `surname`) LIKE '".$wild_search."';";
        $query = mysqli_query($db,$findname);
        $results = mysqli_fetch_all($query);

        if($result) {
            echo "<div id='searchresult'>\n";
            echo "<h1>People Found</h1>\n";
            echo "<table id='searchresult'>\n";
            foreach($result as $rowno => $row) {
                echo "<tr class='searchtablerow'>\n";
                echo "<td>".$row['name'].", ".$row['surname']."</td>\n";
                echo "</tr>\n";
            }
            echo "</table>\n";
        } else {
            echo "<div id='searchresult'><h1>People Found:</h1>\n";
            echo "<p>No one was found.</p>\n";
            echo "</div>\n";
        }
    } else {
    }
?>

In phpAdmin, try the following test statement. Substitute a name you know exists in the table for "smith" in the example:

SELECT `name`,`surname`
    FROM `Account` 
    WHERE `name` LIKE '%smith%'
        OR `surname` LIKE '%smith%'
        OR CONCAT(`name`, `surname`) LIKE '%smith%'
        OR CONCAT(`name`, `surnameprefix`, `surname`) LIKE '%smith%';