Too many MySQL queries?

Question

I have a question if anyone can answer. Please excuse my inexperience with this, but this is my first project that I have attempted and all of this is really new to me. I am in the process of trying to build an inventory system at work using php and mySQL and I have hit a bit of a wall regarding how I am going to display the items that are currently loaned out to people.

I have the items that are being provisioned to users broken down into 4 categories and records of the loans for these items are stored into 4 different tables. I also have another table for users, as well as tables for the items, and their characteristics.

What I want when my page is displayed to to have all of the items that are assigned to each user grouped together in a table. I have two ideas on how I can do this, but I'm not sure which would be the best way.

My first thought was to pull all of the users from the users table and store the information into an array, then pull all of the information from the 4 loan tables and store each table into an array. From there I would do something like

for($i=1;$i>sizeof($usersArray);$i++){
   for($a=1;$a>sizeof($loanTable1Array);$a++){
        if($userArray[$i][userID] == $loanTable1Array[$a][userID]{
            //list items
        }
    }
   for($b=1;$b>sizeof($loanTable2Array);$b++){
        if($userArray[$i][userID] == $loanTable2Array[$b][userID]{
            //list items
        }
    }
   for($c=1;$c>sizeof($loanTable3Array);$c++){
        if($userArray[$i][userID] == $loanTable3Array[$c][userID]{
            //list items
        }
    }
   for($d=1;$d>sizeof($loanTable4Array);$d++){
        if($userArray[$i][userID] == $loanTable4Array[$d][userID]{
            //list items
        }
    }
}

My concern with this though is that I will have around 100-150 users and each table will have an average of 100 different items. This would mean around 40,000 - 60,000 iterations of the loop.

My other idea was to do pull all of the entries from the user table, then use that data to query the other 4 tables using the userID in a where statement like this. But then I read that if you have a query in a loop then you're doing it wrong.

$sql = "SELECT userID FROM users";
$allUsers = runQuery($sql); //data is sanitized before running the query
for($i = 1; $i<sizeof($allUsers); $i++){
    $loan1sql = "SELECT * FROM loantable1 WHERE userID = {$allUsers[$i][$userID]}'";
    $loan1Items= runQuery($loan1sql);
    for($a = 1; $a<sizeof($loan1Items); $a++){
        //list items
    }
    $loan2sql = "SELECT * FROM loantable2 WHERE userID = '{$allUsers[$i][$userID]}'";
    $loan2Items= runQuery($loan2sql);
    for($b = 1; $b<sizeof($loan2Items); $b++){
        //list items
    }
    $loan3sql = "SELECT * FROM loantable3 WHERE userID = '{$allUsers[$i][$userID]}'";
    $loan3Items= runQuery($loan3sql);
    for($c = 1; $c<sizeof($loan3Items); $c++){
        //list items
    }
    $loan4sql = "SELECT * FROM loantable4 WHERE userID = '{$allUsers[$i][$userID]}'";
    $loan4Items= runQuery($loan4sql);
    for($d = 1; $d<sizeof($loan1Items); $d++){
        //list items
    }
}

Doing this would result in 400 - 600 calls to the database each time the page is loaded. Does anyone have any input on what my best course of action would be? Any help would be greatly appreciated.

Answer 1

(refer to answers above. This was too long to add as a comment, but I thought it would be helpful)

@cartalot and @Uours THANK YOU!!!! - I had considered creating one table for all of the loans early on but didn't know how to implement it. This makes perfect sense though. My whole issue was confusing the foreign key - parent key constraints in mySQL with how you can actually join tables to display information on your page.

Not to sound like a complete moron, but I think this might be constructive to someone that reads this down the road. I got confused by how you can create fk - pk relations in myPHPAdmin and what they actually do. I though that these relations were necessary to join tables (obviously wrong). I saw the visual connections and though that these tables were somehow "connected".

I know understand that when you create a foreign key parent key restraints all you are basically doing is limiting they data that you can enter into a table based on what is in another table. You can still join information from different tables without these constraints.

Answer 2

By considering an extra category column , you could have one loantable instead of four . Then you would just use one query by JOINing the tables .

Just an example showing one way to do it :

-- Table structure for table `users`

CREATE TABLE IF NOT EXISTS `users` (
  `userID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`userID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

-- Dumping data for table `users`

INSERT INTO `users` (`userID`) VALUES
(1),
(2);

-- --------------------------------------------------------

-- Table structure for table `loantable`

CREATE TABLE IF NOT EXISTS `loantable` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `category` int(11) NOT NULL,
  `userID` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

-- Dumping data for table `loantable`

INSERT INTO `loantable` (`ID`, `category`, `userID`) VALUES
(1, 1, 1),
(2, 2, 1),
(3, 3, 1),
(4, 1, 2),
(5, 3, 2);

Then you would use just one query like :

SELECT  *
FROM
    `users`
    LEFT OUTER JOIN loantable ON loantable.userID = users.userID
WHERE   1
ORDER BY
    users.userID
    ,category