OKi have 2 problems and one causes the other. SO i would like to fix the first part to keep the second one from having to be done.

I am inserting multiple records into a dbase, php and mysql, using a string. I need to know how to code this so if the values already exist that it DOES NOT enter them.

For example. The insert code looks like:

mysql_select_db($database_main, $main);
foreach ($HTTP_SESSION_VARS['program'] AS $value) {
$sql = "INSERT INTO publisher_advertisers (account_id, program_id) VALUES (".$HTTP_SESSION_VARS['user_id'].", $value)";
mysql_query ($sql) or die(mysql_error());
}

Right now i am getting dups and want to keep that from happening.

Also, is there a way to delete the dups and leave just one record remaining so i can fix it for now!

Thanks

Zack

You can define an unique constraint to the account_id,program_id field-combination so duplicates are denied at the database level. Not sure about the exact syntax in mysql, but in postgres
it was something like:

Alternatively you can insert only rows not existing in the table.
Few syntaxes:
Pick the one you figure out faster or more readable,YMMV.

OK i am guess the 127,42 are the variables so where does the VALUES line go?

Sorry a newb here

Z

well gettting this result:

You have an error in your SQL syntax near 'where (29,126) not in (select * from test)' at line 1


with this code:


HEPLEPLPEE

Z

Gaah..

Just checked, the same syntax is not working for mysql, not atleast for 4.0.18-log.

The same syntax is found from the manual, but it says that it is supported in 4.1 and above. But 4.1 is an alpha-release.

See
http://dev.mysql.com/doc/mysql/en/Nu..._features.html
http://dev.mysql.com/doc/mysql/en/Subqueries.html

and maybe
http://dev.mysql.com/doc/mysql/en/Re...ubqueries.html

It can be writen in MySQL by using the rewrite-rules, but the result is not beatiful.

Yoks! What a mess!
What this does, is that it takes a cross product with two tables leaving the correct output.

Say that we have table test containing values
(1,10), (2,20), (5,27) and we would like to add a valuepair (2,20) there.

Now the select statement in insert does the following:
It first creates a row containing values (2,20). (The 'from test as t1'-part is just fakeing this to look as join operation). Now it joins the result in hand to the table test (called as t2). What we have now is:
(fake1, fake2, t2.account_id, t2.program_id) with rows:
2,20, 1, 10
2,20, 2, 20
2,20, 5,27
The "ON" part in the join does that t2.account_id has to be 2 and t2.program_id has to be 20. now only line left here is
2,20, 2,20
The where part says that t2.account_id has to be null and t2.program_id has to be null. This is not true for this line, so it is left out. Result is empty set, so this (2,20) is not added to the table.

If we want to add an other entry, like (2,50), now we have:
(fake1, fake2, t2.account_id, t2.program_id)
2,50, 1, 10
2,50, 2, 20
2,50, 5,27
where the ON part does that t2.account_id has to be 2 and program_id has to be 50. This holds for none of the rows, so it looks that nothing is left. But since this is a "LEFT JOIN", it means that there has to be atleast one row from the "left table", so what is remaining now is a row
(2,50,NULL,NULL).
Now the last where condition holds (they are both null), so the selection returns those values, which are ultimately inserted into the table.

This is braindamaged and pathologically complex thought -pattern that has to be used here just to archive an insert statement not inserting duplicates. Some things in MySQL are not in good shape..

3.23.54 is what i have, so i maybe need to run the old version?

Or should i upgrade to 4? WIll this break other things by upgrading?

Ok i upgraded to 4.0.18

Should this make the first one work now?

I will try both i guess


Zack

The two forms I used in the first post doesn't work untill in the version 4.1 (support for subqueries). The last form should work fine in 4.0.18 (maybe in earlier versions too).

Well i had to downgrade due to my PHP/Apache version not working with 4.0.18

HOW LAME

Anyway oh well i will try second form

Z

Getting:

INSERT TABLE 'test' isn't allowed in FROM table list

My table looks like this BTW:

Fieldname - Type - Allow Nulls? - Key - Default Value - Extras
account_id int(10) unsigned No None 0
id nt(10) unsigned No Primary auto_increment
program_id int(10) unsigned No None 0
accepted datetime No None 0000-00-00 00:00:00



Zack

Gaah.. I get the same error by installing 3.23.58 and trying those sentences.
Fun, isn't it?

Plan B: Use the unique constraint with ignore keyword:
Plan C: Do the check in application level. That is, first sql-select matching rows from the table to see if it already exists and don't add it if it does.

Plan D:
use postgresql

Yeah really this is SOOO lame howhard it is to do this in mySQL, older versions.

Um how would i use the php to check for dups? The prob is the table is used by another ap so i can not change the structure of the existing data table.

Plan c seems the best route, so what would my code look like to check for dups before inserting?

Zack

At the idea level:
mysql_select_db($database_main, $main);
foreach ($HTTP_SESSION_VARS['program'] AS $value) {
$sql = "SELECT count(*) from publisher_advertisers where
account_id=$HTTP_SESSION_VARS['user_id'] and
program_id=$value";
mysql_query($sql) or die(mysql_error());
if ( the result == 0 ) {
$sql = "INSERT INTO publisher_advertisers (account_id, program_id) VALUES (".$HTTP_SESSION_VARS['user_id'].", $value)";
mysql_query ($sql) or die(mysql_error());
}
}

The php side:
...the sql stuff.. with "select count(*) as dups"
$resultset = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_object($result);
if($row->dups == 0 ) {
... the insert sql stuff...
}