Fetching Data from mysql database (Well Commented)!

[litewarez]

ok so ill be showing you how to do the following:

1. MySql Connection
2. Get Results as a Resource
3. Flip The Results to array
4. Close Connection and clear memory.

Ok so lets see how its done

<?php 

//Connection
$Host = 'localhost';       // This is where the MySql Engine is hosted
$User = 'litewarez';       // This is the username that is used to login to MySql
$Pass = 'YouWishMate';     // This is the password that is used to authenticate with MySql
$DB   = 'SampleDB';        //This is the database where all the information is stored!

//ok so we have all the information needed to connect to the database now we can connect

$ConnectionHandle = @mysql_connect($Host,$User,$Pass);

/*
      ok so we have just run the mysql_connect() function and passed in the information
      that is needed such as  ($Host $User $Pass ).
      if you notine the @ sybol before the mysql_connect(), what this does is stops
      any errors showing on the screen if it fails.. 
      this is great for security but not for debugging
*/

//Now we need to do some logic and see if the connection was successful

if($ConnectionHandle !== false){
      $BaseHandle = @mysql_select_db($DB);
}

/*
      Ok so now whats goin on here is at first we connected to the server
      but we return the output of mysql_connect() into a variable so that
      later on we can use the variable to check whether were connected!

     we have the used the expression 
     <!--
     if($ConnectionHandle !== false){
     ...
     }
     -->

     if we are unable to connect to the mysql server
     the value of $ConnectionHandle will be false 
*/

//Ok so now we do some error checking to see if its safe to proceed and show the website

if($ConnectionHandle === false){
      die("Please be patient, we are doing some work on our servers: Code:0");
}elseif($BaseHandle === false){
      die("Please be patient, we are doing some work on our servers: Code:1");
}

/*
      Ok so above what we are doing is firstly checking if we are not
      connected to the server.
      and then IF we are connected we check to see if we selected 
      the database without any issue
      
      if everything goes smooth it will continue to execute the rest of our code :)
*/

//Here were going to create a string thats going to be 
//used to select data from the database

$SelectString = "SELECT id,username,password FROM users WHERE id = %s";
$User_Id = "12";

/*
      Ok so the first variable contains a MySql string that would usually
      be used to select a particular user from the table "users"

      the reason for the %s is that were going to be using a finction called 
      "sprinf()" witch will make the security easier and cleaner.

      The $User_Id would usually be fetch ising GET or POST or SESSION but
      for this example we just using a static number witch is 12
*/

//Lets run the command and fetch the results

$Data = @mysql_query( sprintf($SelectString ,  mysql_real_escape_string($User_Id) ) );

//Learn More about sprinf() at http://uk.php.net/manual/en/function.sprintf.php#function.sprintf

/*
      Ok so now we should have the results as a resource and they would be held within $Data.
      We cant just echo the results because echo is used to print strings and $Data is a 
      resources so we cant use that so were going to use a function that will get the results into an array
*/

$UserData = mysql_fetch_array($Data); 
//were using Mysql_fetch_array because there's only one result help in there
//otherwise we would use mysql_fetch_ossoc to loop the results.

/*
     Ok so now we should have an array containing id username and password witch is what we queried for erlier.
     we going to print these out now.
*/

$BaseHTML = "
UserData:<br /><br />
User ID: %d <br />
User Name: %s <br />
User Pass: %s <br />
";

//This is just a basic html block that is just used with sprintf to 
//show what data we have on the browser.

$HTML = sprintf($BaseHTML , $UserData['id'] , $UserData['username'] , $UserData['password']);

//Now we will echo the HTML block with the users information replacing the %s and %d

echo $HTML;

//No this is important when coding to high standers as you want to keep the website running at maximum performance.

@mysql_free_result($Data); // this will free the memory thats holding that result.
@mysql_close($ConnectionHandle); //this will close the connection to mysql and free the resources it was using.

/*
      Well i hope you enjoyed this tutorial and please note that im not claiming
      to be a perfect programmer and by using my techniques i cannot guarantee
      the security to be perfect but to the best of my knowledge its pretty 
      secure and also this code is not tested as iv'e just typed it up right now.
*/
?>

Thanks all and please comment as iv'e spent alot of time commenting in so you can get an understanding.

Regards, Cornetofreak

litewarez Reviewed by litewarez on 8th May 2009. Fetching Data from mysql database (Well Commented)! ok so ill be showing you how to do the following: MySql Connection Get Results as a Resource Flip The Results to array Close Connection and clear memory. Ok so lets see how its done Rating: 5

[Curtis129]

thank you mate. this will help me

[Viz0n]

On a security note, its ideal to put database connection info in its own file and chmod it to 600 assuming you run in a suEXEc based mode.

[litewarez]

Thanks: Curtis

Viz0n: this is not a script that should be used but mearly a script that holds the common functions that are used to complete such a task as collecting information from a database system,

just a note to all readers:

Before attempting to create a website please make sure you understand how to structure your website for optimization and security!

Thanks