Your First MySQL Database

on Sat Jul 05 08:34:04 GMT 2008 in PHP and viewed 2353 times

• 
• 

MySQL Databases are a pretty crucial part to expanding your knowledge of PHP. They can be used to store data much easier than in flat files, and are used for most big-time applications like forums or blogs.

That’s right, it’s time for your first MySQL database. First thing to do is of course make your file. So save this code as mysql_info.php

  <?php
  $connection = mysql_connect("localhost", "username", "password");
  mysql_select_db("database", $connection);
  ?>

So edit the host area, though for most places is localhost, the username, password, and the database. There you go. All done. Next, you need a table. So save this as install.php which is what I do for making tables. You can name it anything you want though.

  <?php

  include("mysql_info.php");

  //Just in case it exists....
  $beware = "DELETE TABLE IF EXISTS reviews";
  mysql_query($beware) or die(mysql_error());
  //Make the table....
  $create = "CREATE TABLE reviews (id int AUTO_INCREMENT PRIMARY KEY, title text, content text, author text)TYPE=MyISAM";
  mysql_query($create) or die(mysql_error());
  //Close the connection....
  ?>

So now let me explain. The beware statement is just to make sure I don’t have any repeats of tables or it’ll get an error. So I put that whole statement into a variable. Next you use mysql_query() command. That actually executes the query. Next part. I again make a new variable this time with the CREATE TABLE statement. What this does is make the table with the name to the right of CREATE TABLE. Then we need fields. So it’s open parenthesis ( and the fields between a close parenthesis. ) Between the two is id int AUTO_INCREMENT PRIMARY KEY. That makes a new field called id which is an integer, automatically adds one for every row added, then PRIMARY KEY which makes it original. Then I have more fields which is all text. There you go. Then execute the query, and you’re done. Now let’s add some content.

  <?php

  $insert = "INSERT INTO reviews VALUES('', 'PHP Tutorial', 'php', 'Blitz');
  mysql_query($insert) or die(mysql_error());

  ?>

Ok, first I want to explain the or die part. That just basically says if the query doesn’t work, show the problem. Next it has INSERT INTO. That’s pretty self explanatory. Then you must make sure every value is put into the table in the order that the fields are. So, id, then title, then content, then author for this one. Ok? Ok. So, now you know some stuff about MySQL databases. Look for my tutorial about Altering and Deleting Rows to expand your MySQL knowledge.