PHP MySQL Tutorial

PHP MySQL TutorialPreviously I covered the vast majority of both PHP and MySQL in 2 videos. This time I decided to bridge the gap and show how to simply grab data from and edit data in a MySQL database using PHP.

We specifically cover creating users with limited access for security reasons, setting up secure database connections, pulling data from a database, adding data to the database and prepared statements. All of the code follows the video below.

If you like videos like this, it helps to tell Google Plus with a click here

Code From the Video

mysqli_connect.php

<?php
// Opens a connection to the database
// Since it is a php file it won't open in a browser 
// It should be saved outside of the main web documents folder
// and imported when needed

/*
Command that gives the database user the least amount of power
as is needed.
GRANT INSERT, SELECT, DELETE, UPDATE ON test3.* 
TO 'studentweb'@'localhost' 
IDENTIFIED BY 'turtledove';
SELECT : Select rows in tables
INSERT : Insert new rows into tables
UPDATE : Change data in rows
DELETE : Delete existing rows (Remove privilege if not required)
*/

// Defined as constants so that they can't be changed
DEFINE ('DB_USER', 'studentweb');
DEFINE ('DB_PASSWORD', 'turtledove');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'test3');

// $dbc will contain a resource link to the database
// @ keeps the error from showing in the browser

$dbc = @mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
OR die('Could not connect to MySQL: ' .
mysqli_connect_error());
?>

getstudentinfo.php

<?php
// Get a connection for the database
require_once('../mysqli_connect.php');

// Create a query for the database
$query = "SELECT first_name, last_name, email, street, city, state, zip,
phone, birth_date FROM students";

// Get a response from the database by sending the connection
// and the query
$response = @mysqli_query($dbc, $query);

// If the query executed properly proceed
if($response){

echo '<table align="left"
cellspacing="5" cellpadding="8">

<tr><td align="left"><b>First Name</b></td>
<td align="left"><b>Last Name</b></td>
<td align="left"><b>Email</b></td>
<td align="left"><b>Street</b></td>
<td align="left"><b>City</b></td>
<td align="left"><b>State</b></td>
<td align="left"><b>Zip</b></td>
<td align="left"><b>Phone</b></td>
<td align="left"><b>Birth Day</b></td></tr>';

// mysqli_fetch_array will return a row of data from the query
// until no further data is available
while($row = mysqli_fetch_array($response)){

echo '<tr><td align="left">' . 
$row['first_name'] . '</td><td align="left">' . 
$row['last_name'] . '</td><td align="left">' .
$row['email'] . '</td><td align="left">' . 
$row['street'] . '</td><td align="left">' .
$row['city'] . '</td><td align="left">' . 
$row['state'] . '</td><td align="left">' .
$row['zip'] . '</td><td align="left">' . 
$row['phone'] . '</td><td align="left">' .
$row['birth_date'] . '</td><td align="left">';

echo '</tr>';
}

echo '</table>';

} else {

echo "Couldn't issue database query<br />";

echo mysqli_error($dbc);

}

// Close connection to the database
mysqli_close($dbc);

?>

addstudent.php

<html>
<head>
<title>Add Student</title>
</head>
<body>
<form action="http://localhost/studentadded.php" method="post">

<b>Add a New Student</b>

<p>First Name:
<input type="text" name="first_name" size="30" value="" />
</p>

<p>Last Name:
<input type="text" name="last_name" size="30" value="" />
</p>

<p>Email:
<input type="text" name="email" size="30" value="" />
</p>

<p>Street:
<input type="text" name="street" size="30" value="" />
</p>

<p>City:
<input type="text" name="city" size="30" value="" />
</p>

<p>State (2 Characters):
<input type="text" name="state" size="30" maxlength="2" value="" />
</p>

<p>Zip Code:
<input type="text" name="zip" size="30" maxlength="5" value="" />
</p>

<p>Phone Number:
<input type="text" name="phone" size="30" value="" />
</p>

<p>Birth Date (YYYY-MM-DD):
<input type="text" name="birth_date" size="30" value="" />
</p>

<p>Sex (M or F):
<input type="text" name="sex" size="30" maxlength="1" value="" />
</p>

<p>Lunch Cost:
<input type="text" name="lunch" size="30" value="" />
</p>

<p>
<input type="submit" name="submit" value="Send" />
</p>

</form>
</body>
</html>

studentadded.php

<html>
<head>
<title>Add Student</title>
</head>
<body>
<?php

if(isset($_POST['submit'])){
    
    $data_missing = array();
    
    if(empty($_POST['first_name'])){

        // Adds name to array
        $data_missing[] = 'First Name';

    } else {

        // Trim white space from the name and store the name
        $f_name = trim($_POST['first_name']);

    }

    if(empty($_POST['last_name'])){

        // Adds name to array
        $data_missing[] = 'Last Name';

    } else{

        // Trim white space from the name and store the name
        $l_name = trim($_POST['last_name']);

    }

    if(empty($_POST['email'])){

        // Adds name to array
        $data_missing[] = 'Email';

    } else {

        // Trim white space from the name and store the name
        $email = trim($_POST['email']);

    }

    if(empty($_POST['street'])){

        // Adds name to array
        $data_missing[] = 'Street';

    } else {

        // Trim white space from the name and store the name
        $street = trim($_POST['street']);

    }

    if(empty($_POST['city'])){

        // Adds name to array
        $data_missing[] = 'City';

    } else {

        // Trim white space from the name and store the name
        $city = trim($_POST['city']);

    }

    if(empty($_POST['state'])){

        // Adds name to array
        $data_missing[] = 'State';

    } else {

        // Trim white space from the name and store the name
        $state = trim($_POST['state']);

    }

    if(empty($_POST['zip'])){

        // Adds name to array
        $data_missing[] = 'Zip Code';

    } else {

        // Trim white space from the name and store the name
        $zip = trim($_POST['zip']);

    }

    if(empty($_POST['phone'])){

        // Adds name to array
        $data_missing[] = 'Phone Number';

    } else {

        // Trim white space from the name and store the name
        $phone = trim($_POST['phone']);

    }

    if(empty($_POST['birth_date'])){

        // Adds name to array
        $data_missing[] = 'Birth Date';

    } else {

        // Trim white space from the name and store the name
        $b_date = trim($_POST['birth_date']);

    }

    if(empty($_POST['sex'])){

        // Adds name to array
        $data_missing[] = 'Sex';

    } else {

        // Trim white space from the name and store the name
        $sex = trim($_POST['sex']);

    }

    if(empty($_POST['lunch'])){

        // Adds name to array
        $data_missing[] = 'Lunch Cost';

    } else {

        // Trim white space from the name and store the name
        $lunch = trim($_POST['lunch']);

    }
    
    if(empty($data_missing)){
        
        require_once('../mysqli_connect.php');
        
        $query = "INSERT INTO students (first_name, last_name, email,
        street, city, state, zip, phone, birth_date, sex, date_entered,
        lunch_cost, student_id) VALUES (?, ?, ?,
        ?, ?, ?, ?, ?, ?, ?, NOW(), ?, NULL)";
        
        $stmt = mysqli_prepare($dbc, $query);
        
        i Integers
        d Doubles
        b Blobs
        s Everything Else
        
        mysqli_stmt_bind_param($stmt, "ssssssisssd", $f_name,
                               $l_name, $email, $street, $city,
                               $state, $zip, $phone, $b_date,
                               $sex, $lunch);
        
        mysqli_stmt_execute($stmt);
        
        $affected_rows = mysqli_stmt_affected_rows($stmt);
        
        if($affected_rows == 1){
            
            echo 'Student Entered';
            
            mysqli_stmt_close($stmt);
            
            mysqli_close($dbc);
            
        } else {
            
            echo 'Error Occurred<br />';
            echo mysqli_error();
            
            mysqli_stmt_close($stmt);
            
            mysqli_close($dbc);
            
        }
        
    } else {
        
        echo 'You need to enter the following data<br />';
        
        foreach($data_missing as $missing){
            
            echo "$missing<br />";
            
        }
        
    }
    
}

?>

<form action="http://localhost/studentadded.php" method="post">
    
    <b>Add a New Student</b>
    
    <p>First Name:
<input type="text" name="first_name" size="30" value="" />
</p>

<p>Last Name:
<input type="text" name="last_name" size="30" value="" />
</p>

<p>Email:
<input type="text" name="email" size="30" value="" />
</p>

<p>Street:
<input type="text" name="street" size="30" value="" />
</p>

<p>City:
<input type="text" name="city" size="30" value="" />
</p>

<p>State (2 Characters):
<input type="text" name="state" size="30" maxlength="2" value="" />
</p>

<p>Zip Code:
<input type="text" name="zip" size="30" maxlength="5" value="" />
</p>

<p>Phone Number:
<input type="text" name="phone" size="30" value="" />
</p>

<p>Birth Date (YYYY-MM-DD):
<input type="text" name="birth_date" size="30" value="" />
</p>

<p>Sex (M or F):
<input type="text" name="sex" size="30" maxlength="1" value="" />
</p>

<p>Lunch Cost:
<input type="text" name="lunch" size="30" value="" />
</p>

<p>
    <input type="submit" name="submit" value="Send" />
</p>
    
</form>
</body>
</html>

20 Responses to “PHP MySQL Tutorial”

  1. Lucas says:

    Hi, are you going to continue with PHP/Mysql? It looks like a good start but still there is a lot to talk about 😉 For example PDO… Anyway, big THANKS for your effort 😀

  2. Moshe says:

    I am looking forward to every tutorial you post and enjoy them very much. I have a question: I am developing an application with a central database and several mobile users using MySQL as an exercise. Which site do you suggest for hosting the database during my trials during the development? Thanks.

  3. bongani says:

    Hi Derek. You are doing a fantastic job. Your tutorials are helpful and easy to understand. Kindly request to cover a Zend framework.Thanking you in advance.

  4. Sasha says:

    You make 11 hours tutorials in 19 minutes :). You are very talented programmer and a teacher to.

  5. Leonard says:

    Hello derek, thanks for the amazing tutorials!
    I have a suggestion for your videos: how about you make tutorial series on a big android app project that( whith users, facebook integration, posts, comments, user interactions, geolocation…….) and go through it step by step from the planning, the wireframming, UI design and development, backend programming with REST php mysql to testing, debugging, submitting…
    I kindly ask you to consider this idea since we would all learn how the process of making a complex app really works and how to effectivelly integrate the frontend to the backend ( webservices). I would follow these videos on the project with all the joy in the world. What are your thoughts on this idea?
    Thank you again for all your efforts and the videos you make. God bless you! You are a true mentor!
    Regards from Brazil

    • Derek Banas says:

      Hello Leonard,

      Yes I definitely plan on making a bunch of big apps after I cover all the basics in my new Android tutorial. Thank you for showing your interest in that. May God bless you as well 🙂

  6. Isaac says:

    Derek, thank you so much for the tutorials you provide. I really appreciate your approach on these programming lessons. I come from a networking and systems analysis (IAM) background and would like to focus on programming. I have started with Java by reading books and watching tutorials like yours online. I have two questions and I apologize if you have covered these before. If I want to get into web development what language would you learn next after Java? What profession would be a little easier to break into, web or application development?

    • Derek Banas says:

      You’re very welcome 🙂 I actively work as a web developer / Android app designer. There is a major difference between the corporate world and the small business world where I work. I basically help businesses set up shopping carts. For that all you need is PHP / JavaScript.

      There is a never ending amount of work in this area because the corporations will charge 2 to 3 times more then you need to. When you first start out though you’ll need to prove you know what you are doing. I got a huge portfolio quickly by offering to do the site for free (I owned it) in exchange for a percentage of profits indefinitely.

      If you want to get into a big corporation enter coding competitions, network with people that work where you want to work. Find out the needed skills from them. Study up on sales so that you can negotiate your way in for an interview and then into a position.

      As per application development, I have mainly only made apps for small business owners that either save them time, provide for portable CSM, provide sales tools, etc.

  7. Osahon says:

    Hello, Good Day to you. Thanks a lot for all your tutorials, they are very good.
    Please I need your help, I was asked by my supervisor to design an Online exam application with PHP and MySQL. to test students on Multiple choice, Short answers(German Objective). I have between now and 20th November to submit, please If you can be of help with the codes and structure(send to my email), you would have saved a soul. I am pretty new to programming.
    Thanks
    Osahon

    • Derek Banas says:

      Hello Osahon, The first thing you need to do is to take a deep breath. You have plenty of time to do this. Write out on paper everything you need to do as detailed as possible. I don’t know what restrictions you have? To do this, you need to understand the basics of html, php and mysql. I have tutorials on all of them. If you take your time building the system as you learn you’ll do fine. Best of luck 🙂

  8. scaramuth says:

    Great tutorials! At the end you access the php files we just created through what looks like a chrome browser, I tried to follow along but only get “this webpage is not available”. I saved the files to my hosting provider outside of the normal page files like you suggested, any suggestion on how to find the pages?

Leave a Reply

Your email address will not be published.

Google+