MYSQL Query in PHP

Databases
1

Hi,
I have 3 tables

  1. asset
  2. monitoring
  3. protocols

Each asset can have monitoring setup and each monitoring type can have protocols setup.

I need to select all the assets that have monitoring setup of type ‘calibration’ and also that have protocols setup for the monitoring type.

Asset is linked to monitoring by ‘asset_id’
Monitoring is linked to protocols by ‘monitor_id’

I have attached a diagram of my tables that might help.

I think I need to build the query using joins but I am not sure where to even start.

Any help or suggestions would be great.

Thanks

2

Yes, joins are what you need. And I’d start from the manual :wink:

3

Thanks for your reply.

This is what I have come up with…but still not working. And not sure how to check if protocols have been setup for the monitoring type.

"SELECT asset_id, asset_date, asset_num, asset_serialNum, asset_name, asset_description, asset_type, asset_department, asset_owner
 FROM 
  (asset INNER JOIN monitoring
   ON asset.asset_id = monitoring.asset_id)
  INNER JOIN protocols
   ON monitoring.monitor_id = protocols.monitor_id 
 WHERE monitor_type='callibration'"

Gret if anyone has any more help or nudges in right direction :slight_smile:

4

sorry, “still not working” is not a valid mysql error message

could you elaborate on what’s happening please

p.s. are you perhaps getting 0 results? check the spelling of “callibration”

5

Try it without the brackets around the first join:


SELECT 
    asset_id
  , asset_date
  , asset_num
  , asset_serialNum
  , asset_name
  , asset_description
  , asset_type
  , asset_department
  , asset_owner
FROM asset 
INNER JOIN monitoring
ON asset.asset_id = monitoring.asset_id
INNER JOIN protocols
ON monitoring.monitor_id = protocols.monitor_id 
WHERE monitor_type = 'callibration'

And please be more specific when it “does not work”. Is there an error message, if so what is it? Does it give a result, but not the one you want? If so, give us the result and explain why it isn’t what you want.

6

Sorry for my lack of info and thanks for your replies.
I was getting no results.
I have now added “asset.” to each of the fields I wish to select and I am now getting a result. The result is the same asset repeated 8 times. The asset listed is the correct asset. I have checked and the monitoring type has 8 protocols. The result seems to be showing the asset the same amount of times as protocols that are setup.

Removing the brackets does not make a difference to the result.

I also see that spelling of calibration is incorrect but it is the spelling I have in the database at the moment. I need to edit my insert query when setting up monitoring with the correct spelling.

Also when I remove the protocols join I get a correct result. (just shows 2 assets with calibration monitoring setup).

Here is my updated code.

"SELECT asset.asset_id, asset.asset_date, asset.asset_num, asset.asset_serialNum, asset.asset_name, asset.asset_description, asset.asset_type, asset.asset_department, asset.asset_owner
 FROM 
  (asset INNER JOIN monitoring
   ON asset.asset_id = monitoring.asset_id)
  INNER JOIN protocols
   ON monitoring.monitor_id = protocols.monitor_id 
 WHERE monitor_type='callibration'"
7

If you check for mysql errors in your PHP code, it will be easier to understand why you don’t get results:


$query = "... put your query here ...";
$result = mysql_query($query) or die("mysql error "  . mysql_error() . " in query $query ");

It may not be the most elegant way to handle mysql errors, but it works just fine when you are developing and debugging :slight_smile:

8 
SELECT asset.asset_id
     , asset.asset_date
     , asset.asset_num
     , asset.asset_serialNum
     , asset.asset_name
     , asset.asset_description
     , asset.asset_type
     , asset.asset_department
     , asset.asset_owner
  FROM monitoring
INNER
  JOIN asset
    ON asset.asset_id = monitoring.asset_id)
 WHERE monitor_type = 'callibration'
   AND EXISTS
       ( SELECT 'booyah'
           FROM protocols
          WNERE monitor_id = monitoring.monitor_id )

you can see the effect this has in the query…asset.asset_this, and asset.asset_that… illustrating once again that it is not a good idea to embed the table name into the column name, because of the “noise” this adds to the query

9

This is working for me now. Thanks for all the help.

@r937
I see your point. The query would be a lot easier to read and to write without the table name included in the column name.

@guido2004
Thanks for the tip. Very handy when developing and debugging.

I am slowly building up “best practices” as I get more experience developing. Thanks for the help.