EXTJS 5 and PHP Create with prepared statements

Question

I'm doing a CRUD with PHP in extjs grid. The following code works fine:

include("conect.php");

$contacts= $_POST['contacts'];

$data = json_decode($contacts);
$nome = $data->name;
$email = $data->email;
$phone = $data->phone;
$state = $data->state;

$sqlQuery = "INSERT INTO contact (name, email, phone,state) values ('%s', '%s', '%s', '%s')";
$sqlRes = $conn->query($sqlQuery);

echo json_encode(array(
    "success" => mysql_errno() == 0,

    "contatos" => array(
        "id" => mysql_insert_id(),
        "name" => $name,
        "email" => $email,
        "phone" => $phone,
        "state" => $state
    )
));

However, I intend to use prepared statements.

I made the next attempt, without success:

include("conect.php");

$statement = $conn->stmt_init();

$contacts = $_POST['contacts'];

$data = json_decode($contacts);

$name = $data->name;
$email = $data->email;
$phone = $data->phone;
$state = $data->state;


$sqlQuery = "INSERT INTO contact (name, email, phone, state, id) values (?, ?, ?, ?, ?)";

$statement = $conn->prepare($sqlQuery);

$statement->bind_param("ssssi", $name_val, $email_val, $phone_val, $state, $id_val);

$statement->execute();

$statement->bind_result($col1, $col2, $col3, $col4, $col5);

while($statement->fetch()){
    $output[] = array($col1, $col2, $col3, $col4, $col5);
};


$arr = array(
    'success' => mysql_errno() == 0,

    "contact" => array(
        "id" => mysql_insert_id(),
        "name" => $name,
        "email" => $email,
        "phone" => $phone,
        "state" => $state
    )
);


$statement->close();

echo json_encode($output, $arr);

$conn->close();

What am I doing wrong?

The extjs store:

Ext.define('APP.store.StoreAPP', {
extend: 'Ext.data.Store',

model: 'APP.model.ModelAPP',

pageSize: 25,
autoLoad:true,
autoLoad: {start: 0, limit: 25},
autoSync: false, 

proxy: {
    type: 'ajax',
    api: {
        create: 'php/createContact.php',
        read: 'php/Contact.php',
        update: 'php/updateContact.php',
        destroy: 'php/deleteContact.php',
    },
    reader: {
        type: 'json',
//      root: 'contacts', //Extjs 4
        rootProperty: 'contacts', //Extjs 5
        successProperty: 'success'
    },
    writer: {
        type: 'json',
        writeAllFields: true,
        encode: true,
  //    root: 'contacts', //Extjs 4
        rootProperty: 'contacts' //Extjs 5
    }
}

});

Thanks in advance.

EDIT

Thanks for replying.

I've read everything I could find (internet, books) about mysqli and prepared statements. I could not find an answer so far.

I think the problem is in json_decode and json_encode.

The following code identical to your logic works well:

include("conn.php");

$sqlQuery = "SELECT phone FROM contact WHERE name = ? AND email = ? ";

$statement = $conn->prepare($sqlQuery);

$name = "John";
$email = "email@email.com";

$statement->bind_param("s",$name, $email);

$statement->execute();

$statement->bind_result($col1, $col2);


while($statement->fetch()){
    $output[] = array($col1, $col2);
};

echo json_encode($output);

$statement->close();
$conexao->close();

I think the problem is in json_decode and json_encode.

'contacts' like you say it's a array and its extjs store rootProperty config (= name of database). The data it's provide in textfields form (name, email, phone, state).

Any idea what could be wrong?

EDIT

Next code works, but not quite what I want. It seems to be a mixture of mysql in json decode and encode with mysqli prepared statements.

mysqli_errno() does not work in json_encode; just mysql_errno().

If you have other ideas, I will be grateful.

Thanks.

include("conect.php");

$contacts= $_POST['contacts'];

$data = json_decode($contacts);
$name = $data->name;
$email = $data->email;
$phone = $data->phone;
$state = $data->state;

$sqlQuery = "INSERT INTO contact (name, email, phone, state) values (?, ?, ?, ?)";
$statement= $conn->prepare($sqlQuery);
$statement -> bind_param ("ssss", $name_val, $email_val, $phone_val, $state_val);
$statement->execute();

echo json_encode(array(
    "success" => mysql_errno() == 0,

    "contatos" => array(
        "name" => $name,
        "email" => $email,
        "phone" => $phone,
        "state" => $state
    )
));

score 3 · Accepted Answer · edited May 23 '17 at 10:33

From what I can see, There are a number of issues that need addressing with the above code examples.

The top block of code that you have entered would not enter correct information into the database, Your not assigning the values, just '%s'. I just ran the exact same command and got this as the row:

1, %s, %s, %s, %s

Referring to this line: $contacts = $_POST['contacts']; and the plural naming, I would presume that $contacts will contain more than one value, so the variable will be an array of values, so statements such as $name = $data->name; will not work, You would need to work with the array, maybe using a loop. Without knowing the data being provided its difficult to say.

bind_result is used to display results, but an insert statement wont return any results. If you want to see how many results are being returned use $statement->affected_rows or check the boolean result of $statement->execute for success/fail.

The code below works for me:

<?php

$mysqli = new mysqli("localhost", "test_user", "test_pass", "test_db");

if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

$statement = $mysqli->stmt_init();

$data = new stdClass();
$data->name = "Bob";
$data->email = "bob@example.com";
$data->phone = "555-5555";
$data->state = "Some State";

// I use auto increment for the ID field.
$sqlQuery = "INSERT INTO contact (`name`, `email`, `phone`, `state`) values (?, ?, ?, ?)";

$statement = $mysqli->prepare($sqlQuery);

$statement->bind_param("ssss", $data->name, $data->email, $data->phone, $data->state);

$statement->execute();

$arr = array(
    'success' => mysqli_errno($mysqli) == 0,

    "contact" => array(
        "id" => mysqli_insert_id($mysqli),
        "name" => $data->name,
        "email" => $data->email,
        "phone" => $data->phone,
        "state" => $data->state
    )
);

$statement->close();

echo json_encode($arr);

$mysqli->close();

I would suggest having a read on the Mysqli Docs.

EDIT: To insert multiple entries into the database from an array you would need to use a loop such as this code.

$contacts = json_decode($_POST['contacts']);

foreach ($contacts as $contact) {
    // I use auto increment for the ID field.
    $sqlQuery = "INSERT INTO contact (`name`, `email`, `phone`, `state`) values (?, ?, ?, ?)";
    $statement = $mysqli->prepare($sqlQuery);
    $statement->bind_param("ssss", $contact->name, $contact->email, $contact->phone, $contact->state);
    $statement->execute();
}

This should be expanded to include additional error checking / reported but it should give you working code that answers your question, giving you a good starting point. Hope it helps.

EDIT 2: This other StackOverflow post shows methods of inserting multiple rows using prepared statements

Thanks for replying.I edited my post with some ideas. Read changes, please. Thanks. — jose, Jan 07 '15 at 22:59
I've updated the answer to show how you might approach inserting multiple rows from an array — Scriptable, Jan 07 '15 at 23:31
Thanks Scriptable. I've updated my post too. Thanks for help. — jose, Jan 08 '15 at 00:48
Your welcome, I'm going to double check that code I posted earlier now and update if needed. If this has answered your question could you mark it as the answer so that people know its finished. If not let me know and I'll update my answer — Scriptable, Jan 08 '15 at 00:49

EXTJS 5 and PHP Create with prepared statements

1 Answers1