INSERT statement only works once - unless a row already exists in the table

dougmcc1

I'm still trying to get the basics for PHP/mySQL down. I have a query which adds a row of data to a table. But if there is already a row of data in the table, then no more new rows get added. Here is my query:

mysql_query("INSERT INTO table (attribute1,attribute2,attribute3,attribute4,attribute5) VALUES ('$var1','$var2','$var3','$var4','var5')");

How come it won't add any more rows if one already exists?

Thanks.

LordShryku

Is the new data different from the existing data? My guess at this point would be that you're conflicting on an existing unique constraint of primary key.

dougmcc1

Doesn't matter. Whether the data is the same or diferent, if there is a row in the table, the new row doesn't get added. I have an ID field which is the PRIMARY KEY and it's set to auto-increment as well.

LordShryku

Well damn...there goes that theory. Are you calling mysql_error() to see what is erroring out?

dougmcc1

Nope. Be right back 🙂

dougmcc1

I got an error of 'Duplicate entry '0' for key 2' and realized that key2 is set as a unique field but I wasn't inserting a value into that field so a 0 was being inputted everytime instead of a unique number.

But now I'm stuck on this statement:
mysql_query("INSERT INTO table1 (field1,field2) VALUES ('$val1','$val2') SELECT * FROM table2 WHERE field3='$var3'");

table1 is just like table2 except that it has 2 extra columns. I want all the previous columns in the row to be inserted with data from table1 and then the last 2 columns to be insterted with $val1 and $val2. Do I absolutely HAVE to insert every value one by one or is it possible to use the INSERT...SELECT and then add the extra 2 fields?

LordShryku

You just lost me....

you have table1 (4 columns) and table2 (2 columns). You want to select the two columns from table2, insert them into table one with two other variables? Is this correct?

dougmcc1

Yes, but actually I'm dealing with about 22 and 20 columns respectively. But yeah, thats the idea.

LordShryku

Okay, either way, try this

// assuming right now that you have var1 and var2
// and that you are going to just select everything else
$sql = "INSERT INTO table1 (field1,field2, field3, field4) 
           SELECT '".$var1."' AS field1,
                  '".$var2."' AS field2, 
                  * 
           FROM table2 
           WHERE field3 = '".$var3."'";

dougmcc1

What's the difference between '".$var3."' and '$var3'?

LordShryku

It just seperates the variable from the string. Sometimes you'll get errors if you're using $POST, $GET, $_SESSION variables without seperating them from the string. And it's just good practice for your coding 🙂

dougmcc1

Ah, ok. Hey while I'm at it, how do the long URL's work? How do you take the parameters in a URL and send them to a PHP variable? How do you automatically generate a long URL with all the parameters in it?

LordShryku

Well, everything in a URL can be used as as $GET variable.
So http://blah.com?this=that&do=something
you would then have the variables of
$GET['this'] = "that";
and
$_GET['do'] = "something";

You can insert anything you want into a URL to use like that, but I'd suggest against it if you can. It makes the URL a lot harder to bookmark or remember, plus there is a limit on how much you can put in there before it just truncates the URL.

dougmcc1

Yeah I'm a SEO so I wanted to avoid the long URL's to avoid problems with my site being spidered.

Well, thanks for your help again 🙂 Moving on to joins now. This should be fun...

Oh, one more thing. Do you know where I can read about how to make my site secure?

Thanks!

LordShryku

No problem. You can buy me a beer next time I'm in Illinois 😃

You may want to look around here, search on the forums. There's been a lot of discussions on securing sites.

dougmcc1

"You can buy me a beer next time I'm in Illinois "

You got it.