CODEWITHSUNDEEP
phpsqlcodeigniter
Dillinger
Dillinger

Reputation: 1903

Query syntax error

I'm trying to get the GroupID field of my ea_appointments table but this error appear:

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 'as g FROM (ea_appointments)' at line 1

SELECT COALESCE(MAX(GroupID), 0) + 1 as g FROM (ea_appointments)

Line Number: 330

Now in my table I've no values inserted yet, the GroupID it's needed because I want to get the same id inserting multiple appointments.

For example some like this. SQL FIDDLE.

Now in CodeIgniter I've this code:

    $this->db->select('COALESCE(MAX(GroupID), 0) + 1 as g')->from('ea_appointments');
    $query = $this->db->get();
    $groupId = $query->g;

    $appointment['GroupID'] = $groupId;

    if(!$this->db->insert('ea_appointments', $appointment))
    {
        throw new Exception('Error.');
    }

    $insert_id = intval($this->db->insert_id());

But seems not working, and I don't know why.
NB: This code is called recursively by a foreach, in this foreach is passed as parameter the variable $appointment, this contain each time different services associated for the same appointment.

NEW CODE

   $query = $this->db->query("SELECT COALESCE(MAX(GroupID), 0) +1 AS g FROM ea_appointments");

    $appointment['GroupID'] = $query;

    if(!$this->db->insert('ea_appointments', $appointment))
    {
        throw new Exception('Appuntamento non inserito.');
    }

This query working but when I perform the insert() I get this error:

Severity: 4096

Message: Object of class CI_DB_mysql_result could not be converted to string

Filename: mysql/mysql_driver.php

Line Number: 552

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 ')' at line 1

INSERT INTO ea_appointments (id_users_provider, start_datetime, end_datetime, notes, is_unavailable, id_users_customer, id_services, book_datetime, hash, GroupID) VALUES ('85', '2015-11-15 12:15:00', '2015-11-15 12:15:00', '', 0, '87', '13', '2015-11-15 03:12:48', '345831a567cdf7c5f71a3a5a12121f2d', )

Upvotes: 0

Views: 51

Answers (1)

Niranjan N Raju
Niranjan N Raju

Reputation: 11987

You can actually replace your query like this,

$this->db->select('COALESCE(MAX(GroupID), 0) + 1 as g');
$query = $this->db->get('ea_appointments');

EDIT

$this->db->query("SELECT COALESCE(MAX(GroupID), 0) +1 AS g FROM ea_appointments");

Upvotes: 1

Related Questions