Each SQL dialect is different in some way, shape, form, or
fashion from the next flavor. Some dialects have this
function, while others have that function. In this post, I cover
porting over Oracle SQL to MySQL in order to count the
number of occurrences of a specific weekday found in the current
given month (at the time of writing) purely as a learning
exercise focused on MySQL DATE functions and the
WITH clause…
Image by tigerlily713 from Pixabay
…
[Read more]
Like always I am sharing new things I learn here on my blog. I
was recently working on a requirement for a LAMP stack web
application reporting dashboard in which I needed to store – and
eventually – retrieve a .pdf file. I have read in several places
(this fantastic book is a great resource) that
a viable option is storing images or documents (.pdf in this
case) in the actual database table as opposed to on the server
file system. MySQL has the BLOB datatype that can be
used to store files such as .pdf, .jpg, .txt, and the like. In
this blog post, I cover how I accomplished uploading and storing
the actual .pdf file in a BLOB column in MySQL using
PHP. Any corrections, tips, pointers, and recommendations for
best practices are always welcome. We all learn as we go!!!
Photo by …
[Read more]
There may come a time you need to rename one or more columns in
an existing MySQL table for a variety of reasons. Using
ALTER TABLE, to rename a column is an easy enough
command. But, suppose there are multiple tables in the same
database/schema that have the same-named column and all of those
columns need to be renamed. That could be a lot of ALTER
TABLE statements to type out. Save your energy and time,
avoiding all of those ALTER TABLE commands
all-together. If you are lucky enough to be working with a MySQL
version > 8.0 then the Shell is your salvation. With just a
few lines of Python code in \py mode, all of your
trouble(s) and headache(s) are no more…
Photo by Ibrahim Rifath on …
[Read more]
Date and time values are some of the most important datatypes in
an RDBMS. From tracking order dates to payroll hours,
DATE and DATETIME datatypes are used in
all types of applications. At times as a Developer, you may need
only certain portions of a DATE or
DATETIME value. In MySQL, the EXTRACT()
function can provide you with a specific component of a
DATE or DATETIME value depending on
which INTERVAL is given as a parameter. Continue
reading to see example EXTRACT() queries for
understanding…
Photo by Omar Al-Ghossen on Unsplash …
[Read more]
Have you ever had to type out a massive CREATE TABLE
statement by hand? One with dozens of columns? Maybe several
dozens of columns? There are likely some GUI tools to help with
large CREATE TABLE commands. Or, other
drag-n-drop types of software that I am not familiar
with. What if you could write a few lines of Python code and take
care of a huge CREATE TABLE statement with way less
effort than typed manually? Interested? Continue reading
and see how using pandas, pyodbc, and MySQL…
Photo by Vorsen Furniture on Unsplash
OS, Software, and DB used:
If you are a developer working in a MySQL environment, this blog post is for you. I share 3 MySQL commands or statements that you should know. That is a bold statement, I know. Turns out, once you do know (of) these commands, you will use them all the time. They minimize guesswork which leads to better productivity in other facets of your programming and querying workflow. I use them myself almost daily and am sure you will too once you see how simple they are to use. So why should you know them? Continue reading and find out…
Photo by hannah joshua on Unsplash
OS, Software, and DB used:
In this post, I’ll cover examples of the MySQL
TRIM() function. TRIM() removes
specific characters – or spaces – from a given string, at either:
the beginning, ending, or potentially in both locations depending
on several factors. With an optional keyword argument that
controls which character(s) – if any – are removed,
TRIM() can be tricky so let’s gain
understanding with several easy-to-digest examples…
Photo by Peter Beukema on Unsplash
OS, Software, and DB used:
Self-Promotion:
If you enjoy …
[Read more]
If you write MySQL queries, at some point you are going to have
to provide query results in a specific order. To impose any
ordering in MySQL (this applies to SQL overall
and is not directed only at MySQL), you have to use the
ORDER BY clause. Without it, there is no
guaranteed order. The database is free to send back
query results in any order. As I learn PHP, I make it a point to
explore both the MySQL side, along with the PHP side in
regards to similar type tasks and the efficiency of each. So far
in my PHP journey, I have found that arrays are used
quite extensively. In this post, I’ll cover
array_multisort() – one of many in-built
PHP functions – used for sorting arrays. In the context of the
example data for this post, the arrays are populated by an
unordered MySQL query. Let’s see one example of how you
can establish a sorting order in a PHP …
