Learning MySQL and PHP

• PHP and MySQL are two different languages that work hand in hand very well.
• In order to get us started the right way we will need our database up and running and the web server to be able to run php code. XAMPP is an excellent tool to put your localhost "alive" in under 5 minutes.
• We will need 3 informations in order to access and manipulate our databases: host ("localhost" usually), username and password (in XAMPP this comes by default with username "root" and no password.

• At first, we stored our host name (localhost), username and password in variables to call and update them a little bit easier (I can use the variable '$host' in 1000 pages and update it by only modifying this step instead of modifying all those pages one by one, that's why we store the info in variables).

• Next we define '$connect' with the actual connection code. This is very important because it's a little different from most of the examples out there.
• Notice the '@' character right at the start of the 'mysql_connect' command. It's used to hide the errors that php produces if any of the variables contain informations that are not correct and the script can't connect to the host.
• This is a very important step in writing secure code that will not leave empty doors.
• The errors can output a lot of sensitive informations and we don't wanna put that kind of info in the wrong hands so we will hide the default errors and put our own messages to identify where the error is.
• Let's take a live example for a better understanding: Instead of 'root' as the database username, I will put 'rosot' and delete the '@' that is supposed to hide my errors. Here's the ugly output: 'Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'rosot'@'localhost' (using password: YES) in D:\xampp\htdocs\test\index.php on line 9' It gives out my username, it's not a big thing since it's the wrong one but keep in mind that the same error will be shown if we enter a wrong password so it's not safe.
• Let's put back the '@' character and run this code again. The output is somehow the same because I defined this way but you can change it to whatever you want since you're in full control. We managed to control the errors and display only what we need by using an if statement.
• By default, the command 'mysql_query' executes a query the we define and store in a variable or inside the parenthesis (it's the same thing). This mysql command returns TRUE or FALSE (TRUE = I executed the query successfully, FALSE = no I didin't) and this boolean return will help us figure out if we should display an error message or success.

• if ( ! @mysql_query ( "CREATE DATABASE `roscripts`" ) )

  Another character (!) is joining the game: if ( ! //code here ) means that if the code that follows the character is not true than the condition is true. In pure english it goes this way: "If I can't execute this query, I do this...". This condition is the same with the following but a little more elegant:

  if ( @mysql_query ( "CREATE DATABASE `roscripts`" ) == FALSE )

  So if the code can't execute the query command, it hides the default errors (thanks to '@') and displays the message that I want (in our case the code dies and displays the mysql error : 'die ( mysql_error() );').

Sample Application that takes a word and Enters into database.

1. create a empty php file and name it as index.php
2. lets have a text box to enter the word and 2 buttons, one for submit and other for reset.
3. 
   
   
   
   
   
   
   
   
   " method="post">
   
   
   First name
   
   
   
   
   
   
   
   
4. In form action we are writing , because we are going to write the php functionality in the same page.
5. Now we have to write a functionality in php which will insert the word entered in text box to a specified table in the mysql database.
6. Specifying the database parameters
7. $database="mystore";
   $conn = @mysql_connect( 'localhost', 'username', 'password' ) or die( mysql_errno().': '.mysql_error().NL );
   @mysql_select_db($database) or die( "Unable to select database");
8. Now get the value of text box into a variable $name = $_POST['first'];
9. Build a sql query :$sql = "INSERT INTO terms VALUES ('$name',NULL,NULL,NULL)";
10. Run the query
11. $result = mysql_query( $sql,$conn );
    mysql_free_result( $result );
12. Thus, we have inserted the record successfully.

Display of data from mysql table onto a html page.

1. Now i want the display of records in the same page as and when i insert.
2. So, we need to write a code for displaying the data in mysql table onto html page.
3. For getting the data, we need to fire a sql query "select * from terms".
4. Build the query :$query="select * from terms";
   
5. execute the query and store the result set in a variable.
6. $rt=mysql_query($query);
7. putting the data into a unordered list.
8. echo "
   ";
   while($nt=mysql_fetch_array($rt)){
   echo "
   • $nt[term]
   "; //nt will contain each row.
   }
   echo "
   ";