Problems with JTable::getInstance('frontpage', 'Table');

[Stian]

Hi, this is perhaps quite stupid, butI have the following code:

Code: Select all

global $mainframe;

		JTable::addIncludePath(JPATH_ADMINISTRATOR.DS.'components'.DS.'com_frontpage'.DS.'tables');
		$table =& JTable::getInstance('frontpage', 'Table');
		$table->ordering = $table->getNextOrder();
		$table->content_id = 45;
		if (!$table->store()) {
			echo $table->getError();
			die();
		}

The SQL generated by this is " UPDATE jos_content_frontpage SET `ordering`='6' WHERE content_id='45' "

What I would like to do is to add a new frontpage item, and not update an existing one. So the SQL should be an INSERT.

Please help!

Stian

[SotL]

Hi Stian

table->store() function  depends on the value of "id". I am not 'completely' sure here, but I do believe that unless "id" is empty it produces an update query.

What I mean is that you need to take out:

$table->content_id = 45;

otherwise it will always "update".

The function runs "insert" when id is empty and an update when id has a value already.

I could be wrong, but as far as I know that is how it works.

Try a little test and see, but I am pretty sure that is the way it works.

Hope this helps

[Stian]

I think you're quite right, BUT jos_content_frontpage doesn't have a unique auto increment field (I think it's one of very few in Joomla), which means that this functinonality doesn't work on this table.

Stian

[SotL]

I am not sure how many records you have in the database as of right now, but if not to many you could ADD it. Just make it "auto_id" rather then content_id and then go to your records starting at the top and edit them from 1 twhatever you have. After that you can go back to the "auto_id" and make it a primary key and add auto increment to it and from there it will take care of itself

I do not know of a way to make store() work without an auto_increment field. So maybe someone with more knowledge in this area will come along and help out.

[Stian]

I don't think it's wise to mess with the Joomla! core tables. I'll just use a regular SQL INSERT INTO for now. But I think it should be stated somewhere that that you can't use the JTable class to update this particualer table...

Stian

[SotL]

I agree that it is not wise, and should be imported into the final release, however adding another "field" to the table will not in any way mess up any other code.

It simply provides a way to use Joomlas core functions rather then self scripting to the database (bypassing security). The only "issue" that would arrise is when importing or upgrading to a newer version...you would once again need to add the extra field which is not good because a person could easily forget, but there is no side effect for any other code in the core as it simply does not know that tat extra field is available to be used...and thus isn't used.

So it is a matter of choice. I do hope the devs consider adding an auto_id field to it upon release of the final version of 1.5

[Stian]

I have posted this issue to http://forum.joomla.org/index.php/topic,247531.0.html

Thanks for your input!

Stian

[masterchief]

The content_id is technically a foreign key - it's not appropriate for it to have an auto-increment value because it must marry with a known content item.

[SotL]

That makes since, so let me ask you this then. Is the only way to do an insert in this table is by custom coding rather then using Joomla!'s built in functions? Or are we missing something here?

The other thing is, would it be technicaly wrong to build this table (in the core install) with another field called auto_id that could be auto incremented so the table->store() function would insert or update?

[masterchief]

We should actually make the table class a bit smarter to handle this, but yes, it's a manual job.  But since content_id is a primary key, you could use a statement like REPLACE INTO to that would cover both insert and update operations with only one statement.

[Stian]

I never ment to change the existing field to auto increment, but add a new one. That would take care of business, wouldn't it?

S

[masterchief]

No, the table doesn't need an extra field.