Agni Scribe Agni Scribe - 5 years ago 184
PHP Question

PHP: Issues with querying multiple tables

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
(specifically the line:
if ( ! $query->num_rows )
) gives
the error:
Notice: Trying to get property of non-object in function.php line 18
.

I think this has to do with how the array is to be accessed now that
it holds rows from multiple tables?

2. Once I do get these values, am unsure how to extract the field information from the different tables. As long as there was one table, this foreach loop was fine:

foreach($_SESSION['search_output']['results'] as $value){
echo $value->title;
echo"<br>";
}


However how would one access the differently named fields when there are multiple tables. I was thinking of switch-case, but how to I get the table name using the procedural approach?

Would appreciate any help in understanding how to approach this.

Following are the files

function.php

<?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;
}
?>


index.php

<?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>


final.php

<?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>


setup.php

<?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);
?>

Answer Source
  1. 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.

  2. 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.

  3. Please note that when you use UNION statement all fields in every SELECT must have the same number of columns! Read more.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download