PHP, mySql Select all with the same name

Question

So I have a table where some of the products repeat with but have a different value on number of clicks.

name ----  clicks
iPhone       4
Samsung      2
iPhone       1
Samsung      5

my select function is :

$select_table2 = 'SELECT * FROM `mytable`'; //WHERE NAME IS THE SAME
$sql2 = $db->query($select_table2);
while ($row = mysqli_fetch_array($sql2)) {
 echo $row["name"];
 echo $row["clicks"];
}

I need this output:

iPhone       5
Samsung      7

I don't want to merge the same rows because they have one more column that is different. So please do not suggest simply to merge them and update the clicks...

UPDATE:

$pull_request = 'SELECT SUM(e.product_clicks),e.product_id, u.name, u.product_id FROM `oc_aa_affiliatecollclicktracking` AS e GROUP BY e.product_id  LEFT JOIN  `'.DB_PREFIX.'product_description` AS u ON e.product_id = u.product_id';

I tried it like this but it's not working

Niranjan N Raju · Accepted Answer · 2015-10-19 17:56:20Z

1

use sum() and also GROUP BY name to get desired output.

$select_table2 = 'SELECT name,SUM(clicks) FROM `mytable` GROUP BY name'; 
$sql2 = $db->query($select_table2);
while ($row = mysqli_fetch_array($sql2)) {
  echo $row["name"];
  echo $row["clicks"];
}

while will produce,

iPhone       5
Samsung      7

For more info

EDIT

Group by should come after join.

$pull_request = 'SELECT SUM(e.product_clicks) as clicks,e.product_id, u.name, u.product_id FROM `oc_aa_affiliatecollclicktracking` AS e   
LEFT JOIN  `'.DB_PREFIX.'product_description` AS u ON e.product_id = u.product_id
GROUP BY e.product_id';

edited Oct 19, 2015 at 17:56

answered Oct 19, 2015 at 17:14

Niranjan N Raju

12k4 gold badges24 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Grasper Over a year ago

SELECT SUM(e.product_clicks),e.product_id, u.name, u.product_id FROM oc_aa_affiliatecollclicktracking AS e GROUP BY e.product_id LEFT JOIN '.DB_PREFIX.'product_description AS u ON e.product_id = u.product_id

Niranjan N Raju Over a year ago

add database design as well.

Niranjan N Raju Over a year ago

@Grasper I have edited my answer, please check that.

Niranjan N Raju Over a year ago

Let us continue this discussion in chat.

Andrew Coder · Accepted Answer · 2015-10-19 17:14:35Z

1

You want to perform a SUM command by group

$query = "SELECT `name`,SUM(`clicks`) FROM `mytable` GROUP BY `name`";

Edit: The other answer was more complete than mine. I forgot to select name field. Added.

answered Oct 19, 2015 at 17:14

Andrew Coder

1,0588 silver badges13 bronze badges

Comments

Anfelipe · Accepted Answer · 2015-10-19 17:26:33Z

1

What you need is an aggregate function for suming the clicks [SUM(clicks)], and a Group By clause for defining the classification criteria [GROUP BY name].

The other answers where wrong in the sense that are assuming that by changing the select field list adding the aggregate 'SUM', the associative references (eg: index strings of the $row array ) remains the same, the correct query would be:

$select_table2 = 'SELECT name, SUM (clicks) AS clicks FROM mytable GROUP BY name';

Note the alias on SUM(clicks) AS clicks, so the fields returned in the array $row keep their indexes (clicks, names... instead of 'SUM(clicks)')

And the rest is basically the same.

Cheers!

answered Oct 19, 2015 at 17:26

Anfelipe

77112 silver badges21 bronze badges

1 Comment

Anfelipe Over a year ago

The Alias, the alias, I told ya!, Good everything worked.

Pixel · Accepted Answer · 2015-10-19 17:16:02Z

-1

Try :

$select_table2 = 'SELECT name, SUM (clicks) FROM mytable GROUP BY name';

answered Oct 19, 2015 at 17:16

Pixel

9101 gold badge14 silver badges33 bronze badges

Collectives™ on Stack Overflow

PHP, mySql Select all with the same name

4 Answers 4

4 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related