Fetching data from three tables but getting error of SQL syntax

0
votes

I am fetching data from three tables:

$result = $this->db->query("
    SELECT 
        `meetings`.*,
        `follow_up`.id as follow_up_id,
        `follow_up`.comment as follow_up_comment,
        `follow_up`.date as follow_up_date,
        `follow_up`.time as follow_up_time,
        SELECT first_name, last_name, user_mobile, useralt_mobile from users where id = user_id,
        (SELECT address FROM day_location WHERE `meetings`.assigned_to_id = user_id AND `follow_up`.date = date LIMIT 1) AS location_name
        FROM meetings
        LEFT JOIN follow_up ON `meetings`.id = `follow_up`.`meeting_id`
        WHERE follow_up.`date` BETWEEN '{$fromDate_formated}' AND '{$toDate_formated}'
            " . ($user_id > 0 ? " AND `meetings`.assigned_to_id = '{$user_id}'" : '') . "
    ORDER BY `follow_up`.id DESC
");

Error:

A Database Error Occurred
Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT first_name, last_name, user_mobile, useralt_mobile from users where id = ' at line 7

SELECT meetings.*, follow_up.id as follow_up_id, follow_up.comment as follow_up_comment, follow_up.date as follow_up_date, follow_up.time as follow_up_time, SELECT first_name, last_name, user_mobile, useralt_mobile from users where id = user_id, (SELECT address FROM day_location WHERE meetings.assigned_to_id = user_id AND follow_up.date = date LIMIT 1) AS location_name FROM meetings LEFT JOIN follow_up ON meetings.id = follow_up.meeting_id WHERE follow_up.date BETWEEN '2018-10-01' AND '2018-10-31' AND meetings.assigned_to_id = '1' ORDER BY follow_up.id DESC

Can you please help?

2
phpmysqlsqlcodeigniter
Copy/paste the error text here instead of posting an image. – Felippe Duarte
What does this SELECT first_name, last_name,... is supposed to do? – Felippe Duarte
You should probably use JOIN's instead of these sub-queries. – jeroen
It looks like you're tossing a bunch of random selects into one statement, which is completely messing up the syntax. – aynber
you should add a proper data sample and the expected result – ScaisEdge

2 Answers

0
votes

You need to replace this:

SELECT first_name, last_name, user_mobile, useralt_mobile from users where id = user_id,

With this:

(SELECT first_name, last_name, user_mobile, useralt_mobile from users where id = user_id),
0
votes

for get the users information you should use a join 

$result = $this->db->query("
        SELECT 
            `meetings`.*,
            `follow_up`.id as follow_up_id,
            `follow_up`.comment as follow_up_comment,
            `follow_up`.date as follow_up_date,
            `follow_up`.time as follow_up_time,
            users.first_name, 
            users.last_name, 
            users.user_mobile,
            users. useralt_mobile, 
            (SELECT address FROM day_location WHERE `meetings`.assigned_to_id = user_id AND `follow_up`.date = date LIMIT 1) AS location_name
        FROM meetings
        LEFT JOIN follow_up ON `meetings`.id = `follow_up`.`meeting_id`
        LEFT JOIN users on users.id = `meetings`.assigned_to_id 
        WHERE follow_up.`date` BETWEEN '{$fromDate_formated}' AND '{$toDate_formated}'
            " . ($user_id > 0 ? " AND `meetings`.assigned_to_id = '{$user_id}'" : '') . "
        ORDER BY `follow_up`.id DESC");