Search form mysql database

[Jpergega]

Hi,

I want to create a search form on my website but the code below returns all existing records. How can I make it to return just the record I am searching for? Thanks

<form action="castudent.php" method="post">
        <br><input id="search" name="Username" type="text" placeholder="Username"/>
        <br><input id="search2" name="Firstname" type="text" placeholder="Firstname"/>
        <br><input id="search3" name="Surname" type="text" placeholder="Surname"/>
        <input id="searchB" type="submit" value="Search"/></form>

<?php
$sqlCommand = "SELECT * FROM useraccount";
$query = mysqli_query($con, $sqlCommand);

while ($row = mysqli_fetch_array($query)){ 
echo 'Username: ' .$row['Username']; 
echo '<br /> Firstname: ' .$row['Firstname']; 
echo '<br /> Surname: '.$row['Surname']; 

} 
mysqli_close($con);
?>

[bradgrafelman]

Welcome to PHPBuilder! When posting HTML or PHP code, please use the board's [noparse]

..

or

..

[/noparse] bbcode tags (respectively) as they make your code much easier to read and analyze.

As for your issue... you need to use a WHERE clause in your SELECT query to specify what condition(s) each row must satisfy in order to appear in the result set.

[johanafm]

First off, do not select "*", specify the fields you want. Even when you need all fields.

SELECT name, stuff FROM useraccount

To search for matching rows, specify search criteria

SELECT name, stuff FROM useraccount WHERE field1='val1' OR field2=5

SELECT syntax

[Jpergega]

Thanks I have made the change. However, is not returning me any records just an empty table and is not showing any error messages. Can you see where the error might be? Thanks

<?php
$sqlCommand = "SELECT * FROM useraccount WHERE Username LIKE '%Username%' OR Firstname LIKE '%Firstname%' OR Surname LIKE '%Surname%'";
$query = mysqli_query($con, $sqlCommand);

echo "<table border='1'>
<tr>
<th>Username</th>
<th>Firstname</th>
<th>Surname</th>
</tr>";

while ($row = mysqli_fetch_array($query)){

echo "<tr>";
echo"<td>" .$row['Username'] . "</td>";

echo"<td>" .$row['Firstname'] . "</td>";

echo"<td>" .$row['Surname'] . "</td>";

echo "</tr>";
}

echo"</table>";

mysqli_close($con);

?>

[Jpergega]

<?php
$sqlCommand = "SELECT Username, Firstname, Surname FROM useraccount WHERE Username LIKE '%Username%' OR Firstname LIKE '%Firstname%' OR Surname LIKE '%Surname%'";
$query = mysqli_query($con, $sqlCommand);

echo "<table border='1'>
<tr>
<th>Username</th>
<th>Firstname</th>
<th>Surname</th>
</tr>";

while ($row = mysqli_fetch_array($query)){
echo "<tr>";
echo"<td>" .$row['Username'] . "</td>";
echo"<td>" .$row['Firstname'] . "</td>";
echo"<td>" .$row['Surname'] . "</td>";
echo "</tr>";
}
echo"</table>";

mysqli_close($con);

?>

[traq]

Jpergega;11038449 wrote:

SELECT Username, Firstname, Surname FROM useraccount WHERE Username LIKE '%Username%' OR Firstname LIKE '%Firstname%' OR Surname LIKE '%Surname%'

Does your table contain any rows which match those conditions? Seems unlikely&#8230;?

[Jpergega]

Now I have changed to this line and is giving me the error:
$sqlCommand = "SELECT Username, Firstname, Surname FROM useraccount WHERE Username = '$Username' OR Firstname = '$Firstname' OR Surname = '$Surname'";

[bradgrafelman]

What's the error? Where do you define the variables $Username, $Firstname, and $Surname?

Also, once again:

bradgrafelman;11038433 wrote:

When posting HTML or PHP code, please use the board's [noparse]

..

or

..

[/noparse] bbcode tags (respectively) as they make your code much easier to read and analyze.

[Jpergega]

I made mistake I now changed to:
$sqlCommand = "SELECT Username, Firstname, Surname FROM useraccount WHERE Username = '$Username' OR Firstname = '$Firstname' OR Surname = '$Surname'";

but it always returning only the first record from the table and error messages for this line of code.

[Jpergega]

I defined just above the $sqlCommand

$Username= $_POST['Username'];
$Firstname =$_POST['Firstname'];
$Surname = $_POST['Surname'];

[Jpergega]

Hi I have just re-tested and is working now. Thanks 🙂

[traq]

Jpergega;11038463 wrote:

I defined just above the $sqlCommand

$Username= $_POST['Username'];
$Firstname =$_POST['Firstname'];
$Surname = $_POST['Surname'];

This is dangerous: it makes your script vulnerable to SQL Injection attacks (or simple syntax errors). When you use user input in an SQL query, you need to validate/sanitize it beforehand:

<?php

$Username = mysqli_real_escape_string( $POST["Username"] );
//  . . .

Better yet, use prepared statements so your data will never be confused with SQL commands.

[Jpergega]

traq;11038469 wrote:

This is dangerous: it makes your script vulnerable to SQL Injection attacks (or simple syntax errors). When you use user input in an SQL query, you need to validate/sanitize it beforehand:

<?php

$Username = mysqli_real_escape_string( $POST["Username"] );
//  . . .

Better yet, use prepared statements so your data will never be confused with SQL commands.

Many thanks for your help