[SOLVED] PHP QUERY TO MULTIPLE TABLES

[diesel_]

Hi All,

 

I am asking users to enter a search term this will search the database and list the matches. here is a sample of the table structure

 

ASSET TABLE CATEGORY TABLE

Asset_id Category_id

name name

Category_id

 

now my problem is i want users to be able to search the ASSET TABLE records for a category `name` that is found in the CATEGORY TABLE but my query produces a cartesian product i want to find a way of checking if ASSET TABLE is using a category `name` via the PK->FK link

 

here is my failed query

 

$query = "SELECT * FROM asset, category, manufacturer, model, location, contact, deployment, condition

WHERE asset.category_id = category.category_id

AND asset.manufacturer_id = manufacturer.manufacturer_id

AND asset.condition_id = condition.condition_id

AND asset.location_id = location.location_id

AND asset.contact_id = contact.contact_id

AND asset.deployment_id = deployment.deployment_id

AND asset.asset_id LIKE \"%$trimmed%\"

OR asset.asset_name LIKE \"%$trimmed%\"

OR category.category LIKE \"%$trimmed%\"

OR manufacturer LIKE \"%$trimmed%\"

OR model_name LIKE \"%$trimmed%\"

OR condition LIKE \"%$trimmed%\"

OR location LIKE \"%$trimmed%\"

OR contact.name LIKE \"%$trimmed%\"

OR description LIKE \"%$trimmed%\"

OR comment LIKE \"%$trimmed%\"

OR deployment.deployment LIKE \"%$trimmed%\"

order by asset_id";

 

ignore the \"%$trimmed%\" this is just the result of the search term entered by users

 

Thanks

[diesel_]

bump

 

Is this a difficult query to acheive or have i not been clear.

 

please let me know

 

thanks

[diesel_]

Can some one show me a query that would display records from one table but in one of the fields it places the integer value with the varchar from another table the two tables are linked via the FK

 

TABLE1

Asset_id

Asset_name              TABLE2

Type_id  ---------->Type_id

                            Type_name

 

so i want a query to display

 

Asset_id -- Asset_name -- Type_name

 

 

Thanks

[kevincro]

I might not be understanding what you are needing, but you might try saving the Type_id from table one in a variable and then using that variable in a "where" clause in a second query. 

[diesel_]

Hi

 

TABLE1

Asset_id

Asset_name 

Type_id           

 

TABLE2

Type_id 

Type_name

 

I need a query to use the two tables  but exclude the type_id details, for example

 

SELECT table1.asset_id, table1.asset_name, table2.type_name

FROM table1, table2

Where table1.type_id = table2.type_id

 

It gets tricky becuase i use a search field to form a dynamic query. The search field allows a user to enter a type_name but type_name is not in table1 so it searches table2. The problem i get is that if it finds a match in table2 it prints the results but i only want to print the result if the type_id is used in table1 (i.eTABLE1 asset_id =1, asset_name= packaging, type_id = 2) (TABLE2 type_id=2, type_name = MEDIUM).

 

This database is an inventory database so table2 consists of box types (i.e big, small, medium etc) so when i run a query for all medium boxes in stock i want it to print only the medium boxes in table1. But ignores the condition i awant in place.

 

Thanks

[diesel_]

The best example i can offer is if i ask people to visit

 

h t t p :// www.somesite.com / search . php

h t t p :// www.somesite.com / list . php

 

Anon'ed links per request ~ CV

[diesel_]

can the mods remove the links to the site

 

thanks