Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

V1.11 --> Problem with INSERT

Quote Reply
V1.11 --> Problem with INSERT
I am having a problem inserting a date value into a column of a new row I am adding to a table called SiteDay. The LinkID is added just fine. I have set the DateAdded field as a DATE field (I also tried setting it to TIMESTAMP, but the format is not what I want).

Now, I have read the documentation regarding Date Types at http://www.mysql.com. It seems that the default value of 0000-00-00 cannot be overridden. But this doesn't make sense because date values are added just fine in other tables using the DATE field type. I've also looked at other Links SQL scripts as references to see how the date field is added.

Here is what I have tried so far:

1) Added the following codes before the INSERT codes:

Code:

$today = $SITEDB->get_date;
$in->param ( -name => 'DateAdded', -value => $today );


2) I have also tried using the following INSERT codes:

Code:

$sth = $SITEDB->prepare ("SELECT 1 FROM SiteDay WHERE LinkID = $id");
$SITEDB->do ("INSERT INTO SiteDay (LinkID,DateAdded) VALUES ($id,$today)");


AND

Code:

$sth = $SITEDB->prepare ("SELECT 1 FROM SiteDay WHERE LinkID = $id");
$SITEDB->do ("INSERT INTO SiteDay (LinkID) VALUES ($id)");


I am at a lost...I do not know why the date insertion is not working.

Any suggestions are welcome.

Thanks in advance.

Regards,

Eliot Lee

Quote Reply
Re: V1.11 --> Problem with INSERT In reply to
Okay...I found a solution by using the UPDATE function...

Code:

$SITEDB->do ("UPDATE SiteDay
SET LinkID = '$id',
DateAdded = '$today'
WHERE LinkID = $id
");


I just don't know why the plain INSERT function does not work...

Any explanations are greatly appreciated.

Regards,

Eliot Lee

Quote Reply
Post deleted by Clueless In reply to
Quote Reply
Re: V1.11 --> Problem with INSERT In reply to
Uh..if you had taken the time to READ my POST, you would've noticed that I HAVE ALREADY read documents at the MySQL web site! AND I HAVE ALREADY searched and looked through Threads in the forums and the FAQ site regarding date functions!

Your response is intended for people who do not take the time to research issues and problems OR attempt to solve them on their own, which I DO!

Mad Mad Mad Mad Mad Mad Mad

Regards,

Eliot Lee

Quote Reply
Re: V1.11 --> Problem with INSERT In reply to
Eliot,

I don't think the format of your INSERT command is correct.

You insert a new $in->param() but you don't pass that value to the INSERT at all.

You need to have all the fields you want to insert laid out in the INSERT record:

$sth = $SITEDB->prepare ("SELECT 1 FROM SiteDay WHERE LinkID = $id");
$SITEDB->do ("INSERT INTO SiteDay (LinkID) VALUES ($id)");

You are attempting to insert a single value, $id into a new row of SiteDay, in the LinkID column. If you have any other fields defined "not null" you'd be generating an error.

To UPDATE a row that already exists, you use the UPDATE command, and pass it the parameters you want to set:

$SITEDB->do ("UPDATE SiteDay
SET LinkID = '$id',
DateAdded = '$today'
WHERE LinkID = $id ");

Which is what you did here... you are updating an existing row with new values.


The matching insert command would be: (assuming no other fields are in the table).

INSERT INTO SiteDay (LinkID, DateAdded) values ($id, $today)

Which is what you have here, EXCEPT:

you are attempting to INSERT into an existing record, when what you really mean to do is UPDATE that record with new values.

$sth = $SITEDB->prepare ("SELECT 1 FROM SiteDay WHERE LinkID = $id");
$SITEDB->do ("INSERT INTO SiteDay (LinkID,DateAdded) VALUES ($id,$today)");

INSERT adds a row to a table
UPDATE updates values within a row already in the table.

BTW... if this is what you are doing (inserting a new row into a 2-column table), and before you say "I've done all that" did you define either of those two fields "not null", "unique" or "Primary key" ?? If so, you are generating a duplicate key error which isn't getting printed out.


http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: V1.11 --> Problem with INSERT In reply to
Thanks for the explanation, pugdog...I really appreciate it.

My understanding is that using INSERT is for INSERTing new rows in the table, which is what I am attempting to do not to UPDATE a current row in the table. I sort of understand from your explanation regarding the differences between UPDATE and INSERT...

Question: Why would I use UPDATE if I am inserting a new row in a table? I guess that is what is confusing me.

In Reply To:
did you define either of those two fields "not null", "unique" or "Primary key" ??
Yes...the LinkID is the Primary Key field. And I have added a loop within the Site of the Day script that first checks the SiteDay table to see if the LinkID exists...if it does, then it goes through the random codes again to pull another Link from the Links table...if the ID of the Link is NOT found in the SiteDay table, then the record is added.

Thanks for the tip.

Regards,

Eliot Lee