Member Avatar for Ebiso

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
  • 409 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by smantscheff

Dani AI

Generated

Quick diagnosis and concise fixes.

Two things are combining to hide the home address: the subquery (the IN(...) that itself joins sites) restricts the customer set to those that already have a sites row, and the use of SELECT * across joined tables makes it easy to confuse which column came from which table. is right that the IN(subquery) is redundant—move the search condition into the main WHERE—and use a LEFT JOIN to include addresses that do not have a matching sites row.

Example of a clearer, safer query (use prepared statements rather than interpolating variables):

SELECT
  c.ID AS customer_id, c.First_Name, c.Surname,
  a.ID AS address_id, a.line1, a.town, a.postcode,
  s.ID AS site_id, s.notes
FROM customer AS c
JOIN address AS a ON a.customer_ID = c.ID
LEFT JOIN sites AS s ON s.address_ID = a.ID
WHERE UPPER(c.First_Name) LIKE CONCAT('%', UPPER(?), '%');

Practical notes and troubleshooting:

  • LEFT JOIN on sites returns addresses that have no site record; INNER JOIN drops them.
  • Always list and alias columns instead of SELECT * to avoid name collisions (multiple ID columns will overwrite keys in associative fetches).
  • Validate the dynamic column name ($field) against a whitelist to prevent SQL injection; use mysqli or PDO prepared statements.
  • For debugging: run the query without the sites join to confirm all addresses exist, check mysql_num_rows (or its mysqli/PDO equivalent), and ORDER BY a.ID to see each address row in a predictable order.
  • If addresses still missing, verify address.customer_ID and sites.address_ID values in the data.
Member Avatar for smantscheff

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%'
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.