Day 10
Welcome to day10 of PHP in 15 Days – Guaranteed Training Course. Today we are going to do further activities with MySQL Database. You have come to the point where you need to focus and just keep working hardfor few days more and you will be done. Only few things remaining that you need to understand and let me tell you an exciting news,after covering only these three days today’s and three more, you will be able to develop web-applications on your own. Sounds good, compelling. Yes! Let’s get started.
So today we are going to learn:
· Database Creation
· Table Creation
· Making Table Relation With Other Tables
· Making Connection to MySQL through PHP and DB Selection
So, main thing you can see from this table of content we are mainly going to deal with MySQL from PHPMyAdmin as well as directly from our web-site pages. As, you may understand at this point that our actual need using of MySQL is for our website. We would like to display data from DB, search data, update and delete data from MySQL, through our website. We wouldn’t go everytime in PHPMyAdmin. Only webmaster (The Owner of Website) can go and allow himself to get into PHPMyAdmin and work there. But when it comes to dynamic pages, form submissions by users, everything is automated and we write PHP in a way that it interacts with MySQL dynamically and no one even knows what’s going on behind the scene.
Just for now let’s go back to our PHPMyAdmin and try to create a database and some tables and create some relationship between these tables.
Creating DB, Table and Creating Relation Between Tables:
This is just going to be a repeat from one of our last sessions. As most of the things we have already discussed and done. Let’s go to localhost/PHPMyAdmin and hit enter. Here you can see our database we created in a previous session phpin15days. Just a quick repeat, how you can create a database. It’s very simple. You can create it from here just by clicking New at the top of all links or you just can go to localhost by clicking here and then databases and here you can write the database name like let’s put phpin15days and hit create and your database will be created. Easy enough! As we already have one with the same name so we cannot have another one with the same name. Now in our database we created a table users in a previous tutorial and you can see its structure by clicking structure tab above here. To create a new table you can click the new link here in the left menu bar within this database or you can go to the database by clicking database name and here you can see list of your tables already created and below here you can see the option to create a new table directly. Just put the name of your new table in here define how many fields it’s going to have and just click go.
For now let’s create a table for articles. So name it as articles and it’s going to have 4 columns for now. Hit enter or GO from the link below here. And here we are in the main screen and need to define the structure of each column. This all depends on us whattype of data we want to input and outputfrom our DB according to our project we are working on. I wish you to guess what this table should do, what can be the name of the fields wouldbe here and what type of structure they should have. Please pause this training video and try to think about. Take yourpen and a notebookand write down what comes to your mind. This habit will make you organized and will stretch your capabilities to the next level to work with database.
So, the first field should be the primary key that is actually the main identity of each record coming into this table. Let’s name it artID, so we can remember that this is the id of our articles table. Let’s make it primary and auto increment, so we don’t need to provide data for this column in future. This will be automatically updated and increased by 1 for each next record storing in this table. Next column can be the name of the article so just keep a hinting name like art_name, as there is a column in users table withlabel name, and make its type as VARCHAR as variablecharacters expected in this column, and off-course we need to tell the string length or maximum number of characters this field will hold. These are the comfort zones you should decide using your common sense and keeping in mind that we are not going to overload our DB. So, keep it in reasonable limits, let’s make it to 60 characters.
Next one is article and this field will hold the whole text of the article submitted. Article can have hundreds of words with links and other stuff in it. So, instead of making it a VARCHAR we should give it a type of text. You can see here, there are several types with in text format like tiny text, medium text etc. So, we can keep it medium for now and the last one is on which date it was uploaded. Let’s name it ‘submitted_on’ and type of this to timestamp and bydefault it should take the timestamp.You can see how easy it is to create a table. Hit go. There we go our new table to store articles have been just created. Now! We need to make a relation between these two tables that is users and articles. Think about when a user will submit an article we need to have record of which user submitted this article. So, to serve this, there is a simple thing we can do, and that is, whenever a user will submit an article his userID will be stored in the articles table along with the record of that specific article. So, let’s create another column or field in articles table. Go to the articles table and go to the structure and below here you can see an option to add column, you can define how many columns you want to create and where to position them. So, we need right after the artID, so just select this and click GO. Same thing for this column or field we need to define. Let’s name it the same name as it is in users table and i.e. userID and its type is INT in users table as well also you can write comment for this column to know what was this for or some hints to remind you what kind of field is this one and for what. Let’s put a comment here ‘Primary key of users table.’ Save it. Now we have relationship between this articles table with the users table. Whenever a user will upload an article, with all other information, we will store this user’s id too. We will easily grab the user id who created this article. I am sure you are not confused at this point. This is a simple thing, as you know every single user will have its unique userID, so we can use this userID anywhere to get any information about some particular user and can make relation with any table we want. This is the way we create relationship between different tables. Now we have created a relationship between these two tables. So, how we use this relation, we’ll learn this in a coming up tutorial in few days. In the next video we are goingto make a connection between our site and MySQLdatabase.
Making Connection to Database:
First thing you should need to know that there is no any relation or connection between MySQL i.e. databasewith any website. Database is sitting somewhere else and site is somewhere else. We always need to make a connection between these two and keep that connection alive as far as we need that.
So, let’s write a script that will makea connection with MySQL. It’s as simple as just one line of code. No any technicalities. One thing keep in mind that whenever we write any command forMySQL we almost always start with ‘mysql_’ it means this is a command line for MySQL database.
So just write ‘mysql_connect’ this commandissued for MySQL to make connection and this function takes three arguments. 1: host name 2: username of the user of MySQL and 3rd one is password.
Let’s put the values for these.In our case local host is always 127.0.0.1 although you can just write localhost, but this is better to use and username is you know is root and we don’t have a password, so keep this empty. Just little more to do and we are done. ‘or’ it means if the connection was not made then ‘die’ means just kill the application don’t go further, and you can put here your custom message to better understand what the problem is, ‘Could not connect.’. You will get this message if some problem occurs and unable to connect. Now let’s go to the browser, go to the page we were working on and press enter and here you can see no any error messages. It means we have done it, we just made the connection with MySQL. Another thing you can do is inside die() function, write another function mysql_error(). What this function does, it spits out the error message which is originally generated byMySQL. Very handy and informative. So, I suggest you to keep both of these to better understand whatproblemoccurred.
mysql_connect(‘127.0.0.1’, ‘root’, ‘’) or die(mysql_error().'Could not connect.');
So, now we have created the connection. Let’s change our host IP. And now let’s get back to the browser, hit refresh and here we go. All the errors thrown by MySQL. Here you can see what the actual problem is. Network address problem, accompanying with our own custom message, ‘could not connect.’. Let’s correct it again. Back to the browser and this time, no errors.
Another thing we can do here is that we can put this whole command in a variable and writea conditional statement. If this variable has something means if it’s true, then echo ‘Connection is successful’ else ‘could not connect to MySQL’. Let’s just refresh the browser, and here, you can see the connection is successful.
if($con){
echo 'Connection is successful.';
} else {
echo 'Could not connect to MySQL';
}
Connection with Database:
We have made connection with MySQL. As you know there can be several databases in a MySQL as we seen before. So, it’s very important to select the database to work with. So, let’s select our database ‘phpin15days’ now. Like, the procedure of connecting to MySQL, database selection is also very simple and straight forward and is only one line code.
Just write another command for MySQL, ‘mysql_select_db’ and this command or function takes two arguments, first one is the name of database, as we need to select our ‘phpin15days’ database, just make sure you don’t make a spelling mistake. The second argument is link identifier. This is actually the link or connection we just created between our site and MySQL. So, just put the variable $con we just created, here as link identifier. Also, add the code we discussed before ‘or die’ to get the errors in case something goes wrong.
mysql_select_db(‘phpin15days’, $con) or die(mysql_error().'Could not connect to database.');
Let’s go back to the browser and you can see no any errors. You can use the conditional statement here too. Let’s put this command in a variable $db and write an if statement.
if($db){
echo 'Database selected.';
} else {
echo 'Database was not selected';
}
And that’s it. Go to the browser, refresh and here you can see both of the success messages. Let’s put a br tag between them. And now you can see both the success messages.
Now if we misspell database name and try to run this connection and selection code, you can see MySQL errors along with our custom messages. As MySQL searched for all the databases with the name we provided but couldn’t. So, it threw the error message along with our own custom message. Let’s change database name back to the correct name. This second parameter is reallyimportant, but still if you don’t provide it, MySQL will automatically select the latest connection used. Let’s remove it from here and just check it and you can see no any errors. But I suggest you to make your habit to always mention this with DB selection. As sometimes you might need to connect with two or moreMySQL-databases, so, at that time you must need to mention the link identifierof one of database connections, which one do you want to use for one particular query.
At the moment you can see all of this code has messed up and if you want to use this connection code for another project, you will be going to each line to change, username, password and database name etc. Let’s make it a little cleaner and easy to understand and off-course easily reusable. Let’s create some constants here and put all these values in these constants.
define('host', '127.0.0.1');
define('user', 'root');
define('password', '');
define('db', 'phpin15days');
So, we have given all the values of our project to these constants and let’s just call these constants instead of putting values directly for all the arguments. Let’s remove this IP and just place host here, and then replace root and place user, and then password and then for db name, just place the constant db. Now you can see that it’s very easy to see the host, user, password and database. Also, we can just copy paste this file for all the other projects and just change the credentials here. You don’t need to get into the code and make any errors anymore.
Just a little change here, ‘Could not connect to MySQL’ and little here ‘could not connect to database’, so if we get any errors, we know, which thing actually is not working.
$con = mysql_connect(host, user, password) or die(mysql_error().'Could not connect to MySQL');
$db = mysql_select_db(db, $con) or die(mysql_error().'Could not connect to database.');
Now we can remove the conditional statements and just make our code cleaner. Let’s try one last time, go back to the browser and you can see no errors, and it means, everything is working fine. And this is all for today’s training session. I hope you enjoyed learning new things. Don’t you ever think you have become the master of the code you just learnt, but go and try writing the code on your own! Got my point! See you on day 11, take care.
