Tutorials > Setting Up a Development Environment > Connecting PHP and MySQL

6: Connecting PHP and MySQL

• This article assumes you have completed the previous tutorials up to: Using MySQL - The Basics
• Goto C:\wwwroot and delete index.html, we don't need that anymore. Open index.php
• In PHP you use the function mysql_connect() to create a database connection.
• This function has three arguments: hostname, username, and password.
• For now, hostname is 'localhost', username is 'root', and the password is what you used during installation.
• It is also good to use an if() statement to make sure the connection was successful.
• Copy this code into index.php and try to pay attention to what it's doing.
  
  

  <?php
  if(mysql_connect('localhost', 'root', 'password')) {
  	echo "Connection successful.";
  } else {
  	echo "Failed to connect.";
  }
  ?>

  
• Goto a web browser and refresh localhost. It should now say 'Connection successful.'
• Select the test database using mysql_select_db(). This function only has one argument, the database name.
  
  

  <?php
  mysql_connect('localhost', 'root', 'password');
  if(mysql_select_db('test')) {
  	echo "Database selected.";
  } else {
  	echo "Unable to select database.";
  }
  ?>

  
• Refresh the browser to make sure it worked.
• Run a MySQL query using mysql_query(). This function returns a response which we can store in a variable.
  
  

  <?php
  mysql_connect('localhost', 'root', 'password');
  mysql_select_db('test');
  $result = mysql_query("SELECT * FROM groceries");
  var_dump($result);
  ?>

  
• In PHP, a variable is designated by a dollar sign. The mysql_query() response was stored in the variable $result.
• The function var_dump() shows information about variables, this function is pretty much just used for testing.
• Refresh your browser and you will see 'resource(3) of type (mysql result)'
• In order to go through the individual records in the response, use the function mysql_fetch_assoc() and a while() loop.
  
  

  <?php
  mysql_connect('localhost', 'root', 'password');
  mysql_select_db('test');
  $result = mysql_query("SELECT * FROM groceries");
  echo "<pre>";
  while($row = mysql_fetch_assoc($result)) {
  	var_dump($row);
  }
  echo "</pre>";
  ?>

  
• Wrapping var_dump() in an HTML <pre> element just makes it easier to read. (W3Schools: HTML <pre> Tag)
• Refresh your browser and you will see two arrays containing the information from the database.
• Because we used mysql_fetch_assoc() each time we go through the while loop the associative array $row gets updated.
• If we know what fields are in each row, we can use this loop to format the information.
  
  

  <?php
  mysql_connect('localhost', 'root', 'password');
  mysql_select_db('test');
  $result = mysql_query("SELECT * FROM groceries");
  ?>
  <table border='1'>
  	<tr>
  		<th>Item Name</th>
  		<th>In Cart</th>
  	</tr>
  	<?php while($row = mysql_fetch_assoc($result)) { ?>
  	<tr>
  		<td><?php echo $row['item_name']; ?></td>
  		<td><?php echo ($row['in_cart'] == true)?"Yes":"No"; ?></td>
  	</tr>
  	<?php } ?>
  </table>

  
• Using the HTML <table> element displays the data in a legible format. (W3Schools: HTML Tables)
• Lastly, lets go back to the MySQL command line and add an item to groceries.
  
  

  INSERT INTO groceries (item_name, in_cart) VALUES ('Chicken', 0);

  
• Refresh your browser and it should be updated

Return to Tutorials • Next Tutorial: Sample Website - Grocery List