Prepared Statements using INSERT

[ebanbury]

Hi

I'm having problem getting what should be a simple INSERT using a preparared statement to work?

Am I missing something?

Many thanks

 require_once ('myaccess/dbc.php');

// $stmnt1 = $dbc->stmt_init();


if ($stmnt1 = $dbc -> prepare("INSERT INTO DEV_property_trades VALUES (?,?,?,?,?) ")) {

$stmnt1->bind_param("sssss",$trade_name,$trade_number,$trade_email,$trade_list,$trade_details);

$stmnt1->execute(); 
$stmnt1->close();Code blocks are created by indenting at least 4 spaces

[joshmac]

What happens if you change it to this:

$stmnt1 = $dbc->prepare("INSERT INTO DEV_property_trades VALUES (?,?,?,?,?) ");

if($stmnt1) {
    $stmnt1->bind_param("sssss",$trade_name,$trade_number,$trade_email,$trade_list,$trade_details);
    $stmnt1->execute(); 
    $stmnt1->close(); //Code blocks are created by indenting at least 4 spaces
}

[epicrevolt]

The if ($stmnt1 = $dbc -> prepare("INSERT INTO DEV_property_trades VALUES (?,?,?,?,?) ")) is using only 1 = sign which is setting a variable. If you are trying to see if $stmnt1 is equal to the stuff after it, you would use doule equal signs like ==.

joshmac may have the answer you are looking for.

[ebanbury]

Hi All

I got it working. Many thanks for the help.

I have just discovered a very interesting problem with a prepared statement I have:

In a another form I have called 'add_property_record.php' I have a textarea field.
The INSERT query uses this:

Code blocks are created by indenting at least 4 spaces
... and can span multiple lines

$prop_desc = mysqli_real_escape_string($dbc, nl2br($_POST['prop_desc']));

My prepared statement - which works is as follows:

$query = "INSERT INTO DEV_property_details VALUES (?,NOW(),NOW(),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

$stmnt = $dbc->prepare($query);

$stmnt->bind_param('iisssssssssisssssssiiiissssssssssssssss',$prop_id, $ID, $add_owner, $add_block, $add_road, $add_house, $add_unit, $add_area, $add_postcode, $add_zone, $prop_cat, $prop_hot, $prop_sold, $prop_renew, $prop_contact, $prop_type, $prop_saletype, $prop_desc, $prop_tenure, $prop_bedroom, $prop_bathroom, $prop_price, $prop_size, $prop_garage, $prop_park, $prop_pool, $prop_garden, $prop_alarm, $prop_appliance, $screenpath, $image_main_name, $image_kitchen_name, $image_bed_name, $image_bath_name, $image_living_name, $image_dining_name, $image_garden_name, $pdfpath, $pdf_main_name);

$stmnt->execute(); 
$stmnt->close();ode blocks are created by indenting at least 4 spaces
... and can span multiple lines

What I have discovered is if, in the form you enter some lines into the textarea field, pressing 'enter' on the keyboard for a carriage return - the code inserts to the database like this for example:

hello<br />\r\nhello<br />\r\n<br />\r\nhello

However if I revert back to the old way of doing the insert i.e.:

 $query = "INSERT INTO property_details
VALUES (0, NOW(), NOW(), '$ID', '$add_owner', '$add_block', '$add_road', '$add_house', '$add_unit', '$add_area', '$add_postcode', '$add_zone', '$prop_cat', '$prop_hot', 'Active', 'new', '$prop_contact', '$prop_type', '$prop_saletype', '$prop_desc', '$prop_tenure', '$prop_bedroom', '$prop_bathroom', '$prop_price', '$prop_size', '$prop_garage', '$prop_park', '$prop_pool', '$prop_garden', '$prop_alarm', '$prop_appliance', '$screenpath', '$image_main_name', '$image_kitchen_name', '$image_bed_name', '$image_bath_name', '$image_living_name', '$image_dining_name', '$image_garden_name', '$pdfpath', '$pdf_main_name')";

mysqli_query($dbc, $query);

The carriage returns are executed to the database as
hello<br />hello<br /><br />hello

And are displayed back to user as this:

hello

hello

hello

Why is the prepared statement doing this? Anyone know how I can stop this?

Many thanks for the help - very much appreciated.

[ebanbury]

ps. Really sorry - the code looked like it had been blocked properly - and I can't work out how to edit my post to re-block the code..?

[ebanbury]

Hi

I've just solved the problem. Always easy in the end :-)

I was double escaping

So it was :

$prop_desc = mysqli_real_escape_string($dbc, nl2br($_POST['prop_desc']));

And should be:

$prop_desc = nl2br($_POST['prop_desc']);

This works just great.