Problem with Insert

phpnewbie911

Hello everyone,
I've been scanning the post and have only gathered pieces of what I'm looking for. I'm also new to this. So I'm hoping someone can help me. I'm using Mysql and I need a query that lets me check my db to see if the field is already in the db, and if not insert the row. But if it is, do not insert, just echo. And the other problem is that I don't quiet understand how to insert the current time and date. This is the query I use now but it doesn't check to see if the row is already there and I get zero's inserted into the db where I want the current time and date to automatically insert.

<?php

$id = "$0";
$ref = "$r";
$fn = "$fn";
$e = "$e";
$t = "$t";

$dbh=mysql_connect ("localhost", "mydbname", "mypassword") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("mydbname");

$query = "insert into mytable values ('$id', '$r', '$fn', '$e', '$t', '$timestamp', 'now()')";
$result = mysql_db_query ("mydbname", $query);
if ($result){
echo "Your Subscription Has Been Confirmed. Click on this link to download your free bonus gifts.";

}
else{
echo mysql_errno().": ".mysql_error()."<BR>";
}

mysql_close ();

My varibles $r, $fn, $e, $t are tranfered into the page thru a string from an email response. The $0 varible is for the auto incrment field that I have in the first column of the table. And I notice from the post that I scanned (looked over) that I may need an unique index.
Would someone please share some knowledge with me on this problem. I hope this is clear.

Thank In Advance!
🙂

nnichols

For a start you don't want to pass "$0" to your auto_increment column, just pass it an empty string "".

In your query you are passing the string 'now()' into your database. Your DATE/TIMESTAMP column won't like that because it is expecting a date. Remove the 'single quotes' from 'now()' and it will work.

To make sure your INSERT is unique you could eithe setup a unique index on one of your columns, that way mySQL will return an error when your try to INSERT duplicate data, or you could do a SELECT first. If the SELECT query returns a row then you have a match, so don't INSERT, else do INSERT.

<?php 

$ref = "$r"; 
$fn = "$fn"; 
$e = "$e"; 
$t = "$t"; 


$dbh=mysql_connect ("localhost", "mydbname", "mypassword") or die ('I cannot connect to the database because: ' . mysql_error()); 
mysql_select_db ("mydbname"); 

$query = "insert into mytable values ('', '$r', '$fn', '$e', '$t', '$timestamp', NOW())"; 
$result = mysql_db_query ("mydbname", $query); 
if ($result){ 
echo "Your Subscription Has Been Confirmed. Click on this link to download your free bonus gifts."; 

} 
else{ 
echo mysql_errno().": ".mysql_error()."<BR>"; 
} 

mysql_close (); 


?>

Hope this helps 😉

P.S. When you include php code in your post put it between [PHP ][/code] tags, it makes it much easier to read

phpnewbie911

Thanks nnichols. I think it will work. But right now its not, probably due to me not understanding my db fields (time and date). In the time field, should I have its type set to timestamp and null or what? Here is my table settings and the query I'm using. Can anyone see whats wrong?

My table setup is:

id int(11) NOT NULL AUTO_InCREMENT
Sid varchar(9) NOT NULL
fname varchar(25) NOT NULL
email varchar(30) NOT NULL
type varchar(4) NOT NULL
time time NOT NULL 00:00:00
date date NOT NULL 0000-00-00

And this is the new script I use:


<?php 

$ref = "$r"; 
$fn = "$fn"; 
$e = "$e"; 
$t = "$t"; 


$dbh=mysql_connect ("localhost", "mydbname", "mypassword") or die ('I cannot connect to the database because: ' . mysql_error()); 
mysql_select_db ("mydbname"); 

$query = "insert into mytable values ('', '$r', '$fn', '$e', '$t', '$timestamp', NOW())"; 
$result = mysql_db_query ("mydbname", $query); 
if ($result){ 
echo "Your Subscription Has Been Confirmed. Click on this link to download your free bonus gifts."; 

} 
else{ 
echo mysql_errno().": ".mysql_error()."<BR>"; 
} 

mysql_close (); 


?>

Can anyone see whats wrong?
Thanks In Advance!

nnichols

You say it's not working, but what is happening? Are you being returned an error from mySQL, or is it just inserting null data?

In the time field, should I have its type set to timestamp and null or what?

I would use a TIMESTAMP or a DATETIME field instead of your DATE and TIME fields.

I would use DATETIME if you want to set this for the creation time of the record - and just use NOW()

I would use TIMESTAMP if you want to be able to record changes to records. If you update a record containing a TIMESTAMP field the TIMESTAMP will automatically update itself. This ony happens with the first TIMESTAMP column in a table, so if you have two TIMESTAMP columns in a table only the first one will auto update.

Hope this helps 😉