Posting Edits to MySQL Database

2 posts, 0 answers
  1. Paolo
    Paolo avatar
    3 posts
    Member since:
    Apr 2016

    Posted 30 Apr 2016 Link to this post

    Hi,

     

    I have been struggling with this for days and cannot figure it out - any help would be greatly appreciated!

     

    I am creating a UI Grid with php wrappers where I am trying to read & update data to/from a database stored in MySQL.

     

    The reading part is working just fine - but when I try to post, I keep getting error. The reason is that the "$columnName = mysql_real_escape_string($_POST["fieldname"]);" commands are not returning anything in my database connection file.

    Perhaps it is because I am batch editing so it is not posting 1 single field value but instead an array of them?

    Here is my code:

     

    MAIN PHP FILE:

    <?php
    require_once '../lib/DataSourceResult.php';
    require_once '../lib/Kendo/Autoload.php';

    $transport = new \Kendo\Data\DataSourceTransport();
    $read = new \Kendo\Data\DataSourceTransportRead();
    $read->url('data/connection2.php?type=get')
         ->contentType('application/json')
         ->type('GET'); //corresponds to "verb" in connection2.php

    $update = new \Kendo\Data\DataSourceTransportUpdate();

    // Specify the url of the PHP page which will update the bank table
    $update->url('data/connection2.php?type=update')
      ->contentType('application/json')
      ->type('POST'); //corresponds to "verb" in connection2.php


     
    $transport ->read($read)
               ->update($update)
               ->parameterMap('function(data) {
                  return kendo.stringify(data);
              }');

     

              
    $model = new \Kendo\Data\DataSourceSchemaModel();

    $FirstNameField = new \Kendo\Data\DataSourceSchemaModelField('First');

    $FirstNameField->type('string');

    $LastNameField = new \Kendo\Data\DataSourceSchemaModelField('Last');

    $LastNameField->type('string');
    $idField = new \Kendo\Data\DataSourceSchemaModelField('id');
    $idField->type('number');

    $model->id('id')
          ->addField($FirstNameField)
          ->addField($LastNameField)
          ->addField($idField);

          
    $schema = new \Kendo\Data\DataSourceSchema();
    $schema->data('data')
           ->model($model);

    $dataSource = new \Kendo\Data\DataSource();

    $dataSource->transport($transport)
               ->pageSize(50)
               ->serverPaging(false)
               ->batch(true) //batch editing
               ->schema($schema);

    $grid = new \Kendo\UI\Grid('grid');

    $First = new \Kendo\UI\GridColumn();
    $First->field('First')
                ->title('First Name')
                ->width(150);
    $Last = new \Kendo\UI\GridColumn();
    $Last->field('Last')
                ->title('Last Name')    
                ->width(250);

     

    $grid->addColumn($First, $Last)
         ->addToolbarItem(new \Kendo\UI\GridToolbarItem('save'), new \Kendo\UI\GridToolbarItem('cancel'))
         ->dataSource($dataSource)
         ->pageable($pageable)
         ->editable(true)
         ->navigatable(true)
         ->attr('style', 'height:550px');
    ?>


    <?php
    echo $grid->render();
    ?>

     

     

     

    AND HERE IS THE CONNECTION file (connection2.php)

    ((Note the error is happening with $myID  & $myLastName..the values are not actually being passed in (they are displaying as empty when I echo these variables) so I suspect that is the root cause why the data is not being posted to my database. This means "id" & "Last" are not getting passed into the connection file properly for all the records I am attempting to batch edit.

     

    <?php

    $link = mysql_connect("localhost", "myusername", "mypassword') or die("Unable To Connect To Database Server");
    mysql_select_db("mydb") or die("Unable To Connect To My DB");

     

    $verb = $_SERVER["REQUEST_METHOD"];

    // handle a GET
    if ($verb == "GET") {
    $arr = array();
    $rs = mysql_query("SELECT * FROM mytable");


    ini_set('memory_limit', '-1');

    while($obj = mysql_fetch_object($rs)) {
    $arr[] = $obj;
    }

    header('Content-Type: application/json');
    echo "{\"data\":" .json_encode($arr). "}";
    }


    // handle a POST
    if ($verb == "POST") {

    $myID = mysql_real_escape_string($_POST["id"]);
    $myLastName = mysql_real_escape_string($_POST["Last"]);

    $rs = mysql_query($link,"UPDATE mytable SET Last= '" .$myLastName ."' WHERE id = " .$myID);

    if ($rs) {
    echo json_encode($rs);
    }
    else {
    header("HTTP/1.1 500 Internal Server Error");
    echo "Update failed for ID: " .$myID;
    }
    }
    ?>

  2. Rosen
    Admin
    Rosen avatar
    3253 posts

    Posted 04 May 2016 Link to this post

    Hello Paolo,

    Looking at the code you have pasted I suspect that the issue you have described is caused by the format in which the data is posted. As the custom DataSource parameterMap function is set to stringify the data send with the request, you will need to deserialize the request at the server prior to accessing it. In the format used in the code snippet, it is not possible to directly access the individual fields as they are not send as such. Please take a look at this online demo. Note the line $request = json_decode(file_get_contents('php://input')); which is handling the deserialization of the request params.

    Regards,
    Rosen
    Telerik
     
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
     
Back to Top