OpenLamp.tech Issue #7 - MySQL Metadata with CodeIgniter 4
OpenLamp.tech Issue #7 - MySQL Metadata with CodeIgniter 4
Subscriber Perks Inside; Buggy PHP Code; Working with PHP Arrays; CodeIgniter 4 AJAX Requests; Importing Data in MySQL; 15 Exciting SQL Projects
Hey glad to have you here! 👍
If you are not yet subscribed, please use the Subscribe button below and join:
Subscriber Perks!!! Get access to my Medium-first published blog posts and articles by visiting, Medium Friendly List. Use the password, mediumfriendlist, to sign in and access the page with the ‘Friend Links’ for all of my Medium posts that were first published on the platform there, before being cross-posted anywhere else.
MySQL Database Metadata with CodeIgniter 4
Data about data is known as metadata. The MySQL database is full of metadata. This metadata is quite useful if you know where to find it, how to get it, and what to use it for. In this month’s featured piece, I am exploring several built-in methods in CodeIgniter 4 that make it super-easy to access important - and useful - metadata…
Housekeeping
For the duration of the examples, assume I have this database connection where I'm working with the well-known sakila practice database:
$db = db_connect('sakilaGroup');listTables()
We need to know what tables are available and what their names are.
Otherwise, how could we write our SELECT queries without knowing the table names, right?
In MySQL, you can use either of these 2 commands and know what tables are present in the database:
SHOW TABLES; --(if you're currently in the target DB)
SHOW TABLES IN dbname;CodeIgniter 4 has a method you can use for this information as well, listTables().
foreach ($db->listTables() as $table_name) {
echo $table_name.'<br>';
}This method returns the tables in the currently connected database (the sakila DB in our case).
tableExists()
It can be helpful knowing if a table exists in the database prior to using it for any operations. tableExists() returns either TRUE or FALSE depending on if the table is present in the database:
if ($db->tableExists('rental')) {
echo 'Table exists';
} else {
echo 'Table does not exist.';
}Outputs:
Table exists
Should the table not exist, the method returns FALSE as shown in this example:
if ($db->tableExists('starbucks')) {
echo 'Table exists';
} else {
echo 'Table does not exist.';
}Returns in the browser:
Table does not exist.
getFieldNames()
Mostly, we want to list out the specific columns and/or expressions for the application queries as opposed to retrieving all columns.
In MySQL we can use either of these 2 commands and know what columns are in a particular table:
DESC table_name;
SHOW COLUMNS IN table_name;CodeIgniter 4 has the getFieldNames() method available for this kind of information.
$cols = $db->getFieldNames('actor');
foreach ($cols as $column) {
echo 'Column name: '.$column.'<br>';
}
We can also use the getFieldNames() on a SELECT query and retrieve the field names that are part of that query:
$query = $db->query("SELECT `first_name`, `last_update` FROM `actor`;");
foreach ($query->getFieldNames() as $col_name) {
echo 'Column name: '.$col_name.'<br>';
}
fieldExists()
Including a column name that does not exist in a table in any SQL statement is going to throw an error. CodeIgniter 4 has the fieldExists() method that returns either TRUE or FALSE depending on if the column exists in the specified table.
fieldExists() accepts 2 parameters:
column name
target table.
if ($db->fieldExists('first_name', 'actor')) {
echo 'Field exists in the table.';
} else {
echo 'Field does not exists in the table.';
}Returns:
Field exists in the table.
However, notice in this next example, the field 'first_name' does exist, just not in the 'store' table:
if ($db->fieldExists('first_name', 'store')) {
echo 'Field exists in the table.';
} else {
echo 'Field does not exists in the table.';
}The output is:
Field does not exist in the table.
getFieldData()
In MySQL, you can use the DESC (or DESCRIBE) statement to get information about the columns in a table such as a name, data type, length, etc.
You can retrieve a somewhat similar set of metadata using the getFieldData() method, providing the target table name as the parameter:
$field_data = $db->getFieldData('actor');
foreach ($field_data as $data) {
echo 'Field Name: '.$data->name.', Field Type: '.$data->type.', Field Length: '. $data->max_length.', Primary Key: '.$data->primary_key. '<br>';
}
getIndexData()
The getIndexData() method returns exactly what its name says: index information for a table:
$index_keys = $db->getIndexData('actor');
foreach ($index_keys as $key) {
echo 'Key name: '. $key->name.', Key Type: '.$key->type.', ';
foreach ($key->fields as $value) {
echo 'Key field: '.$value.'<br>';
}
}
getForeignKeyData()
I think this method could be very useful if you need to know what columns to JOIN on at runtime by finding one tables' PRIMARY KEY and matching on another tables' same-named FOREIGN KEY. I plan to try and test this method in an application I am working on with a large number of tables in order to help keep the code as flexible and dynamic as possible.
$foreign_keys = $db->getForeignKeyData('city');
foreach ($foreign_keys as $key) {
echo 'Constraint name: '. $key->constraint_name.'<br>'.'Table name: '.$key->table_name.'<br>'.'Column Name: '.$key->column_name.'<br>'.'Foreign Table Name: '.$key->foreign_table_name.'<br>'.'Foreign Column Name: '.$key->foreign_column_name;
}
I hope you have found the examples of these CodeIgniter 4 Metadata methods useful. I plan to write more in-depth blog posts on them in the future so be sure you are subscribed to OpenLamp.tech along with my blog, Digital Owl’s Prose, and be notified when I publish them.
I have curated some fantastic reads for you this week. Let’s get to them!
This Week’s Picks
✔️ How to Send AJAX request with CSRF token in CodeIgniter 4
#codeigniter #php
The makeitweb blog has some great LAMP-stack-focused blog posts and I'm always reading the site’s content. I recently happened on this one while searching Google on how to to use CSRF token in CodeIgniter 4. This particular site covers many different PHP frameworks with Laravel and CodeIgniter being the most common.
✔️ Using Server Side Data Tables in Laravel
#laravel #php
I use jQuery Data Tables and really appreciate how easy they are to use. The Data Tables are a godsend for someone like me who isn't that well versed with JavaScript.
Learn how to use them with Laravel in this post…
✔️ Importing Data Using MySQL and Arctype
#mysql
The Arctype content is always fantastic and full of useful information. I really enjoy reading their stuff. This post has good information on loading data into MySQL covering any one of those available means to get the data in.
From LOAD DATA INFILE to LOCK TABLES to INSERT INTO table_name VALUES(), there are little nuggets scattered throughout the article.
✔️ Buggy PHP Code: The 10 Most Common Mistakes PHP Developers Make
#php
I should make reading - and implementing - the fixes for these top 10 mistakes as part of a 'Developer Devotional' for 2022. There is so much great information in this post that all I'm going to say is to bookmark and study it. I know I am.
✔️ Working With PHP Arrays in the Right Way
#php
This is another staple article in my opinion and is bookmark worthy.🔖
With clear explanations and examples of many of the PHP array functions, I seem to learn something new every time I crack this article open. I think anyone should spend some time reading it.
⭐⭐⭐ Many of the posts I curate and share in this newsletter, I read and enjoyed on Medium. If you are not yet and member and wish to join the platform, I would be more than grateful if you would use my sign-up link. At no extra cost to you, I will receive a small portion of your membership fee when you sign up. Thank you! 🙏
Recent Posts from Digital Owl’s Prose
Below are some of the recent posts I have published over on my blog, Digital Owl’s Prose.
Extra stuff…
Did you enjoy this issue of OpenLamp.tech? Please share it with others so that they can enjoy the content as well. Thank you!!!
“Reply” or comment and let me know what you think of this issue of OpenLamp.tech.
What do you want to see more of?!?
What do you want to see less of?!?
I’d love to know!
Also, share with me links to your published blogs and articles so that I can read them and possibly include them here in the newsletter!
Thank you for reading. I hope you have a great rest of your week.
Take care.
Joshua Otwell
Visit my blog Digital Owl’s Prose, where I write regularly on CodeIgniter, PHP, and SQL. I’m also active on Medium, LinkedIn, Twitter, and Instagram.
How can I help you?
Are you thinking of starting up a blog? I use WordPress for my blog, Digital Owl’s Prose. Let’s both save money on the plans offered. 💸
Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. ✉️
Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger.
I enjoy reading Refind: The essence of the web, every morning in your inbox. Subscribe for free. Help me get a premium subscription by signing up yourself with my referral link.
Free mobile wallpapers for creators.
Want to support this newsletter and my work? Drop some spare change in my Tip Jar.💰
Disclosure: Some of the services and products links in this email are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission.
Cover Image credits: Image by Gerd Altmann from Pixabay