0

I have two tables,

One with items:

ic_items

 item_id  name                description 

      1   Pepperino           Hot sauce from Argentina
      2   Vamos el Couchiero  Hot sauce from Spain

one with votes: (flame = 1 vote)

ic_flames

  flame_id  item_id

     1         1
     2         1
     3         2

Now I want to connect them. So something like this is joining the first table: 

  item_id  name               description                 flame_counter

      1   Pepperino           Hot sauce from Argentina         2
      2   Vamos el Couchiero  Hot sauce from Spain             1

I use this query: 

SELECT ic_items.item_id, COUNT(ic_flames.flame_id) AS flame_counter
FROM ic_flames 
JOIN ic_items ON ic_items.item_id=ic_flames.item_id
GROUP BY ic_items.item_id

When I fill in this query on phpmyadmin I get a table with the flame_counter, but when I go to ic_items I don't see the flame_counter.

How can I get the flame_counter into my ic_items table?

mtr.web
  • 1,505
  • 1
  • 13
  • 19
FoxOnFireHD
  • 145
  • 11
  • I am getting the feeling that you think running the query with the JOIN in it will make a permanent join of these tables. It wont! The joining is only done to produce the result table for that one query. Or did I misunderstand – RiggsFolly Mar 29 '18 at 19:42
  • If you want the connection to look permenant, look up VIEWS. Create a view and then use that when you want to look at this data as if it were a single table – RiggsFolly Mar 29 '18 at 19:56
  • @RiggsFolly oh damn.. I want it to be permanent yes. How do I create that view – FoxOnFireHD Mar 29 '18 at 20:00

2 Answers2

0

You could do a subquery to count the table-entries in ic_flames wich have the current item_id. Although it would be much more nicer just to implement a counter-entry into ic_flames instead of a new line for every vote. Here is a not tested example for your case. Hope it helps ;)

SELECT   ic_items.item_id, 
         ic_items.name, 
         ic_items.description,
         (
         SELECT   COUNT(*) 
         FROM     ic_flames AS sub_ic_flames
         WHERE    sub_ic_flames.item_id = ic_items.item_id
         GROUP    BY sub_ic_flames.item_id
         ) AS flame_counter
FROM     ic_items

//EDIT

To update flame_counter in table ic_items use the following query.

UPDATE   ic_items
SET      flame_counter = (
             SELECT   COUNT(*) 
             FROM     ic_flames AS sub_ic_flames
             WHERE    sub_ic_flames.item_id = ic_items.item_id
             GROUP    BY sub_ic_flames.item_id
         )
wayneOS
  • 1,427
  • 1
  • 14
  • 20
  • it works, but how do I get that flame_counter into the ic_items table? – FoxOnFireHD Mar 29 '18 at 19:58
  • @FoxOnFireHD why would you do that? with that query you get your needed data. otherwise why even having the `ic_flames` table? – wayneOS Mar 29 '18 at 20:00
  • I understand what you mean, but for a school project I NEED to have two tables and connect them somehow. I want that connection to be permanent. – FoxOnFireHD Mar 29 '18 at 20:05
  • @FoxOnFireHD i updated my answer. Hope this is what you want. but then you would need the field `flame_counter` in your `ic_items` table. – wayneOS Mar 29 '18 at 20:16
  • @FoxOnFireHD your welcome! would you mind accepting my answer then. thanks – wayneOS Mar 29 '18 at 20:18
0

The selection here is going to do just that, select. If you want to add them to existing items in an existing table, that is an UPDATE statement, and if you want to create a new column for those values, you need an ALTER TABLE statement. In your case, the alter table needs to come first, and you will need to combine your JOIN with an UPDATE, and it would look something like this: 

ALTER TABLE table_name
ADD flame_counter int(250);

UPDATE ic_items
SET flame_counter = COUNT(flames.flame_counter)
FROM (
    SELECT ic_items.item_id, COUNT(ic_flames.flame_id) AS flame_counter
        FROM ic_flames 
    JOIN ic_items ON ic_items.item_id=ic_flames.item_id
    GROUP BY ic_items.item_id
) AS flames;
mtr.web
  • 1,505
  • 1
  • 13
  • 19