Wednesday, January 19, 2011

today I learnt a bit of SQL, MySQL and PHP.

Today I learnt a bit of SQL, MySQL and PHP.

Databases are designed to store and retrieve data.
MySQL is server software that provides access to a database.
SQL is a computer language used to interact with the database.
PHP is used to make webpages based on the data stored in the database. It also allows users to store data submitted from a webpage to a database.

I started by copying the table of country populations
in to a text files separated by spaces. Then I wrote a php script to create a database and within it make a table and then load the data from the file into the table. The query looks like this:

mysql_query("INSERT INTO countries (rank, country, pop)
VALUES ('$rank','$name','$pop')");

the name of the table is 'countries' while rank, country and pop are table headers. The values are received from the file.

Then another php script outputs the contents of the database to be displayed to the user in a web browser.

$result = mysql_query("SELECT * FROM countries");

while($row = mysql_fetch_array($result))
echo "";
echo "" . $row['rank'] . "";
echo "" . $row['country'] . "";
echo "" , $row['pop'] , "";
echo "";

At this point a user can insert more data into the table using the php generated web page or delete data:

mysql_query("DELETE FROM countries WHERE rank=$_POST[rank]");

$_POST[rank] is provided by the webpage based on the users' input and it used to identify which entry is to be deleted.

But one of the most important things SQL is used for it to output certain entries that meets a criteria and also to display data in a certain way.

mysql_query("SELECT * FROM countries WHERE pop $_POST[operator] $_POST[value]");

this query takes in an operator eg. = < <= > >= != and a value and it returns entries where the population is true with the operator and value.

a sample query could be: SELECT * FROM countries WHERE pop > 50000

$result = mysql_query("SELECT * FROM countries ORDER BY rank ASC");

This last one gives back results in a certain order. In this case the data would be ordered by ascending rank (starting from 1 and going up).

If you want to learn more see w3schools for their tutorial for SQL and PHP
For a simple, all-in-one package server software to try on see XAMPP

No comments:

Post a Comment