Monday, March 19, 2012

Insert one or multiple rows in mysql db using php

Normally, one of the first thing that you need to do after connect to a mysql database is insert data. This tutorial is about inserting a single row or multiple rows into a mysql database using php. If you don't know how to connect to a database check our previous tutorial "Connect to a mysql database using php".

Let's imagine for this example that my table name is "people" and inside this table i have 3 columns "id", "first_name" and " last_name".

This way to insert a single row with the person "Chuck Norris" i need to execute the following query:

$validation = mysql_query("INSERT INTO platform_types 
                         (id, first_name, last_name) 
                         VALUES ('1', 'Chuck', 'Norris')"
                         ,$db_con);

if (!$validation) //test query for errors 
{
    echo("Error in query: " . mysql_errno($apbl_db_con) . ": " 
    . mysql_error($apbl_db_con) . '
');             
}
//Closing connection (don't forget to close the connection 
//when you don't need to query stuff to the db)
apbl_db_close($apbl_db_con); 

The $db_con variable is the one returned by mysql_connect when you connect to the database. If you need to insert multiple rows at same time the procedure is the same but you add more values at the end like this:

$validation = mysql_query("INSERT INTO platform_types 
    (id, first_name, last_name) VALUES 
    ('1', 'Chuck', 'Norris'), 
    ('2', 'Jackie', 'Chan')",$db_con);

//...the rest of the code it's the same.
 Notice that if your "id" column is set to auto-increment (usually is) you don't need to give a value to the "id" in the query because it will auto-increment by him self. Then your query will look like this:

$validation = mysql_query("INSERT INTO platform_types 
    (first_name, last_name) VALUES 
    ('Chuck', 'Norris'), 
    ('Jackie', 'Chan')",$db_con);

//...the rest of the code it's the same.

No comments:

Post a Comment