URGENT: Implementing search with multiple dissimilar MySQL tables

[abhishekgoogle]

Hi,

This is Abhishek. I am a newbie in PHP and we have to develop an Inventory management application. We have a MySQL database structure which looks like this (dissimilar)

VORTEX

Table 1: hardware
==========================================
hwid | total | manufacturer | hardware type | model no | specs | remarks|
==========================================

Table 2: assetserial
===========================================
assetid | serialnum | hwid
===========================================

Table 3: assetowner
============================================
assetid | username | locid
============================================

Table 4: userloc
=============================================
username | locid | country | city | site | cubicle |
=============================================

I have to implement search facility in this database wherein the user interface will just contain a text box wherein he types a string and fetch complete details from the database.

For eg: If someone searches for "abhishek" it must search for abhishek in all the tables and suppose it finds abhishek in the "userloc" table then it should be able to display abhishek's location from userloc, what assets abhishek possesses from assetowner, what are the serialnumbers of those assets fom assetserial and what are the hardware types of those assets (i.e. monitors/desktops/workstations...etc..) from the hardware table.

I dunno how to link the tables because they are dissimilar and also the fact that fields like username, locid,hwid etc are repeated in the 4 tables... so even if I actually search in each table it might find multiple instances of abhishek in userloc and assetowner....

Please help!!! Thanks in advance

Abhishek

[campkev]

it's will be verydifficult to search every field of every table for the input.
I suggest doing something like a drop down box with which fields to search and then a text box with what to search for. Then, you can setup your query based on the input.

[abhishekgoogle]

it's will be verydifficult to search every field of every table for the input.
I suggest doing something like a drop down box with which fields to search and then a text box with what to search for. Then, you can setup your query based on the input.

As such all the data is dependent and related but still the database structure is isolated with respect to the features we are providing in the application.... like for example:
When purchase order is placed, table 1 is only updated
When equipment is tagged and bonded the table 2 is updated
When eqpt is issued to user table 3 is updated
When location is reqd or there are internal moves then only table 4 is updated.
Keeping all this in view the database was designed.

Now the most probable options the end user might search for username and want to know his corresponding location, what assets he has, what are the serial numbers of these assets and the hardware type i.e. desktop, monitor etc...

can u give me some pointers on how to go a about implementing the dropdown scheme and how to populate dropdown... Is is good to write cases like first search in all four tables and then depending on results find in rest 3, then rest 2 and finally one...

Regards
Abhishek