php - return multiple values to from mysql

Question

I'm dealing with a junction table for a many to many relationship between products and categories. With this particular function I'm attempting to take in a product_ID variable and get all associated category names in an array. However, with the current setup I'm only getting one category per product ID when I know several have 2. Any thoughts? Also anyone know of a good simple solution to more visually keeping track of M2M relationships in mysql?


    //INPUT PRODUCT ID, GET ASSOCIATED CATEGORIES
    function productcat($pid) {
        global $sql;
        $query = "SELECT * FROM Product_Category WHERE Product_ID = '$pid'";
        $result = mysqli_query($sql, $query);
        $catidarray = array();
        while($row = mysqli_fetch_array($result)) {
                array_push($catidarray, $row['Category_ID']);
        }

        foreach($catidarray as $i) {
            $query = "SELECT * FROM Categories WHERE Category_ID = '$i'";
            $result = mysqli_query($sql, $query);
            $namearray = array();
            while($row = mysqli_fetch_array($result)) {
                array_push($namearray, $row['Name']);
            }

        }

        return $namearray;
    }

you have table product and table category as many to many? then how many tables does it comprise in your database in relation to those tables? — Vainglory07
I'm pretty new to both php and mysql. But my table setup is pretty simple I have: Categories: with Category_ID and several other fields Products: with Product_ID and several other fields Product_Category (Junction Table) with Product_Category_ID, and Product_ID/Category_ID indexing those fields in the respective Product and Category tables — Dustin Maxey

Vainglory07 Vainglory07 · Accepted Answer · 2014-02-15T06:03:49

There is something wrong with your function.

In your second foreach, you put the variable $namearray inside the loop which on every run resets its value to empty array $namearray = array();. Just put that outside the foreach:

//--> put it here
$namearray = array();
foreach($catidarray as $i) {
    $query = "SELECT * FROM Categories WHERE Category_ID = '$i'";
    $result = mysqli_query($sql, $query);

    while($row = mysqli_fetch_array($result)) {
        array_push($namearray, $row['Name']);
    }
}

And, I just want to make some suggestions on your function. Since you have the junction table, you don't really need to have a separate query for product and category in order to get your desired values from those tables.

Just do the INNER JOIN to maximize the use of your table relations.

Use your junction table Product_Category because that's the real purpose why created that.

SELECT *
FROM Product_Category AS a
INNER JOIN Category AS b
ON a.Category_ID = b.Category_ID
WHERE Product_ID = $pid

In your function, you may try this: havent tested but hope this will give you idea.

//INPUT PRODUCT ID, GET ASSOCIATED CATEGORIES
function productcat($pid) {
    global $sql;
    $query = "SELECT *
              FROM Product_Category as a
              INNER JOIN Category as b
              ON a.Category_ID = b.Category_ID
              WHERE Product_ID = {$pid}";
    $result = mysqli_query($sql, $query);
    $namearray = array();
    while($row = mysqli_fetch_array($result)) {
            array_push($catidarray, $row['Name']);
    }
    return $namearray;
}

Then that's it :)

And by the way, in the latest version of php, mysql_ functions are already deprecated. Much better if you will be going to use PDO or MySQLi. Check this also: PDO vs. MySQLi

php - return multiple values to from mysql

1 Answers