Primary and foreign keys: The backbone of relational databases We spoke about normalization but never fully covered primary and foreign keys. It’s not something that’s unique to MySQL, but it’s something that you hear quite frequently: primary key and foreign key. We have already looked at an example of this in the previous MySQL article, but I wanted to elaborate on it a little further. https://blog.devgenius.io/php-p93-mysql-normalization-7e9a9809311e What is a Primary Key in SQL? It’s just a column or combination of columns inside of your table that is unique for each of the entries within that table. A quick example is
Tag: PHP MySQL
Normalization in MySQL streamlines database organization We’ll touch on one last topic and call it a day with MySQL. The last topic is normalization. What does it mean to normalize a database? Let’s say that you have a table that stores your customers and one of the fields there is customer_type, such as direct or distributor, those two words are going to be entered thousands of times unnecessarily. Why not have a separate table for them and just specify which number they represent? For example, if id of 1 is set to distributor, then if we look at a customer’s customer_type and see that the value is set to 1, then we’ll
From Creation to Deletion: MySQL’s Complete Data Toolkit What are CRUD operations? Create, Read, Update, Delete. That’s what we have created over the last few articles. The last piece of the puzzle is to combine them into one file that knows how to manage our authors table. If you’re jumping straight into this article, I recommend that you read the earlier articles leading up to this one first. You don’t need to, but it’ll help. MySQL Introduction MySQL DB Connection MySQL Tables MySQL Insert MySQL Insert with Prepared Statements MySQL Insert Multiple Records with Prepared Statements MySQL Select MySQL Update (PUT vs
Erasing Data with Surgical Precision The last of the operations is the delete operation. Just like with our update functionality, there is no delete method. We can submit a hidden method with delete as the value. We can use post and specify a value, but we’re building up for Laravel. Laravel accepts various requests and can process them easily. Here’s a list from their documentation. https://laravel.com/docs/9.x/controllers#actions-handled-by-resource-controller As you can see, if we wanted to delete a photo, we need to provide the {photo} resource. In our case, we’ll need to provide the author’s id. Previous Code Review Make sure that you’re up to date on the previous code. Author.php DB.php UpdateAuthorEmail.php
Revamping Data with Precision The next logical step in our MySQL progression is updating an existing resource. We’re going to introduce the differences between PUT and PATCH and put it to rest once and for all. It really is a simple concept and I’m not sure why people find it so confusing. https://blog.devgenius.io/php-p88-mysql-select-d7a633e736b PUT vs PATCH Let’s take a look at our authors table. It contains the following fields: id first_name last_name email If we update a resource with the PUT request, we need to send all of the fields. That means that if we update the email, we need to send the id, first_name, last_name, and email. If
The Art of Data Retrieval We’ve gone through the insertion process and have a few records inserted inside of our authors table. Now what? A common task is to retrieve those entries and display them on the screen. That’s where the SELECT statement comes through. https://dinocajic.medium.com/php-p87-mysql-insert-multiple-records-with-prepared-statements-c5c7bcfb42a3 A Simple Select Statement For the selection to work, we need to: Connect to the database Pass our select query Retrieve the results, which is just the rows of data. Loop through each of the results and extract data for each row that was returned. Close the connection Straight forward. Let’s isolate the selection process first. It
MySQL Prepared Statements for Bulk Data Inserts You may notice a small buildup between articles, and that’s intentional. No reason to overwhelm or dilute the topic. In this article, we’ll answer one additional question related to inserting data with prepared statements: do you need to keep binding variables for each set of data that you’re inserting? Let’s just spoil that response: you don’t. Setup the binding once, change the variables, and execute. It may look a little strange, but as long as you know the flow, it will work. https://blog.devgenius.io/php-p86-mysql-insert-with-prepared-statements-57f37daeb109 Recap <?php namespace Authors; require_once(“DB.php”); use Database\DB; class Author {
MySQL’s Shielded Data Transformation There’s one big flaw in our previous MySQL insert statement: it’s open to SQL Injection Attacks. Prepared statements virtually eliminate that concern. You tell the SQL server what you’re intending to do and then you give it the data. For example, you can tell the server that you’re planning on inserting data. If you send a delete statement disguised in your insert statement, that doesn’t make sense and will not have the same kind of effect that it would without prepared statements. https://blog.devgenius.io/php-p85-mysql-insert-51a9e3bfedd3 https://medium.com/geekculture/cybersecurity-p9-sql-injections-471de647e8d7 Recap Our Author class had an insert statement that accepted data and inserted it into the authors table. <?php namespace
Infusing Tables with Fresh Data Energy We have our database connection, our database, and our table. We just need to start adding some data to it. This is a basic functionality that you’ll need to do constantly. Each time a form is submitted, where’s that data going to go? Will it be emailed or will it be stored in a database? Let’s do this the right way: the object oriented way. Our assignment for this article will be to connect to a database and insert an author into our authors table. If you need to review how we got to this point,
MySQL tables neatly store data in rows and columns In the previous article, we covered how to create a database and establish a connection to it. It’s time to go a step deeper now. It’s time to create some tables. If you remember the previous analogy, the database can be thought of as an Excel Workbook and the tables can be thought of as sheets within the Workbook. https://blog.devgenius.io/php-p83-mysql-db-connection-8a34c4056863 Tables are part art part science. Architects must decide how far they want to normalize a database without losing readability. We’ll cover the basics of tables in this article and will