Sunday, November 11, 2007

Getting Started With PHP and MySQL in Windows with EasyPHP - Part 2

MySQL Tutorial - Configuring MySQL in EasyPHP for Windows


MySQL Logo

Intro
This is the second part of the tutorial Getting Started With PHP and MySQL in Windows with EasyPHP witch you can find see here. Make sure you see this first.

In this tutorial we will see:
  • Running MySQL server with EasyPHP
  • Configuring MySQL server
  • Adding a user in MySQL
  • Running queries MySQL command line interpreter
Running MySQL server with EasyPHP

Goto Start - Programs - EasyPHP and Click on EasyPHP.exe. This will start the Apache webserver, PHP and MySQL automatically

Configuring MySQL server

This is a step you do only once every installation.
Goto Start - Run - and type "C:\Program Files\EasyPHP1-8\mysql\bin\mysqladmin.exe" -uroot password secret
where secret is you password and click Run
What this command does is set the root (administrator, the guy that can do anything in the system) to have the password "secret" in this case. Make sure you note is somewhere you might need it at a later time.

To test that this is ok, start the Command
interpreter:
goto Start - Run and type


"C:\Program Files\EasyPHP1-8\mysql\bin\mysql.exe" -uroot -psecret
and click Run

You should see something like "Welcome to MySQL monitor. (....)

mysql>

If you see this you are ready. You have successfully configured MySQL server.

Adding a user in MySQL

You have configured the root account, and know you are ready to start experimenting. But using the root account is not the safes way to play with you database. The coolest thing to do is add a user to every MySQL database and use that account so that you don't mess everything up when you make a mistake. The way to do this is:

  • Start the MySQL Command Interpreter with your root account: Start - Run -
    "C:\Program Files\EasyPHP1-8\mysql\bin\mysql.exe" -uroot -psecret
  • Create a database: Just type "create database database_name" where database_name is the name you want. For now we will just name it "people". So you type "create database people"
  • Then you have to create the user and add it to the database. This is done with one command: GRANT ALL PRIVILEGES ON people.* TO 'my_user'@'localhost' IDENTIFIED BY 'my_password' WITH GRANT OPTION; - where my_user you put your user and my_password you password. For this tutorial will be user and pass. So
  • "GRANT ALL PRIVILEGES ON people.* TO 'user'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
  • There is no need to explain exactly what this command does and i don't thing that you really care :). In few word that command says to the server "Hey user witch uses password pass can do anything to the people database"
  • To login with this user and password Start - Run -
    "C:\Program Files\EasyPHP1-8\mysql\bin\mysql.exe" -uuser -ppass
This is the "official" end of this tutorial. The goal was to install PHP and MySQL to your Windows Machine, so that you can use all that beautiful tutorials you can find through Google. What's next is some simple SQL commands, only the basics without much details so you can get to the point.

Running queries and SQL commands in MySQL command line interpreter

The official definition of queries is "To interrogate a collection of data such as records in a database". This in simple words means that queries are the questions you give to the server and the response you get. An example of query is: "Give me the names of all people that live in New York and there last name is "Davis"". SQL (not MySQL) is the official language, the way to write this so that the computer understands what you want.

We will see the basics of MySQL in the next tutorial. For now i will give you just an example; Just type in the Command Line Interpreter:

use people
- This selects the database witch you are about to work with. in this case people database witch we just have created

CREATE TABLE example (
id INT,
name VARCHAR(100),
address VARCHAR(100)
);

MySQL Create Table Example

- This creates a table witch has an integer id, a name and an address. Varchar basically means text. More on that in next Tutorials.

Now Type:

SELECT * FROM example;

MySQL Select Example

- This command tell the MySQL serve to give you a list with all the entries in the example table. Since there aren't any entries you get:
"Empty Set" Respones. You can imaging what that is

Ok lets put some entries in there. The command for this is:

INSERT INTO example VALUES ('1', 'Nikos', 'New York');
INSERT INTO example VALUES ('2', 'Elias', 'Paris');
INSERT INTO example VALUES ('3', 'James', 'New York');

now run that Select command again:

SELECT * FROM example;

MySQL Select * Example

SELECT * FROM example WHERE address like "%New York%";


MySQL - People From New York

1 comment:

Anonymous said...

thanku very much...info provided is really helpful it worked 4 me.