MYSQL :: Obtain Most Popular Cities From My Users Table

Create Users Table For Users To Log On Or ...?
I'm pretty new to MySQL, what I was wondering is, for web systems that you need to login to, do developers generally create a users table and store all their users in there, or do they create users such as the root user in mysql ? If so, how do you add extra fields such as firstname last name etc and how would you go about putting them into groups?

Most Popular Entry?
I have a quite simple question which I hope can be answered.

Whenever someone downloads a video from my site, a database entry is made with the time and name of the video. I run a php script everyday which discards entries older than 7 days. So I have all that figured out.

What I want to know is, how do I work out what the 10 most popular entries are in the database (by name)?

For example, 8 entries may have the name Video1.wmv, 4 entries may have the name Video4.wmv.etc.

I'm not sure if this can be done via a mysql query, or if php will somehow have to select the most popular entries.

Popular Pages
Is it possible to use data from the url, record it into MySQL or text data file
so as to display the popular pages from a site.

Any PHP+MySQL ideas or links are needed?

-----------------------------
I had the idea started with no luck...

the url is basic structure like:
domain.com/tmplate.php/1038/BMW

list($first, $last) = explode('/', substr($PATH_INFO,1));
$entry_line = "$first/$last
";

$fp = fopen("/public/count.txt", "a");
fputs($fp, $entry_line);
fclose($fp);

the explode grabs the 1038/BMW
from the url and records into a text file.

Distance Between Two Cities?
I am just curious as to how i would do it? I am thinking something like:

cityA cityB distance
london Manchester 100KM
Manchester Edinburgh 100KM
Now if i wanted to work out the distance between london and edinburgh what select statement would/could i use?
select distance from table where??

Nearest Cities Query
I have a table with around 3 million entries each a city in the world. Other than countrycode, name etc... I have latitude and longitude for each, with an index on each field. There are also indexes on countrycode, name etc...

The following query takes around 8 seconds to complete on cities in the us, there are around 200,000.

Any ideas on how to make this query faster by an order of magnitude? Adding extra columns to the table is also an option.

I am using MySQL 4.1 as my database server.

SELECT id,(ACOS((COS(RADIANS(latitude)) * COS(RADIANS(longitude)) * COS(RADIANS(?)) * COS(RADIANS(?))) + (COS(RADIANS(latitude)) * SIN(RADIANS(longitude)) * COS(RADIANS(?)) * SIN(RADIANS(?))) + (SIN(RADIANS(latitude)) * SIN(RADIANS(?)))) * 6371) as distance from gislocation WHERE ACOS((COS(RADIANS(latitude)) * COS(RADIANS(longitude)) * COS(RADIANS(?)) * COS(RADIANS(?))) + (COS(RADIANS(latitude)) * SIN(RADIANS(longitude)) * COS(RADIANS(?)) * SIN(RADIANS(?))) + ( SIN(RADIANS(latitude)) * SIN(RADIANS(?)))) * 6371 < ? && (countrycode = ?) && (id != ?) order by distance ASC limit ? [params=(double) 40.714168548584, (double) -74.0063934326172, (double) 40.714168548584, (double) -74.0063934326172, (double) 40.714168548584, (double) 40.714168548584, (double) -74.0063934326172, (double) 40.714168548584, (double) -74.0063934326172, (double) 40.714168548584, (int) 50, (String) US, (long) 4074408, (int) 10]

How To Obtain Automatically Generated Id
I want to write a php script that creates a new row in a table with an auto increment field and then creates additional records in other tables where the automatically generated field has to be used.

Is there a way to get the automatically generated number as a result of an Insert query so that I can then use it in the next queries?

Obtain Date From YEARWEEK
Does anyone know how to obtain the date for the first day of the week when provided with a YEARWEEK? So if I provided 200632 (this week) as input I would expect to receive 2006-08-06 (last Sunday - first day of this week) as output.

I can probably do this easily enough in Perl but I'm looking for an easy way to do this in a single MySQL statement, so I can do something like:

select FIRST_DAY_OF_WEEK(YEARWEEK(DATE)), SUM(HITS) from BLOGSTATS where BLOG_ID=95 group by YEARWEEK(DATE);

where FIRST_DAY_OF_WEEK is the function I am hoping exists somewhere.

How To Obtain Autoincrement Value After Insertion?
In SQL Server within a stored procedure I can obtain a recently inserted row's "identity" value (same as autoincr in MySQL) by using the keyword @@IDENTITY. So it is possible to do something in TSQL like so..

DECLARE @ID INT
INSERT INTO mastertable ([fields list]) VALUES ([values list])
SET @ID = @@IDENTITY

Now I have the value in variable @ID and I can use it for inserting foreign keys in detail records in a master-detail relationship like so..

INSERT INTO detailtable (ID_Field, [fields list]) VALUES (@ID, [values list])

How can I do the same thing In MySQL?

Grab All Users Not In Second Table.
I have two tables. I want to grab all the users from the users table who are not found in the second table. It's just seems to be one of those days where I can't perform the simple tasks, I think I need to go back to bed.

[table] users
[field] userid

[table] user_details
[field] userid

Select userid from users where userid not in user details

Best Practices For A 'Users' Table
I am wondering what would be the best way to design the table(s) for users in a database, or at least get some opionions of how people would go about it.

Say I have the normal stuff about a user such as name, email, phone etc, a good number of user attributes. Then I also have profile kind of attributes such as photo, more personal details, various preferences etc, again pleanty of attibutes. Then I also have a bunch of access or priviledges attributes such as status and whether they can access/modify certain modules.

I could get all these in a single table since a user is going to potentially have all of these attributes once (though a lot could initially be null) but this is going to be one huge (wide) table maybe 30 columns or more.

Would it make sense to split it into 3 tables like 'users', 'profiles', 'priviledges'? If so, should all these be created at the same time when a user signs up or as needed when a user decides to add a profile or get assigned special proviledges. If I create then on demand I will probably be saving a lot of unnecessary rows but I will have to be dealing with outer joins and defaults most of the time, ie more complex code. On the other hand if I depend on all three existing (besides the waste issue) I might get the wrong results if, for any reason, a row for each user fails to get created in all tables (or is accidentally removed).

Table Of Users Having Many To Many Relationship With Itself?
i hope to implement a user subscription feature on my webdatabase, where users can subscribe to each other's postings.

Can i achieve this by having one table to map the USERIDs to each other, and then relate this to the main USER table?

Currently I have two user tables that are exact mirrors of each other, and then a junction table to relate the two, but im beginning to think this is kinda dumb.

Fields In Users Db Table For Authentication
which fields do you place in your db for user authenication?

for example..:
CREATE TABLE `users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`Username` VARCHAR(40) NOT NULL default '',
`Password` VARCHAR(40) NOT NULL default '',
`Lastlogin` datetime default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

i wonder for some more useful fields for software logging/management people sometimes (or seldom :-)) use like:
lastip
createdon
lastlogin
active ('yes', 'no', 'banned') -

Can't Add New Users & Passwords To Grant Table
I am running MySQL 4.1.7 on my Windows XP system. I can create databases, ect. on the command line, but when I try to run a PHP script I receive the 'Access denied' error message. I have tried using the Grant command to add a new user but nothing comes up when I show SHOW GRANT except the root user. How do I add the new users for running the PHP script?

Self Join (?) To Get Data For Multiple Users From One Table
I am trying to generate a report containing ebay feedback scores for multiple users. Here is the database structure for the table that contains the scores:

TABLE: feedback

identities_id int(10) <- contains the user id which is stored in another table
feedback int(10) <- contains the feedback score for a specific date and time
date_recorded datetime <- date the feedback was captured
I need to get the feedback scores for let's say two users (I will want up to five but I figure the query will be almost the same) over a thirty day period. Here is the query I have created thus far (which does not return the correct results):

mysql
SELECT user1.feedback AS user1feedback
, user2.feedback AS user2feedback
, UNIX_TIMESTAMP(user1.date_recorded) AS date_recorded
FROM feedback as user1
LEFT JOIN feedback as user2 ON user1.date_recorded = user2.date_recorded
WHERE user1.identities_id = 1
OR user2.identities_id = 2
AND user1.date_recorded > NOW() - INTERVAL 30 DAY
ORDER BY date_recorded ASC

This returns virtually the same data for both users which means I am not distinguishing the two properly.

Replace Some Username Values With The Users ID In A Different Table
I really can't figure this out. I want to replace some username values with the users ID in a different table.

The tables are

pictures
user (Example FRED)
picture (Example pic.gif)

users
id (Example 5233)
user (Example FRED)

What I want to do is replace the user column in pictures with the correct id from the users table

So in the pictures table, it would no longer be FRED it would be 5233

Is there a query I can run to do this?

I'd really appreciate some feedback

Making Site Login Add Users To Phpbb Database Table
I have a login script for my site that I got off the net and was planning to use, but, I also wanted to make it so that when a user signed up thru the registration form it would also add them to the phpbb database's table. It wasn't discussed heavily from where i got the script but from what was mentioned the encryptions differ which results in a rejected login. Code:

Sorted List Of Cities In Countries (was "What Is The Best Way To Do This?")
I have database with cities, countries, regions etc.

I need to take list of countries from one table, then I need to take list of cities of each country from previous list and sort ALL cities of all countries by some value.

What is the best (easiest, fastest) way to do that?

Users
This is my first time with MySQL need a point in the right direction ;).
I have a private page where only one user can access. On the page there is a number beside the number there is a button. Each time the user presses the button the number goes one down (and the value should stay like this it shouldn't return when the user comes back!) when it is zero the button is disabled.

Edited 1 time(s). Last edit at 03/11/2006 07:14AM

How To Switch Users
I tryed my sql manual and could not find a solution to my problem.

when i open mysql command line client 5.x it prompts for root password and when supplied i loged in sucessfully. that is ok.

my question is how to login as a diffrent user from mysql prompt?

assume currently i have loged in as root and wanted to login as xyz.

How To Select All Users?
How do i get all the users that are listed in my database to show up in a table?

Would it be something like ?

<?php SELECT username, id FROM 'users' WHERE rank by 'id' ?>

How would I do that? and how would I make it into a table?

List All Users
I'm running through a book on mySQL and have messed up adding users. I've got everything sorted now, but I want to clean up all the screwed up ones I've entered along the way. The only problem is I can't remember what I typed, so don't know what to delete. Is there a way of listing all the users, so I can pull off the ones I don't need?

Merge Users
im completly new to mysql and have not really touched it before.
i have a gaming website running php fusion, i have a main site and several other subsites (one for each game played) what i want is once someone registers on the top site they are registered on all the sites, php fusion isnt yet able to do this so i thought maybe i could do it in mysql, is it possible to have 2 or more tables that will always be the same apart from the names? ie main_user and bf2_user both with the same info in? either this or can you assign more than one name to the same table?

How To Create Users On A Mac?
I have installed MySql on my Mac, running Mac OSX 10.4

MySql is started automatically on system startup, and I can see the
"MySql" icon in the system preferences. So far, everything should run
normally.

Now how can I administrate, in order to specify users? From the
readme file, I concluded that by default, the at least a user
"mysql" should be initially defined with empty password, but when
I enter on the bash command line

mysql -u mysql

I get the error message

'Access denied for user 'mysql'@'localhost' (using password: NO)'

Users Privilege
i noticed that when i connect to MySQL with php using:

mysql_connect();
or:
mysql_connect("dbhost", "dbusername", "dbpassword");

always will be connected and the user can do anything like creating databases or delete.
how can i prevent it? i just want the root user can do it.

Can't Log In B/c Deleted All Users
I installed the mysql-administrator GUI admin program, and my first task was to make it more secure by disabling anonymous logins and deleting/changing root access. I used the mysql-administrator to delete all accounts, and my goal was to then add another admin account right after, but mysql-administrator crashed on me and had a seg fault for some reason.

Now I don't seem to have any login ability as all users are gone. I've found articles online that speak of changing the root password, but I can't even log in as root or any other user (anonymous, included), so how do I get in? Luckily, I just started playing with mysql and have no valuable data. I'm running Fedora Core 4, and did a 'yum remove' of mysql and mysql-server. Then did a 'yum install' of both of tham again.

The problem is it's still acting like the old install (still looking for old config/password files of the old install??). Can someone tell me how to change/add users or completely remove mysql/d, and install again?

Removing Users
I can't seem to figure out how to remove users with php. I tried to do it with this
$query = mysql_query("REVOKE ALL PRIVILEGES, GRANT OPTION FROM user");

Where user is the name of the user I want to delete. That's what it said to do in the GRANT/REVOKE syntax part of the site but it doesnt do anything when I run the script.

Creating New Users
I have added a new user to the mysql.user table and gave it the same permissions as the 'root' user (all Y's in the database).

When I use these details in an asp connection string I get the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[MySQL][ODBC 3.51 Driver]Access denied for user 'nmssiteuser'@'localhost' (using password: YES)

Adding New Users
I can't seem to add a new user to my database! I've tried it through phpMyAdmin (under privileges) and also running the query

GRANT ALL PRIVILEGES ON *.* TO 'myusername'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION

which returned no errors and according to phpMyAdmin the user is entered into the `users` table. But when I try to login with that name I get

Access denied for user: 'myusername@localhost' (Using password: YES)

So all I can use to login is 'root' with no password. Not very secure!

DB Design Users
If you have a database with several table and u want users to access the information. What is the available methods to allow these users to access information ( READING and ADDING at least).

Do I add users like the root. Or creating table which hold users informations in which case I wonder how these users access data without mysql user???

No Users In Mysql
I am in the process of setting up a 'testing server' using IIS, PHP, MySQL etc. The server is up and running and PHP is working fine. I have installed MySQL 4.1.12a and the server is up and running, I have managed to connect to the 'mysql' db through the console without any problems.

However, when I installed phpMyAdmin I got the 'client does not support authentication' error message. I have found this in the manual and understand how to deal with it, but on closer inspection there are no users listed within the user table in 'mysql'. As far as I am aware there should be some default users with a new install??

How do I setup a couple of new users and ensure that phpMyAdmin can access the server etc?

Current Users
How can I check what are the current users right now (except for the admin)?
I can't find this in a manual.

Setting Up Users
I have a website and db where people can login get setup and then enter information into the db, everybody has a unique client number, can I create a user for the db when they set themselves up as a user and restrict them from changing anything except data that is connected to their client number?

Users And Permission
Being that no user is accepted from the command line (even root)
using the command:

mysql -u root

does anyone know if editing the 'users' database in /var/log/mysql
with vi would help?

Listing Users
I would like to list the users and their permissions on my MySQL server.

None Of Users Privileges
I have no privileges anymore to create a database or view privileges and users. Does somebody know how I can add a user or give my root all privileges again?

Adding Users
I can add users but how do I make it so the users can not see all the DB, I know I can restrict the user so they cannot veiw the data but I dont want them to even see the other DB's

ADDING NEW USERS
I have set up a linux machine running ubuntu. I finally was able to login to mysql with root, but I am unable to add other users. I use this command

mysql> grant select, insert, update, delete, index, alter, create, drop
-> on books.*
-> to bookorama identified by 'bookorama123';

and the response I get is:

QUERY OK 0 ROWS affected

I have no clue as to what is wrong. I tried logging into the user I thought I created, but it acts like it's not there.

List All Users
im trying to make a activity list that list all users who havent logged in in the last 60 days

i have the following code but it wont only list the last 60 days activity....

SELECT * FROM hq_user
WHERE user_currentvisit < (CURRENT_TIMESTAMP() - INTERVAL 60 DAY);
ORDER BY user_currentvisit ASC;

Creating Users
I've been having a little trouble creating users cross-platform. I have
a little script that works just fine on Solaris 8 using this command:

eval "/path/to/mysql --user=root --password=*****
--socket=/path/to/socket --database=mysql --execute="INSERT INTO user
(Host, User, Password, Select_priv, Insert_priv, Update_priv,
Delete_priv, Shutdown_priv, File_priv, Create_tmp_table_priv,
Lock_tables_priv) VALUES ('localhost', 'LocalUser', PASSWORD('******'),
'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y')""

With the obvious changes to make the statement actually work. However,
if I move this over to Linux, I get Invalid Authorization Specification
errors when I try to connect as LocalUser after a reload, and using
GRANT seems never actually to grant privileges (e.g. no "Y"s in the user
table). Does anybody know how I can successfully create a user with my
script?

Ranking Users
i'm trying to rank the users in our site.
table contains:
- userid
- userrates
and with thousands of records.
member with the highest rate goes #1, next is #2 and so on.
i want the query to be like:
Code:
<something goes here> WHERE userid = '$userid' order by userrates desc
from the code above it will show the rank of this user.
then the next user logsin and it shows again his/her ranking.
hope someone understands this
any input is appreaciated

Finding Users
ive got 2 tables, one of users and one of orders created by those users. The orders keep track of the user_id. I am looking to get a list of users who have not placed an order, or in other words, users who do not have their user_id in the order table.

Users And Privileges
I just installed mysql and was testing to see if i could setup privileges, now I am logged in as root using the mysql console and wanted to create a user and also give them some privileges..
I am using

mysql> GRANT SELECT,INSERT,UPDATE
-> ON *.*
-> TO bro@localhost
-> IDENTIFIED BY 'bobo';

mysql doesn`t give me an error or anything so i figured that it accepted it, but when i try to login using this new user it seems as if its now there. now i used phpMyAdmin to login using root then went to look at privileges and I donb`t see the new user anywhere..
I guess i did something wrong somewhere, any ideas on what it was? abit lost as im new and just following an ebook on this stuff

Registered Users
I have a simple bit of mysql to display the number of users on my service, by simply selecting the highest userid from the database.

"SELECT MAX(id) FROM users";

It would appear that i have not thought this through correctly, as when a user is deleted from the database, it still shows the number of registered users as being the same.
Is there an easy way to cound the number of registered users, without affecting performance too much?

Users Without Entries
Note that I'm using mysql 3....ugh.
I have a table containing users, primary key is 'id'
Another table contains their activities on a specific day
What I want to find is all users who have not entered an activity between 2 dates.
Basically the problem is that I don't know how to join 1 table along only non-existant rows of another.

Users And Trucks
I have 2 tables users and trucks. In the trucks I have a field that stores the users id.
I want to make a query that will give my all the trucks which users id doesn't exist in the table users. So if you know how this query should look like please respond.

This query gives me the total opppsite of what I want.
SELECT * FROM users, trucks WHERE truck_user=user_id

Obtain Most Popular Cities From My Users Table

I've got a 'users' table: USER_ID --- CITY_ID 1 ---- 32 2 ---- 20 3----- 32 4 ---- 32 5 ----- 17 .... I would like to obtain the most popular cities from this table, and the number of occurrences. Is this possible to make it easy with an SQL statement?

I've got a 'users' table:

USER_ID --- CITY_ID
1 ---- 32
2 ---- 20
3----- 32
4 ---- 32
5 ----- 17
....

I would like to obtain the most popular cities from this table, and the number of occurrences. Is this possible to make it easy with an SQL statement?