Hi there,

I have a system to create news-items, which can belong to certain
categories.
The categories are linked by id with an extra "item_id-category_id"-
table. (InnoDB + FK's)

I want to be able to create a new News Item, together with it's
categories.
My current code is below. It works, but if the categories fail, the
News item is still inserted.
If something fails, nothing should happen...
Best of all, would be to have everything in 1 query if possible. (IMO)

The categories are inserted via an <select name="categories[]"
id="categories[]" multiple="multiple">-field which is dynamically
populated.

Thanks in advance!


$qryInsertItem =
'INSERT INTO `news_items` ( `open` , `date` , `url` , `title` ,
`body` )
VALUES ( \''.mysql_real_escape_string($_POST['open']).'\',
\''.mysql_real_escape_string($date).'\', \''.
mysql_real_escape_string(MkUrlSafe($_POST['title'])).'\',
\''.mysql_real_escape_string($_POST['title']).'\',
\''.mysql_real_escape_string($_POST['body']).'\');';

mysql_query($qryInsertItem, $rConn);

if(is_array( $_POST['categories'] )){

foreach( $_POST['categories'] as $key =>$val){
$cats .= '('.mysql_insert_id().', '.$val.'),';

};

$cats=trim($cats, ',');

}else{

$cats=NULL;

};

$qryInsertCats = 'INSERT INTO `news_cat_item` ( `item_id` , `cat_id` )
VALUES '.$cats.';';


mysql_query($qryInsertCats , $rConn) )

On 1 Oct, 21:48, frizzle <phpfriz...@gmail.com> wrote:

> "I want to be able to create a new News Item, together with it's categories."

Possesive its has no apostrophe! This should say "I want to be able to
create a new News Item, together with its categories."

Now, can you post the table schemas?
And explain the circumstances under which something fails?
And state whether you are using transactions?

On Oct 2, 2:28 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 1 Oct, 21:48, frizzle <phpfriz...@gmail.com> wrote:
>
> > "I want to be able to create a new News Item, together with it's categories."
>
> Possesive its has no apostrophe! This should say "I want to be able to
> create a new News Item, together with its categories."
>
> Now, can you post the table schemas?
> And explain the circumstances under which something fails?
> And state whether you are using transactions?

Sorry about the "it's". English isn't my native tongue, but i do want
spell correctly of course, so thanks for pointing it out to me.

About my system:

> Now, can you post the table schemas?
They're below my reply.

> And explain the circumstances under which something fails?
If the parent's category id appears not to exist, it refuses (as it
should) to create orphans in the intermediate table (news_cat_item).
It should also discard creating the first item as well then. So I
suppose it should all be done in 1 query, maybe with some COMMIT
commands? But then i wouldn't know how to get the last_insert_id();.

> And state whether you are using transactions?
Sorry, I don't understand this question/remark.

My table structures are below...

Thanks, frizzle.


-- Table structure for table `news_items`
--

CREATE TABLE `news_items` (
`id` int(10) unsigned NOT NULL auto_increment,
`open` enum('y','n') NOT NULL default 'n',
`date` tinyint(9) unsigned default '0',
`url` varchar(185) NOT NULL default '',
`title` varchar(255) NOT NULL default '',
`body` mediumtext NOT NULL,
`views` int(5) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `url` (`url`),
KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


-- Table structure for table `news_cats`
--

CREATE TABLE `news_cats` (
`id` int(10) unsigned NOT NULL auto_increment,
`order` int(10) unsigned NOT NULL default '0',
`url` varchar(185) NOT NULL default '',
`title` varchar(255) NOT NULL default '',
`descr` text NOT NULL,
PRIMARY KEY (`id`),
KEY `cat_url` (`url`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


-- Table structure for table `news_cat_item`
--

CREATE TABLE `news_cat_item` (
`id` int(10) unsigned NOT NULL auto_increment,
`item_id` int(10) unsigned NOT NULL default '0',
`cat_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `item_id` (`item_id`,`cat_id`),
KEY `cat_id` (`cat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
-- Constraints for table `news_cat_item`
--
ALTER TABLE `news_cat_item`
ADD CONSTRAINT `news_cat_item_ibfk_1` FOREIGN KEY (`item_id`)
REFERENCES `news_items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `news_cat_item_ibfk_2` FOREIGN KEY (`cat_id`)
REFERENCES `news_cats` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;