Why will this variable: $prodname[$i] not INSERT

[vincej]

Hi - I'm quite confused. I have a variety of variables derived from a post array which all INSERT  except for one: $prodname[$i].  Yet it will echo out, and Var_dump shows that it is a string.  I have checked the table and the field is a 50 character varchar. If I hard code into my app 'foobar' ( a string ! ) instead of the variable it will insert.  Theother variables that I am using will also insert - ok they are floats and ints.

 

If I remove the $prodname[$i] from my query, the query executes fine with the remaining values, but it fails with it in.

 

Can anyone tell me what I need to do to get this one variable to insert ?  I'm using MySQL 5.5

 

MANY THANKS !

 

Here is the code:

 

function confirmed_Order(){
    $prodname = $_POST['prodname'];
$prodid =$_POST['prodid'];
$quantity = $_POST['quantity'];
$pricelb = $_POST['pricelb']; 

$numloops = count($_POST['prodid']);

for ($i = 0; $i < $numloops ; $i++) {

echo "ProdName".$prodname[$i] . "ProdID".$prodid[$i]."Qunatity".$quantity[$i]. "Pricelb".$pricelb[$i];  // ECHOS FINE 
var_dump($prodname[$i]); // CONFIRMS IT IS A STRING 

$sql ="
INSERT INTO `confirmedorder` (Prodid, Prodname, pricelb,quantity)
VALUES ($prodid[$i], $prodname[$i],$pricelb[$i], $quantity[$i])
";
$this->db->query($sql);
	}
}

[PFMaBiSmAd]

Ummm. You didn't show us what the string is that doesn't work?

 

Anyway, you need to use your database class's escape function (mysql_real_escape string or mysqli_real_escape_string) on ALL string data that you put into a query that could contain any special sql characters that could break the sql syntax of the query statement.

[vincej]

Sorry - the string is just a product name like, 'chicken breasts'  . I didn't think it would matter ..

[vincej]

Bit of a newb question perhaps , surely applying the database class's escape function might only help those strings where there is a odd character. I have tested the variable with a string as simple as 'steak' - and it still fails  ... so forgive me but I don't still don't get it.

[PFMaBiSmAd]

You need single-quotes inside the query statement around string data, otherwise mysql will treat the string as an identifier. You also need to escape string data (doing so prevents special sql characters from breaking the sql syntax and it will prevent sql injection by hackers.)

[vincej]

Brilliant ! putting in the quotes fixed it !!

 

Now I have a new oddity:

 

If the variable $pricelb[$i] is empty ( ie it is a product that has no price lb ) then the query fails even though the default is NULL.  Ok, so I put single quotes around that too and now that also works,

 

However - if a product has no Pricelb the field is not inserted with NULL - it comes  0.00

 

Whta's going on with that and is there a way I can get it to insert NULL ?

 

[PFMaBiSmAd]

For an empty numerical value, the sql syntax is broken (i.e. two consecutive ,, commas) and produces a sql syntax error.

 

With the single-quotes around a numerical value, it is treated as a string containing a numerical value and an empty string is converted to a zero.

 

There are two ways to get the default/null sql value to be used -

 

1) Don't put the field name into the field list (this will require you to dynamically build the field list in the query statement) and don't put the value into the VALUE list.

 

2) Put the DEFAULT or the NULL keyword in as the value (with no quotes around it inside the query statement.)

[vincej]

Many thanks for your help !

 

Option 1 looks complicated and time consuming , Option 2 I guess means that Null / Default would appear on the published price list as the given value for pricelb .. so perhaps 0.00 is just the best out come ...

[PFMaBiSmAd]

Null / Default would appear on the published price list

 

You would display anything you want, such as an empty string, when your code detects a null value for any field value.