Case on PHP SQL queries different from case on MySQL database. Cannot use lower_case_table_names = 1

Question

I'm having an issue with a website I wrote in PHP on a WAMP server on Windows, issue is, it's now being deployed on a Linux server. The site is quite big and I've done all my queries this way in all caps:

"SELECT COLUMN FROM TABLE"

And most of the tables/columns in the database use a different case (For example, Column).

I cannot use lower_case_table_name=1 since there are other PHP websites on the server that would be affected negatively. Is there a way for me to switch all of my database's tables and columns into upper case?

Why not just use all lowercase table names as is pretty much the standard convention in SQL? Column names aren't a problem as these are case-insensitive. — Mike Brant
– Mike Brant, Commented Apr 16, 2013 at 20:11
Because I'd have to go and change every single request in 40 different PHP files. — Sefam
– Sefam, Commented Apr 16, 2013 at 20:14
That is what global search/replace is for. 40 files is a small project. — Mike Brant
– Mike Brant, Commented Apr 16, 2013 at 20:17
The 40 files fetch things from 30 different tables. I'm just gonna go and do it manually, It's going to be faster than arguing about it. — Sefam
– Sefam, Commented Apr 16, 2013 at 20:21
You have been given some usable solutions below. I was just suggesting sticking with all lowercase for code potability (and it really helps with readability of your SQL in your code when the SQL keywords are upper case and the database objects are lowercase. — Mike Brant
– Mike Brant, Commented Apr 16, 2013 at 20:26

M Khalid Junaid · Accepted Answer · 2013-04-16 20:12:08Z

1

you have change the names of columns and tables if you database is not so huge you can try this

select concat('rename table ', column_name, ' to ' , lower(column_name) , ';') 
from information_schema.columns where table_schema = 'your_schema_name';

and rename or change the case for more help see and lower_case_table_names

answered Apr 16, 2013 at 20:12

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sefam Over a year ago

I have about 30 tables. Some of them only have two-three fields, but some of them have up to ten. I guess I just have to get to work.

M Khalid Junaid Over a year ago

yes you have to if u cannot set lower_case_table_names variable on server

Lawson · Accepted Answer · 2013-04-16 20:03:00Z

0

ALTER TABLE yourtablename RENAME TO YOURTABLENAME;

answered Apr 16, 2013 at 20:03

Lawson

6341 gold badge5 silver badges19 bronze badges

Collectives™ on Stack Overflow

Case on PHP SQL queries different from case on MySQL database. Cannot use lower_case_table_names = 1

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related