PHP TutorialsWeb Developer

PHP basics: MySQL Database Tutorial

Written by:

PHP and databases just go hand in hand. Being able to connect to and interact with databases is so easy and a must with any web site. Luckily PHP provides so many easy and convenient methods of database control.

First off there are a few basic terms and structures you need to understand before getting to some basic code. The hierarchy of databases first starts with the server you are on (usually localhost) and then goes to the database itself (say nickfrosty). Each database will have tables that will store information related to each other (like users or blog_comments). The tables have different fields that store the specific information for the table. Say for the users table, we have fields like username, password, and email. Then the record is an individuals grouping of fields that can be read all together. For example, a user’s profile information specific to one user’s account.

It can be kind of overwhelming and confusing at the beginning, but you’ll get it! If you want a different explanation of the structure, you can try this website. Now that you have a basic understanding of database structures, we can start with some code and learn how we can use PHP to interact with a MySQL database.

PHP/MySQL connect to database

<?php 
$connection = mysqli_connect("server", "username", "password", "database_name"); 
?>

mysqli_connect – this function takes four simple and self explanatory string parameters, the server address (e.g. localhost), your username, password, and the name of the database you want to view or change.

Running queries and counting the results

<?php 
$query = mysqli_query($connection, "SELECT * FROM users"); $results = mysqli_num_rows($query); 
?>

$query – the mysqli_query() function takes two parameters, the database connection we created earlier and a string parameter of the SQL query you want to run. Usually the query strings you use will look a lot alike, at least until you start getting into more complex queries. In this query we are saying we want to “select all records from the ‘users’ table” in our database.

Check this out:  Fix PHP executable not found error in VS Code

The queries you run can get more and more extravagant, like “SELECT id,user,password,status FROM users WHERE id=’2926′ AND user=’nickfrosty'”. This query is saying “select only the id, user, password, and status fields from the ‘users’  table, but only where the id field matches ‘2926’ and the user field matches ‘nickfrosty'”. Get it? You can learn more about how SQL commands are structured here.

$results – the mysqli_num_rows() functions take one mysqli_query() as its parameter. and returns the number of records retrieved by the query. In this case we are storing our query in the variable named $query. So if we just pass the variable as the parameter we can reuse the same query later on to retrieve the records returned.

Reading the returned results (fetch assoc)

<?php 
    while ($row = mysqli_fetch_assoc($query)){
          echo 'id: '. $row['id'] .' <br />'; 
          echo 'user: '. $row['user'] .' <br />'; 
          echo 'email: '. $row['email'] .' <br />'; 
     } 
?>

Using a while loop and the mysqli_fetch_assoc() function, we can loop through each record that the $query has returned. The function returns an array that we store in the $row variable. The returned array will have keys with the names exactly the same as the fields from your database table, e.g. user, email, etc. You can then retrieve and work with each of the fields individually from the record as you please by reading the array with the respective fields key, e.g. $row[‘user’].

For our example, we are just displaying the data stored in the id, user, and email fields from the record. And since we are in the while loop while displaying (see what I did there?), then every single user’s info will be displayed.

Check this out:  Create a website redirect with php headers

Learn more about MySQL and MySQLi

After understanding how to begin interacting with the server and database. The next step is to learn what different types of queries you can run. Each type is capable of accomplishing different tasks. You can learn more of the specifics from the php.net website.

Leave a Reply

%d bloggers like this: