MySQL – PHP PDO Get All Column Names from Table in Specific Database

MySQLPHP

I currently have the following query. Which fetches all column names for a table. But when there are 2 tables with the same name (in different databases) it fetches those column names as well.

In the following query $table of course means the requested table. In this case for example customer.

select column_name from information_schema.columns 
where lower(table_name)=lower(\''.$table.'\')

So my question is: How do I specify which database the query should look into.

Best Answer

In MySQL database is a synonym for schema. Subsequently, the information you're looking for is in the column table_schema:

select column_name from information_schema.columns 
where lower(table_name)=lower(\''.$table.'\')
  and lower(table_schema)=lower(\''.$mydb.'\')

WARNING !!!

Before you do anything, mysqldump the entire database or tarball /var/lib/mysql

If you are concerned about case sensitivity, you need to hunt down the usernames in a special way

I tried this little experiment

use test
DROP TABLE rolando;
CREATE TABLE rolando
(id int not null auto_increment,
name varchar(20),primary key (id));
INSERT INTO rolando (name) VALUES
('rolando'),('Rolando'),
('ROLANDO'),('ROLANDO'),
('rolando'),('rolando');

I loaded the sample data

mysql> use test
Database changed
mysql> DROP TABLE rolando;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE rolando
    -> (id int not null auto_increment,
    -> name varchar(20),primary key (id));
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO rolando (name) VALUES
    -> ('rolando'),('Rolando'),
    -> ('ROLANDO'),('ROLANDO'),
    -> ('rolando'),('rolando');
Query OK, 6 rows affected (0.08 sec)
Records: 6  Duplicates: 0  Warnings: 0

I ran these queries (please note subtle differences)

mysql> SELECT name,COUNT(1) FROM rolando GROUP BY name;
+---------+----------+
| name    | COUNT(1) |
+---------+----------+
| rolando |        6 |
+---------+----------+
1 row in set (0.00 sec)

mysql> SELECT name,MD5(name),COUNT(1) FROM rolando GROUP BY name,MD5(name);
+---------+----------------------------------+----------+
| name    | MD5(name)                        | COUNT(1) |
+---------+----------------------------------+----------+
| ROLANDO | b3f7ba680fe83ab0b5174737e8d536a2 |        2 |
| Rolando | e0732d78dc135b8fcc33ec79bfa89d1f |        1 |
| rolando | e80d4ab77eb18a4ca350157fd487d7e2 |        3 |
+---------+----------------------------------+----------+
3 rows in set (0.00 sec)

mysql> SELECT name,MD5(name)=MD5(LOWER(name)) from rolando;
+---------+----------------------------+
| name    | MD5(name)=MD5(LOWER(name)) |
+---------+----------------------------+
| rolando |                          1 |
| Rolando |                          0 |
| ROLANDO |                          0 |
| ROLANDO |                          0 |
| rolando |                          1 |
| rolando |                          1 |
+---------+----------------------------+
6 rows in set (0.00 sec)

mysql> SELECT name,COUNT(1) FROM rolando GROUP BY name;
+---------+----------+
| name    | COUNT(1) |
+---------+----------+
| rolando |        6 |
+---------+----------+
1 row in set (0.00 sec)

mysql> SELECT name,(name LIKE lower(name)) like1,
    -> (name = binary LOWER(name)) like2 FROM rolando;
+---------+-------+-------+
| name    | like1 | like2 |
+---------+-------+-------+
| rolando |     1 |     1 |
| Rolando |     1 |     0 |
| ROLANDO |     1 |     0 |
| ROLANDO |     1 |     0 |
| rolando |     1 |     1 |
| rolando |     1 |     1 |
+---------+-------+-------+
6 rows in set (0.00 sec)

mysql>

Give this view of my experiment, a username with all uppercase or mixed case can be equal to an all lowercase UNLESS YOU USE BINARY operator with the comparison

I do not think BINARY can be applied to ON DELETE CASCADE.

However, you could something like this: To delete all usernames that are lowercase only, try running one of the following:

DELETE FROM logins WHERE username = BINARY LOWER(username);

DELETE FROM logins WHERE MD5(username) = MD5(LOWER(username));

If you run this now, you might nix all usernames in the other tables. Run this instead

SET FOREIGN_KEY_CHECKS=0;
DELETE FROM logins WHERE username = BINARY LOWER(username);

SET FOREIGN_KEY_CHECKS=0;
DELETE FROM logins WHERE MD5(username) = MD5(LOWER(username));

You will have to go to all tables that have a username in it and execute

SET FOREIGN_KEY_CHECKS=0;
DELETE FROM <whatevertable1> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
DELETE FROM <whatevertable2> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
DELETE FROM <whatevertable3> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
.
.
.
DELETE FROM <whatevertableN> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';

Since you have a ton of other tables, use the INFORMATION_SCHEMA to script it for you:

DELETE_SQLFILE=/root/SensitiveDelete.sql
MYSQL_CONN="-uroot -ppassword"
echo "SET FOREIGN_KEY_CHECKS=0;" > ${DELETE_SQLFILE}
mysql ${MYSQL_CONN} -ANe"SELECT CONCAT('DELETE FROM ',table_schema,'.',table_name,' WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP ''\^.*[A-Z].+$''\) FROM information_schema.columns WHERE column_name='user_name'" >> ${DELETE_SQLFILE}
cat ${DELETE_SQLFILE}

If the file is correct to you, run it in the mysql client like this

mysql ${MYSQL_CONN} -A < ${DELETE_SQLFILE}

Give it a Try !!!

Mysql – Should I use left join to do the job in this scenario

Your last query

mysql> select table_name from tables as a left join (select distinct table_name from tables as b)  on a.table_name!=b.table_name;
ERROR 1248 (42000): Every derived table must have its own alias

fails because the as b must be outside the parentheses like this

mysql> select table_name from tables as a left join (select distinct table_name from tables) as b  on a.table_name!=b.table_name;

Your method 2 failed

mysql> select count(table_name) as num, table_name from tables group by table_name where num > 1;

because the group by table_name must be last like this

mysql> select count(table_name) as num, table_name from tables where num > 1 group by table_name;

SUGGESTION

Perhaps you should start with a GROUP BY ... HAVING query like this

SELECT table_name,COUNT(1) table_count
FROM information_schema.tables
WHERE table_schema NOT IN
('information_schema','performance_schema','mysql')
GROUP BY table_name HAVING COUNT(1) > 1;

This will definitely give all tables whose name appears in multiple databases

Form that as a subquery and join it to gather all databases the table appears in

SELECT
    A.table_name,
    GROUP_CONCAT(B.table_schema) TheTableAppearsInTheseDatabases
FROM
(
    SELECT table_name,COUNT(1) table_count
    FROM information_schema.tables
    WHERE table_schema NOT IN
    ('information_schema','performance_schema','mysql')
    GROUP BY table_name HAVING COUNT(1) > 1
) A INNER JOIN information_schema.tables B
USING (table_name) GROUP BY A.table_name;

Please notice that I use INNER JOIN rather than LEFT JOIN because it will really form a Cartesian product (2704 X 2704) and then perform comparisons.

I know this works because I tried it out in MySQL 5.5.12 on my Windows7 machine

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 5.5.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>     SELECT
    ->         A.table_name,
    ->         GROUP_CONCAT(B.table_schema) TheTableAppearsInTheseDatabases
    ->     FROM
    ->     (
    ->         SELECT table_name,COUNT(1) table_count
    ->         FROM information_schema.tables
    ->         WHERE table_schema NOT IN
    ->         ('information_schema','performance_schema','mysql')
    ->         GROUP BY table_name HAVING COUNT(1) > 1
    ->     ) A INNER JOIN information_schema.tables B
    ->     USING (table_name) GROUP BY A.table_name;
+--------------------------+-------------------------------------------------------------------------------------------------------------------+
| table_name               | TheTableAppearsInTheseDatabases                                                                                   |
+--------------------------+-------------------------------------------------------------------------------------------------------------------+
| a                        | junk,test,robottinosino                                                                                           |
| acl                      | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive                                     |
| blocks                   | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| blog                     | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive                                     |
| blog_category            | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| blog_entrycat            | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive                                     |
| blog_meta                | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_archive,weisci_jaws_staging2                                     |
| blog_trackback           | weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging                                     |
| calendar_events          | weisci_jaws_staging,weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2                                     |
| calendar_meta            | weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging                                     |
| calendar_questions       | weisci_jaws_staging,weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2                                     |
| calendar_tickets         | weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging                                     |
| calendar_transactions    | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging,weisci_jaws_archive                                     |
| captcha_complex          | weisci_jaws_staging,weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2                                     |
| change_log               | test,junk                                                                                                         |
| chat_staff               | test,junk                                                                                                         |
| comments                 | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging,weisci_jaws_archive                                     |
| donations_charities      | weisci_jaws_staging,weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2                                     |
| donations_charities_meta | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| donations_donations      | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| foo_reference1           | timpost1,timpost2                                                                                                 |
| foo_reference2           | timpost1,timpost2                                                                                                 |
| foo_reference3           | timpost1,timpost2                                                                                                 |
| groups                   | weisci_jaws_staging,weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2                                     |
| ipvisitor                | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| job_post                 | giannosfor,test                                                                                                   |
| layout                   | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| listeners                | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| mediamanager_files       | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging                                                         |
| mediamanager_group       | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| mediamanager_photos      | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| mediamanager_video       | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| menus                    | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| menus_groups             | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| mytable                  | ryanzec,user1267617,cabita,dotancohen,johnlocke,neeraj,test,user391986,cool_cs,javier,mathieu                     |
| mytext                   | jakobud,newstuff                                                                                                  |
| occupation_field         | giannosfor,test                                                                                                   |
| policy_agentblock        | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| policy_ipblock           | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| prova                    | veto,vito                                                                                                         |
| registry                 | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| registry_bk              | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                                         |
| session                  | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| static_pages             | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| static_pages_translation | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| t                        | preeti,rollup_test                                                                                                |
| t1                       | abidibo,test                                                                                                      |
| t2                       | test,abidibo                                                                                                      |
| t3                       | test,abidibo                                                                                                      |
| table1                   | table_test,supercoolville,test                                                                                    |
| table2                   | supercoolville,test,table_test                                                                                    |
| tags                     | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| tags_content             | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| tbl_banner_position      | weisci_jaws_staging2,weisci_jaws_live                                                                             |
| tbl_banner_upload        | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                                         |
| tbl_global_banner        | weisci_jaws_staging2,weisci_jaws_live                                                                             |
| tms_authors              | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| tms_repositories         | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| tms_themes               | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| updates                  | test,junk                                                                                                         |
| url_aliases              | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive                                     |
| url_maps                 | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| users                    | weisci_jaws_archive,weisci_jaws_staging,giannosfor,veto,weisci_jaws_live,weisci_jaws_staging2,friends,sample,vito |
| users_groups             | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| users_meta               | weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live                                     |
+--------------------------+-------------------------------------------------------------------------------------------------------------------+
65 rows in set (0.91 sec)

mysql>

Give it a Try !!!

Best Answer

Related Solutions

MySQL – automatically update all references to specific table to use on delete cascade

WARNING !!!

Before you do anything, mysqldump the entire database or tarball /var/lib/mysql

Mysql – Should I use left join to do the job in this scenario

SUGGESTION

Related Question