MYSQL - Insert

To insert a new entry in our table, we have to specify the table name in the database in which we wish to insert the records and the values we want to insert. Here is sample example explaining the insertion code.

	mysql_query("INSERT INTO Students (FirstName, LastName, Age, RollNo)
VALUES ('Dolly', 'Hunter', '15', 'AX1828')");

mysql_query("INSERT INTO Students (FirstName, LastName, Age, RollNo) 
VALUES ('Max', 'Harry', '22', 'AX1724')");

mysql_info();
	

The query inserts 2 rows into our table Students. It is often preferable to use mysql_info() at the end of any sql interaction. It prints our the details of last mysql interaction in terms of number of rows inserted/affected etc.

MYSQL - Select

In order to obtain values from tables in database, we use select query. Select query can select from a single specied row to everything present in a table. We need to select a single row in examples like if we wish to check user password etc during login. We will begin with selecting a single value stored in our table Students

	$name = "Dolly";
	$query = "select * from Students where FirstName='$name'";
	$answer = mysql_query($query)or die("Oops !! Something went wrong.");
	$res_array = mysql_fetch_array($answer);
	// Now we can use all the values stored in the row of user whose FirstName is Dolly
	echo $res_array['RollNo']; // prints out roll no (AX1828)
	echo $res_array['Lastname']; // prints Hunter
	// similarly any other details of the user can be accessed with a query
	

However, method used above is not advisable if we don't need all the fields of the particular row. Lets say , we are interested in finding out only the RollNo of the user whose Firstname is Dolly

	$name = "Dolly";
	$query = "select RollNo from Students where FirstName='$name'";
	$answer = mysql_query($query)or die("Oops !! Something went wrong.");
	$res_array = mysql_fetch_array($answer);
	// Now we can use all the values stored in the row of user whose FirstName is Dolly
	echo $res_array[0]; // prints out roll no (AX1828)
	
	// similarly if we wish to have two values lets say LastName and RollNo
	$query = "select LastName, RollNo from Students where FirstName='$name'";
	$answer = mysql_query($query)or die("Oops !! Something went wrong.");
	$res_array = mysql_fetch_array($answer);

	echo $res_array[0]; // prints out last name (Hunter)
	echo $res_array[1]; // prints out roll no (AX1828)
	

This is a more efficient way to perform query, as it reduces the number of queries because of selection of only the required fields of a row.

To select all the records we have in our table , we use a * operator

	$query = "select * from Students";
	$answer = mysql_query($query)or die("Some error occurred");
	while($array_res = mysql_fetch_array){
	   echo $array_res['Firstname'];
	   
	   }
	   
	   // Prints the first name of all the users present in the table
MYSQL - Update

Many a times , we need to update the already entered values to new value. This can be done by using UPDATE query of mysql

	$query = "UPDATE Students set age='19' where Firstname='Dolly'";
	$up_q = mysql_query($query)or die("Oops !! Cannot update now.");
MYSQL - Delete

To delete an already existing entry, we use a DELETE query.

	$query = "delete from Students where FirstName='Dolly'";
	mysql_query($query)or die("Can't delete now");

Once, we are done with basic database handling, lets see some other pre-defined functions of mysql which will further ease our database access.

Member Login

Member Login




Not a Member? Sign Up!




Login to comment

Be the first to comment on this topic







  


<<< Wanna review

Continue >>>