Front-End DB Update Help!

10 posts by 2 authors in: Forums > CMS Builder
Last Post: July 11, 2014   (RSS)

By Perchpole - July 8, 2014

Hello, All -

I'm sure this will sound a bit odd - but stick with it. There's method in my madness....!

I am attempting to update a CMSB record using a front-end form page - called update.php. The code for the UPDATE query is in a separate file called save.php - which looks like this:

<?php
  require_once 'cmsb/lib/init.php';
  mysqlStrictMode(false);
 
  $query = "UPDATE `{$TABLE_PREFIX}news` SET
              title            = '".mysql_escape( $_REQUEST['title'] )."',
              summary          = '".mysql_escape( $_REQUEST['summary'] )."',
              content          = '".mysql_escape( $_REQUEST['content'] )."',
              updatedDate      = NOW(),
              updatedByUserNum = '0'
                      
              WHERE num = '".mysql_escape( $_REQUEST['num'] )."'";
  }
 
                      
    mysql_query($query) or die("MySQL Error Creating Record:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
    
    $recordNum = mysql_insert_id();

    $_REQUEST = array();
    
?>

The variables are passed from update.php to save.php via Ajax.

It works brilliantly. The only issue is how do I pass info or commands back from save.php to update.php?

Let's say that after the query I want update.php to go to a new page or show a success message...?

I'm sure it's not as mad as it sounds!

:0)

Perch

By claire - July 8, 2014

Hey Perch, what's your AJAX call look like?

--------------------

Claire Ryan
interactivetools.com

Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

By claire - July 9, 2014

Okay then - first thing you need to do is echo something out of save.php. Add something like this as the last line from the script:

echo 'success';

Echo is what you must use if you want to return anything to an ajax call.

Then you change your Javascript like so:

// <![CDATA[
function ClickToSave () {
    var data1 = form.title.getData();
    var data2 = form.summary.getData();
    var data3 = form.content.getData();
    var data4 = "<?php echo $selectedPageNum; ?>";
    $.post('save.php', {
        title: data1,
        summary: data2,
        content: data3,
        num: data4
      })
      .done( function(data) {
        if(data === 'success') {
          alert('Saved successfully!');
        }
      });
    }
// ]]>

The .done chunk is a callback that will be executed when something returns from $.post. The function(data) bit is the important part - anything that comes back, such as your echo statement in this case, is passed into this as 'data'. Then you can put whatever Javascript you want in there to handle the result.

You can do all kinds of interesting stuff with calls like this, but the most basic implementation is this - you send a request using jQuery or AJAX or whatnot to a PHP script, and you echo a string back to that request, then check it and do things with it. So you can echo 'success' if it worked, or echo 'failure' and do something else if it didn't.

If you're using Chrome and you want to test this stuff, then it's very helpful to open the page in question, right-click and hit Inspect Element anywhere, and then click Network in the Inspector tabs. Then click your submit button. You should see the network call happening in the Network list, and from there you can click on it and view all kinds of useful information, particularly the echo response itself.

--------------------

Claire Ryan
interactivetools.com

Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

By Perchpole - July 10, 2014

Hi, Claire -

Thanks fo this. It works really well. Just a couple of things...

1) Instead of a success alert, how could I load another page or refresh the current one?

2) (just so I can learn something new) Instead of simply echoing "success" to trigger the function, could I expand things a little? How would I go about using an IF statement?

if(x){
       echo "success"
     }
else {
       echo "failure"
}

What is "x" and how would I amend the function to work with either response?

:0)

Perch

By claire - July 10, 2014

Sure - if you want to load another page, all you need is the right Javascript line.

This will take you to google.com:

window.location.href = "http://www.google.com";

This will reload the current page:

location.reload();

So the code might look like this:

// <![CDATA[
function ClickToSave () {
    var data1 = form.title.getData();
    var data2 = form.summary.getData();
    var data3 = form.content.getData();
    var data4 = "<?php echo $selectedPageNum; ?>";
    $.post('save.php', {
        title: data1,
        summary: data2,
        content: data3,
        num: data4
      })
      .done( function(data) {
        if(data === 'success') {
          location.reload();
        }
        if(data === 'failure') {
          alert('Record was not saved!');
        }
      });
    }
// ]]>

In the PHP file, the 'x' in the if statement can be just about anything. See this page on W3Schools: http://www.w3schools.com/php/php_if_else.asp

In your case, let's say you amended the function as follows:

$recordNum = mysql_insert_id();
if($recordNum == null) {
  echo 'failure';
}
else {
  echo 'success';
}

Basically you'll have to figure out what constitutes a failure and a success, then test for that when it comes time to echo the response. This code above counts only null recordNums as failures and everything else as a success, but you could get more specific if you wanted to.

--------------------

Claire Ryan
interactivetools.com

Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

By Perchpole - July 10, 2014

Brilliant!

You're a star. Thanks,

:0)

Perch

By Perchpole - July 11, 2014

Claire -

Just one last thing!

Let's assume that instead of editing an existing record I want to create a new one. I've created a button which fires the following Ajax code:

function ClickToCreate () {
    var data1 = "New Title";
    var data3 = "Main Text...";
  $.post('save.php', {
    title: data1,
    content: data3,
  })
  .done( function(data) {
    if(data === 'success') {
    alert('New Page Created!');
    }
  });

All of this works as expected. However, instead of a simple "success" message I want to load the new page as soon as I've created it. I should be able to do this with:

window.location.href = 'http://www.mydomain.co.uk/edit.php?page=n';

...but how do I get n?!

When the query INSERTS a new record into the DB it will generate a new record $num. How do I get that $num back into the function(data) and use it to form a complete URL?

:0s

Perch

By claire - July 11, 2014

Alright, this is a little bit tricky, but basically what you need now is to return an array that contains a status message and other info.

So in your save.php, the echo will look like this if it's saved correctly:

$recordNum = mysql_insert_id();
$status = array('msg' => 'success', 'id' => $recordNum);
echo json_encode($status);

The json_encode function is the next level up from just echoing a string of characters, and it's essential for doing more complex AJAX functionality. What it does is take a PHP array and encode it in the JSON format - Javascript Object Notation.

You don't really need to know how it works :P All you need to know is how to work with it in the AJAX call.

So here's your code:

function ClickToCreate () {
    var data1 = "New Title";
    var data3 = "Main Text...";
  $.post('save.php', {
    title: data1,
    content: data3,
  })
  .done( function(data) {
    var status = JSON.parse(data);
    if(status.msg === 'success') {
     window.location.href = 'http://www.mydomain.co.uk/edit.php?page='+status.id;
    }
  });

JSON.parse is the accompanying function that takes the data encoded by json_encode and turns it into a Javascript object. (It's supported in most browsers, see here: http://caniuse.com/json, and you can use jQuery's $.parsejson function if you're stuck.)

Once you have a Javascript object, it's really easy to call any of its variables, as you can see.

The only thing you need to watch out for is that the AJAX call always expects a parseable array now, even if there's been a failure. So your failure code will have to look like this:

echo json_encode(array('msg' => 'failure'));

You can send any kind of status message in the same way, and test them in the .done function however you like.

--------------------

Claire Ryan
interactivetools.com

Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

By Perchpole - July 11, 2014

Wonderful!

:0)