#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    451
    Rep Power
    8

    Ordering db data


    Hi,

    I was wondering if there's a way to order the data retrieved from the db. If I have these tables:

    Code:
    TABLE 1
    item_id		name		category
    1		item1		2
    2		item2		1
    3		item3		2
    
    TABLE 2
    category_id	name		category
    1		cat1		3
    2		cat2		3
    3		maincat1	NULL
    I want to get an array like this:
    Code:
    Array (
    [category_id] => Array ( [item_id] => value
    			,[item_name] => value
    			,[category_name] => value)
    )
    Is this even possible without making a custom php function? It could be done in php but if there's a cleaner method, please tell me
    Last edited by derplumo; January 22nd, 2014 at 03:38 PM.
  2. #2
  3. Jealous Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,302
    Rep Power
    9400
    Not in just a query. You know how to do it in code?
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    451
    Rep Power
    8
    yes something like this I think:

    PHP Code:
    <?php
    $arr1_stmt 
    $db->prepare('
        SELECT *
        FROM table1
    '
    );
    $arr1_stmt->execute();
    $arr1 $arr1_stmt->fetchAll();

    $arr2_stmt $db->prepare('
        SELECT *
        FROM table2
    '
    );
    $arr2_stmt->execute();
    $arr2 $arr2_stmt->fetchAll();

    foreach(
    $arr1 as $row1) {
        
    $arr3[$row1['category']] = array('item_id' => $row1['item_id']
                                        ,
    'item_name' => $row1['name]
                                        ,'
    category_name' => $arr2[$row1['category']]['category']
    }


    ?>
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    You definitely need to read up on SQL basics like joins. This is how you associate tables in a relational database.

    When you have a constant query without any external input, a prepared statement is useless. There are no parameters you could pass to it. So just use query().

    Besides that, there are still a lot of open questions:

    • Do you really want an array or just loop through the rows?
    • What is the strange "category" column in the category table supposed to mean? Is it the parent category? Then why is it filled with names as opposed to category IDs?
    • What if a category has no items?

    And most importantly: Is this really your current database setup with all the right names, or did you make it up for us? Because the problem of made up examples is that we often end up fixing the same problem twice: Once for the demo data and then again for the real data. Better use the real data from the start.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    451
    Rep Power
    8
    Oops, yes the 'maincategory' column should be an ID... Was tired I guess, sorry

    So just use query()
    Of course.

    Do you really want an array or just loop through the rows?
    I want to display various items in different sections, each one with items from the category. So I guess looping would be good too?

    What if a category has no items?
    Then it will not be displayed.

    we often end up fixing the same problem twice
    I'll do the next time, but the only difference from my tables are the rows. But the thing is that I wanted to know if there was a better way to do this.
  10. #6
  11. hiding my <b> from ur <strong>
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2004
    Posts
    959
    Rep Power
    181
    Short answer is that you can't create multi-dimensional or associative arrays using an element's value right out of the gate w/ mysql, so you're doing what everyone else would do.

    You'll find yourself doing this a lot, however, so you're probably going to want to make yourself a reusable function for this. Here's a helper function I created/use that will use a particular element value as an array one or two keys deep:

    PHP Code:
    /**
     * Associate
     *
     * Returns array keyed to items specified.
     *
     */
    if ( ! function_exists('assoc')){
        function 
    assoc(&$array,$key,$key2=false) {
            
    // cast as array if an object is being passed
            
    if(is_object($array)){
                
    $array = (array)$array;
            }
            
    $newarray = array();
            
    // only one key
            
    if(!$key2){
                foreach (
    $array as $values){
                    
    // cast as array if an object of values is being passed
                    
    if(is_object($values)){
                        
    $values = (array)$values;
                    }
                    
    $newarray[$values[$key]] = $values;
                }
            
    // or two keys
            
    } else {
                foreach (
    $array as $values){
                    if(
    is_object($values)){
                        
    $values = (array)$values;
                    }                
                    
    $newarray[$values[$key]][$values[$key2]] = $values;
                }
            }
            
    $array $newarray;
        }

    Last edited by daprezjer; January 19th, 2014 at 05:41 PM.
    ****
    Enjoy my post? Drop some props by hitting the scales button up top. JBL

    Website Design in Los Angeles and Washington, DC by PoweredPages.com
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    451
    Rep Power
    8
    Thanks for the function!

    I was just wondering if there could be something that was better or easier, but apparently there's none.
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    The relational model is table-based, so if you want anything other than tables, there has to be some kind of conversion.

    But that doesn't mean you have to do it. And nobody forces you to use the relational model in the first place.

    There are object-relational mappers like doctrine which take care of all the low-level queries and wrap the data in convenient objects. If you don't like tables at all, consider switching to a different database model. MySQL isn't the only option. For example, there's the document-oriented MongoDB. It might fit your needs and preferences more than a table-based database.

    But of course each of these options come with a learning curve. You have to weigh the short-term advantages of plain MySQL (easy to learn, everybody knows it, even the crappiest freehoster supports it) against the possible long-term disadvantages (lots of boilerplate code, cumbersome low-level queries, not necessarily appropriate for every application).
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  16. #9
  17. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    451
    Rep Power
    8
    Thanks for noting those, but I stay with the Mysql
  18. #10
  19. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    451
    Rep Power
    8
    sorry man, your function doesn't work...

    I used the function above in my code like this:
    PHP Code:
    $all_items_ordened assoc($all_items,'category',false); 
    I was wondering if this could be a good practice too:

    PHP Code:
    $categories_stmt $db->prepare('   
        SELECT items.*
        FROM items
        LEFT JOIN categories ON items.category = categories.category_id
        WHERE categories.name = :name   
    '
    );   
    $categories_stmt->execute(array(
        
    ':name' => "maincat1"
    ));   
    $categories $categories_stmt->fetchAll(); 

    foreach(
    $categories as $category) {
        
    $item_information_stmt $db->prepare('   
            SELECT *
            FROM items
            WHERE category = :category 
        '
    );   
        
    $item_information_stmt->execute(array(
            
    ':category' => $category['category_id']
        ));   
        
    $item_information $item_information_stmt->fetchAll(); 
        
    $db_data[$category['category_id']] = $item_information;

    I don't think it is because it has to run the query some times so it would take some time...
  20. #11
  21. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    This pattern is extremely inefficient. You can easily see that when you look at the number of queries: Let's say you have 50 categories. Using a join, you can fetch all data in a single query. Using your approach, you suddenly need 50 queries for the same data. If you do this several times in your code, you'll quickly end up with hundreds of queries instead of just a handful. I'm pretty sure this will also kill all optimization attempts by MySQL.

    To be honest, I don't really understand you. On the one hand, you insist on using plain MySQL. Fine. But on the other hand, you do everything to circumvent it. This makes no sense.

    I think you should make a definite decision: If you want to use plain MySQL, then use it properly. The relational model is based on tables and joins, so that's what you have to deal with. If you want some other model, use an abstraction layer or a different database system. But don't stick to MySQL and at the same time break its fundamental concept.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  22. #12
  23. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,733
    Rep Power
    1959
    Might be a bit off-topic from your question.

    Shouldn't the category values in the TABLE2 be a value of the parent cat_id instead of the name? Similar to the category field in TABLE1.
  24. #13
  25. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    451
    Rep Power
    8
    Originally Posted by Jacques1
    This pattern is extremely inefficient. ... If you do this several times in your code, you'll quickly end up with hundreds of queries instead of just a handful. I'm pretty sure this will also kill all optimization attempts by MySQL.
    I thought that already but I just wanted to be sure about it... Could've figured it out -_-

    MrFujin, I edited the category in table2, I had forgot it...

    Oh yea, the function works again. Just replace
    PHP Code:
    $array $newarray
    with
    PHP Code:
    return $newarray
    Last edited by derplumo; January 22nd, 2014 at 04:18 PM.

IMN logo majestic logo threadwatch logo seochat tools logo