catfan / Medoo

The lightweight PHP database framework to accelerate the development.
https://medoo.in
MIT License
4.84k stars 1.15k forks source link

Wrong sql #277

Closed luxixing closed 7 years ago

luxixing commented 9 years ago

These are medoo generator sql:

··· Array ( [0] => SELECT "uid","market_channel" FROM "users" WHERE "registration_time" <= 1438444799 AND "sub_platform" = 4 AND "referid" = '' GROUP BY "refer" [1] => SELECT "uid","referid as refer" FROM "users" WHERE "registration_time" <= 1438444799 AND "sub_platform" = 4 AND "referid" != '' GROUP BY "refer" )


it can not execute
luxixing commented 9 years ago

I found a bug in function: "column_quote"

the code is

··· protected function column_quote($string) { $quote = '"'; if (substr($string, 0, 1) === '#') { $quote = ''; } return $quote . str_replace('.', '"."', preg_replace('/(^#|(JSON))/', '', $string)) . $quote; } ···

I fixed it

the sql function can not use by yourself can you fixed it ?

catfan commented 9 years ago

If you want column alias, please the documentation here http://medoo.in/api/select.

And notice this code from Medoo initialization that forcing MySQL to use standard quoted identifier.

$commands[] = 'SET SQL_MODE=ANSI_QUOTES';

And I think you have to check the datetime format again.

luxixing commented 9 years ago

@catfan Thank you for your reply The second questioin can you help me ? These are questions $medoo->select($table, ['count(uid) as test'], $where); It will be generator wrong sql $medoo->select($table, ['distinct uid'], $where);

$medoo->select('table name as b', ['b.*'], $where);

Many more question

Thank you very much

I hope you can understand me

ekeyme commented 9 years ago

@catfan Yeah, you are right. I think you should know that $commands[] = 'SET SQL_MODE=ANSI_QUOTES' feature is only support mysql4+ version. And you might willing to show medoo 0.9.8.3 requires mysql4+ too. thanks!

p.s Is there a version of medoo support mysql3? I have a wackward mysql3 on server of my company.

alexroyce315 commented 8 years ago

I've got the same issue, the medoo generators sql like this SELECT * FROM "tableName" ORDER BY "addTime" DESC LIMIT 0,10 . I think maybe there is something wrong with using " and '. So, I made a copy by simlply replacing all " to ' and using it in my website. If you do this, make sure that the field name should not be any keyword of database or sql or etc..