PHP + MySQL

Php Mysql Banner
When studying electronics, it is very rare that you would come across a syllabus that teaches PHP + MySQL. It’s for the guys studying IT.

I found myself needing it when developing electronic systems that requires Internet connectivity. Simply put, PHP and MySQL allows an electronic device to retrieve and store data through the Internet. This will of course enable that particular system to be controlled and accessed from anywhere in the world as long as there’s an Internet connection.

So, what is PHP and MySQL?

PHP

PHP was created in 1995. It was originally called Personal Home Page tools. Because of its’ evolution, it is now called PHP : Hypertext Preprocessor.
It is a server-side scripting language and it functions by retrieving information from the MySQL database to create a dynamic content on the webpage.
It can also write information into the MySQL database.
It is also very versatile, it can run on different platforms, such as Windows, Linux, etc.

MySQL

MySQL is a database management system.
Server Query Language (SQL) is used to issue commands to retrieve or store data on the database.
The MySQL database resides in a server that stores information in tables.

As stated above, PHP and MySQL are often use for web development, as a matter of fact this very website that you’re on uses it. It is very versatile and is only limited by your imagination.

Let’s get started to see how it works!

Many online tutorials and books will tell you to install a virtual server on your computer to run your code when learning PHP and MySQL. The tool is called XAMPP and it runs on both Mac and Windows operating systems. http://www.apachefriends.org/en/xampp.html

Personally, I’ve chosen to use an actual server when learning PHP and MySQL. You can easily find a free hosting provider but make sure that it supports PHP + MySQL.

This one supports up to 2 MySQL databases. For learning purposes, that is more than enough.

Proceed to sign up, and log into the CPanel.

Click on MySQL to create a new database.

Once the database is created, head over to phpMyAdmin to manage the new database.

As a general rule of thumb, change the Collation settings to UTF8 general_ci. This is the standard character set and encoding used in most web development.

On the main page, phpMyAdmin is telling that there is no tables in the database. Let’s go ahead and create one. I named it ‘table1’ and created 3 fields. Fields can also be referred to as columns.

My simple aim is to record an On or Off status sent by an electronic system and also the date and time of the occurrence.

In actual fact, I don’t really need the Number column, but it is a good practice to create a column and set it as a primary key.

Primary key is an identification of the particular table and it is used to link a few tables together, the database needs a common attribute in order to do the linking.

Take note that I set the “No.” column to auto increment. Every time when I add data to the table, the No. column will increment by itself, it doesn’t need the user’s intervention.

After the table is successfully created, let’s enter some sample data into it.

SQL language is case-insensitive (‘INSERT’ is the same as ‘insert’) except for the names of the Column.

To enter data into the table, we use the INSERT instruction.

INSERT INTO table1 (column1,column2) VALUES (value1,value2)

Take note that we don’t insert values into the first column (No.) because it is automatically done by the database (auto increment).

For the date and time column, just use the built-in function now() to get the current date and time. Do note that it will get the server’s date and time, NOT your computer.

After creating four rows of data in the table, let’s view it by using the SELECT instruction.

SELECT * FROM table1

The asterisk (*) is a wildcard, meaning to choose ALL. If we wanted to see one column only, we can use SELECT Status FROM table1.

Here’s the results.

We can do many things with the data in the table. Use the Export function to create an Excel file, then you can proceed to do some analysis with it, create graphs, charts, etc.

There are many more instructions that you can use in MySQL. I’ll make a summary for each of it.

SELECT
SELECT * FROM tableName
INSERT
INSERT INTO tableName (column1,column2) VALUES (value1,value2)
WHERE
SELECT * FROM tableName WHERE Status=1
ORDER BY
SELECT * FROM tableName ORDER BY No DESC
UPDATE
UPDATE tableName SET Status=1 WHERE No=4
DELETE
DELETE FROM tableName where No=4

Now, I’ll proceed to PHP.

I’m using my favourite web development program, Dreamweaver. I’ve been using it since the Macromedia days.

This is the home screen.

Before we start, we need to set up the server in Dreamweaver.

Go to Site -> New Site. Give it a name and choose the local directory to store your files.

Under Servers, add a new one by clicking on the ‘+’ symbol. The details of your server can be found in the CPanel.

On the bottom right of the screen, you can find your server window. To connect, click on the Plug icon.

I’ve wrote a simple php page that will display the table contents.

mysql_connect(“mysql11.000webhost.com”,”a3467664_waihung”,”abc123″);

This line establishes a connection to the database. The first argument is the url of the MySQL database, followed by the username and the password.

mysql_select_db(“a3467664_db”, $con);

This line specifies the name of the database to be connected.

$result = mysql_query(“select * from table1 order by No ASC”);

This line is to extract table1 from the database and order it according to the No. column in ascending order (1,2,3,..).

while($row = mysql_fetch_array($result))
{
echo $row[‘No’] . “&nbsp&nbsp&nbsp” . $row[‘DT’] . “&nbsp&nbsp&nbsp” . $row[‘Status’];
echo “<br />”;
}

This while loop prints every row of the table and it will stop at the last row of the table.

Press F12 in Dreamweaver to debug. It will proceed to upload the php code to the server and launch the page from the server. As mentioned before, PHP is a server-side scripting language. You cannot run it from your computer, it will not work.

Next, we will use PHP to insert data into the database. It will take a user’s input from a HTML form and write it into the table. For this, we will need two files, a HTML file and a PHP file.

The HTML file contains a text field and a submit button. The user will enter 0 or 1 in the Status field and hit the Submit button, which will then call the PHP file to process the input.

And the PHP file.

$sql=”INSERT INTO table1 (DT,Status) VALUES (now(),’$_POST[status]’)”;

This line inserts a new row into table1 with the current date/time and also the user’s input from the status text field in the HTML file.

Upon successful insertion, the PHP file will echo “1 record added”.

That’s the fundamentals of PHP and MySQL and I hope this has been informative to my dear readers.

Thank you.

Download Code

Leave a Reply