-1

I'm trying to do a search form where you can search in multiple database tables, and its working when i search in only one table but when i try with two tables im getting a "Undefined index" error.

This is the html code :

        <table class="table table-striped">
        <tr>
            <th>Customer Name</th>
            <th>Email</th>
            <th>Address</th>
            <th></th>
        </tr>

        <?php print("$searchoutput"); ?>


        </table>

And this is the php code above html tag. The code for searching is in the second php tag:

 <?php include("includes/database.php"); ?>
 <?php

$query = "SELECT 
        costumers.id,
        costumers.first_name,
        costumers.last_name,
        costumers.email,
        costumer_addresses.address,
        costumer_addresses.address2,
        costumer_addresses.city,
        costumer_addresses.state,
        costumer_addresses.zipcode
        FROM costumers 
        Inner Join costumer_addresses
        on costumer_addresses.costumer=costumers.id
        order by join_date DESC";

$result=$mysqli->query($query) or die ($mysqli->error.__LINE__);

?>
<?php 
    $searchoutput='';
    if(isset($_POST['search'])){
    $searchq=$_POST['search'];
    $searchq=preg_replace("#[^0-9a-z]#i", "", $searchq);

   $query = mysqli_query($mysqli,"SELECT * FROM costumer_addresses WHERE address LIKE '%$searchq%' OR address2 LIKE '%$searchq%' OR city LIKE '%$searchq%' OR state LIKE '%$searchq%' 
        UNION 
        SELECT * FROM costumers WHERE first_name LIKE '%".$searchq."%' OR last_name LIKE '%".$searchq."%' OR email LIKE '%".$searchq."%' ") OR die($mysqli->error.__LINE__);


    $count = mysqli_num_rows($query);
    if($count == 0){
        $searchoutput = 'There was no search results!';
    }else{
        while($row = mysqli_fetch_array($query)){
            $first_name=$row['first_name'];
            $last_name=$row['last_name'];
            $email=$row['email'];
            $address=$row['address'];
            $address2=$row['address2'];
            $city=$row['city'];
            $state=$row['state'];

            $searchoutput.='<tr>';
            $searchoutput.='<td>'.$row['first_name'].' '.$row['last_name'].'</td>';
            $searchoutput.='<td>'.$row['email'].'</td>';
            $searchoutput.='<td>'.$row['address'].' '.$row['city'].' '.$row['state'].'</td>';
            $searchoutput.='<td><a href="edit_customer.php?id='.$row['id'].'" class="btn btn-default btn-sm">Edit</a></td>';
            $searchoutput.='</tr>';


        }
    }
}
?>
Ardit Imeri
  • 55
  • 9
  • Where specifically are you getting the error? What index are you looking for and why do you expect it to be there? – David Apr 17 '18 at 09:50
  • I'm getting the error in while loop because its only accepting first_name,last_name and email from the first table – Ardit Imeri Apr 17 '18 at 09:55
  • The query being executed is `SELECT * FROM costumers ...`. Does `customers` *have* an `address` column? The error implies that it doesn't. And the first query (which you execute and then ignore) also implies that it doesn't. It sounds like your second query should be modeled after your first query in terms of table joins. – David Apr 17 '18 at 09:59
  • I have two tables in database `costumers` and `costumer_addresses` and i have a foreign key of `costumers id` in `costumer_addresses` table – Ardit Imeri Apr 17 '18 at 10:16
  • When you execute your query manually on the database, what are the results? `UNION` and `JOIN` are two *very* different things, but your two queries attempt to use them interchangeably. Your first query has the `JOIN` syntax you're looking for, why don't you use that? – David Apr 17 '18 at 10:29
  • I used `INNER JOIN` instead of `UNION` and its working now! Thank you, have a nice day. – Ardit Imeri Apr 17 '18 at 12:40

1 Answers1

0

Please use 

$query = mysqli_query($mysqli,"SELECT * FROM costumers where first_name like '%$searchq%' or last_name like '%$searchq%' or email like '%$searchq%' 
    UNION 
    SELECT * FROM costumer_addresses where address like '%".$searchq."%' or address2 like '%".$searchq."%' or city like '%".$searchq."%' or state like '%".$searchq."%' ") or die($mysqli->error.__LINE__);

instead of 

  $query = mysqli_query($mysqli,"SELECT * FROM costumers where first_name like '%$searchq%' or last_name like '%$searchq%' or email like '%$searchq%' 
    UNION 
    SELECT * FROM costumer_addresses where address like '%$searchq%' or address2 like '%$searchq%' or city like '%$searchq%' or state like '%$searchq%' ") or die($mysqli->error.__LINE__);
Md Nazrul Islam
  • 363
  • 4
  • 13
  • I edited just like you but im getting the same error :( – Ardit Imeri Apr 17 '18 at 10:25
  • $mysqli->query("SELECT * FROM costumers where first_name like '%$searchq%' or last_name like '%$searchq%' or email like '%$searchq%' UNION SELECT * FROM costumer_addresses where address like '%".$searchq."%' or address2 like '%".$searchq."%' or city like '%".$searchq."%' or state like '%".$searchq."%' "); – Md Nazrul Islam Apr 17 '18 at 12:41