Ready to weave even more complex spells with PHP? If you're excited, hold on to your wizard hat, because we're about to venture into the world of PHP and MySQL! It's going to be a thrilling ride!
What is MySQL?
MySQL is like a massive, sprawling library filled with countless books. Only instead of books, it's full of data tables, and instead of librarians, we have Database Managers to organize and retrieve the data. It's an open-source relational database management system that can be enchanted to work hand in hand with PHP to build dynamic applications.
PHP and MySQL are a match made in heaven. PHP is a server-side scripting language, and MySQL is a database management system. Together, they can be used to create dynamic web pages and applications.
Connecting to MySQL with PHP
Just as a magic wand connects a wizard to their magic, PHP connects to MySQL using specific functions. You use the mysqli_connect() function to establish a connection. It's like the incantation that binds the wand to the wizard's will.
$db_host = 'localhost';
$db_user = 'username';
$db_password = 'password';
$db_name = 'database';
$connection = mysqli_connect($db_host, $db_user, $db_password, $db_name);
If you're successful, the mysqli_connect() function will return a magical object representing the connection. If something goes wrong, you can check what the issue was using mysqli_connect_error().
Basic SQL Commands
SQL commands are like the ancient spells used by wizards to interact with their libraries. These are the four fundamental incantations every PHP wizard needs to know: SELECT, INSERT, UPDATE, and DELETE.
SELECT
The SELECT command is like a locating spell. It helps you find and retrieve data from your tables.
$query = "SELECT * FROM users";
$result = mysqli_query($connection, $query);
The SELECT command is followed by the columns you want to retrieve data from. The * symbol means all columns. You can also specify the columns you want to retrieve data from.
$query = "SELECT name, age FROM users";
$result = mysqli_query($connection, $query);
The mysqli_query() function returns a magical object representing the result of the query. You can use the mysqli_fetch_assoc() function to retrieve the data from the result object.
while ($row = mysqli_fetch_assoc($result)) {
echo $row['name'] . ' is ' . $row['age'] . ' years old.';
}
INSERT
The INSERT command allows you to add new records to your table. It's like conjuring a new book into your library.
$query = "INSERT INTO users(username, password) VALUES ('john', 'password123')";
mysqli_query($connection, $query);
The INSERT command is followed by the table name and the columns you want to insert data into. The VALUES keyword is followed by the values you want to insert into the columns.
UPDATE
The UPDATE command lets you modify existing data. It's like rewriting an existing book in your library.
$query = "UPDATE users SET password='newpassword123' WHERE username='john'";
mysqli_query($connection, $query);
The UPDATE command is followed by the table name. The SET keyword is followed by the column you want to update and the new value. The WHERE keyword is followed by the column you want to use to identify the record you want to update.
DELETE
The DELETE command allows you to remove data. It's like banishing a book from your library.
$query = "DELETE FROM users WHERE username='john'";
mysqli_query($connection, $query);
The DELETE command is followed by the table name. The WHERE keyword is followed by the column you want to use to identify the record you want to delete.
Fetching Data from MySQL Using PHP
Once you've cast your SELECT spell, you'll need to read the data returned by your spell. The mysqlifetchassoc() function is your crystal ball, showing you the data that your SELECT spell found.
$query = "SELECT * FROM users";
$result = mysqli_query($connection, $query);
while($row = mysqli_fetch_assoc($result)) {
echo $row['username'];
}
The mysqli_fetch_assoc() function returns an associative array containing the data from the current row. You can use the column names as keys to access the data.
Closing the Connection
When you're done using your wand, you need to put it away. Similarly, when you're done using your connection, you need to close it.
mysqli_close($connection);
The mysqli_close() function closes the connection to the database. It's like putting your wand back in its holster.
With every new spell you learn, you become a more powerful PHP wizard. Keep practicing these spells, and soon you'll be creating fantastic dynamic applications with PHP and MySQL!