How to create a new table row with no data (only an ID)?

Need Help
1

I’m trying to create a new row in a database for a table that only has an ID for data. This is to correlate rows in another table.

    $this->Item->create();
    $this->Item->set();
    $this->Item->save();

This code doesn’t generate a new row in the table even though this is the set of methods to create a new row in the table described in all the help / documentation. So far the only way I’ve been able to generate a new row is to create a $data array that sets the id manually, which doesn’t make a lot of sense. I should be able to create the row and get the new ID then use it in the other fields.

CakePHP 2.0.

2

Did you tried

$this->Item->create();
$this->Item->set(‘field1’,null); // or $this->Item->set(‘field1’,‘’);
$this->Item->save();

3

Hi Ruslan, thank you for the suggestion.

Unfortunately neither of those suggestions worked.

For the moment I will have to resort to a fairly ugly solution of getting the highest ID then increment it by one and setting it the ID manually, but that seems very inelegant. Either CakePHP has a way of doing this that I don’t know about or this is a pretty big failing.

4

Probably, I didn’t understand your task.
Better to see table’s DDL for understand your picture.

5

Table design is

id not null auto increment

That’s the whole table.

6

Auto increment - you have to write data in another fields (MySQL DB generates IDs) , or you have to control ids . but it’s not auto increment
:slight_smile: Create fake field char(1) and $this->Item->set(‘field1’,’’);
But I miss understand for what needs that table.

7

Ok, so basically CakePHP doesn’t handle this properly. That kind of sucks, but I understand - I’ll stick with my current solution of getting the max ID and then setting the ID manually.

8

Yes.
BTW. Could you show INSERT SQL query for table with one auto increment only ? :slight_smile:

9

Sure, any of these:

INSERT INTO tablename () values (); INSERT INTO tablename (id) values (default);

10

Doing this may cause race conditions and thus duplicate IDs. Use Ruslan’s suggestion and just add a dummy column that you fill with a dummy value.

11

I don’t think this would cause race conditions, Bankai. MySQL is generating the IDs using auto-increment and assigning them to a new row during the time of running the one statement. There is no more chance of a race condition / duplicate ID than any other insert statement, unless you have specific information about MySQL handling this insert differently than other inserts.

12

Of course, auto increment columns in MySQL work fine. However, you wrote yourself that you getting the highest ID, increment and then save it, e.g. something like this:

$item = $this->Item->find(‘MAX (id’));
$item->id = $item->id +1;
$this->item->save();

This definetely has a race condition. If CakePHP requires some data to be filled in in order to do an actal save, just add the dummy column. I can’t blame the Cake developers for not doing an INSERT if there is nothing to save…

13

Hi Bankai - yes, that initial solution had a race condition, but I labeled it as a non functional hack when I initially proposed it - “that doesn’t make a lot of sense” - I knew that was an inelegant / inoperable solution, which is why I’m here to figure out a solution in CakePHP.

The latter solutions I provided, which I discovered after creating this post, work properly and are the only ones that are really of interest here, not my old hack solution, but at the moment it sounds like the only implementation of the proper solution is a direct query.

Cake has essentially prohibited access to something that works fine in MySQL - creating row with now data other than a single AA column ID table. There are cases where a single AA ID table is good practice - like defining a domain. But how useful it is is beside the point, as it isn’t de facto a standard that single column ID tables are bad design - the problem is that I cannot access a fundamental functionality of MySQL because the CakePHP developers didn’t take it into account.

14

This was the solution I ended up using:

    $this->ModelName->query('INSERT INTO table_name () values ();');
    $array_with_id = $this->ModelName->query('select last_insert_id() as id;');
    $newId = $array_with_id[0][0]['id'];
15

I’m glad you found a solution that works for you. I’d like to point out that the Model class is intended as an Object-Relational Mapping, making it convenient in dealing with ‘records’ of data, but because of the abstractions limits your access to some lower-level behaviors like you were trying to use. Given that you have a table with a single column, I don’t think the ORM patterns that Model tries to afford will work well. Instead you could either use raw SQL as you’ve done, or use the Datasource layer directly.