Solved

How can i get a mysql common field name that have highest vivit ?

Posted on 2009-07-03
21
232 Views
Last Modified: 2012-05-07
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.
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

Open in new window

0
Comment
Question by:Styleminds
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 4
  • +1
21 Comments
 
LVL 5

Expert Comment

by:boowhup
ID: 24775062
assuming your table is called table.....

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 ;


0
 
LVL 10

Expert Comment

by:jfromanski
ID: 24775100
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;



0
 
LVL 10

Expert Comment

by:jfromanski
ID: 24775116
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;
0
Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

 

Author Comment

by:Styleminds
ID: 24775137
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
0
 
LVL 39

Assisted Solution

by:Roger Baklund
Roger Baklund earned 100 total points
ID: 24775145
No need for a self join in this case, just do:
select section,sum(visits) as total_visits
from TableName
group by section
order by total_visits desc
limit 1

Open in new window

0
 

Author Comment

by:Styleminds
ID: 24775160
ok how can i gather the section name that have higher visits and it related visits total ?
0
 
LVL 10

Expert Comment

by:jfromanski
ID: 24775182
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
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24775191
>> how can i gather the section name that have higher visits and it related visits total ?

Did you try my suggestion?
0
 

Author Comment

by:Styleminds
ID: 24775202
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.
0
 
LVL 10

Expert Comment

by:jfromanski
ID: 24775206
cxr: I know that there is no need for self join, but this way it's easier to explain.
0
 
LVL 10

Expert Comment

by:jfromanski
ID: 24775214
What you want to do if you will have two or more sections with equal and highest number of visits?
0
 

Author Comment

by:Styleminds
ID: 24775224
what do you mean can you please give example .
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24775246
>> 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_visits']);

...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'];
}

Open in new window

0
 

Author Comment

by:Styleminds
ID: 24775257
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
0
 
LVL 10

Expert Comment

by:jfromanski
ID: 24775263
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>

Open in new window

0
 
LVL 10

Expert Comment

by:jfromanski
ID: 24775270
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>

Open in new window

0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24775283
>> 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.
0
 

Author Comment

by:Styleminds
ID: 24775292
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 ?
0
 
LVL 10

Accepted Solution

by:
jfromanski earned 400 total points
ID: 24775317
This code will return all sections with highest sum of visits:
<?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
 
$high=0;
while ($row = mysql_fetch_array($result,MYSQL_NUM)) {
    if( $row[1]>=$high ){
      echo "<tr><td>".$row[0]."</td><td>".$row[1]."</td></tr>";
      $high = $row[1];
    }
}
 
?>
</table>

Open in new window

0
 

Author Comment

by:Styleminds
ID: 24775334
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
0
 
LVL 10

Expert Comment

by:jfromanski
ID: 24775351
All sections with the highest number of visits will be displayed. For the data as below you get:
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

Open in new window

0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Uncontrolled local administrators groups within any organization pose a huge security risk. Because these groups are locally managed it becomes difficult to audit and maintain them.
The conference as a whole was very interesting, although if one has to make a choice between this one and some others, you may want to check out the others.  This conference is aimed mainly at government agencies.  So it addresses the various compli…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question