February 23, 2012 learn_php

PHP and MySQL database connection

PHP and MySQL Database connection

A dynamic website is not dynamic to it’s fullest unless it contains any means to store data or retreive it. There is the need to track user changes to anything within the website, for example, their user profile. Back in the older days, websites used to utilize what is known as ‘flat text files’ to store data. This previous approach you might say that it is insane, and indeed, I agree with you. When it came to searching for specific data or even merging one text file with another, it could cause headaches and even corrupt the files. Also, as programmers we have to be on the lookout to have the proper locking mechanisms, and at the same time, proper UNlocking mechanisms to be able to handle these files. These files, if accessed by 2 or more connections would cause data inconscistency. To top it off, these files become so huge, that they are very hard to use, manage, etc.

For that same reason is that the relational databases become in handy. Relational database can be seen as simple as, tables with columns and rows. The colums are the attributes of the table, and the rows are just the data pertaining to those columns, but are grouped into one record(row). These database tables contain primary keys, and these are used to uniquely identify each record within a table. Let’s say you have a table named users. This table will store data pertaining to that specific user, and we could use the name of the user to identify the person, but think of it, how many people live in the world with the same name. Here in Puerto Rico, I have met over 20 people with my same name. Inclusive, last week I went on a trip to Miami, and my flight was to the airport of Fort Lauderdale at 7pm. There was a flight before mine also to Fort Lauderdale, and to my surprise, there was a person with my same name and last name. (What are the odds?). So you see, keys are very important when storing data.

Today I want to talk about how to connect PHP with MySQL database. MySQL comes from SQL, also known as SEQUEL. To learn a little more about SQL, you can check out this post from a buddy of mine: Devcoders.net – SQL Intro.

Connection

So for now, let’s assume we have a database created, with a few tables, which we will need to query. The first thing that needs to be done is create a connection between php and mysql database. To open, or reuse a connection to a MySQL Server, we would use a predefined function in PHP mysql_connect(). This function as of PHP version 4.3.0 accepts 5 parameters, 3 of which will be used for this example. Those 3 parameters are the following, and in order:

  • Hostname
  • Username
  • Password

In my previous experiences, I have found that it is better to have a dbconn.php file within a folder. That way, if multiple pages within your site need to access the db, then all you need to do is include the dbconn file. Let’s go ahead and create that file.

Filename: dbconn.php

Next, we need to make sure that a connection was made, and if there wasn’t then stop the script execution. This is achieved with a simple if statement:

Filename: dbconn.php

Once this is done, you may execute your code. Once your code is executed, I recommend you close the database connection if it is not needed for the time being. With the simple mysql_close(); function, this can be achieved. All that is needed it the connection parameter:

Filename: dbconn.php

That’s how easy it is to connect to a MySQL Database using PHP and MySQL.

5 thoughts on “PHP and MySQL database connection

  1. Insted of mysql_library i suggest to use mysqli or pdo for database transactions. The pdo is powerful, fast, comfortable to use.

  2. Debes saber que las funciones mysql_ has sido desaconsejadas en su lugar hay que utilizar las mysqli_ o las PDO que son las recomendadas (son OOP)

    Saludos

Leave a Reply

Your email address will not be published. Required fields are marked *

The qTranslate Editor has disabled itself because it hasn't been tested with your Wordpress version yet. This is done to prevent Wordpress from malfunctioning. You can reenable it by clicking here (may cause data loss! Use at own risk!). To remove this message permanently, please update qTranslate to the corresponding version.