Robert Dickey Robert Dickey - 7 months ago 31
SQL Question

MySql and MySqli Left Join

I have been having trouble for the past couple of hours trying to wrap my head around a way to pull similar data from two different tables. I have playing around with Join statements but have been unable to accomplish what I'm trying to do..

First I am trying to pull a list of my clients and display them and their information from that table in a html table (this is complete). Secondly, I would like to take their index (known as MID) and use to reference data in another table which contains more data on their MID. Each client has multiple "projects" located in another table, and each one of these projects has a value called "package" the value can either be a 1,2, or 3. What I would like to be able to do is to pull all the customer data from one table, and also pull the total number of each package they have. I.e in my table I have a blank place for pack1, pack2, pack3, which I hope will contain the total number of rows that contain that number in the package with the same MID.

Example [ name ] [totalprojects][ totalpackage1][totalpackage2][totalpackage3]
Bob 25 4 12 9


Here is my code

$query_load_accounts = "SELECT * FROM useraccounts ORDER BY MID";
$result_load_accounts = mysqli_query($dbc, $query_load_accounts);

echo '<center><table border="1">';
echo '<cellpadding="10">';
echo '<th>Company Name</th>';
echo '<th>Member Since</th>';
echo '<th>Package 1</th>';
echo '<th>Package 2</th>';
echo '<th>Package 3</th>';
while ($row = mysqli_fetch_assoc($result_load_accounts))
{

echo "<tr>
<td><center>{$row['company']}</td></center>
<td><center>membersince</center></td>
<td><center>notused</center></td>
<td><center>package1</center></td>
<td><center>package2</center></td>
<td><center>package3</center></td>";

}
echo "</center></table>";
mysqli_close($dbc);
mysqli_free_result($result_load_accounts);


Other useful information - The other table's name is "projects" and the fields needed are MID (customer specific) and Package (just a package number i.e package 1 14.99, package 2 is 24.99 ext) I would like to be able to I guess use a Join statement to total the amount of each package for each user.. im just having so much trouble with it

*UPDATE I have tested the following statement and it pulls the data correctly, now I just need to know how to sort that data in PHP. I.E count the rows for each user


$query_load_accounts = "SELECT useraccounts., projects. FROM
useraccounts LEFT JOIN tributes ON useraccounts.mid = projects.mid
ORDER BY UID";





Thanks to zero activity in this thread I have resolved the issue myself. Good Riddance.

'SELECT mid,
SUM(IF(package = "1", 1,0)) AS `1`,
SUM(IF(package = "2", 1,0)) AS `2`,
SUM(IF(package = "3", 1,0)) AS `3`,
COUNT(package) AS `total`
FROM projects
GROUP BY mid
ORDER BY mid DESC'

Answer

'SELECT mid, SUM(IF(package = "1", 1,0)) AS 1, SUM(IF(package = "2", 1,0)) AS 2, SUM(IF(package = "3", 1,0)) AS 3, COUNT(package) AS total FROM projects GROUP BY mid ORDER BY mid DESC'