MorkaLork Development

Interesting stuff I've picked up over the years...

PHP/MySql part 1

2009-04-16 18:09:44 | 261 views | php mysql interact database common server webhotel connection

The basics



There is no real point in using PHP if you're not gonna interact with a sql database. Mysql is one of the most commonly used SQL databases on the web, so I'm gonna take care of some basics in this article.
If you're developing on your own machine you're gonna need a server and mysql. This article won't get into how to fix all that since there is a good tutorial here: http://php-mysql-tutorial.com/wikis/php-tutorial/installing-php-and-mysql.aspx.
Install apache and mysql and get going. Or, you can get a good webhotel somewhere that supplies mysql and you won't need to get into all that stuff.

Getting into it



Right, so how do we interact with a database using PHP? This article assumes you have the basic skills of PHP, otherwize, go to www.php.net and read all the articles there.

Basically, you will have to create a connection to the database first. You can do this in several ways, and there is a separate article covering PHP/MYSQL connections, so for more detailed information, check that section out.
A connection might look like this:


$host="server"; // Host name
$username="MyUsername"; // Mysql username
$password="MyPassword"; // Mysql password
$db_name="MyDataBase"; // Database name
$tbl_name="MyTable"; // Table name

mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");


mysql_connect is the command to connect to the SQL and mysql_select_db will connect to the database right after. There are several good reasons to put a connection in a separate function. One of those reasons is that you are probably gonna work alot with Mysql once you've gotten a hang of it thus you will connect to your SQL alot. Instead of writing this, just make a function and save it in a separate file:


function ConnectDB()
{
$host="server"; // Host name
$username="MyUsername"; // Mysql username
$password="MyPassword"; // Mysql password
$db_name="MyDataBase"; // Database name
$tbl_name="MyTable"; // Table name

mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

}


This way, you just have to implement that file, let's say we call it Connector.php in your file and call the function:


require_once("Connector.php");
ConnectDB();


Now you have a connection to your database. Now you might want to do something with it. Let's say, create a table and insert things to that table.


require_once("Connector.php");
ConnectDB();

$sql = "CREATE TABLE Friends
(
Name varchar(50),
Status varchar(30),
Age int
)";
mysql_query($sql);
mysql_close();


Creating a table is, obviously, done by using the synthax CREATE TABLE tbl_name (columnName type #, col type # etc).
The column name should be self-explanatory, like in our case; it's quite obvious what we're storing.
The type depends on what you're gonna put into that particular column. Varchar takes anything, as string, while int only takes whole number, like 1, 9, 234 etc.
There are several types, check out the subitem concerning the differant datatypes for more information.

Now that we've created a table, we wan't to enter some data into it:



require_once("Connector.php");
ConnectDB();

$sql = "INSERT INTO Friends VALUES ('Home Simpson', 'Stupid', 38)";

mysql_query($sql);


Now we have a friend inserted to the database. For more information on how to use INSERT, se separate article about entering data into mysql.

To fetch this data, we'll need use yet another statement:



require_once("Connector.php");
ConnectDB();

$sql = "SELECT * FROM Friends";

$result = mysql_query($sql);


For more information on how to use the result, see the article about how to obtain data from mysql.

Right, that's the basics regarding MYSQL/PHP, please read through the subitems for more specific information.




Article comments

Feel free to comment this article using a facebook profile.

I'm using facebook accounts for identification since even akismet couldn't handle all the spam I receive every day.