Generated SQL not correct.

[Chillifish]

I have the following code:

$this->statuses = $database->debug()->select(

    "wp_ch_station",
    ["[><]wp_ch_statuses" => ["status" => "id"]],
    ["id", "name", "status", "time"],
    ["id[!]" => $client_id]
);

This is outputting the following SQL:

SELECT
    "id",
    "name",
    "status",
    "time" 
FROM
    "wp_ch_station" 
    INNER JOIN
        "wp_ch_statuses" 
        ON "wp_ch_station"."status" = "wp_ch_statuses"."id" 
WHERE
    "id" != '31'

I can't work out why there are quotes around the periods on the INNER JOIN line. As far as I can see, I'm creating the Medoo code properly.

[Chillifish]

There were issues with the previous SQL, I now have the following:

$this->statuses = $database->debug()->select(

    "ch_station",
    ["[><]ch_statuses" => ["status" => "id"]],
    ["ch_station.id", "ch_station.name", "ch_statuses.status", "ch_station.time"],
    ["ch_station.id[!]" => $client_id]
);

This produces the following SQL:

SELECT
    "wp_ch_station"."id",
    "wp_ch_station"."name",
    "wp_ch_statuses"."status",
    "wp_ch_station"."time" 
FROM
    "wp_ch_station" 
    INNER JOIN
        "wp_ch_statuses" 
        ON "wp_ch_station"."status" = "wp_ch_statuses"."id" 
WHERE
    "wp_ch_station"."id" != 31

If I paste that into phpmyadmin (or anything else) it has lots of check syntax errors. If I clean it up as below, I get the expected results.

SELECT
    `wp_ch_station`.id,
    `wp_ch_station`.name,
    `wp_ch_statuses`.status,
    `wp_ch_station`.time 
FROM
    `wp_ch_station` 
    INNER JOIN
        `wp_ch_statuses` 
        ON `wp_ch_station`.status = `wp_ch_statuses`.id
WHERE `wp_ch_station`.id != 31

[Chillifish]

I've got it to work by going through the Medoo class and changing quotes to backticks and deleting quotes where I saw fit. I don't know enough about the class to know if it will have any knock on effects, but it does work now. I must say, I'm a bit confused by why Medoo has been coded this way.

[turbopixel]

see #508

tannu13 commented on 14 Dec 2016 Yeah that is a drag, running it via Command Line, though you can run below command SET SQL_MODE = ANSI_QUOTES Think, this changes (for the session) your default quotes and the query works :) https://github.com/catfan/Medoo/issues/578#issuecomment-304253233

case 'mysql':
    // Make MySQL using standard quoted identifier
    $commands[] = 'SET SQL_MODE=ANSI_QUOTES';

https://github.com/catfan/Medoo/issues/116#issuecomment-45970936

[Chillifish]

Thanks, I tried using that command, but it didn't work.

[catfan]

Please search the issue before. This have been discussed too much time.

This is SQL standard. One of reason using this way is for compatible to other database.

We provided output query with database default quote identifier on new version. 2430a8c2779e167744faf2786881dd2aee6b73be