How to fix mysql uppercase query script in php

Question

I am currently working on the website that uses ADODB library. In entire website all the queries are written in UPPERCASE.

The problem is when I run the query it doesn't work because of table name which is UPPERCASE. But when I change the table name to lowercase it works.

$sql = "SELECT * FROM MEMBERS where USERNAME = '$username'";

$db = ADONewConnection('mysql');
$db->debug = true;
$db->Connect(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_NAME);

$resultFriends = $db->Execute($sql);
    
while ($row = $resultFriends->FetchRow()) {
        var_dump($row);
        die;
}

Here is the error I get:

ADOConnection._Execute(SELECT * FROM MEMBERS where USERNAME = 'fury', false) % line 1012, file: adodb.inc.php

ADOConnection.Execute(SELECT * FROM MEMBERS where USERNAME = 'fury') % line 15, file: index.php

I have read some articles about it but that doesn't help. e.g. How to make MySQL table name case insensitive in Ubuntu?

Bear in mind I don't want to change the scripts. There are 1000 files and 10000 places.

Is there any library or are there any way that I can run this queries without error?

Answer 1

The version for live sire was linux kernel. but the new dev site is ubuntu.

I have done this on ubuntu/ mysql CML and it didn't work.

The solution is I had to reconfigure the mySql database in AWS/rdbs

You have to modify the “lower_case_table_names” parameter for your DB Instance(s). Prior to today, the lower_case_table_names parameter was not modifiable, with a system default of zero (0) or “table names stored as specified and comparisons are case sensitive.” Beginning immediately, values of zero and one (table names are stored in lowercase and comparisons are not case sensitive) are allowed. See the MySQL documentation for more information on the lower_case_table_names parameter.

The lower_case_table_names parameter can be specified via the rds-modify-db-parameter-group API. Simply include the parameter name and specify the desired value, such as in the following example:

  rds-modify-db-parameter-group example --parameters "name=lower_case_table_names, value=1, method=pending-reboot" --region us-east-1

Support for modifying parameters via the AWS Management Console is expected to be added later this year.

setting the lower_case_table_names parameter via a custom DB Parameter Group and doing so before creating an associated DB Instance. Changing the parameter for existing DB Instances could cause inconsistencies with point-in-time recovery backups and with Read Replicas.

Amazon RDS