0

Got three tables (blogs, tags, and blogtags) all AI and ID set to primary key. I'm making a tagging system to keep track of my sites (localhost). The code below is sort of working, but mysql_insert_id just doesn't seem reliable enough since I get some duplicate rows and the occasional 0 value in it.

/// inserts the blog into blog table.
$insert = mysql_query("INSERT INTO blogs (id, url, user, pass, dname, islocal, cat2post) VALUES ('', '$blog', '$bloguser', '$blogpassword', '','NO','$_POST[cat2blog]')")or die( 'Error: ' . mysql_error());

$taggit1 = mysql_insert_id();

$page->content .= "<p class=\"alert\">Success - External blog Added!</p>";

/// let's see what tags we have and explode them.
//$tags  = $_POST['tags'] which is an array of words seperated by comma
$tags = 'fishing';
$pieces = explode(",", $tags);

/// go through the tags  and add to tags table if needed.
foreach ($pieces as $l){
$l = trim($l);  

$query = "SELECT id FROM tags WHERE tag = '$l'";
$result = mysql_query($query) or die( "Error: " . mysql_error() . " in query $query");
$row = mysql_fetch_array($result);
$taggit2 = $row[0];

if ($taggit2 == '') {
$insert2 = mysql_query("INSERT INTO tags (id, tag) VALUES ('','$l')")or die( 'Error: ' . mysql_error());
$taggit2 = mysql_insert_id();
$page->content .= "<p class=\"alert\">This tag didn't exist - so I inserted a new tag</p>";
}

/// for each tag we have, let's insert the blogstags table so we can reference which blog goes to which tag.  Blogstags_id should map to the id of the blog.

$insert3 = mysql_query("INSERT INTO blogstags (id, tag_id, blogstags_id) VALUES ('','$taggit2','$taggit1')")or die( 'Error: ' . mysql_error());

}

Guess I need a different solution than mysql_insert_id - ideas? Suggestions?

As requested table structures:

CREATE TABLE IF NOT EXISTS `blogs` (
  `id` int(11) NOT NULL auto_increment,
  `url` text NOT NULL,
  `user` text NOT NULL,
  `pass` text NOT NULL,
  `dname` text NOT NULL,
  `islocal` varchar(3) NOT NULL,
  `cat2post` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;


CREATE TABLE IF NOT EXISTS `blogstags` (
  `id` int(11) NOT NULL auto_increment,
  `tag_id` int(11) NOT NULL,
  `blogstags_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;


CREATE TABLE IF NOT EXISTS `tags` (
  `id` int(11) NOT NULL auto_increment,
  `tag` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
Ed Charkow
  • 71
  • 6
  • 3
    This doesn't answer your question, but you might also want to remove the SQL injection vulnerabilities from your code - http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php – El Yobo May 12 '11 at 02:27
  • this just runs on my local machine - I'm aware there is no security. – Ed Charkow May 12 '11 at 02:30
  • Can you post your table structures. – GWW May 12 '11 at 02:33
  • 1
    I think you'd be better off finding out why `mysql_insert_id()` isn't returning what you want it to. There are [better ways](http://stackoverflow.com/questions/5955453/continue-postgres-transaction-with-exceptions-in-java/5956303#5956303) to safely insert new entries without duplicates. – mu is too short May 12 '11 at 02:37
  • `show create table blogs`, please. – Nemoden May 12 '11 at 02:37
  • You may want to look into this, in addition to your (I believe invalid) insert_id issue: http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain – Denis de Bernardy May 12 '11 at 02:39
  • @dennis - I'm aware this isn't secure. Need to figure out why it's not functioning properly and I can move onto that after it if needed. This is something to run on my local machine and I'll be the only one using. – Ed Charkow May 12 '11 at 02:45
  • 1
    @Ed - You may be seeing some errors precisely because of the SQL injection problem. If any of your tags have quotation marks or backslashes in them, your code won't work as expected. If that's not an issue, feel free to disregard. But I'd suggest running a query just to make sure. – Conspicuous Compiler May 12 '11 at 02:58
  • I've been testing this out with just a single tag with a single word - and it doesn't make a difference. In fact the insert3 query is the only one goofing up and it's just numbers. Thanks for the suggestion though, appreciate it. – Ed Charkow May 12 '11 at 03:00

2 Answers2

1

mysql_insert_id() is working fine. The problem could be that you are using persistant connections. With persistant connections, all kinds of funky concurrency issues can happen. Don't use them unless you really, really have to.

Sander Marechal
  • 22,978
  • 13
  • 65
  • 96
  • I 'think' i've got this licked - not sure what's different but it's working now. The only real thing I did was empty all the tables in the database and did a repair. Not sure if that was it, or if it was something else. – Ed Charkow May 12 '11 at 06:39
0

Two options - you could switch to PostgreSQL which allows you to return an auto_incremented ID as part of the insert query.

Or, if you are sticking with MySQL, you can use the MySQL LAST_INSERT_ID() function -

HorusKol
  • 8,375
  • 10
  • 51
  • 92