MYSQL

Talking about the storage in MYSQL, it is done in multi-dimensional tables. We make a table at the beginning and then values are stored in these tables using queries.

Queries are a type of request to our already existing database to either seek information about some of the entries or in order to add new ones or update the already existing entries.

Mysql Connection

Mysql interaction starts with a connection to the mysql server. This is done by mysql_connect() function in PHP. It needs servername, username and password as three arguments.

	mysql_connect(servername,username,password);
	

Opening a database connection will be more clear through this example :-

$con = mysql_connect("yourServerName","userName","passWord");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

// some code

mysql_close($con); // closes the mysql connection

However, the connection closes automatically at the end of the script. It is a good practice to close the connection by your own. Last line in the above example closes the connection.

Creating database

After we are able to open the connection with our database, lets make our first database say myFtrstDB.

$query = "CREATE DATABASE myFirstDB";
$answer = mysql_query($query)or die("Database creation failed.);
echo "Database creation successfull";

Please note that you need to establish the connection with mysql server first. Only then you can create database or perform any other action.

Creating tables

Lets create some tables now in the already created table. In a table , you can have as many number of fields you wish. Each field stores values for each of the entries.We need to select a database (already created) to make a table under that database. Here is the basic syntax for creating a table.

// connecting to mysql
$con = mysql_connect("yourServerName","userName","passWord")or die("Cannot connect to mysql");
// selecting database
mysql_select_db("myFirstDB", $con);
// creating table in selected database(myFirstDB)
$sql = "CREATE TABLE Student
(
FirstName varchar(25),
LastName varchar(25),
Age int,
RollNo varchar(10)
)";

// Execute query
mysql_query($sql,$con);
Unique and Auto-incrementing fields

Many a times, we wish to have some unique field like Username or some auto counting fields so that we can get idea about the number of people already member of our site. For this, we can set it to be a primary key. Primary key must have a unique value in a table. Moreover, it also doesn't take NULL value. For counting the number of enteries in the table, we can set the AUTO_INCREMENT . It automatically increases the value of the field by 1 , everytime when a new entry is added to the table.

$sql = "CREATE TABLE Students 
(
personID int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(personID),
FirstName varchar(25),
LastName varchar(25),
Age int,
RollNo varchar(10)
)";

mysql_query($sql,$con);

Lets see other important operations to interact with the newly created table in our next chapter

Member Login

Member Login




Not a Member? Sign Up!




Login to comment

Be the first to comment on this topic







  


<<< Wanna review

Continue >>>