Btukfyl

EDIT: Added current data in use

I have a small functions file I've created as a database interface to be called for handling queries in a simple manner.

// Run SQL query

function runSQLQuery($sql) {

    // Open DB

    $db = connectToDB();



    if( $db->connect_errno ) {

        $data['response'] = false;



        $data['error_code'] = $db->connect_errno;



        $data['error'] = $db->connect_error;



        return $data;

    }



    // Run & return query

    $response = $db->query($sql);



    if( is_bool($response) ) {

        $response = ($response) ? 'true' : 'false';

    }



    switch($response) {

        case "true":

            $data['raw'] = $response;



            $data['response'] = true;



            $data['error'] = null;



            $data['id'] = $db->insert_id;



            return $data;



        case "false":

            $data['raw'] = $response;



            $data['response'] = false;



            $data['error_code'] = $db->errno;



            $data['error'] = $db->error;



            return $data;



        default:

            $data['raw'] = $response;



            $data['response'] = true;



            $data['error'] = null;



            if( $response->num_rows > 0) {

                while( $row = $response->fetch_assoc() ) {

                    $data['data'] = $row;

                }

            }

            else {

                $data['data'] = null;

            }



            return $data;

    }

}



// Open database connection

function connectToDB() {

    // Connect to DB

    return new mysqli(

        DB_SERVER,

        DB_USER,

        DB_PASS,

        DB_NAME

    );

}

In most cases I'll build an SQL query somewhere else and pass it to this runSQLQuery function, then echo the response as JSON for my web-app.

I've had no issues with simple SELECT, UPDATE, DELETE commands, all have worked successfully. However I want to implement something slightly more complex for one function, a SELECT INNER JOIN query. The function building this query and executing is:

public function get($args) {

    extract($args);



    // Get event

    if(isset($userId)) {

        $sql =

        "SELECT j.id, j.event_address as address, j.event_datetime as datetime, j.event_note as note, j.event_price as price, j.client_id as userId, j.client_card_id as cardId, GROUP_CONCAT(p.event_package_id) as packages             

        FROM nr_jobs as j 

        INNER JOIN nr_job_packages as p ON p.event_id = j.id 

        WHERE j.client_id = $userId

        GROUP BY j.id;";

    }

    if(isset($jobId)) {

        $sql =

        "SELECT j.id, j.event_address as address, j.event_datetime as datetime, j.event_note as note, j.event_price as price, j.client_id as userId, j.client_card_id as cardId, GROUP_CONCAT(p.event_package_id) as packages             

        FROM nr_jobs as j 

        INNER JOIN nr_job_packages as p ON p.event_id = j.id 

        WHERE j.id = $jobId

        GROUP BY j.id;";

    }



    $events = runSQLQuery($sql);

    $events["sql"] = $sql;



    return $events;

}

For some reason running this specific query returns false. The JSON output I receive on my web app is

{

  "raw": "false",

  "response": false,

  "error_code": 0,

  "error": "",

  "sql": "SELECT j.id, j.event_address as address, j.event_datetime as datetime, j.event_note as note, j.event_price as price, j.client_id as userId, j.client_card_id as cardId, GROUP_CONCAT(p.event_package_id) as packages FROM nr_jobs as j INNER JOIN nr_job_packages as p ON p.event_id = j.id WHERE j.client_id = 1 GROUP BY j.id;"

}

If I run this query directly I actually get the correct results, several rows of data with a CONCAT column of packages as 1,2,3 as expected.

Database structure:

CREATE TABLE IF NOT EXISTS nr_jobs(

    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    event_address TEXT NOT NULL,

    event_datetime DATETIME NOT NULL,

    event_note TEXT,

    event_price DECIMAL(13,4) NOT NULL,

    client_id BIGINT NOT NULL,

    client_card_id BIGINT NOT NULL,

    FOREIGN KEY (client_id) REFERENCES nr_clients(id),

    FOREIGN KEY (client_card_id) REFERENCES nr_payment_cards(id)

);



CREATE TABLE IF NOT EXISTS nr_job_packages(

    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    event_package_id BIGINT NOT NULL,

    event_id BIGINT NOT NULL,

    FOREIGN KEY (event_id) REFERENCES nr_jobs(id) ON DELETE CASCADE,

    FOREIGN KEY (event_package_id) REFERENCES nr_packages(id) ON DELETE CASCADE

);

Output of query run directly:

+----+-------------------------------------------------------------------------------+---------------------+------+-----------+--------+--------+----------+

| id | address                                                                       | datetime            | note | price     | userId | cardId | packages |

+----+-------------------------------------------------------------------------------+---------------------+------+-----------+--------+--------+----------+

|  1 | SIM HQ LT3.02A, 461 Clementi Rd, Singapore, South West 59, Singapore          | 2018-11-28 10:38:00 |      |  320.0080 |      1 |      3 | 2,3      |

|  2 | Tay Eng Soon Library, Blk A, SIM HQ, Singapore, South West 59, Singapore      | 2018-11-28 10:42:00 |      |  320.0080 |      1 |      3 | 2,3,4    |

|  3 | SIM HQ Corporate Office, 461 Clementi Rd, Singapore, South West 59, Singapore | 2018-11-28 10:43:00 |      | 1020.0000 |      1 |      3 | 1,2,3,4  |

|  4 | L.A. City DOT, Los Angeles, California 90012, United States                   | 2018-11-28 12:15:00 |      |  860.0000 |      1 |      4 | 2,3,4    |

+----+-------------------------------------------------------------------------------+---------------------+------+-----------+--------+--------+----------+

If the query has no issue, and other queries work within my function, why is this specific SELECT INNER JOIN query failing with no error?

EDIT:

nr_jobs data:

SELECT * FROM nr_jobs;

+----+-------------------------------------------------------------------------------+---------------------+------------+-------------+-----------+----------------+

| id | event_address                                                                 | event_datetime      | event_note | event_price | client_id | client_card_id |

+----+-------------------------------------------------------------------------------+---------------------+------------+-------------+-----------+----------------+

|  1 | SIM HQ LT3.02A, 461 Clementi Rd, Singapore, South West 59, Singapore          | 2018-11-28 10:38:00 |            |    320.0080 |         1 |              3 |

|  2 | Tay Eng Soon Library, Blk A, SIM HQ, Singapore, South West 59, Singapore      | 2018-11-28 10:42:00 |            |    320.0080 |         1 |              3 |

|  3 | SIM HQ Corporate Office, 461 Clementi Rd, Singapore, South West 59, Singapore | 2018-11-28 10:43:00 |            |   1020.0000 |         1 |              3 |

|  4 | L.A. City DOT, Los Angeles, California 90012, United States                   | 2018-11-28 12:15:00 |            |    860.0000 |         1 |              4 |

+----+-------------------------------------------------------------------------------+---------------------+------------+-------------+-----------+----------------+

nr_job_packages

SELECT * FROM nr_job_packages;

+----+------------------+----------+

| id | event_package_id | event_id |

+----+------------------+----------+

|  1 |                2 |        1 |

|  2 |                3 |        1 |

|  3 |                2 |        2 |

|  4 |                3 |        2 |

|  5 |                4 |        2 |

|  6 |                1 |        3 |

|  7 |                2 |        3 |

|  8 |                3 |        3 |

|  9 |                4 |        3 |

| 10 |                2 |        4 |

| 11 |                3 |        4 |

| 12 |                4 |        4 |

+----+------------------+----------+

Answer: Previous function contained a typo.