OpenLampTech

Share this post

OpenLamp.tech Issue #7 - MySQL Metadata with CodeIgniter 4

openlamptech.substack.com

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

Joshua Otwell
Dec 31, 2021
Share this post

OpenLamp.tech Issue #7 - MySQL Metadata with CodeIgniter 4

openlamptech.substack.com

Hey glad to have you here! 👍

If you are not yet subscribed, please use the Subscribe button below and join:

Thanks for reading OpenLamp.tech! Subscribe (it’s free) and receive new posts by email, be part of a great community, and support my work.


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).

MySQL sakila database tables

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>';
}

CodeIgniter 4 getFieldNames() method

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>';
}

CodeIgniter 4 getFieldNames() method on a SELECT query

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:

  1. column name

  2. 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>';
    }
}

CodeIgniter 4 getIndexData() method

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.

➡️ Read this post…

✔️ 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…

➡️ Read 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.

➡️ Read this post…

✔️ 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.

➡️ Read this post…

✔️ 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.

➡️ Read this post…


⭐⭐⭐ 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.

  • 15 Exciting SQL Project Ideas & Topics for Beginners – 2021

  • CodeIgniter 4 Query Builder set() function with update()

  • 5 PHP Date Functions and Their MySQL Equivalents


Extra stuff…

  • How to Use These 6 Unusual SQL Functions

  • The Part Time Creator Manifesto

  • Power tools you need in your content marketing toolbox


Did you enjoy this issue of OpenLamp.tech? Please share it with others so that they can enjoy the content as well. Thank you!!!

Share


“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.

Share OpenLamp.tech


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.💰

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

Share this post

OpenLamp.tech Issue #7 - MySQL Metadata with CodeIgniter 4

openlamptech.substack.com
Comments
TopNewCommunity

No posts

Ready for more?

© 2023 Joshua Otwell
Privacy ∙ Terms ∙ Collection notice
Start WritingGet the app
Substack is the home for great writing