PHP functions inside query

Question

I use the following query through php:

<?php 
    $this->db->query("INSERT INTO order` SET custom_field = '" 
     . $this->db->escape(isset($data['custom_field']) ? json_encode($data['custom_field']) : '') 
     . "',  payment_company_id = '" . $firstvalue[1] 
     . "',  payment_tax_id = '" . $secondvaluein . ");
?>

The insertion in custom_field column is done normally and i need to use its value splitted to put it in payment_company_id and payment_tax_id columns.

Data saved in custom_field are like this:

{"1":"value of first custom field","2":"value of second custom field"}

I used the following code before my script:

<?php
    $myfinalfield = $this->db->escape(isset($data['custom_field']) ? json_encode($data['custom_field']) : '');
    $array = explode('","', $myfinalfield);
    $firstvalue = explode(':"', $array[0]);
    $secondvalue = explode(':"', $array[1]);
    $secondvaluein =  str_replace('"}','', $secondvalue[1]);
?>

in order to use $firstvalue[1] and $secondvaluein to the insert as you see to the first query above, but my columns are filled with nothing. Any ideas on this?

P.S. Also if i type greek characters then even in custom_field column even if its collation is utf8_general_ci i getn wrong encoding.

Thank you in advance

What is `$myvar` when you try to `explode()` into `$array` ? It shouldn't be `$myfinalfield` actually ? — DontVoteMeDown, Apr 19 '17 at 11:05
Perhaps you found my error! Let me check! ANy ideas about encoding i ask at end ? — Stavros B, Apr 19 '17 at 11:06
If you'd print the query instead of querying? What would you get? — yunzen, Apr 19 '17 at 11:08
Not sure, you could check out [this post](http://stackoverflow.com/q/11806319/1267304) and [this post](http://stackoverflow.com/q/32782413/1267304). — DontVoteMeDown, Apr 19 '17 at 11:09
I have read those posts but i don't do any connection its an inner file and also other variables are being saved just fine in the table. — Stavros B, Apr 19 '17 at 11:15
`json_encode` requires only a utf-8 data. Output the `json_last_error_msg()`. — Deadooshka, Apr 19 '17 at 11:26

score 1 · Accepted Answer · answered Apr 19 '17 at 11:27

I suspect you are not checking for an error after running your query.

When I try to make sense of the query string you are generating, I get this:

INSERT INTO order` SET custom_field = 'something',  /* wrong! */
            payment_company_id = 'something',  payment_tax_id = 'something);

That isn't a valid query. The following would be more valid. (Notice the extra backtick and the extra close-quote at the end of the query.)

INSERT INTO `order` SET custom_field = 'something',  
            payment_company_id = 'something',  payment_tax_id = 'something');

Your best bet for doing this kind of work is to use php to create strings containing your queries, then use the strings in calls to query(). That way when things don't work you can dump the query strings and inspect them.

Something like this, adapting your code.

$query =  "INSERT INTO `order` SET custom_field = '" 
 . $this->db->escape(isset($data['custom_field']) ? json_encode($data['custom_field']) : '') 
 . "',  payment_company_id = '" . $firstvalue[1] 
 . "',  payment_tax_id = '" . $secondvaluein . "'";
$this->db->query($query) || die ("Query failed: " . $query);

(In a production web app, die() is considered harmful because it gives rubbish to the end user. But it's handy for debugging.)

PHP functions inside query

1 Answers1