Member Avatar for Ryan_11

I have a table that logs customer data but i want to search for the customer, upon finding that customer i want to click on the name and find it in another table that store other data. But here is where i am stuck i want to check which table the record is in and then display it. Is there anyway to do that. like using an IF statement or case. The criteria would be based on equipment type as the two other table stores data based on the type of equipment. Any help or reference would be good

  • 3 Contributors
  • 2 Replies
  • 210 Views
  • 12 Minutes Discussion Span
  • Latest Post Latest Post by tapananand

Dani AI

Generated

Good summary of the problem from and useful nudges from and about missing details. The simplest, most maintainable answer is to record which equipment type (or a foreign key to the equipment row) on the customer record so the application can immediately pick the correct detail table. If the schema cannot be changed, a few safe options follow.

A common, explicit approach using joins (works when a customer can only have one equipment row per type):

SELECT c.*, a.field1 AS a_f1, b.field1 AS b_f1
FROM customers c
LEFT JOIN equip_a a ON a.customer_id = c.id
LEFT JOIN equip_b b ON b.customer_id = c.id
WHERE c.id = :id;

In the result check which equip column is not NULL and render that set of fields.

An alternative that scales when many equipment tables exist is UNION (returns rows with a tag indicating the source table):

SELECT 'A' AS equip_type, a.* FROM equip_a a WHERE a.customer_id = :id
UNION ALL
SELECT 'B' AS equip_type, b.* FROM equip_b b WHERE b.customer_id = :id;

Then in PHP (PDO) fetch rows and dispatch on equip_type. For fast existence checks before a full fetch, use a lightweight SELECT 1 FROM <table> WHERE customer_id = :id LIMIT 1 per candidate table.

Important notes: always use prepared statements; never build table names from raw user input (if dynamic, use a whitelist map); index customer_id on each equipment table; expect multiple rows if a customer can have multiple equipment entries and handle accordingly. For long-term simplicity and performance, consolidate equipment into a single table with an equipment_type column or add a linking table so the app never has to probe multiple schema locations.

Recommended Answers

All 2 Replies

Member Avatar for naui95

It isn't clear what are you asking for... at least i didn't get it.

Member Avatar for tapananand

Your Question is not clear. Describe your situation clearly and patiently.

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.