CODEWITHSUNDEEP
phpmysql
Grasper
Grasper

Reputation: 1313

PHP, mySql Select all with the same name

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

Upvotes: 0

Views: 1402

Answers (4)

Niranjan N Raju
Niranjan N Raju

Reputation: 11987

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';

Upvotes: 1

Anfelipe
Anfelipe

Reputation: 771

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!

Upvotes: 1

Pixel
Pixel

Reputation: 910

Try :

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

Upvotes: -1

Andrew Coder
Andrew Coder

Reputation: 1058

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.

Upvotes: 1

Related Questions