PHP orders to work with Database and some useful MYSQL orders

Here , I list a bunch of Mysql orders that are needed most.
To work with Mysql on yourown computer you can install WAMP server and then go to phpmyadmin page:

http://localhost/phpmyadmin/index.php

There , create a new database . A database is look like a matrix. It contains tables that are rows of the matrix and fields that are columns of the matrix.

phpmyadmin is very user friendly so you don't need to deal with the Queries that are needed to create a database , tables and fields .(the orders in SQL are called query)

On the other hand phpmyadmin will return you the queries of the creation process and you can learn them if you want. BUT most servers have database manager program that is very user friendly.
In a website you can create your databases , tables and fields on the server and then you use PHP codes to put or delete data inside the the databases.

PHP orders to work with Database:

-----------------------------------------------
Connecting to a database :

To connect the stream to a database we need two PHP orders :

mysql_connect and mysql_select_db

The form of these orders are in the following :

connection variable = mysql_connect( "Host name" , "Username" , "password");
mysql_select_db("database name" , connection variable);

For example :

Code:
$myconn = mysql_connect( "localhost" , "root" , ""); // When you use WAMP as sever your host would be "localhost" and the username for localhost is "root" and there's no password for it. This line of code would connect the stream to the localhost server , and assign "$myconn" variable to this connection.

mysql_select_db("mydb2",$myconn); // This line of code would connect the stream to "mydb2" database on the localhost server through "myconn" variable.

This code connects your stream to "mydb2" database on yourown computer.

-----------------------------------------------
Inject queries in to the database:

To order to your databases you can use This PHP order :

mysql_query (a string variable contains mysql query);

An example is in the following

-----------------------------------------------
Insert data in to a database :

This SQL query adds data in to your mysql :

INSERT INTO TableName (FieldName1,FieldName2,...) VALUES ('value for field1','value for field2',...)

For example :
Open your phpmyadmin and create a database with this features :

name : dabase2
tables : just one table "users" cotains three fields username , password , joindate.

Now after connection add these codes :

code :
$entered_usr = "Thomas";
$entered_pass = "secret";
$Today = date("Ymd");
$salt = "qw";
$entered_pass = crypt($entered_pass,$salt); // this order hashes the password so it can't be hacked easilly.
$sql = "INSERT INTO users (username,password,joindate) VALUES ('$entered_usr','$entered_pass','$Today')"; // this is our query to insert the data in to the table "users" .
mysql_query($sql); // this order sends the query in to the database to be acted.

-----------------------------------------------
Searching a database (extract data from database) :
The Query is :

SELECT * FROM table_name WHERE Field_name = 'Value_for_the_field'

Let's describe this part by an example :
If we want to find the password corresponds to a username we can use the following codes.

code:
$sql = "SELECT * FROM users WHERE username='$entered_usr' "; // this query select from "users" table the enteries that have username equals to '$entered_usr'.
$search_result = mysql_query($sql); // the result of the query is saved in "$search_result" variable
$password = mysql_result($search_result,0,1); // here the password is extracted from data. more explanation in the following.

The search result format is :

mysql_result($search_result,result index,Field index)

result index : that begins from zero. Here it should be zero because we should have just one result.
Field index : that begins from zero . for example it's zero for "username" and one for "password" field.

-----------------------------------------------
How to count the results of a search :
there are two way to search the number of results :

1. Direct counting

For Example : these codes count the number of results of an entered username and if there is any result for it , it shows a message that the entered username's already existed in the database and the user should choose an other username.

code :
$entered_usr = "James"; // it's just an example it can be replaced by a text box.
$Sql = "SELECT COUNT(*) FROM  users  WHERE username='$entered_usr' "; // this query counts the number of rows in the database that contain the entered username.
$Result = mysql_query($Sql) or die(mysql_error() . "<br>SQL: " . $Sql); //  the result of this query is just a number that would be put in "$Result" variable.
if(mysql_result($Result, 0) > 0 ){
 echo "username's already existed";
}

2. Count After "SELECT" order

$Total_number_of_Results = mysql_num_rows($search_result); // it counts the number of results that are exracted from the database.

-----------------------------------------------
Update the entered data in a database
For example these codes change the password of a user to a new password in the "users" table.

code:
$sql = "UPDATE users SET password = '$new_entered_password' WHERE username = '$entered_usr' "; // "users" is the table name
mysql_query($sql);