0

I am trying to create a search form for my database where a user can search for a customer name and all of that customers addresses will be displayed. my structure looks like this

Customer table

  • ID
  • First Name
  • Last name
  • Company Name

Address Table

  • ID
  • line 1
  • post code
  • town
  • Customer_ID

Site Table

  • ID
  • Address_ID
  • notes

I take either the first, last, or company name as an input($field) and store this as a variable along with which column they want to search in($query) then I use the following query to check the database for matching criteria

$data = mysql_query("SELECT *
     FROM customer 
     INNER JOIN address ON customer.ID = address.customer_ID
     INNER JOIN sites ON address.ID = sites.address_ID 
     WHERE customer.ID IN (SELECT customer.ID 
     FROM customer
     INNER JOIN address ON customer.ID = address.customer_ID 
     INNER JOIN sites ON address.ID = sites.address_ID
     WHERE upper(customer.$field) LIKE'%$query%')") ;

I print the results using

    while($results = mysql_fetch_array($data)){
            echo "<br>"; 
            echo $results['First_Name']; 
            echo " "; 
            echo $results['Surname']; 
            echo $results['town']; 
            echo " "; 
            echo $results['postcode'];

The problem occurs when one customer has multiple addresses. A home address and a site address that are different. The query will only print one of the addresses, the site address ( which is submitted second and seems to overwrite the home address)

in the address table both of these addresses contain the same Customer_ID, how can I get them to both be displayed rather than just one?

2
Contributors
1
Reply
2
Views
5 Years
Discussion Span
Last Post by smantscheff
0

Your second clause is redundant.
In effect your query reads:

select * from customers where id in (...)

This can select at most one customer per id.

Instead use:

SELECT *
FROM customer 
INNER JOIN address ON customer.ID = address.customer_ID
INNER JOIN sites ON address.ID = sites.address_ID 
WHERE upper(customer.$field) LIKE'%$query%'
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.