CODEWITHSUNDEEP
phpmysqlsqljoin
user470760
user470760

Reputation:

Using Joins to query two tables

I have two tables, "inventory" and "inventory_type". The "inventory" table stores the name of it and the type, and the "inventory_type" table stores the type name such as RAM, CPU, etc. and the sort order. I have never used JOINS before and I am not sure which type to use, but none of them seem to work with the following code.

As a side question, with my code below, would "inventory" be the left table or would "inventory_type" be joined in on the left?

function getInventoryOptions($db, $default_value, $datacenter)
{
    $query = "SELECT inventory.id, inventory.name, inventory_type.short_name
                FROM inventory LEFT JOIN inventory_type
                ON inventory.type = inventory_type.id WHERE inventory.datacenter = " . $datacenter . " ORDER BY inventory_type.sort_order ASC";

    $result = mysql_query($query, $db);

    echo '<option value="">None</option>';

    if ($result)
    {
        while($row = mysql_fetch_array($result))
        {
            $id = $row["inventory.id"];
            $name = $row["inventory.name"];
            $type = $row["inventory_type.short_name"];

            if ($default_value == $id)
            {
                echo '<option selected value="' . $id . '">' . $type . ": " . $name . '</option>';
            }
            else
            {
                echo '<option value="' . $id . '">' . $type . ": " . $name . '</option>';
            }
        }
    }
}

Upvotes: 0

Views: 71

Answers (2)

geomagas
geomagas

Reputation: 3280

With the assumption that the inventory.type column is not null (please confirm that) there's no need for a LEFT JOIN. A INNER JOIN will do the trick.

Since you only need an associative array, consider using mysql_fetch_assoc() instead of mysql_fetch_array(), or specify MYSQL_ASSOC as a second parameter.

Moreover, consider porting to mysqli or PDO altogether (see others' replies).

The returned associative array's keys are plain field names, without the table name part.

Finally, for your side question, yes, inventory would be the left table, with all its records participating in the result regardless of whether or not there's a corresponding record in the inventory_type table.

Upvotes: 0

Mark
Mark

Reputation: 8451

I think the problem lies here:

$id = $row["inventory.id"];
$name = $row["inventory.name"];
$type = $row["inventory_type.short_name"];

try:

$id = $row['id'];
$name = $row['name'];
$type = $row['short_name'];

* Note that the mysql extension is now deprecated and will be removed sometime in the future. That's because it is ancient, full of bad practices and lacks some modern features. Don't use it to write new code. Use PDO or mysqli_* instead.

UPDATE

Here's the equivalent of your function in mysqli:

function getInventoryOptions($db, $default_value, $datacenter)
{
     $query = "SELECT inventory.id, inventory.name, inventory_type.short_name
               FROM inventory LEFT JOIN inventory_type
               ON inventory.type = inventory_type.id WHERE inventory.datacenter = " . $datacenter . " ORDER BY inventory_type.sort_order ASC";

$link = mysqli_connect("[your_host]","[your_user]","[password]","[database]") or die("Error " . mysqli_error($link));



//execute the query.

$result = $link->query($query);

//display information:

while($row = mysqli_fecth_array($result)) {

        $id = $row['id'];
        $name = $row['name'];
        $type = $row['short_name'];

        if ($default_value == $id)
        {
            echo '<option selected value="' . $id . '">' . $type . ": " . $name . '</option>';
        }
        else
        {
            echo '<option value="' . $id . '">' . $type . ": " . $name . '</option>';
        }
}

Upvotes: 2

Related Questions