Styleminds
asked on
How can i get a mysql common field name that have highest vivit ?
Hello i have this my sql entry result showed below what i need is an either mysql query or a php function that return to me what is the section that have most visited regardless to the country where the answer based on the result below is
featured products with a 8 visits
So how can i retrieve that result
Thanks.
featured products with a 8 visits
So how can i retrieve that result
Thanks.
id refer section date visit country c
---------------------------------------------------------------------------------------------------------------------
2 product/159/electric-oud-eq featured products 03-07-2009 1 Germany no
3 product/559/trsamples-korg-pa800 featured products 03-07-2009 2 Germany no
4 product/446/universal-bc featured products 03-07-2009 2 Germany no
5 product/559/trsamples-korg-pa800 featured products 03-07-2009 1 United States no
6 product/559/trsamples-korg-pa800 featured products 03-07-2009 2 Australia no
7 product/159/electric-oud-eq news products 04-07-2009 2 United States no
if your id key is unique, than
SELECT a.section,SUM(b.visit) FROM table AS a, table AS b WHERE a.id=b.id GROUP BY a.section;
SELECT a.section,SUM(b.visit) FROM table AS a, table AS b WHERE a.id=b.id GROUP BY a.section;
of course - this will return all sections, lets order them:
SELECT a.section,SUM(b.visit) AS s
FROM yourtable AS a, yourtable AS b
WHERE a.id=b.id
GROUP BY a.section
ORDER BY s DESC;
SELECT a.section,SUM(b.visit) AS s
FROM yourtable AS a, yourtable AS b
WHERE a.id=b.id
GROUP BY a.section
ORDER BY s DESC;
ASKER
Sorry but didn't get what a and what b ?
Let explain more what i need to prevent any misunderstanding.
What i need a result of what section row results (having same name ) have higher total of visits
where here we have two results with different name featured products and near products so if we do calculate manually the featured products have more visits where it we calculate the visits all together it gives 8
Hope this get the issue more clear so what i need is a function or mysql command that retrieve this result
Let explain more what i need to prevent any misunderstanding.
What i need a result of what section row results (having same name ) have higher total of visits
where here we have two results with different name featured products and near products so if we do calculate manually the featured products have more visits where it we calculate the visits all together it gives 8
Hope this get the issue more clear so what i need is a function or mysql command that retrieve this result
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok how can i gather the section name that have higher visits and it related visits total ?
a and b are aliases for "yourtable", s is an alias for SUM(b.visit). This query will
1. select all pairs (a.section,sum(b.visit)) where a.id=b.id
so it will be in your case
featured products 8
featured products 8
featured products 8
featured products 8
featured products 8
news products 2
2. then group this results by section, so you get
featured products 8
news products 2
3. then order this recordset descending by s (i.e. sum(visit)), giving at last
featured products 8
news products 2
1. select all pairs (a.section,sum(b.visit)) where a.id=b.id
so it will be in your case
featured products 8
featured products 8
featured products 8
featured products 8
featured products 8
news products 2
2. then group this results by section, so you get
featured products 8
news products 2
3. then order this recordset descending by s (i.e. sum(visit)), giving at last
featured products 8
news products 2
>> how can i gather the section name that have higher visits and it related visits total ?
Did you try my suggestion?
Did you try my suggestion?
ASKER
all you gave cxr is a mysql_query so i am not sure what to do next so whoever willing to help please write a full coded solution with comment on the lign that need to be clarified that will help much better as i am knew into this thanks in advance.
cxr: I know that there is no need for self join, but this way it's easier to explain.
What you want to do if you will have two or more sections with equal and highest number of visits?
ASKER
what do you mean can you please give example .
>> all you gave cxr is a mysql_query
You asked for "either mysql query or a php function"...?
Below it is as a function. Call it like this:
$section = SectionWithMostVisits();
If you want it to return both the section name and the visit count, change the last line into this:
return array($row['section'],$row ['total_vi sits']);
...and call it like this:
list($section,$visits) = SectionWithMostVisits();
You asked for "either mysql query or a php function"...?
Below it is as a function. Call it like this:
$section = SectionWithMostVisits();
If you want it to return both the section name and the visit count, change the last line into this:
return array($row['section'],$row
...and call it like this:
list($section,$visits) = SectionWithMostVisits();
function SectionWithMostVisits() {
$sql = "select section,sum(visits) as total_visits
from TableName
group by section
order by total_visits desc
limit 1";
$res = mysql_query($sql);
if(!$res) die(mysql_error());
$row = mysql_fetch_assoc($res);
return $row['section'];
}
ASKER
thanks CXR so if i echo $row['visit'] will i get the total number of visits ! as i my result need to be
for example
the highest surfed is featured products section with 8 visits
for example
the highest surfed is featured products section with 8 visits
See php code showing all results in a table.
<?php
$result = mysql_query("SELECT a.section,SUM(b.visit) AS s FROM yourtable AS a, yourtable AS b WHERE a.id=b.id GROUP BY a.section ORDER BY s DESC;");
?> <table> <?php
while ($row = mysql_fetch_array($result,MYSQL_NUM)) {
echo "<tr><td>".$row[0]."</td><td>".$row[1]."</td></tr>
}
?>
</table>
missed double quote
<?php
$result = mysql_query("SELECT a.section,SUM(b.visit) AS s FROM yourtable AS a, yourtable AS b WHERE a.id=b.id GROUP BY a.section ORDER BY s DESC;");
?> <table> <?php
while ($row = mysql_fetch_array($result,MYSQL_NUM)) {
echo "<tr><td>".$row[0]."</td><td>".$row[1]."</td></tr>";
}
?>
</table>
>> if i echo $row['visit'] will i get the total number of visits
Not quite. The function (with the modification) returns an array with two items. Call it like this:
list($section,$visits) = SectionWithMostVisits();
...then the variables $section and $visits will contain the section name and the visit count, respectively.
Not quite. The function (with the modification) returns an array with two items. Call it like this:
list($section,$visits) = SectionWithMostVisits();
...then the variables $section and $visits will contain the section name and the visit count, respectively.
ASKER
Thanks jfromanski and cxr for your help a question for jfromanski so if i got two section that have different name but have same visits number so what i will get do i get in looping while two results ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry i am giving your hard times but didn't get what you meant ! is that i gives by asc order begining by highest visits to the lowest ! or just the highest beside you don't reply my question if there section have same igher visits do they will be arrayed and listed or only one ! please explain
All sections with the highest number of visits will be displayed. For the data as below you get:
other products 4
featured products 4
other products 4
featured products 4
id refer section date visit country c
---------------------------------------------------------------------------------------------------------------------
2 product/159/electric-oud-eq other products 03-07-2009 1 Germany no
3 product/559/trsamples-korg-pa800 featured products 03-07-2009 2 Germany no
4 product/446/universal-bc featured products 03-07-2009 2 Germany no
5 product/559/trsamples-korg-pa800 other products 03-07-2009 1 United States no
6 product/559/trsamples-korg-pa800 other products 03-07-2009 2 Australia no
7 product/159/electric-oud-eq news products 04-07-2009 2 United States no
This would return the entry with the most visits.
Select top 1 * from table order by visit ;
If you wanted the one with 8 visits only.
Select Top 1 * from table where visit = 8 order by visit ;
If you wanted _all_ results with 8 visits.
Select * from table where visit = 8 order by visit ;