Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Posted by Dennis Skoko
Dennis Skoko
12,860 Points

How to input data in MySQL using PHP.

Hi everyone

Have been doing a website for fun to learn PHP and MySQL. How I have a webpage where I can type in values and when I press submit. That form will be stored in the database.

I have been looking around the internet and I often get results where they use a different approach to connect to the database. They use a function called mysqli_connect(). But I'm connecting the database as they do in "Using PHP and MySQL".

If someone can point me on the right path to accomplish this that will be much appreciated! This is what I tried but didn't work. If you need more information just let me know.

Regards, Dennis Skoko

    $add_member = $db->prepare("INSERT INTO roster (name, avatar, role) VALUES (?, ?, ?)");
    $add_member->bindParam(1, $_POST["member_name"]);
    $add_member->bindParam(2, $_POST["member_avatar"]);
    $add_member->bindParam(3, $_POST["member_role"]);
    $add_member->execute();

1 Answer

Jim Hill
Jim Hill
1,758 Points

Hey there Dennis.

Just to clear things up, in PHP world there are multiple ways of connecting to different data stores. The two you will encounter most often with MySQL are mysqli and PDO.

The biggest difference is that mysql is specific to MySQL whereas PDO (PHP Data Objects) operates as a standard way of talking to lots of different data stores. So using PDO you could switch to another DBMS e.g. from MySQL to PostgreSQL without it affecting your code. It is also object-orientated which may or may not matter to you at this stage.

The biggest bonus of both is using prepared statements and parameterised querying. This will likely sound like jargon so again to explain in a nutshell. It is there to help you with security and helps you avoid what's called a "MySQL Injection" attack. As a basic explanation imagine a user enters some data on a form. They submit the data and you want to add it into your database:

INPUT into mytable VALUES ($_POST['user_entered_data'])

It is now possible for a naughty user to enter something malicious as their entered data. If the user entered:

); DELETE FROM customers WHERE 1 or username = '';

Your query would become:

INPUT into mytable VALUES (); DELETE FROM customers WHERE 1 or username = '';)

Oh.

So this is where PDO steps in. By making a prepared statement, the variables are inserted as parameters and behind the scenes PDO can sanitise and check all the data to stop it being injected as an actual SQL query.

In your example

You have basically just made a prepared statement but you also need to connect to the database first to then be able to run your query. An easy way to run this is like:

try {

    $conn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    

    $add_member = $conn->prepare("INSERT INTO roster (name, avatar, role) VALUES (?, ?, ?)");
    $add_member->bindParam(1, $_POST["member_name"]);
    $add_member->bindParam(2, $_POST["member_avatar"]);
    $add_member->bindParam(3, $_POST["member_role"]);
    $add_member->execute();

} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

That is now connecting, setting some attributes on PDO, creating your prepared statement, running it and if there are any problems a PDOException is thrown and the error message is printed.

Hope that clears it up.

Dennis Skoko
Dennis Skoko
12,860 Points

Thanks for the quick respond. My mistake for not writing that above my code I included the file that connects to the database. I finally manged to make it work and it was from a misspell in my code. But thank you for telling me the differences between mysqi_connect and PDO and SQL injection.

Dennis Skoko