MYSQL Query in PHP

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.Attachment 55387

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

Yes, joins are what you need. And I'd start from the manual ;)

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.

Code:

---

"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 :)

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"

Try it without the brackets around the first join:

Code:

---

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.

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.

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'"

---

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 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 )

---

Quote:

---

Originally Posted by Jordan_B

I have now added "asset." to each of the fields I wish to select and I am now getting a result.

---

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

Quote:

---

Originally Posted by Jordan_B

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.

---

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

PHP Code:

---

$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 :)

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.