javascript Edit-In-Place example using MySQL

[jcb9119]

I am seeking a simple Edit-in-place (as it is sometimes called) textarea example using MySQL. Something that also accomodates a Rich Text editor like TinyMCE for user update. Edit-in-Place enables a user with the ability to -- without leaving a page – edit/update a specific portion of that page. And it usually interacts with a database.I have been playing with a javascript/jQuery plugin called jeditable. But the out of the box jeditable example is written using an SQLite database including SQLite classes which confuse me. I am told jeditable works with PHP/MySQL -- a good thing! Yet I can’t seem to get my arms around what the SQLlite PHP code example is exactly doing so that I might attempt to replicate it in PHP/MySQL. In fact I would feel most comfortable with a basic jeditable PHP/MySQL example to serve as a starting point for my upcoming development. I am open to non-jeditable examples. I have read through jeditable user forums and end up reading others asking each other my same question. Others in forums have said they have accomplished this, yet no solid examples of it ever surface. Any ideas?

[justsomeguy]

It's hard to comment without seeing the database class, but it's really only doing select, insert, update, and delete operations. It should be easy enough to convert those to use a different DBMS. There might be additional utility methods to do things like sanitizing data or returning records in a certain format.

[jcb9119]

My .HTML was cloned after:http://www.appelsiini.net/projects/jeditable/default.html [View/Source for details] I am only focusing on textarea updates for now. Therefore, I modified the reference to editable_textarea in my version of the code. I deliberately changed the value for indicator to 'Saving...Test...Saving' which is how I verify the following code segment is being executed. I also pointed this segment at save.php on my localhost. See below:$(".editable_textarea").editable("http://localhost:8888/edit_in_place/php/save.php", { indicator : 'Saving...Test...Saving', type : 'textarea', submitdata: { _method: "put" }, select : true, submit : 'OK', cancel : 'cancel', cssclass : "editable"});According to: https://github.com/tuupola/jquery_jeditable/tree/master/phpsave.php is to be populated with the following:<?phprequire_once 'config.php';$query = sprintf("INSERT INTO config (token, value) VALUES ('%s', '%s')", $_POST['id'], stripslashes($_POST['value'])); $dbh->exec($query);/* sleep for a while so we can see the indicator in demo */usleep(2000);$renderer = $_GET['renderer'] ? $_GET['renderer'] : $_POST['renderer'];if ('textile' == $renderer) { require_once './Textile.php'; $t = new Textile(); /* What is echoed back will be shown in webpage after editing.*/ print $t->TextileThis(stripslashes($_POST['value'])); } else { /* What is echoed back will be shown in webpage after editing.*/ print $_POST['value']; }config.php is populated with the following code which seems to grant the application access to SQLite then create the config table. I believe under MySQL I will first need to create/define a database, then create a table under that database, then use appropriate MySQL access methods. I will have to research the try/catch block in the PHP language:<?phperror_reporting(E_ALL ^ E_NOTICE);try { $dbh = new PDO('sqlite:/tmp/editable.sqlite');} catch(PDOException $e) { print $e->getMessage();}/* Create table for storing example data. */$dbh->exec('CREATE TABLE config (id INTEGER primary key, token VARCHAR(255), value TEXT, date DATETIME)');?>The Textile.php file also referenced in save.php is downloadable at same URL, yet I can’t determine exactly what it does. And maybe I don't need to know. But I probably need to know whether SQLite database code/classes exist within Textile.php.Do you think there is a reasonable hope I can get this to execute correctly for textareas under MySQL? Thank You

[justsomeguy]

Are there any errors from Javascript? You probably shouldn't have to change much with the Javascript, but make sure you're using a debugging tool to help with errors and things. All of the database changes happen in PHP though.First things first: the config.php file has a line that sets the error reporting value. If this PHP script is accessed using ajax, debugging will be easier if you use an error log instead. Unless you change the structure of the scripts a little bit you'll still need to use a Javascript debugger to look at the ajax response for PHP syntax errors, but for runtime errors you'll be able to check the log. To set error logging, replace the error_reporting line with this:

error_reporting(E_ALL);ini_set('display_errors', 0);ini_set('html_errors', 0);ini_set('log_errors', 1);ini_set('error_log', dirname(__FILE__) . DIRECTORY_SEPARATOR . 'error.log');

That will send all runtime errors to a file called error.log in the same directory as the PHP script (and also stop errors from going to the browser). Make sure that PHP has access to write to that file. You can use this script to test, if you run this and see a file called error.log with the message you see in the script, then everything is working. If you run the script and don't see that file then PHP doesn't have write access to that folder.

<?phperror_reporting(E_ALL);ini_set('display_errors', 0);ini_set('html_errors', 0);ini_set('log_errors', 1);ini_set('error_log', dirname(__FILE__) . DIRECTORY_SEPARATOR . 'error.log');error_log('test error message');?>

Make those changes to config.php and ensure that the error log is working to help you debug PHP problems.Your PHP code is using the PDO class, which is a database abstraction class built in to PHP. You can change that to work with MySQL. The manual page for the class constructor shows an example of connecting to MySQL in example 1:http://www.php.net/manual/en/pdo.construct.phpThe code in config.php to create the table isn't really necessary. If you're using MySQL then you probably also have phpMyAdmin, so you can use that to create the table and the database if necessary. That only needs to be done once, not every time you run it. You can remove that from the file once you have the database and table set up.For security, I would recommend you replace this part:

$query = sprintf("INSERT INTO config (token, value)VALUES ('%s', '%s')", $_POST['id'], stripslashes($_POST['value']));

with this:

$magic = ini_get('magic_quotes_gpc');if ($magic){  $id = $dbh->quote(stripslashes($_POST['id']));  $val = $dbh->quote(stripslashes($_POST['value']));}else{  $id = $dbh->quote($_POST['id']);  $val = $dbh->quote($_POST['value']);}$query = sprintf("INSERT INTO config (token, value) VALUES (%s, %s)", $id, $val);

That detects if magic quotes is enabled, and if so it strips the slashes that get added automatically. It also quotes and escapes the values before using them in the query to protect against SQL attacks and errors.The textile thing is a shorthand HTML format, and there's probably options in the HTML or Javascript side whether or not to use that. Unless you want to change the front end to not use that it's probably better to just leave that there and make sure the Textile.php file is available.

[jcb9119]

Your advice is really helping me progress! I had a couple more problems, but the error log helped me isolate problems on a $dbh->quote method call. It turns out, my test account was actually not set up to log in. Thank You. The next question or hurdle is: When I update a textarea in place, it successfully updates my screen and I verified the new text resides in the new MySQL table. The problem is, if I hit F5 and reload the page, the original text reappears. On the other hand, if do same by executing the “Normal textarea” code located at http://www.appelsiini.net/projects/jeditable/default.html then press F5, it reloads the page and correctly redisplays with my updates. How should I proceed?

[jcb9119]

Your advice is really helping me progress! I had a couple more problems, but the error log helped me isolate problems on a $dbh->quote method call. It turns out, my test account was actually not set up to log in. Thank You. The next question or hurdle is: When I update a textarea in place, it successfully updates my screen and I verified the new text resides in the new MySQL table. The problem is, if I hit F5 and reload the page, the original text reappears. On the other hand, if do same by executing the “Normal textarea” code located at http://www.appelsiini.net/projects/jeditable/default.html then press F5, it reloads the page and correctly redisplays with my updates. How should I proceed?

I am pasting my current error log information below:[16-Mar-2011 23:48:30] write to error log ready to test for magic quotes[16-Mar-2011 23:48:30] write to error log no magic quotes[16-Mar-2011 23:48:30] write to error log before query exec INSERT[16-Mar-2011 23:48:30] write to error log after query exec INSERT[16-Mar-2011 23:48:30] PHP Notice: Undefined index: renderer in C:\wamp\www\edit_in_place\php\save.php on line 34[16-Mar-2011 23:48:30] PHP Notice: Undefined index: renderer in C:\wamp\www\edit_in_place\php\save.php on line 34[16-Mar-2011 23:48:30] write to error log after doing renderer GET/POST

[justsomeguy]

When I update a textarea in place, it successfully updates my screen and I verified the new text resides in the new MySQL table. The problem is, if I hit F5 and reload the page, the original text reappears.

It sounds like the text on the page is static instead of being loaded from the database. If the database changes, and the page is loading data from the database, then you would see the updated data if you refresh.

[jcb9119]

I understand what you are saying. I want to determine whether it is actually executing correctly. Do you have any thoughts regarding the undefined index in my error log?If it is working correctly, as a workaround I could modify the process to“echo” the contents of the database to the screen.With all the javascript going on under the covers, I wondered whether the plugin should be writing updates back to my .HTML file? Is that realistic? Thank You for all your help so far!

[justsomeguy]

You can get rid of the undefined index notice by replacing this line:$renderer = $_GET['renderer'] ? $_GET['renderer'] : $_POST['renderer'];with this:

$renderer = (isset($_GET['renderer']) ? $_GET['renderer'] : (isset($_POST['renderer']) ? $_POST['renderer'] : ''));

That is a couple if statements to check if renderer is set in $_GET or $_POST before trying to access it. The notice was because it was trying to access that but it didn't exist, so this checks first.

If it is working correctly, as a workaround I could modify the process to“echo” the contents of the database to the screen.

That's not a workaround, that's the way it should be. The page displaying this should get the contents of the database and print that on the page instead of having the text hard-coded into the page.

With all the javascript going on under the covers, I wondered whether the plugin should be writing updates back to my .HTML file? Is that realistic?

No, it's much easier to make that HTML file a PHP file, and have PHP get the contents of the database each time the page is loaded and show that as the content. The alternative would be a PHP file that has to open the HTML file, find the content to replace, and replace it. Since everything is already in a database, it makes sense to just get the content from there and show it.

[jcb9119]

I am following your logic and it seems solid to me.Here is my concern:Text updates on jeditables .html on their site reload correctly. Yet my .html on my local web server does not. Their site is located at http://www.appelsiini.net/projects/jeditable/default.html Details below.Details:If I issue “Normal textarea” edits/updates on jeditable’s site, then F5 to reload their .html page, everything updates on the screen and reloads as it should with my latest text updates. Therefore I do not think there is a language (like say PHP) dynamically reading their database. If I carry out the same test on my jeditable .html, it performs the updates correctly on my screen. And it correctly populates the new MySQL table. But after I F5 to reload the page, my .html reloads the “Normal textarea” field with the original text -- not the updates. Do I have a valid concern?

[justsomeguy]

The jeditable site is probably using PHP or another language to get the content from the database. That site is running PHP 5.3.5 on Apache. If you want to save the data at all, in a text file, or database, or whatever else, you need to use a server-side language. Javascript cannot change HTML or text files, and it can't interact with a database.

If I issue “Normal textarea” edits/updates on jeditable’s site, then F5 to reload their .html page, everything updates on the screen and reloads as it should with my latest text updates. Therefore I do not think there is a language (like say PHP) dynamically reading their database.

I don't know how you reach that conclusion. When I see a page getting updated with dynamic data, my first thought is that they're storing the data in a database and using a language like PHP to read it. That's the way I've always done it, regardless of the language and type of database used.

If I carry out the same test on my jeditable .html, it performs the updates correctly on my screen. And it correctly populates the new MySQL table. But after I F5 to reload the page, my .html reloads the “Normal textarea” field with the original text -- not the updates.

Again, this is screaming to me in giant red bold text that the content on the page is not coming from the database. If you see one piece of text in the database, and another on the page, then obviously what you're seeing in the page did not come from the database. If your code is set up so that your changes are made to the database, then wouldn't you also want to read from the database and put the right thing on the page? It doesn't make sense to make changes to the database, but then not bother to get the content from the database to show on the page.