Insert entire JSON to database after decode using PHP PDO

Question

I want insert this json from jquery.ajax (content of index VALOR don't always have data):

[
{
    "input": "calleFiscal",
    "valor": ""
},
{
    "input": "numFiscal",
    "valor": "numero fiscal"
},
{
    "input": "colFiscal",
    "valor": ""
},
{
    "input": "delefacionFiscal",
    "valor": ""
},
{
    "input": "estadoFiscal",
    "valor": "11"
},
{
    "input": "calleComercial",
    "valor": "calle comercial"
},
{
    "input": "numComercial",
    "valor": ""
},
{
    "input": "colComercial",
    "valor": ""
},
{
    "input": "delefacionComercial",
    "valor": ""
},
{
    "input": "estadoComercial",
    "valor": "3"
},
{
    "input": "calleEntrega",
    "valor": ""
},
{
    "input": "numEntrega",
    "valor": ""
},
{
    "input": "colEntrega",
    "valor": "colonia entrega"
},
{
    "input": "delefacionEntrega",
    "valor": ""
},
{
    "input": "estadoEntrega",
    "valor": "11"
}
]

Is created by mapping a div using jquery, and I try to insert in a database with this:

$addresses = json_decode($this->dataActionClient['addresses'],true);

$sqlAd = "INSERT INTO t_direcciones_clientes (Id_Cliente,Calle,Numero,Colonia,Municipio,Estado,Tipo) VALUES (:idc,:calle,:num,:col,:deleg,:edo,:tipo)";
$resultAd = $this->dbConnect->prepare($sqlAd) or die ($sqlAd);

$fields = array('calle','num','col','deleg','edo');
$types = array('fiscal','comercial','entrega');

$resultAd->bindParam(':idc',$id_cliente,PDO::PARAM_INT);
$counType = 0;

foreach ($addresses as $key => $value) {
    $key++;
    $resultAd->bindParam(':'.$fields[$key], $value['valor']);
    if ($key == 4 || $key == 9) {
        $resultAd->bindParam(':tipo', $types[$counType]);
        $counType++;
        $resultAd->execute();
    }
}

Explanation of that code:

I have 3 areas (fiscal, comercial, entrega) and each one has 5 inputs (Calle, Numero, Colonia, Municipio, Estado, Tipo) then I need insert 3 rows in a table and these 3 rows have the same Id_Cliente but have differente Tipo and different content of your 5 inputs. But doesn't work, and display this error:

Tried to bind parameter number 0. SQL Server supports a maximum of 2100 parameters.

Maybe my method is wrong and if exists any way to do this, I was the grateful.

Edited

I solve my problem changing some values according system functionanily but thanks to everyone.

You are using question mark placeholders - ?, but creating/using named placeholders ':'.$fields[$key]?? Also, bindParam() use a 1-index, where your array is a 0-index (which is what I believe your error is stating Tried to bind parameter number 0), so you might need to increase your keys by 1. — Sean
– Sean, Commented Jul 21, 2013 at 0:00
Hmm my first comment was wrong, you're not binding 2 parameters, but 5, the statement is getting executed when $key hits 4, so you are 2 short... and what @Sean said ;) — dbf
– dbf, Commented Jul 21, 2013 at 0:01
You are stilling using question mark placeholders - ? - in your prepare(), but creating/using named placeholders - ':'.$fields[$key] - in your bindParam(). for example - ? != :idc, ? != :calle, etc. — Sean
– Sean, Commented Jul 21, 2013 at 0:17
Sure, I fixed that.. but now appear this error: COUNT field incorrect or syntax error. — SoldierCorp
– SoldierCorp, Commented Jul 21, 2013 at 0:20

Sean · Accepted Answer · 2013-07-21 05:17:44Z

1

I think you need to add 1 more counter, that you can reset to 0 after 5 loops. Try this -

$counField = 0; // counter to access field array, will be reset to 0 after 5 loops
$counType = 0;

foreach ($addresses as $key => $value) {
    $resultAd->bindParam(':'.$fields[$counField], $value['valor']);
    if ($key == 4 || $key == 9 || $key == 14) {
        $resultAd->bindParam(':tipo', $types[$counType]);
        $counType++;
        $counField++; // increase if the last of 5 loops
        $resultAd->execute();
    }
    else {
        $counField++; // increase if not the last of 5 loops 
    }
}

here is a phpFiddle example that shows the outcome - http://phpfiddle.org/main/code/k4b-nja

answered Jul 21, 2013 at 5:17

Sean

12.4k3 gold badges31 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SoldierCorp Over a year ago

Thanks, but I solve my answer of different way and I post my correct answer in the edited post. But thanks for your time.

Collectives™ on Stack Overflow

Insert entire JSON to database after decode using PHP PDO

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related