Main objective for this post is to open up a can of worms. I would like to get some advise on what is the best way/ most efficient way to search a phpmyadmin database.
I am just starting to learn PHP. Main goal is to be able to write my own search databases as that has always interested me. I played around with some code to build a basic search database and have it working now.
Below is the code I used. I would guess as it is my first attempt at this I am going about it the wrong way. Assuming this was a large database, how could I make this more efficient? Am I going about it the wrong way using PHP? Is there a better way to do this?
The goal is to have searchable database where the user has multiple options to query to help refine the results.
<html>
<head>
<title> test Search </title>
<style type="text/css">
table {
background-color: #ffffff;
}
th {
width: 200px;
text-align: left;
}
</style>
</head>
<body>
<h1> test Search</h1>
<form method="post" action="index.php">
<input type="hidden" name="submitted" value="true"/>
<label>Colour 1: <input type="text" name="criteria" /></label>
<label>Colour 2: <input type="text" name="criteria2" /></label>
<label>PostCode: <input type="text" name="criteria3" /></label>
<label>Suburb: <input type="text" name="criteria4" /></label>
<label>State: <input type="text" name="criteria5" /></label>
<input type="submit" />
</form>
<?php
if (isset($_POST['submitted'])) {
// connect to the database
include('connect.php');
//echo "connected " ;
$criteria = $_POST['criteria'];
$criteria2 = $_POST['criteria2'];
$criteria3 = $_POST['criteria3'];
$criteria4 = $_POST['criteria4'];
$criteria5 = $_POST['criteria5'];
$query = "SELECT * FROM `Mainlist` WHERE (`Colour1`like '%$criteria%') and (`Colour2`like '%$criteria2%')
and (`PostCode`like '%$criteria3%') and (`Suburb`like '%$criteria4%') and (`State`like '%$criteria5%')
LIMIT 0,10";
$result = mysqli_query($dbcon, $query) or die(' but there was an error getting data');
echo "<table>";
echo "<tr> <th>School</th> <th>State</th> <th>Suburb</th> <th>PostCode</th> <th>Logo</th> <th>Uniform</th></tr>";
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
echo "<tr><td>";
echo $row['School'];
echo "</td><td>";
echo $row['State'];
echo "</td><td>";
echo $row['Suburb'];
echo "</td><td>";
echo $row['PostCode'];
echo "</td><td><img src=\"data:image/jpeg;base64,";
echo base64_encode($row['Logo']);
echo "\" /></td></td>";
echo "</td><td><img src=\"data:image/jpeg;base64,";
echo base64_encode($row['Uniform']);
echo "\" /></td></td>";
}
echo "</table>";
}// end of main if statment
?>
</body>
</html>