My search function works well when it queries just a single table. But when I query more than one table in the same database (using 'UNION'), am faced with 2 issues:
1.
$query
if ( ! $query->num_rows )
Notice: Trying to get property of non-object in function.php line 18
foreach($_SESSION['search_output']['results'] as $value){
echo $value->title;
echo"<br>";
}
<?php
function search($conn, $search_term) {
$sanitized = $conn->real_escape_string($search_term);
$query = $conn->query("(SELECT * FROM news WHERE title LIKE '%{$sanitized}%' OR body LIKE '%{$sanitized}%' OR sources LIKE '%{$sanitized}%'
OR date LIKE '%{$sanitized}%')
UNION
(SELECT * FROM events WHERE eventname LIKE '%{$sanitized}%' OR eventsumm LIKE '%{$sanitized}%' OR eventdate LIKE '%{$sanitized}%')
UNION
(SELECT * FROM recipes WHERE rectitle LIKE '%{$sanitized}%' OR recsummary LIKE '%{$sanitized}%')
UNION
(SELECT * FROM reviews WHERE bookname LIKE '%{$sanitized}%' OR revbody LIKE '%{$sanitized}%' OR revsource LIKE '%{$sanitized}%')
UNION
(SELECT * FROM foodiq WHERE iqitem LIKE '%{$sanitized}%' OR iqbody LIKE '%{$sanitized}%')");
if ( ! $query->num_rows ) {
return false;
}
$rows = array(); // ADD THIS
while( $row = $query->fetch_object() ) {
$rows[] = $row;
}
$search_results = array(
'count' => $query->num_rows,
'results' => $rows
);
return $search_results;
}
?>
<?php require_once ('setup.php');
require_once ('function.php');
if (session_id() === "") { session_start(); }
?>
<!DOCTYPE html>
<html>
<body>
<?php
if ( isset( $_GET['s'] ) ) {
$search_results='';
$search_term = htmlspecialchars($_GET['s'], ENT_QUOTES);
$search_results = search($conn, $search_term);
}
?>
<form role="search" method="get" class="search-form" action="">
<label>
<input type="search" class="search-field" placeholder="Search" value="searchitem" name="s">
</label>
<input type="submit" class="search-submit" value="Search">
</form>
<?php if (isset($search_results)) :
print_r($search_results); exit;
$_SESSION['search_output'] = $search_results;
header("Location: final.php");
exit;
endif; ?>
<body>
</html>
<?php
session_start();
?>
<!DOCTYPE html>
<html>
<body>
<?php
if(isset($_SESSION['search_output']) && is_array($_SESSION['search_output']))
{
echo $_SESSION['search_output']['count']. " result(s) found <br>";
foreach($_SESSION['search_output']['results'] as $value){
echo $value->title;
echo"<br>";
}
}
else
echo "Sorry, no results found";
?>
<body>
</html>
<?php
$hn = 'localhost';
$db = 'kkh';
$un = 'water';
$pw = 'water';
#DB Connection:
$conn = new mysqli($hn, $un, $pw, $db);
if ($conn->connect_error) die($conn->connect_error);
?>
YOU SHOULD check if there any connection errors like right after ->query
:
/* check error */
if ($conn->error) {
printf("Query failed: %s\n", $conn->error);
exit();
}
Please not it's not the same as $conn->connect_error
.
You may access your fields using field names of your 1st table in union statement.
Let's assume the table news
has these fields: id
, title
, author
, date_created
. Then simply use $row->id
, $row->title
, etc.
To know which table is related with a relevant row you may want to use something like:
SELECT *, 'news' AS source FROM news ....
UNION
SELECT *, 'events' AS source FROM events ...
and then $row->source
will contain your table.
Please note that when you use UNION
statement all fields in every SELECT
must have the same number of columns! Read more.