get data from to tables !

i have user list , and i have select box to filter userlist one of the select box options is show by most viewed so i have also need user information too . i want to sort my users based on most viewed profile in my user list . i have these two tables but i don't know how to right correct query to make this happen . i used grouping like this :

开发者_JS百科

$sql ="select userid , count(*) form profile_visit group by userid " ; 

but it's not make sense to me , i don't think this query will help me at all .

+-----------+---------------+------+-----+-------------------+----------------+
| Field     | Type          | Null | Key | Default           | Extra          |
+-----------+---------------+------+-----+-------------------+----------------+
| userid    | int(11)       | NO   | PRI | NULL              | auto_increment |

| username  | varchar(128)  | NO   |     | NULL              |                |

| password  | char(40)      | NO   |     | NULL              |                |

| email     | varchar(128)  | NO   |     | NULL              |                |

| name      | varchar(256)  | NO   |     | NULL              |                |

| lastname  | varchar(256)  | NO   |     | NULL              |                |

| job       | varchar(256)  | NO   |     | NULL              |                |

| birthdate | varchar(100)  | NO   |     | NULL              |                |

| address   | varchar(1024) | NO   |     | NULL              |                |

| website   | varchar(100)  | NO   |     | NULL              |                |

| tel       | varchar(100)  | NO   |     | NULL              |                |

| role      | tinyint(1)    | NO   |     | 0                 |                |

| reg_date  | timestamp     | NO   |     | CURRENT_TIMESTAMP |                |

+-----------+---------------+------+-----+-------------------+----------------+

and profile_visit table like this

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| ip_address | varchar(70) | NO   |     | NULL    |                |
| userid     | int(11)     | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

---

Try something like this:

$sql ="SELECT userid , COUNT(*) AS visits FROM profile_visit GROUP BY userid ORDER BY visits DESC" ; 

That should group as you were expecting, but order the results in descending order based on the number of visits they have had.

I would ask whether it is necessary to have a separate table? Do you need details of all the visits to be stored, or could you just increment a "visits" integer for each user?

---

Disregarding the typo in form from your query looks reasonable. It should give you the profile identifiers and their view counts.

As I understand your question, you simply need to get the data out sorted in descending order, which is achievable by simply appending an order by to your query (using an alias for the aggregated column makes it an easier read):

select userid , count(*) as visitcount
from profile_visit
group by userid
order by visitcount