Hello andwelcome to day 11. Today’s sessions is going to be very exciting. What we will do today is, we are going to insert data in our database, but this time without using PHPMyAdmin. Also, we will write pure MySQL queries to insert data. So, fasten your seat belts, we are going to take off in a moment. So, the table of content for today is:

·         What Is Query

·         Insert Query

·         Insert Query For Multiple Data

·         Insert Data through PHP

·         Insert Form Data To Database

What is Query:

Query in terms of MySQL is basically a line or string or sentence of command complying with MySQL syntax and containing full detail of what kind of information you need to, insert, update, delete or search for. All processing of MySQL depends on the command you issue in your query. So, basically you place an order to MySQL to do something. There are 4 things you can do with a query. Create new data in DB, Read the data from DB means you can search for any data, Update any record that already exists in database and you can delete any record from database. So, in short, we call these activities collectively a CRUD.

C:Create

R:Read

U:Update

D:Delete

Now let’s go to PHPMyAdmin and write some basic queries.

Insert Query:

Here we are in PHPMyAdmin and within our database phpin15days. You can see SQL tab or link here just click on it and you are in the query writing mode. Here you can write queries. This query here at this level will be executed for database phpin15days. But we want to write query to insert record in one of the table. So, let’s first go inside a table of our database. You can go to any table you want in which you need to insert data, like articles or users, but for now let’s just go to our users’ table. From here click on this SQL tab and you are in query writing mode or you can say it as command prompt. Below here you can see many tabs, select, insert, update, delete and clear etc. These arehere tohelp and provideyou the basic query syntax as well as basic query which makes it more user friendly. Let’s click on insert tab and you can see we have a fully functional query, with hints for values and columns. We can use this query but right now let’stry to write our own query that will help us understand it easily. Let’s remove this all.

To insert a record we always write ‘INSERT INTO’ these are reserved words of MySQL to insert some data in DB. MySQL hasextremely readable query syntax that seemsjust like general day to day English language. Here you can guess thatwe are telling MySQLto insert something into, so, into what, would be our table, just put the table name here. So, whenever you want to insert something you write ‘INSERT INTO’ and then table name in which you want to insert data. Now right after table name start parenthesis and then ‘VALUES’that’s another reserved word, and then parenthesis again. 

INSERT INTO users  () VALUES ()

In firs parenthesis, we will declare which are the fields or column names in which we need to insert data. On the right side you can see all the names of the columns of this table. Fornow just assume we want to insert data in only one column. So, simply write the name of this column here and that’s going to be name. After these parenthesis we have written‘VALUES’a reserved word, which tells MySQL that now the values will be defined in next coming parenthesis. So, here simply put the value for the specified column. It means put the value to insert as a new record in name column. Here you need to be careful about the type of this column. If the structure is other than INT or its family, or not a Boolean, float or double, you must place the value within single quotes. Let’s check the type of this column and here you can see it isVARCHAR, so let’s put a value here and that is my name ‘Saud’ in single quotes. Click GO and that’s it, we have inserted first record in users’ table with writing a query.

INSERT INTO users (name) VALUES (‘Saud’)

Let’s browse and see whether the value has been inserted in users’ table. And yes it is. We have done it. We inserted first record successfully in one of the tables by writing a MySQL query.

You may be confused right now and can think, why are we doing this? We have PHPMyAdmin, so, what is the purpose to understand these written queries?

The answer is, I clarified this before, that most of the time you will insert data in database through your site dynamically and this will work for you then and if I am not wrong you need to use thesequeries execution around 99% of total record manipulation of database. We will write suchqueries right inside thePHP code and will execute them. So, you must have to understand the syntax as well as the methodology.

Here you can see the ‘joined’ field is empty. So, we can update this field. Make it timestamp and give it a default value, so we don’t need to provide a value to insert anymore. It will automatically pick the current timestamp. Let’s go to the structure of this table. And let’s change this field only, by clicking here, change and you can see we make its type date before, let’s change it to timestamp and in default value change it to timestamp too. Now, whenever a new record will be inserted MySQL will take the current time and save in this field automatically. Save it. And you can see table has been altered. So, now, here we will get the timestamp when new record will be inserted. You can see the format of these zeroesis there and according to data. Let’s insert another record. Click SQL to go to the command prompt.

You can put back-techs around table name as well as field names, rather, it’s better to put back-techs.

Start parenthesis and then ‘VALUES’ and then parenthesis again. This time we want to insert the record only in the name column again so, just name here and value is going to be a name, let’s write ‘Micheal’ here, and click GO, 1 row inserted again. Let’s browse the table and you can see new record has been inserted with the value we provided along with the timestamp. Use anyMySQLfunctionality thatcan make things easier, like we usedfor a column‘joined’. Let’s get back to command prompt. Now we want to insert values in two or more column in users’ table forone record. Let’s do this. We want to insert data for ‘name’ and ‘username’ so just put a comma between the names these columns. This is how you can insertdata for as many columns as you wantfor one record.

Now we need to provide values here.Few things you must always consider while writing the insert query.

·         Sequence must remain same within name of the columns as well as the values you provide.

·         Number of names of columns must be equal to the number of values.

·         Data type of values should match the data type set for relevant columns.

First is ‘name’ so first value will be inserted in‘name’ column and so on. Provide the value for ‘username’ as well. Now we have provided some dummy data within parenthesis, just click GO and here we go, another record has been inserted. Now let’s browse the table to see new inserted data. You can see one new record containing two values for two different fields. So, this the way you can insert a record and you can insert data in any or all of the fields you want. Let’s go to the structure of users table and you can see here two more fields ‘password’ and salt with the character lengths 64 each. When we will insert data through PHP in these columns we will encrypt the data to 64 or 32 characters.

For now let’s just insert another dummy recordin all of the columns of users table. Here is the insert query with all fields and values maintaining the sequence and type. You can write MySQL reserved words in lower case and this will work but it’s better to write them in upper case. Let’s change these all to lower case. Click GO. And you can see without any error 1 new row inserted again. Let’s browse the table and you can see all the data has been inserted in all specified columns.

insert into users (`name`, `username`, `password`) values (‘Saud’, ‘saudash’, ‘123’)

Now let’s move one step further and try to insert multiple records by writing just one query. Same as before, write the whole query and write the values for one more dummy record, till here you are inserting just one record and you are familiar with this, but as we want to insert more than one records, we need just to put a comma and again parenthesis and within these, we provide the values for the second record, just keep the sequence and consider the type of data and just continue for third one just put a commaagain and then values for third record. So, here you can see we defined field names once only, used ‘VALUES’ reserved word for once and for each record, just providing values between parenthesis and separated them with commas. Let’s try to insert these records. Hit GO. OK. Here we have an error message. Yes! We missed to mention the name of table, as MySQL didn’t know in which table we want to insert this data. Let’s put ‘users’ the name of users table. Try one more time, click GO and this time ‘3 rows inserted’. Let’s browse the table and you can see all three records we just inserted.

insert into `users` (`name`, `username`, `password`) values (‘james’, ‘jame123’, ‘1234’), (‘Micheal’, ‘mic12’, ‘pass12’), (‘Jhonson’, ‘jhon12’, ‘word23’)

This is how we can write the insert query and insert record in MySQL. You need to have a sound grip on the insert query syntax and related issues, so that when we use this query within PHP, you wouldn’t get confused at all.

Insert Data Through PHP:

Now, we have learnt how to insert data using PHPMyAdmin Command prompt andexactly understand how to write an insert query.Now is the time to insert data throughPHP pages. Let’s do it now.

As we are going to deal with MySQL so, first thing we need is connection to MySQL. We need to connect to database as well as select the database we want to work with. Instead making a connection we can use our separate connection file we created in a previous training session. We can require that file whenever and wherever we needMySQL connection. So, let’s just require_once our connection file. Now we have the connection established and ready use it for execution of queries. Here we just need one more thing and that is mysql_qury and then parenthesis and between parenthesis and double or single quotes, we will write the same insert query we learnt before.mysql_query is a special command issued for MySQL to execute a query, we’ll just provide it. You can format this as you like. But just keep in mind that your syntax should be correct. So, let’s write the a query here

$qr = mysql_query("

INSERT INTO users () VALUES ()

");

And you can see that, this is same query we have been writing in PHPMyAdmin. Let’s try this in our browser and run this script. You can see we get no errors. The query has been run and it’s perfectly fine.  Now let’s put some dummy data for only one column that is name and put a dummy name and i.e. ‘Stone Cold’ and again run our script in browser. Now let’s go and check if any record has been submitted in users table or not. You can see two empty records. The first one is when we run the empty query but the second one is also empty. Let’s check back why it wasn’t recorded. And off-course, we missed the table name. Let’s put the table name `users` and get back to the browser, refresh and now it must have inserted one record. And you can see, another record with our dummy data has been inserted.

$qr = mysql_query("



        INSERT INTO users (`name`) VALUES ('Stone Cold')



        ");

We can off-course insert data in more than one columns. Let’s do this for ‘username’ and give this a dummy value ‘stone132’ and another field name that is `password` and value for this, let’s just put ‘abcdef’ for now. You can see we keep the sequence for columns and valuesas well as data type in consideration for the values we want to insert according to the type we set for columns. Let’s run our script one more time, and get back to the PHPMyAdmin and browse the users table again. And you can see all our dummy data has been inserted in the relevant columns. This is how we write insert query with in PHP and execute it.

$qr = mysql_query("



        INSERT INTO users (`name`, `username`, `password`) VALUES ('Stone Cold', 'stone132', 'abcdef')



        ");

But right now when our query executes successfully we don’t get any idea about within our browser. So, let’s give our website users a success message when their data is saved in database successfully. So, just putthis query within a variable. Create a conditional statement,if this query executes successfully, echo ‘Record inserted successfully’ otherwise echo ‘record was not inserted.’ This will show the message either query executes successfully or not.

if($qr){

echo 'Record inserted successfully.';

} else {

echo 'Record was not inserted.';

}

Let’s get back to our PHP page and run the script again. Here we go, success message. So, now the user would know that the record he submittedhas been inserted successfully.

Now we have a fully developed script which is capable of inserting the data into the users table. But still, this code is inserting the hard quotedvalues into the table. We need a script or codethatcan grab the form data, a user inputs through our site, and insert it into the users’ table.

To serve this we need a form on our site. Let’s say we are going to create a user registration form. Let’s create a form quickly in a new file and name it‘form.php’. We will submit this form data on our page i.e. insert.php file we just created, which has the whole script to insert data into the table. So, here we have our form created. This is really a simple HTML form, no too much technicalities at all. Let’s check this out in our browser.

<h2> Registration Form </h2>

<form method="post" action="insert.php">

    Name: <input type="text" name="name"><br>

    User Name: <input type="text" name="username"><br>

    Password: <input type="password" name="password"><br>

<input type="submit" value="Register">

</form>

Here you can see the form thatvisitors of our site are going to fill and submitto get registered with us. Don’t get confuse yourself by user visiting and registering. The real thing behind the scene is as simple as just submitting the form data on a file named insert.php.  Let’s try to register one user. Let’s put some dummy data in all fields, I am notgoing to sanitize this data right now.As we have already studied about sanitizing data in detail and you can watch one of the previous video tutorials of this course in which I discussed sainting the form data in detail. So, todayI just wantto demonstrateform data submission to database and I am not going into sanitizing this data again. Sonow, we have provided some dummy data in our form, just try to submit it. Here you can see the success message. This is because our form submitted data on insert.php and took us to that page, but what happened here, the script which was written and hard quoted, executed and this execution of script has no any relationship with our form submission. We need to make a relation between our code and form data. Let’s go to the users table and see what our script inserted.And off-course you can see, previously hard quoted data inserted one more time. Now, we will grab the form data and insert that into our table. So, let’s first grab the form data. So, first of all create a conditional statement, if the form submitted, as we don’t want this script to generate any errors. So, if the form has been submitted then and only then, extract, I’m going to use the extract function. What this function will do is, it will grab all the keys and will make each of them avariable and will assign their relevant values to them. So, just use extract and put the whole ‘$_POST’ super global array into it. Now, we want this insert query executed only if the form has been submitted. So, we need to bring this query along with its output within this conditional statement. Let’s cut it from here and bring it within this conditional statement.

Now, the only thing is remaining to replace the hard quoted data with actual data our user will submit and we stored it in variables we just extracted from $_POST array.  Now go ahead and change hard quoted ‘Stone Cold’ with ‘$name’, ‘stone132’ with ‘$username’ and ‘abcdef’ with ‘$password’ variables. Just try to take care of the data typeof the values you supply. So, we need to put single quotes around these variables to make their type string. So, here we are. We have successfully converted our hard-quoted script into a fully dynamic script. What’s this gona do, it will grab the form data and fit it into the insert query, execute the query and if query executes successfully it will show us a success message, otherwise a failure message.

if($_POST){

extract($_POST);

$qr = mysql_query("

            INSERT INTO users (`name`, `username`, `password`) VALUES ('$name', '$username', '$password')

            ");



if($qr){

echo 'Record inserted successfully.';

    } else {

echo 'Record was not inserted.';

    }

}

Now it’s time to run the code. Let’s get back to the user registration form, refresh it, put some dummy data here again and hit Register button. Here we go.You can see the message ‘Record inserted successfully’. This success message is the real one and is the result of the form submission. The script executed only when the form was submitted and tried to execute the insert query putting the data we just submitted through the registration form and on successful query execution itdisplayed the success message. Let’s browse the users’ table to verify and here you can see the data we just registered with, has been stored. So, now we have successfully develop a code, which is letting our website visitors’ register with us.

I feel there is one thing, that might confuse you and that is this extract function I used. Although it makes our work whole lot easier, but let’s do it in another way. Let’s just grab the data from our form in a way that might look, little bit more realistic. So, just call all values from ‘$_POST’ one by oneby mentioning the keys within the square brackets, and assign these to our own defined variables. Now, I’m sure it’s pretty much clear to you.

if($_POST){
$name = $_POST['name'];
$username = $_POST['username'];
$password = $_POST['password'];
$qr = mysql_query("
            INSERT INTO users (`name`, `username`, `password`) VALUES ('$name', '$username', '$password')
            ");

if($qr){
echo 'Record inserted successfully.';
    } else {
echo 'Record was not inserted.';
    }
}

Let’s get back to the browser and check if everything is fine. Go to the registration form and put dummy data here again, hit register and there we go, ‘Record inserted successfully.’ Just check the users table to make sure everything was stored, and here we have our new data.

So, this is all for today’s session. I recommend you try to insert data in different tables, for different columns and repeat theprocess as many times as you can.See you on day12 with new venture, take care.

Practice files Day #11

         Download