Simple CRUD with PHP & MySQLi

It’s all well & good being able to get info from a database, but what you really want to do it add, edit and remove it.

To save you tons of time looking for the code (like I did), here’s some snippets that should make life a lit easier when working with MySQL.
I’m also going to be using the more up-to-date MySQLi methods. They’re just better all round.

Note that you’ll need to understand how to make a database and add columns in them with a tool like phpMyAdmin or Sequel Pro (Mac).
I’ve added no validation either, as it varies each time it’s needed.

Connection to DB

<?php	
	$db_server = "localhost"; // This value can change from host to host.
	$db_user = "root";
	$db_pass = "root";
	$db_name = "crud";	
	$mysqli = new MySQLi($db_server, $db_user, $db_pass, $db_name) or die(mysqli_error());
?>

Create

This snippet just adds one row, with an ID that should auto increment. Adding the ‘id’ bit isn’t strictly needed, but it makes it easier to scam the code, and know it’s there. The ‘$name’ is assuming the info it’ll add to the DB is from a variable, just as you’d expect, $name = $_POST['inputname']; or just plain text $name = "Lorem Ipsum";

<?php
	require('_database.php');
	$mysqli->query("INSERT INTO people (id, name) VALUES ('','$name')") or die ($mysqli_error($mysqli));
?>

Read

This is the one you’ll no doubt use a million times. Getting info from a DB is probably the main reason you even want to work with a database anyway! Here’s how.

<?php
	require('_database.php');
	$name_result = $mysqli->query("SELECT * FROM people") or die($mysqli_error($mysqli));
	while($name_row = $name_result->fetch_object()) {
		echo '<p>'.$name_row->id.' - '.$name_row->name.'</p>';
		// Looks like: <p>1 - Lorem Ipsum</p>
	}
?>

Using the method above, you can either get one row, or an infinite number. Very useful.

Update

This one is useful. Simply select the columns you want to edit and set their new value, but only where the row ID is whatever the $id value is. Be sure to not forget this. I’ve heard stories or people forgetting to and essentially loosing all their info where they replaced the column value on every row. A huge error when the DB contains 5 million rows!

<?php
	require('_database.php');
	$mysqli->query("UPDATE people SET name='$name' WHERE id='$id'") or die($mysqli_error($mysqli));
?>

Delete

Like the rest, this one is pretty easy. Be sure to select the row to delete, as you could accidently delete the whole table! Have you seen when stupid people add DROP TABLE people in tutorial tests? It’s like that, but your own fault.

<?php
	require('_database.php');
	$mysqli->query("DELETE FROM people WHERE id='$id'") or die($mysqli_error($mysqli));
?>

Conclusion

Well, hopefully you can now create, read, update and delete rows from a database. I’d love to see what you’ve made using these snippets, so leave a comment and show us all!

Tune in next week where I’ll show you simple SQL queries, so you can choose what data you get from the database.