PHP – Displaying MySQL items according to it’s category and also getting each item’s id#. – WordPress

1 Views

I need help pulling all the information out of a MySQL table I made. I have a website in WordPress and I need to display a list of stores alphabetically according to their category. I also need to get each store’s #id out of the database so I can use it with the Advanced Custom Fields plugin to get more specific information (telephone, address, etc.) to display with each item.

The table contains a list of store ids, store names, post-name, and store category name.

Read More

The mysql database looks like this: 

[column: object_id]
185
188
etc. 

[column: post_title]
Andrew Minton Jewelers
Marshalls
etc.

[column: post_name]
andrew-minton-jewelers
marshalls
etc.

[column: name]
Apparel and Accessories
Apparel and Accessories
etc.

I have already managed to print the Category and Store Name in the format I want. 

[Category 1]
-- Store Name 1
-- Store Name 2
-- Store Name 3

[Category 2]
-- Store Name 1
-- Store Name 2
-- Store Name 3

This is the php code I used to do that: 

$query_stores = "
                            SELECT a.object_id, b.post_title, b.post_name, c.name
                            FROM wp_term_relationships a
                            INNER JOIN wp_posts b
                            ON a.object_id = b.ID
                            JOIN wp_terms c
                            ON a.term_taxonomy_id = c.term_id
                            WHERE c.term_id < 13
                            ORDER BY c.name ASC, post_title ASC";

                        $results = mysql_query( $query_stores );

                        $categories = array();

                        while($row = mysql_fetch_assoc($results)){
                            $categories[$row['name']][] = $row['post_title'];
                        }

                        // any type of outout you like
                        foreach($categories as $key => $category){

                                echo '<h3>' . $key. '</h3><ul>';
                                foreach($category as $item){
                                    echo '<li>' . $item . '</li>';
                                }
                                echo '</ul>';

                        }

What I need help doing now is getting the ‘object_id’ for each individual store out of the database. I need that id so I can grab specific information for each store using Advanced Custom Fields on WordPress.

The end result would look something like: 

[Category 1]
-- Store Name 1 | echo get_field(phone_number, object_id)
Post Views: 1

Related posts