Jump to content

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


vincej

Recommended Posts

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);
	}
}

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.)

Link to comment
Share on other sites

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 ?

 

Link to comment
Share on other sites

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.)

Link to comment
Share on other sites

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 ...

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.