Incorrect value being placed in mysql

PHP
1

I am confused as to why my insert statement in entering an incorrect value into mysql.

var_dump($_POST) shows the correct value being passed, which is “1” but the insert statement is inserting it as “0”. The mysql field is ‘varchar (4)’ and can accept a null value. I have tried changing null to no, but still it inerts “0”.

I have posted the var_dump,echoed the $sql statement and the $sql insert code.to show the point. I would be grateful if someone could point out my error. Many thanks

BTW The field in question is ‘size’.

$size = mysqli_real_escape_string($conn, $_POST['size']);

array(9) { ["sub"]=> string(11) "demobox0017" ["company"]=> string(4) "DEMO" ["dept"]=> string(4) "DEMO" ["address"]=> string(47) "22 London Road Charles House London W12 3RT " ["service"]=> string(8) "standard" ["altdate"]=> string(0) "" ["activity"]=> string(14) "New Box Intake" ["destroydate"]=> string(10) "16/12/2027" ["size"]=> string(1) "1" } 

echoed $sql
INSERT INTO `act` (service, activity, size, department, company, address, user, destroydate, date, requestdate, item, new) VALUES ('Standard', 'New Box Intake', '0', 'DEMO', 'DEMO', '22 London Road Charles House London W12 3RT ', 'Admin', '2027-12-16', NOW(), NOW(), 'DEMOBOX0017', 1)

$sql = "INSERT INTO `act` (service, activity, size, department, company, address, user, destroydate, date, requestdate, item, new) VALUES ('$service', '$activity', '$size', '$dept', '$company', '$address', '$authorised', '$newdate', NOW(), NOW(), '".strtoupper($items)."', 1)";
mysqli_query($conn, $sql) or die ('Error inserting act box:' . mysqli_error($conn));

html
<select class="chosen-select" data-placeholder="Select a Box Size" id="size" name="size">
      <option value=""></option>
       <option value="0">Standard</option>
       <option value="1">Printout</option>
       <option value="2">Other</option>
       <option value="3">Transfer</option>
</select>
2

Do you use any error reporting?

<?php
declare(strict_types=1); // fail fast - PHP7 Oly
error_reporting(-1); // maximum errors
ini_set('display_errors', 'true'); // show on screen
mysqli_report(MYSQLI_REPORT_ERROR 
| 
MYSQLI_REPORT_STRICT); // maximum MySql errors

Did you try echoing the query in PhpMyAdmin?

There is a very useful option in PhpMyAdmin which formats the query and can be copied and pasted into your post:

$sql = "
INSERT INTO 
	`act` 
	(
		service, 
		activity, 
		size, 
		department, 
		company, 
		address, 
		user, 
		destroydate, 
		date, 
		requestdate, 
		item, 
		new
	) 
	VALUES 
	(
		'Standard', 
		'New Box Intake', 
		'0', 
		'DEMO', 
		'DEMO', 
		'22 London Road Charles House London W12 3RT ', 
		'Admin', 
		'2027-12-16', 
		NOW(), 
		NOW(), 
		'DEMOBOX0017', 
		1
	)
";
4

Could we see the code in the correct sequence? In isolation each line that you’ve shown appears correct, but clearly by the time $size arrives in the query string, the value has been changed from one in your $_POST array, to zero in the separate $size variable. What is its value when it comes back from mysqli_real_escape_string()?

You might want to look at prepared statements instead of concatenating variables directly into the query strings, for a number of reasons.

1 Like
5

Hi John
Thanks for reply. There are no errors reported using error reporting. Where is that option in ,PhpMyAdmin? many thanks

6

I shall look into prepared statements for future use. Many thanks for reply.

1 Like
7

https://secure.php.net/manual/fa/function.mysqli-report.php

8

Many thanks

1 Like
9

It doesn’t look to me as if it’s a mysqli problem though - the echoed query already has a zero value in it, where you might expect a one if the selection is correct. So it seems that mysqli is just doing as it’s told.

1 Like
10

I’m with @droopsnoot on this. I feel like $size is being replaced by something else. Could we see the entire code as droop has requested?

11

Sorry guys. It was being replaced by another rogue $size which should have been removed. Many thanks for all your help.

2 Likes
12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.