Editable table data with auto-recalculate dynamically in PHP

[kaprikorn07]

Hello,

I would like to make a table column editable that is written in PHP. And that edited value should be subtracted with the data in another column automatically to change the value of it.
Eg: I have 3 columns, Pass/Fail/Blocked. $pass and $fail are taken from DB & Blocked is chnaged by user. I would like to make Blocked editable and fail should be recalculated by subtracting Blocked data dynamically.

Also please let me know how to validate the blocked data input to be only numberic.

I tried embedding javascript in PHP, but doesn't work. Please help.

[peeyush.budhia]

You can use filter_var($input, FILTER_VALIDATE_INT) in PHP to validate the user input.

Replace $input with the value entered by the user.

[Taywin]

You should validate the input both from the client side (JavaScript) and server side (in your PHP script). On the client side (in JavaScript), you simply use isNaN(testVar) by replacing testVar with your variable. It returns true if the testing variable is NOT A NUMBER.

I am still unclear about your "editable table column" meaning. Do you mean it will automatically resize the table column? Does it have to be fixed size for you? The reason is a browser would automatically adjust column size (on the page load) for you if you assign the width property to the table. So you could just assign the table width. If you want the first column to be a fixed width, you simply assign a fixed width value to the column as well.

[kaprikorn07]

Thanks for the replies.

echo "<td name=\"Blocked\" id=\"blk\"contenteditable=\"true\" input type=\"number\" pattern=\"\[0-9\]\{3\}\" oninput=\"checkField(this.value);\" ><font color ='Blue'/font></td> ";

Editable column meaning column whose data can be edited by user. In above code, I am able to make data editable, but not able to validate or recalculate the value of variable $fail.

I would like to make something like this.
$fail = 5, $pass = 50, $Blocked=0. After editing $Blocked=2, I want the table to be recalculate dymanically with "oninput" kind of option to : $new_fail=$fail-$Blocked.

Am I using "oninput" properly?

This is more of a javascript thing. If you want changes to be made dynamically on the server, then you'll need ajax to pass the data onwards.

[jstfsklh211]

php is a server side language you can't make a dynamic site using php alone you need to use a client side language to send the info back the server as well as make the display changes on the client side

google ajax

Not sure at what stage contentEditable has reached with regard to browsers. Here's some code I had a play with:

<table>
    <thead>
        <tr>
            <th>Pass</th><th>Fail</th><th>Blocked</th>
        </tr>    
    </thead>
    <tbody>
        <tr><td>80</td><td>50</td><td contentEditable>0</td></tr>
        <tr><td>80</td><td>34</td><td contentEditable>0</td></tr>
        <tr><td>80</td><td>16</td><td contentEditable>0</td></tr>
        <tr><td>70</td><td>22</td><td contentEditable>0</td></tr>
        <tr><td>50</td><td>40</td><td contentEditable>0</td></tr>
        <tr><td>50</td><td>30</td><td contentEditable>0</td></tr>
    </tbody>
</table>
<script>
var editable = document.querySelectorAll('td[contentEditable]');

for (var i=0, len = editable.length; i<len; i++){
    editable[i].setAttribute('data-orig',editable[i].innerHTML);
    editable[i].onblur = function(){
        if (this.innerHTML == this.getAttribute('data-orig')) {
        }
        else {
            //my bit
            var fail = parseInt(this.previousElementSibling.innerHTML);
            var blocked = parseInt(this.innerHTML);
            this.previousElementSibling.innerHTML = fail - blocked;
            //end my bit    
            this.setAttribute('data-orig',this.innerHTML);
        }
    };
}
</script>

Reference for onblur functionality: http://jsfiddle.net/davidThomas/ajzYF/

I am not a js guy, so this is pretty poor, but may give you some ideas. There is not integer validation, it just forces all input to be an integer via the parseInt - maybe not quite what you want.

[kaprikorn07]

Thank you all for the replies.
@diafol, I am using PHP basically. So I want to embed AJAX/JS in PHP rather than in HTML.

Thanks

That doesn't make much sense. You need to have a js front end to have the "immediate feedback" of subtraction. If you wish to male changes on the server before feeding back to the client, fair one, yes you need to send via ajax, do the updating to the DB and then once changes are confirmed, make changes via js. Not quite sure what you mean about embedding js in php though.

Forgive me if you're already aware of the following, but your comment suggests that you may not be...

• PHP cannot interact with the html page once sent as it is a server side language.
• JS cannot interact with anything on the server, such as files or the DB, although it can respond to user interaction.
• In order to get the server involved with user interaction without effecting a page refresh/reload, Ajax needs to be used.

So the page is loaded initially, possibly with the help of php for displaying the data in the table.

You need a js script to respond to the contentEditable tag changes. This needs to grab the new value (along with the 'id' of the record - this can be appended to the <tr> tag by php when creating the page) and send it to the server via Ajax.
The php code picks up the value, validates it, does the calculation, and if the results are valid (e.g. fail is not a negative number), then updates the
record with the specific id value with the new data. If the query runs successfully, you can send a success msg back to js or even send the new data back. The choice is yours.

[peeyush.budhia]

Yes, you can achive this by using ajax in your application.

[kaprikorn07]

@diafol,

I am a Web development noob. I am using PHP for my pages, instead of HTML. As you said, I need JQUERY/AJAX to save the data to server and re-calculate.

Could you pls give me an example on how to do it.

@peeyush.budhia, Thanks for the reply mate. It would be helpful if you could let me know how to do it.

Thanks.

Are you saying that you're using php to spit out html instead of using html as it was meant to be used? I can't imagine why you'd want to do that, but I shouldn't make any difference. The client (browser) sees static html - no php at all.

I have given you one example already - a freebie if you would, I am not about to gve you another one. Please read the forum sticky (first thread) and the site guidelines with regard to how much work you are expected to show yourself.

Here's a sample of what you should have read:

• Do not ask for code. We are not a coding service. We will help you fix your code. If anyone posts a complete working solution for you, they are enabling cheaters. If you use that code you are a cheater.
• Do not bore us with how new you are. We can tell by your code. Do not apologize. We were all new once, and unless you are completely brain dead you will get better.
• Do not ask us to "take it easy on you."
• Do not say "I don't know what's going on". That's obvious since you posted for help. Use your time wisely and explain as best you can.
• Do not post your requirements and nothing else. We view that as a lazy do-nothing student that wants us to do their work for them. That's cheating and we will be hard on you.

[kaprikorn07]

@diafol, Hi.. I did not mean that. Apologies, if my words interpreted that to you. I meant the exact mechanism on how to code it.

I am a member of various coding forums, I am aware of the rules. Anyways appreciate your enthusiasm in making others to learn!

Thanks..

No problem, but there are hundreds of ajax scripts out there - either vanilla js or library-based like jQuery.

[kaprikorn07]

Hi guys,

I am trying to validate table input as number only with length=3 like this:

echo "<td id=\"blk\"contentEditable=\"true\" input type=\"text\" pattern=\"\[0-9\]\{3\}\" oninput=\"checkField(this.value);\" >0<font color ='White'/font></td> ";

But neither validation nor length working! what is wrong in this?

A ready made page for testing here: http://www.w3schools.com/tags/tryit.asp?filename=tryhtml5_input_pattern

pattern is not supported by Safari nor IE <10 - so its use may be limited.

WHat's the purpose of this...

<font color ='White'/font>

Doesn't make any sense. Avoid <font></font> tags anyway, use CSS.

[kaprikorn07]

I mean to do this in PHP, so I am putting all the html code in php echo!

And instead of submit button, I am trying to do onchange() for table data that I wanted to edit.
But all this doesn't seem to work! Is this not the way to do in PHP?

Also I have made the editable table data's default value as 0. Once it is clicked to edit and 0 is deleted and no entry is made the table row becomes empty. I would like to make it 0 again. Basically wanna validate if user gives input other than 0 or empty.

I mean to do this in PHP, so I am putting all the html code in php echo!

Why? What advantage does that give you?

And instead of submit button, I am trying to do onchange() for table data that I wanted to edit.

I believe that I already gave you an example of how to do this with the contentEditable:

editable[i].onblur = function()...

You can't use onChange with a td element.

But all this doesn't seem to work! Is this not the way to do in PHP?

I don't see what you're trying to do in PHP. Javascript and PHP have totally different uses.

Also I have made the editable table data's default value as 0. Once it is clicked to edit and 0 is deleted and no entry is made the table row becomes empty. I would like to make it 0 again. Basically wanna validate if user gives input other than 0 or empty.

The function I provided would allow you to do whatever you want with it.

[kaprikorn07]

I want the user to edit Blocked column by double clicking and Fail column getting recalculated to new_fail= $fail-$blocked after $Blocked is edited.

All theese pages are dynamic, hence written in PHP. Since user input needs to validated, I would like to use JS.

Are you clear now, @diafol?

I understand that the DATA is derived from PHP/MySQL, but that doesn't mean that you have to produce the whole page in php.

This is an "example" of your "variable data" block:

<tr><td>80</td><td>50</td><td contentEditable>0</td></tr>
<tr><td>80</td><td>34</td><td contentEditable>0</td></tr>
<tr><td>80</td><td>16</td><td contentEditable>0</td></tr>
<tr><td>70</td><td>22</td><td contentEditable>0</td></tr>
<tr><td>50</td><td>40</td><td contentEditable>0</td></tr>
<tr><td>50</td><td>30</td><td contentEditable>0</td></tr>

This could be produced in a number of ways:

$content = '';
foreach($rows as $r)
{
    $pass = $r['passed'];
    $blocked = $r['blocked'];
    $failed = $pass - $blocked;
    $content .= "<tr><td>$pass</td><td>$fail</td><td contentEditable>$blocked</td></tr>";
}
?>
...
<tbody>
    <?php echo $content;?>
</tbody>
...

That's pretty much it for php and the table. The code I supplied earlier will work with this. If you want to make changes to the server (update DB etc) then you run ajax in the editable[i].onblur code block.

[kaprikorn07]

Thanks.

I have a doubt on how to display all the pages instead of specific page based on the page id.
For example : Test.php?keyOne=1000&keyArea=200&keyVersionId=007

This opens certain test page based on keyArea=200.

But I would like to display all the pages with keyOne=1000 and keyVersionId=007 irrespective of keyArea

You take the data from the querystring and run your SQL accordingly. It should be simple - that's just simple php conditional block. From your description, this is a simple OR operator?

$bind = array();
$where = array();
if(isset($_GET['keyVersionId']))
{
    $where[] = "`keyVersionId` = :kvi";
    $bind[':kvi'] = $_GET['keyVersionId'];
}
if(isset($_GET['keyOne']))
{
    $where[] = "`keyOne` = :k1";
    $bind[':k1'] = $_GET['keyOne'];
}
if(isset($_GET['keyArea']))
{
    $where[] = "`keyArea` = :karea";
    $bind[':karea'] = $_GET['keyArea'];
}


$whereClause = if(!empty($where)) ? ' WHERE ' . implode(" OR ", $where) : '';

Just tag on the $whereClause string to your prepared statement and bind the parameters (if using PDO), with $bind array and execute() method.

[kaprikorn07]

Sorry, I did not understand the above code :(

Also one more thing, I am having checkboxes for blocked testcases in a table now. So when user checks the checkbox how do I store it to DB.