Web Developer in Boulder CO The Blog

Tag: mysql

Don't insert when maximum is reached

Published by Nicholas Dunbar on May 9th, 2014

How to control if a record is inserted based on a condition? Don't insert a record if certain conditions are not met. There is a way of using pure MySQL to stop inserting records when a certain number of them has been reached. Let us say that you have...

MySQL CONCAT limitation

Published by Nicholas Dunbar on April 12th, 2014

So you're trying to concat two large strings and it keeps resulting in a truncated or empty string? The problem may be that CONCAT can only result in a string of length 1024 bytes long. Worse is there is no setting in MySQL that allows you to increat...

Get size of database in bytes and rows

Published by Nicholas Dunbar on February 9th, 2014

How to get the size of the database in MySQL? Replace database_name with the database name from which you want the size in bytes and rows: SELECT t.table_schema,SUM(t.table_rows) AS row_count,SUM(t.data_length) AS data_volume FROM information_schema.t...

MySQL Generate a list of numbers

Published by Nicholas Dunbar on February 8th, 2014

This will generate a column named SeqValue that will range from 0-255 so a total of 256 values. SELECT (TWO_1.SeqValue + TWO_2.SeqValue + TWO_4.SeqValue + TWO_8.SeqValue + TWO_16.SeqValue + TWO_32.SeqValue + TWO_64.SeqValue + TWO_128.SeqValue) SeqV...

Using Table Names With Spaces in PHP and mySQL

Published by Nicholas Dunbar on January 2nd, 2014

The key to writing SQL queries using table names that contain spaces is to make sure you use the correct quotes.This is wrong:SELECT * FROM tablename ORDER BY ''field name'' ASCThis is correct:SELECT * FROM tablename ORDER BY `field name` ASCThese (`) ...

Copy MySQL schema from remote host to local host

Published by Nicholas Dunbar on March 12th, 2013

The secret is to use a program called mysqldump which comes with your installation of mysqld. user@localhost the_directory>mysqldump --no-data -u root remote_db_name -h address_of_remote_machine.com > temp-db-schema.mysql user@localhost the_directory...

Install PHP CLI with MySqli extension/module on Linux

Published by Nicholas Dunbar on March 6th, 2013

So you want to use PHP from the command line or you have PHP from the command line but you don't have mysqli installed. run the following shell script: #!/bin/bash echo "Are you logged in as root?"; select yn in "Yes" "No"; do case $yn in ...

Set up Squirrel to connect to a MySQL database.

Published by Nicholas Dunbar on February 13th, 2013

We are assuming you have already installed Squirrel In order to set up Squirrel Database Client to connect to a MySQL database you must: 1.) go to http://dev.mysql.com/downloads/connector/j/#downloads 2.) select your platform, if you are running it on...

XOR two tables in MySQL

Published by Nicholas Dunbar on December 13th, 2012

SELECT ot.id FROM old_table ot LEFT JOIN new_table nt ON ot.id=nt.id WHERE nt.id IS NULLOnly return that which they don't have in common.Another way of saying it is only return where they do not intersect. There are many Boolean operations that can be...

MySQL splitting a comma delimited string into a column of rows

Published by Nicholas Dunbar on November 2nd, 2012

If you want to split a field of your database by commas or some delimiter into a column of rows or a list using pure MySQL here is how you can do it. I pulled this from the MySQL cookbook and then modified it to not rely on the pivot table t10. Origi...

Check if a database exists in MySQL from the command line

Published by Nicholas Dunbar on November 1st, 2012

To check if a database exists from the command line in MySQL echo the following query and pipe (|) it to the MySQL command line utility:echo SELECT COUNT(*) as num FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMATA.SCHEMA_NAME="some_database_name"; | mysq...

Generate a MySQL query string using MySQL

Published by Nicholas Dunbar on October 18th, 2012

Sometimes you may want to use MySQL to generate a series of SQL statements. In this example we want to create a series of colon (;) delimited MySQL commands that the contents of a series of tables that exists in many different databases. The secret t...