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
– DontVoteMeDown, Commented Apr 19, 2017 at 11:05
Perhaps you found my error! Let me check! ANy ideas about encoding i ask at end ? — Stavros B
– Stavros B, Commented Apr 19, 2017 at 11:06
If you'd print the query instead of querying? What would you get? — yunzen
– yunzen, Commented Apr 19, 2017 at 11:08

O. Jones · Accepted Answer · 2017-04-19 11:27:50Z

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

Collectives™ on Stack Overflow

PHP functions inside query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related