Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Oct 2009
    Location
    United States
    Posts
    157
    Thanks
    8
    Thanked 4 Times in 4 Posts

    MySQL JOIN multiple tables with related (but different) data, or multiple queries

    Hello everyone,

    Currently I am working on a query that selects from multiple tables to assemble the result. I'm not sure if I can do it the way I am trying to do it, or if I will need to (or if it's better to) switch to multiple queries and then assemble an array that way.


    Tables I'm working with (shown with key constraints only):

    Monsters (monsterID)
    Monster Abilities (monsterID, abilityID)
    Ability Effects (abilityID, effectID)
    Effects (effectID)

    With these tables, I'm trying to get a result back like so:

    PHP Code:
    array (
          [
    0] => array (
                [
    monsterID] => 1
                
    [abilities] => array( 
                                      [
    0] => array(
                                                  [
    abilityID] => 1
                                                   
    [effects] => array(
                                                                       [
    effectID] = 1
                                                                    
    )
                                               )
                                  )
                    )
         [
    1] => array ( etc.... )

    I know I can use group_concat to get the list of abilities.. like so:

    PHP Code:
    SELECT monsters.monsterIDGROUP_CONCAT(abilities.abilityID) AS `abilities`
    FROM `monsters`
    LEFT JOIN `monsterabilitiesON monsters.monsterID monsterabilities.monsterID
    LEFT JOIN 
    `abilitiesON monsterabilities.abilityID abilities.abilityID
    GROUP BY monsters
    .monsterID 
    Which Returns:

    PHP Code:
    [0] => Array
            (
                [
    monsterID] => 1
                
    [abilities] => 1,2
            

    When it came to getting effects for the abilities, I seemed to run in to problems. So, I'm wondering if it is possible to do it in one query, and if so, would it be better than a few single queries? If a single query is possible and worth it, could you point me to resources on how it'd be done?

    Thank you all for your time,
    ~Votter
    Last edited by votter; 04-03-2014 at 09:59 PM.
    Adobe Dreamweaver shall be destroyed!

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,279
    Thanks
    23
    Thanked 611 Times in 610 Posts
    PHP Code:
    <?php
    /*    First open connection   */
    $DBhost "localhost";
    $username "my_user";
    $password "my_password";
    $database "world";
    $mysqli = new mysqli($DBhost,$username,$password,$database);

    /*   Then form the query   */
    $query "DELETE a FROM wp_posts 
    AS a INNER JOIN ( SELECT post_content, MIN( id ) AS min_id FROM wp_posts 
        WHERE post_type = 'post' 
        AND post_status = 'publish' 
        GROUP BY post_content 
        HAVING COUNT( * ) > 1 )
    AS b ON b.post_content = a.post_content 
    AND b.min_id <> a.id 
    AND a.post_type = 'post' 
    AND a.post_status = 'publish'"
    ;

    /*    Then exicute the query    */
    $results mysqli_query($query);



    $query "DELETE FROM wp_posts
    WHERE post_content NOT LIKE '%<img src=%'
    AND ID not in (select post_id as p from wp_postmeta where meta_key like '_wp_attached_file')
    OR `post_title` like '%.png%'
    OR post_date < DATE_SUB(NOW(), INTERVAL 15 day)"
    ;
    $results mysqli_query($query);
    ?>
    Evolution - The non-random survival of random variants.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."

  • #3
    Regular Coder
    Join Date
    Oct 2009
    Location
    United States
    Posts
    157
    Thanks
    8
    Thanked 4 Times in 4 Posts
    Quote Originally Posted by sunfighter View Post
    PHP Code:
    <?php
    /*    First open connection   */
    $DBhost "localhost";
    $username "my_user";
    $password "my_password";
    $database "world";
    $mysqli = new mysqli($DBhost,$username,$password,$database);

    /*   Then form the query   */
    $query "DELETE a FROM wp_posts 
    AS a INNER JOIN ( SELECT post_content, MIN( id ) AS min_id FROM wp_posts 
        WHERE post_type = 'post' 
        AND post_status = 'publish' 
        GROUP BY post_content 
        HAVING COUNT( * ) > 1 )
    AS b ON b.post_content = a.post_content 
    AND b.min_id <> a.id 
    AND a.post_type = 'post' 
    AND a.post_status = 'publish'"
    ;

    /*    Then exicute the query    */
    $results mysqli_query($query);



    $query "DELETE FROM wp_posts
    WHERE post_content NOT LIKE '%<img src=%'
    AND ID not in (select post_id as p from wp_postmeta where meta_key like '_wp_attached_file')
    OR `post_title` like '%.png%'
    OR post_date < DATE_SUB(NOW(), INTERVAL 15 day)"
    ;
    $results mysqli_query($query);
    ?>
    Wrong forum? hehe
    Adobe Dreamweaver shall be destroyed!

  • #4
    Regular Coder
    Join Date
    Jun 2009
    Posts
    144
    Thanks
    3
    Thanked 20 Times in 20 Posts
    Mysql doesnt output multi-dimensional arrays. You can have it out put that information as one result set with all of the required information, but you will have to use PHP to input it into that format.

    Move you concat gtoup into your join, and once you get everything you need to create the array use PHP to do it.

  • Users who have thanked itxtme for this post:

    votter (04-05-2014)

  • #5
    Regular Coder
    Join Date
    Oct 2009
    Location
    United States
    Posts
    157
    Thanks
    8
    Thanked 4 Times in 4 Posts
    Thanks for the response and pointing me in the right direction! I think I've figured it out with your help. Thanks again!
    Adobe Dreamweaver shall be destroyed!


  •  

    Posting Permissions

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