PHP: Issues with querying multiple tables

Question

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

    ?>

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

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

PHP: Issues with querying multiple tables

Sign up