Link to home
Start Free TrialLog in
Avatar of Bruce Gust
Bruce GustFlag for United States of America

asked on

How would I construct this INSERT statement in PDO?

I'm converting some older, procedural code into PDO. I've got an INSERT statement that looks like this:

$query="insert into tblscreeningconsumertosubperiod (fn,ln,dob,email,createdt,screeningsubperiodid,consumerhistoryid) values ('$fn','$ln','$dob','$email',{fn NOW()},$screeningsubperiodid,-1)";

I recognize the {fn NOW()) as something that is used in T-SQL, but I don't know how to incorporate it here (http://stackoverflow.com/questions/278777/t-sql-using-fn-now-in-where).

Also, I've got that dynamic where I'm introducing a "-1" for the consumerhistoryid. Not sure how to approach that...

Thoughts?

If I write this in PDO, I would be starting off with something like this:
Avatar of JesterToo
JesterToo
Flag of United States of America image

It would be really helpful, probably necessary. to see your complete table schema for this table... including full column definitions and constraints, if any,

What is the "-1" signifying?  That there is no id value yet?  Can you use a NULL for this column?
Avatar of Bruce Gust

ASKER

As bizarre as this might sound, I don't have access to the schema, at least not right now. But...

Here's what I'm planning on at first brush: Would this work:

$sql_8=$con->prepare("insert into tblscreeningconsumertosubperiod (fn,ln,dob,email, screeningsubperiodid, createdt, consumerhistoryid) values (;fn, :ln, :dob, :email, :screeningsubperiodid, :now,:consumerhistoryid)");
            $sql_8->bindParam(':fn', $fn, PDO::PARAM_STR);
            $sql_8->bindParam(':ln', $ln, PDO::PARAM_STR);
            $sql_8->bindParam(':dob', $dob, PDO:PARAM_STR);
            $slq_8->bindParam(':email', $email, PDO::PARAM_STR);
            $slq_8->bindParam(':screeningsubperiodid', $screeningsubperiodid, PDO::PARAM_INT);
            $slq_8->bindParam(':now', $now, PDO::PARAM_STR);
            $slq_8->bindParam(':consumerhistoryid', $consumerhistoryid, PDO::PARAM_INT);

..and then my :now and my :consumerhistoryid I simply define at the top with:

$now = date_create('now')->format('Y-m-d H:i:s'); //using this instead of the original {fn NOW()}
$consumerhistoryid=-1;

How does that look?
ASKER CERTIFIED SOLUTION
Avatar of JesterToo
JesterToo
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Just noticed on the bind statement for the NOW value... you have it as a string... if the db column is actually defined as a string, then you WILL need the formatting like you showed, otherwise, you can omit it.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Gentlemen!

I've been negligent in addition to being on vacation, hence the delay in resolving this question.

I'm working in a .NET shop so I was able to verify GETDATE() as being what I need as a sound substitute for {fn NOW()} because I'm interacting with a SQL server. The code has since been reviewed and approved so we're good to go.

Again, my apologies for not getting back with you sooner.