Ok so the problem is very simple, basically when you put for example "W" it should output hotel names and guest's surnames that contain that character. It doesn't that hotel names however it never gives me an output for guest's no matter what I put. There are several matching for guest's that should appear however I get nothing. I can't see any mistakes with my code... Help.
<!DOCTYPE html>
<html>
<head>
<title>Database</title>
<link href="style.css" rel="stylesheet" type="text/css"> <!-- This is linking style sheet (css)into this HTML page-->
<link href='https://fonts.googleapis.com/css?family=PT+Serif:400italic' rel='stylesheet' type='text/css'>
</head>
<body>
<div class="navigation">
<form action="index.php" method="get">
<input type="submit" name="mainpage" value="Main Page" class="submitbut" id="but1" />
</form>
</div>
<form action="index.php" method="post">
<input type="text" name="search" id="searching" />
<input type="submit" name="data_submit" value="Search" id="scan" />
</form>
<?php
if( isset( $_GET['mainpage'] ) ) exit( header( "Location: mainpage.php" ) );
if ( isset( $_POST["data_submit"] ) ){
$search_term = strip_tags( trim( $_POST['search'] ) );
$conn = new PDO( 'mysql:host=localhost;dbname=u1358595', 'root' );
$stmt = $conn->prepare("SELECT * FROM `hotel` h
INNER JOIN `booking` b ON h.`hotel_id`=b.`hotel_id`
INNER JOIN `guest` g ON g.`guest_id`=b.`guest_id`
WHERE `name` LIKE :search_term;");
$stmt->bindValue(':search_term','%' . $search_term . '%');
$stmt->execute();
echo "
<table>
<tr>
<th>Hotels Matched</th>
</tr>";
while($hotel = $stmt->fetch()) {
echo "
<tr>
<td><a href='details.php?name=".$hotel['name']."'>".$hotel['name']."</a></td>
</tr>";
}
echo "</table>";
$stmt = $conn->prepare("SELECT * FROM `guest` g
INNER JOIN `booking` b ON g.`guest_id`=b.`guest_id`
INNER JOIN hotel ON b.`hotel_id`=h.`hotel_id`
WHERE g.`last_name` LIKE :search_term;");
$stmt->bindValue(':search_term', '%' . $search_term . '%');
$stmt->execute();
echo "
<table>
<tr>
<th>Guests Matched</th>
</tr>";
while($hotel = $stmt->fetch()) {
echo "
<tr>
<td><a href='details.php?name=".$hotel['first_name']."'>".$hotel['last_name']."</a></td>
</tr>";
}
echo "</table>";
$conn = NULL;
}
?>
</body>
</html>
With PDO Prepared statements with LIKE prepare FULL literal first.See PDO Wiki ie.
$name = "%$name%";
I have simplified your code using one query. I have tested it on 2 tables, you will need to JOIN other table
<!DOCTYPE html>
<html>
<head>
<title>Database</title>
</head>
<body>
<form action="index.php" method="post">
<input type="text" name="search" id="searching" />
<input type="submit" name="data_submit" value="Search" id="scan" />
</form>
<?php
$host= "localhost";
$username="XXXX";
$password="XXXX";
$database="XXXX";
function writeTable($host,$database, $username, $password,$search_term) {
//Create query
$sql = "SELECT hotel.name AS hotel, guest.name AS guest
FROM `hotel`
LEFT JOIN `guest` ON hotel.guest = guest.id
WHERE hotel.name LIKE ?
OR guest.name LIKE ?
";
$html = '<table cellpadding="1" cellspacing="1">'. "
";
//array for column names
$columnNames = array("hotel","guest");
//table header
$html .= '<tr>';
foreach ($columnNames as $value){
$html .= '<th>' . $value . '</th>';
}
$html .= '</tr>'. "
";
// connect to the database
$db = new PDO("mysql:host=$host;dbname=$database", $hotelname, $password);
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
//Prepare and execute query
$stmt = $db->prepare($sql);
$stmt->execute(array($search_term,$search_term));
// setting the fetch mode
$stmt->setFetchMode(PDO::FETCH_ASSOC);
//Add content
while($row = $stmt->fetch()) {
$html .= '<tr>';
$html .= '<td>' . $row['hotel'] . '</td>';
$html .= '<td>' . $row['guest'] . '</td>';
$html .= '</tr>'. "
";
}
$html .= '</table>';
echo $html;
// close the connection
$dbh = null;
}
$search = strip_tags(trim($_POST['search'] ) );
if(isset($search) ){
if ($search != ''){
$search_term = '%'.$search.'%';
}else{
$search_term ='';
}
}
writeTable($host,$database, $hotelname, $password,$search_term);
?>
You should be able to modify to suit.