SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2010
    Location
    Ireland
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.DB Tables.jpg

    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Yes, joins are what you need. And I'd start from the manual

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2010
    Location
    Ireland
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    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.

  6. #6
    SitePoint Enthusiast
    Join Date
    Jun 2010
    Location
    Ireland
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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'"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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 View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Jordan_B View Post
    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

  9. #9
    SitePoint Enthusiast
    Join Date
    Jun 2010
    Location
    Ireland
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •