How do I use multiple joins? - PHP

[nerdpowah23]

Here are the tables.
-- Table structure for table `items`
--
CREATE TABLE IF NOT EXISTS `items` (
`item_id` bigint(20) NOT NULL auto_increment,
`item_name` varchar(255) NOT NULL,
`item_description` text NOT NULL,
`item_type` varchar(25) NOT NULL,
`item_cost` double NOT NULL,
`item_sell_value` double NOT NULL,
PRIMARY KEY (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- Table structure for table `users`
--
CREATE TABLE IF NOT EXISTS `users` (
`userid` bigint(20) NOT NULL auto_increment,
`username` varchar(50) NOT NULL,
`password` varchar(255) NOT NULL,
`email` varchar(75) NOT NULL,
`userlevel` varchar(50) NOT NULL,
`register_date` datetime NOT NULL,
`last_login` datetime NOT NULL,
`total_logins` bigint(20) NOT NULL,
`ip` varchar(50) NOT NULL,
`locked` tinyint(1) NOT NULL,
PRIMARY KEY (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
--
-- Table structure for table `user_equipped_items`
--
CREATE TABLE IF NOT EXISTS `user_equipped_items` (
`userid` bigint(20) NOT NULL,
`primary_id` bigint(20) NOT NULL default '0',
`secondary_id` bigint(20) NOT NULL default '0',
`melee_id` bigint(20) NOT NULL default '0',
PRIMARY KEY (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `user_items`
--
CREATE TABLE IF NOT EXISTS `user_items` (
`userid` bigint(20) NOT NULL,
`item_id` bigint(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

<?php
$query = mysql_query("SELECT * FROM items LEFT JOIN user_items ON
items.item_id=user_items.item_id WHERE user_items.userid='$userid'");
$numrows = mysql_num_rows($query);
if($numrows > 0){
while($row = mysql_fetch_assoc($query)){
$name = $row['item_name'];
$id= $row['item_id'];
$type = $row['item_type'];
$cost = $row['item_cost'];
$sell_value = $row['item_sell_value'];
?>
<tr>
<td><?php echo"$name";?></td>
<td><?php echo"$type";?></td>
<td><?php echo money_format('%(#10n', $cost);?></td>
<td><?php echo money_format('%(#10n', $sell_value);?></td>

My question is, how do I join the user_equipped_items table into the query so I can exclude all items that the user currently has equipped from the while loop?

 

[descalzo]

Try saying what you want in words. The fields in user_equipped_items do not seem to relate to the other tables.

 

[nerdpowah23]

If the item id's in the user_equipped_items table (Let's say there the primary weapon id in the user equipped table is 1 and the user_items table has an item_id of 1, meaning they match) match in the user_items table and have the match excluded from the result.

 

[descalzo]

ie, user_items are his total inventory, user_equipped_items are the two (or less) items he is "using"

You want a list of items in his inventory that his is not using. Right?

 

[nerdpowah23]

Yes.

 

[descalzo]

SELECT items.*, user_equipped_items.primary_id , user_equipped_items.secondary_id FROM user_items
          INNER JOIN user_equipped_items ON user_items.userid = user_equipped_items.userid
          INNER JOIN items ON items.item_id=user_items.item_id
          WHERE user_items.userid='$userid' ;

Each row should be all the item info plus the id's of the primary and secondary items.
Just compare user_items.item_id with the primary and secondary and if it matches either, skip it.

 

[leafypiggy]

Simplest way: Get rid of the equipped table, and just add a column to the user's items table named "equipped", and remove them from the data using PHP and a regex.

 

[descalzo]

Another approach is to first run:

SELECT primary_id , secondary_id FROM  user_equipped_items 
          WHERE userid='$userid' ;

Grab the two values.

Run your original query and in the loop check the item_id against the two values and skip any that match.

 

[misson]

One approach would be to use a (left or right) outer join on the `user_items` table against each of the columns in `user_equipped_items` and include only those that are NULL for all three joins.

SELECT i.item_name, i.item_type, i.item_cost, i.item_sell_value
    FROM items AS i
      JOIN user_items AS inv ON i.item_id=inv.item_id 
      LEFT JOIN user_equipped_items AS prim
        ON inv.userid=prim.userid AND inv.item_id=prim.primary_id
      LEFT JOIN user_equipped_items AS scnd
        ON inv.userid=scnd.userid AND inv.item_id=scnd.secondary_id
      LEFT JOIN user_equipped_items AS mle
        ON inv.userid=mle.userid AND inv.item_id=mle.melee_id
    WHERE inv.userid=?
      AND COALESCE(prim.userid, scnd.userid, mle.userid) IS NULL

Leafypiggy's solution is also workable, though you wouldn't need to filter out the items on the PHP side. Simple add a condition to the WHERE clause to exclude any user's items that are equipped.

ALTER TABLE user_items ADD COLUMN equipped ENUM ('primary', 'secondary', 'melee');
-- ensures a user has at most one item in each slot
ALTER TABLE user_items ADD UNIQUE INDEX (userid, equipped);

-- Transfer data from old user_equipped_items table to user_items.equipped
-- Transactions won't be effective for MyISAM, but are for InnoDB
SET @old_autocommit=@@autocommit;
SET @@autocommit=0;
START TRANSACTION;

UPDATE user_items AS inv 
  JOIN user_equipped_items AS eq
     ON inv.item_id=eq.primary_id
  SET inv.equipped='primary';

UPDATE user_items AS inv 
  JOIN user_equipped_items AS eq
     ON inv.item_id=eq.secondary_id
  SET inv.equipped='secondary';

UPDATE user_items AS inv 
  JOIN user_equipped_items AS eq
     ON inv.item_id=eq.melee_id
  SET inv.equipped='melee';

DROP TABLE user_equipped_items;

COMMIT;
SET @@autocommit=@old_autocommit;

-- The query to get unequipped items
SELECT i.item_name, i.item_type, i.item_cost, i.item_sell_value
    FROM items AS i
      JOIN user_items AS inv ON i.item_id=inv.item_id 
    WHERE inv.userid=? 
      AND i.equipped IS NULL

If MySQL had better support for standard SQL, there'd be a third option: set difference. Select the user's inventory and select the equipped items, then subtract the latter from the former. It would look something like:

(SELECT i.item_name, i.item_type, i.item_cost, i.item_sell_value
    FROM items AS i
      JOIN user_items AS inv ON i.item_id=inv.item_id 
    WHERE inv.userid=?)
EXCEPT
(SELECT i.item_name, i.item_type, i.item_cost, i.item_sell_value
    FROM items AS i
      JOIN user_equipped_items AS inv 
        ON i.item_id=inv.primary_id OR i.item_id=inv.secondary_id OR i.item_id=inv.melee_id
    WHERE inv.userid=?)

Depending on the properties of items (are names unique? Can a user have only one of an item?), you might have needed to include item.item_id in the sub-selects, or have used EXCEPT ALL.

Other Issues
You probably don't want a left join of items on user_items, as this will give you all the items in the table.

Mixing naming conventions (underscored, e.g. "item_id", and space-elided, e.g. "userid") is a bad idea. It forces developers to either have to remember which columns follow which convention (which increases the likelihood of mistakes) or look at the schema (which takes additional time).

Since the table named "items" has "item" in the name, the "item_" prefix in the column names is redundant.

Don't use SELECT *; select only the columns you need.

While you're generally not likely to exhaust integer IDs, declaring the surrogate key columns UNSIGNED will double their number.

If a user logged in every minute of every day for 100 years, that's still less than 5.3E6 logins, which will easily fit in an INT. There's no reason to make the `total_logins` column so large. Similarly, even SHA-512 only takes 128 bytes to store and an IPv6 address will fit in 17 bytes (if stored as a string) or 6 bytes (if stored as a binary value), so 255 and 50 bytes are more than the `password` and `ip` columns, respectively, need. Of course, since it's a varchar column, you won't pay a penalty for the larger size of the latter two.

One column that's potentially too small is "users.email". According to RFC 3696 errata #1690, the maximum length of an e-mail address is 254 characters. It's unlikely that you'll see an e-mail address that long in the wild, but it's safer to account for the possibility, especially considering that there isn't a penalty for increasing the size of the column to 254 characters.

With the InnoDB engine, you can use foreign key constraints to ensure data integrity between the tables. There are other differinces; see "MySQL Engines: MyISAM vs. InnoDB" by Narayan Newton, "MyISAM versus InnoDB" on StackOverflow and "InnoDB vs. MyISAM – A Comparison of Pros and Cons" by Yang Yang on Kavoir.

Use prepared statements, and a DB extension that supports them (such as PDO). The are safer, easier and more performant.

CREATE TABLE IF NOT EXISTS `users` (
    `id` BIGINT(20) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL,
    `password` VARCHAR(128) NOT NULL,
    `email` VARCHAR(254) NOT NULL,
    `level` VARCHAR(50) NOT NULL,
    `register_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_login` TIMESTAMP NOT NULL,
    `total_logins` INT NOT NULL,
    `ip` BIGINT NOT NULL,
    `locked` tinyint(1) NOT NULL,
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `items` (
    `id` BIGINT(20) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT NOT NULL,
    `type` VARCHAR(25) NOT NULL,
    `cost` DOUBLE NOT NULL,
    `sell_value` DOUBLE NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `inventories` (
    `user` bigint(20) UNSIGNED NOT NULL,
    `item` bigint(20) UNSIGNED NOT NULL,
    `equipped` ENUM ('primary', 'secondary', 'melee'),
    UNIQUE INDEX (user, equipped),
    FOREIGN KEY (user) REFERENCES users (id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (item) REFERENCES items (id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

<?php 
// Data access
$itemQuery = $db->prepare("SELECT i.item_name, i.item_type, i.item_cost, i.item_sell_value
    FROM items AS i
      JOIN inventories AS inv ON i.item_id=inv.item_id 
    WHERE inv.userid=? 
      AND i.equipped IS NULL
"); 

$itemQuery->execute(array($userid));

// Presentation
?>
<table class="inventory">
    <thead>...</thead>
    <tbody>
      <?php foreach ($itemQuery as $item) { ?>
        <tr>
            <td><?php echo $item['name']; ?></td>
            <td><?php echo $item['type'];?></td>
            <td><?php echo money_format('%(#10n', $item['cost']);?></td> 
            <td><?php echo money_format('%(#10n', $item['sell_value']);?></td>
        </tr>
      <?php } ?>
    </tbody>
</table>

The PHP code sample still mixes DB access and presentation, which should be kept in separate layers, but the concepts of a Data Access Layer, tiered architecture and separation of concerns is a topic for another day. However, note how easy it would be to separate the two sections noted in the comments.