Is it possible to get the total of each different item use by users in a table and than add the total to the matching item in another table?

[holly9]

Hi i spend hours on this and i have search all over but i couldn't find the right code.I already know how to count the items.below is the sql code i use to test .

 

 

 

 

 

 

 

SELECT Items.item
FROM Items
LEFT OUTER JOIN (

SELECT item, COUNT( item )
FROM Members

GROUP BY item

)Members ON Items.item = Ad.Members

 

i don't get the results i want with above.

 
echo "<h4>Members
table:</h4>
<table border=\"1\">
<tr>
  <th>username</th>
  <th>item</th>
 </tr>
<tr>
  <td>kim</td>
  <td>orange</td>
 </tr>
<tr>
  <td>tom</td>
  <td>apple</td>
 </tr>
</table>
<br><br>
 
<h4>Items table:</h4>
<table border=\"1\">
<tr>
  <th>item</th>
 </tr>
<tr>
  <td>orange</td>
</tr>
<tr>
 <td>apple</td>
 </tr>
<tr>
 <td>grape</td>
 </tr>
</table>
<br><br>
 
 
<h4>I want to get this table:</h4>
<table border=\"1\">
<tr>
  <th>item</th>
  <th>COUNT(item)</th>
 </tr>
<tr>
  <td>orange</td>
<td>1</td>
 </tr>
<tr>
  <td>apple</td>
  <td>1</td>
</tr>
<tr>
 <td>grape</td>
 <td></td>
 </tr>
</table>";

 

[monkeypaw201]

Hopefully I don't spark a great debate about this, but could you not do it programmatically instead?

 

eg.

 

SELECT `item` FROM `items`

then do a foreach()

 

SELECT COUNT(`item`) AS `item_count` FROM `members` WHERE `item` = '$current_item_id_from_loop'

[holly9]

hi thanks but i wanted to avoid loops with mysql because i read that it can make the database run slower.Above is an example.I have many items in 2 tables and i'll be doing different things so there will be about 180 querys if i use loops.i have loops right now but i'm changing it.

[Jessica]

Hopefully I don't spark a great debate about this, but could you not do it programmatically instead?

 

eg.

 

 

SELECT `item` FROM `items`

then do a foreach()

 

SELECT COUNT(`item`) AS `item_count` FROM `members` WHERE `item` = '$current_item_id_from_loop'

 

 

NO! Bad. There is NO debating, that's just wrong.

[Jessica]

OP, explain better what you're trying to do, and post the table's structures.

[KevinM1]

 

Hopefully I don't spark a great debate about this, but could you not do it programmatically instead?

 

eg.

 

SELECT `item` FROM `items`

then do a foreach()

 

SELECT COUNT(`item`) AS `item_count` FROM `members` WHERE `item` = '$current_item_id_from_loop'

 

 

NO! Bad. There is NO debating, that's just wrong.

 

 

The reason why it's bad is that queries should never, ever, ever be performed in a loop.  It's a surefire way to kill site performance, as it costs more to hit a database repeatedly with queries than it does one long query (generally speaking).

[holly9]

OP, explain better what you're trying to do, and post the table's structures.

 

 

hi the tables are in the first post.the items were suppose to be in menus at the online store that shows how many items are in stock.there will also be menus of items in directory that shows the amount of active/approve ads.it's ok i'll just do another method instead.

[Jessica]

...

 

The database tables. Not your generated HTML tables.

[Barand]

 

... ON Items.item = Ad.Members

 

there is no column "members" and no table/alias "Ad"

[holly9]

sorry it was a typo it was suppose to be:

Members.item 

[Barand]

to output a total you first need to select it

 

SELECT Items.item, Members.total
FROM Items
LEFT OUTER JOIN (
    SELECT item, COUNT( item ) as total
    FROM Members
    GROUP BY item
)Members ON Items.item = Members.item

[monkeypaw201]

 

The reason why it's bad is that queries should never, ever, ever be performed in a loop.  It's a surefire way to kill site performance, as it costs more to hit a database repeatedly with queries than it does one long query (generally speaking).

 

Thank you for elaborating to assist others (like myself) by learning something new.

[holly9]

 

to output a total you first need to select it

 

SELECT Items.item, Members.total
FROM Items
LEFT OUTER JOIN (
    SELECT item, COUNT( item ) as total
    FROM Members
    GROUP BY item
)Members ON Items.item = Members.item

 

 

Cool thank you!.Your code works.I had to add the total colomn first to the Members table.

[Barand]

 

I had to add the total colomn first to the Members table

 

No, you didn't have to and you shouldn't.

 

the total column comes from the count in the sub-query (alias Members) in that query

[holly9]

No, you didn't have to and you shouldn't.

 

the total column comes from the count in the sub-query (alias Members) in that query

Thanks!.I deleted the total coloumn in Members and it still works