A few mysql questions

[DGK101]

Hello everybody,

I have a few questions to ask about mysql – I hope you can help.

I am running mysql and PHP on a windows XP platform.

1)I have tried setting the root users’ password but I cannot seem to get it right. I use the mysql prompt with the following instructions: mysqladmin –u root password new-password, then followed by mysqladmin –u root –p reload. All seems to go fine, except for when I enter my new password that I had set – it says access is denied.
2)Do I have to administer all my users via the prompt or can I set users up and change passwords (especially my root user) via a PHP based front-end?
3)I have a text file with over 65 000 records that I would like to import into my DB. When I try to import, via PHPmyadmin, it throws me an ‘initid : no database’ error. If I cut the text file down to 100 records it imports the records perfectly. What is the best way to import these records into my DB? I preferably would like to use a PHP based front-end to accomplish this task.
4)How can one change the Date format in the DB – I need to change it to dd/mm/yyyy.

All input will be greatly appreciated…

D

 

[hvass]

It is really not worth trying to gloss over how the grant or privileges system works on mysql

http://dev.mysql.com/doc/mysql/en/GRANT.html

It may not seem it but it is very simple - it is simply a special database called mysql, the main table of which is user which has the user name and password.

You can make changes to this database using mysqladmin etc.. but if you have grant rights (which you do as root) you can simply update the user table or use the set_password syntax

http://dev.mysql.com/doc/mysql/en/SET_PASSWORD.html

So for 1) use

http://dev.mysql.com/doc/mysql/en/SET_PASSWORD.html

to set your root password then 2) use phpmyadmin
to set and administer all of your users

3) for loading larger volumes of data I would recommend using load_data syntax very quick

http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html

4) dates in mysql are yyyymmdd unless you go around fiddling with

http://dev.mysql.com/doc/mysql/en/Server_SQL_mode.html

new in 4.1 - if you want to display them differently then there is a date_format(date,format) function

http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html

date_format(somedate,'%d/%m/%Y')

 

[DGK101]

Great stuff!!

Thanks for all the input, It was really helpful.

D