Dynamic Row Updating In Mysql

[lewishowe]

Bonjour,

 

Been hunting the internet to find a solution myself, but instead thought I'd just ask on here. After all, if you don't ask, you don't get.

 

Basically, I am creating a internal web based e-mail generator for the company I work for, that will send an e-mail to 'handover' certain 'tasks' to the next team on the next shift - It will be used internal only, so please excuse some of the sloppy coding but we only need it to 'work' - Doesn't really matter how!

 

Anyway, the when someone goes through the form, they are given the options to add 'tasks' to the e-mail, these 'tasks' gets stored into a MySQL database, with the 'DONE' value of '0', ready for when the next person goes to generate the 'handover' - When the next person goes to generate the 'handover', they get a list of the 'tasks', that were previously inserted, by the previous person. Like so:

 

 

They are given the option to mark the task as done, or not.

 

Now here's the part where I get stuck...

 

The subsequent 'handover' queries the MySQL database to see which 'tasks', have a 'DONE' value of '0' (not done). It then lists them. Here is the code:

 

$toq2= mysql_query("SELECT * FROM tasks WHERE done='0'");
$toq2p = mysql_fetch_array( $toq2 );
$toq2r = mysql_num_rows( $toq2 );
if ($toq2r >= 1)
{
echo "<table border='0'><tr><td></td><td></td><td></td><td><img src='/assets/tick.png' width='20px' height='20px' border='0' /></td>";
$toq4= mysql_query("SELECT * FROM tasks WHERE done='0'");
while($toq4p = mysql_fetch_array( $toq4 )) {
echo "<font color='black' size='2'><tr><td><font size='2'><strong>{$toq4p['system']}</strong></font> - </td><td><font size='2'><em>{$toq4p['region']}</em></font> - </td><td><font size='2'>{$toq4p['description']}</font></td><td><input type='checkbox' name='todo1[]' value='1'></td></font></tr>"; }
echo "</table><br />";
}
else
{
echo "<h3><font color='red'>NOTHING TO HANDOVER</font></h3>";
}

 

Problem I have, is that because the amount of tasks is never consistent, I can't have a set query for the amount of values, therefore I need to find a way to dynamically set each row to update the 'done' value in the MySQL table to '1' if the box is ticked and leave it as '0', if the box isn't ticked.

 

I hope I've explained myself well enough and appreciate any help that anyone can provide.

 

Many thanks,

 

P.S: Tasks are created as needed and are NOT in a schedule.. as so:

 

 

When inserted from here, the value of 'done' is set to '0' - From the task list (picture in original post), if the person ticks the checkbox, I want it to be able to update that value of 'done' to '1'.

[Muddy_Funster]

Normaly you would use the values passed from the form checkboxes to generate the where clause in an update statement. We will need more info about your table and form structure to give more detail.

[smoseley]

What you want is a simple AJAX update method. Easy enough to do.

 

Change your input to this (I'm assuming your primary key is called "id"):

 

<input type="checkbox" name="tasks[]" value="<?=$toq4p['id']?>" onclick="toggle(this)" />

 

(PS - always use double-quotes for HTML attributes... single-quoted HTML is a big no-no)

 

Then add this javascript: (requires jQuery)

 

function toggle(input) {
input = $(input);
$.ajax('/update_task_todo.php?id=' + input.value + '&todo=' + (input.checked ? 1 : 0));
}

 

Then create the php script (update_task_todo.php) to update the record based on the id.

 

<?php
$id = (int) $_GET['id'];;
$todo = (int) $_GET['todo'];
$sql = "UPDATE tasks SET todo = {$todo} WHERE id = {$id}"
mysql_query($sql);
echo mysql_rows_affected();;;

Edited November 12, 2012 by smoseley

[Muddy_Funster]

...

(PS - always use double-quotes for HTML attributes... single-quoted HTML is a big no-no)

...

really? do please justify that statement if you will, especialy in refference to html generated through the use of php echo's, because every time I have used it - it's passed the w3c validation without a single problem.

 

Also, the OP didn't say anthing what so ever about needing or wanting ajax functionality so where did you get that idea?

[smoseley]

really? do please justify that statement if you will, especialy in refference to html generated through the use of php echo's, because every time I have used it - it's passed the w3c validation without a single problem.

Single quotes are valid markup, but it's a w3c recommendation to use double-quotes, as some SGML parsers don't recognize single-quotes. It's also a standard convention in every development team I've ever worked in over the last 15 years to use double-quotes for HTML, and single-quotes for JS. If you want to do it backwards, go for it... but other coders will hate you and refactor your code so it fits conventions.

 

Also, the OP didn't say anthing what so ever about needing or wanting ajax functionality so where did you get that idea?

If you actually read what the OP wants his application to do, he wants AJAX, whether or not he specifically asked for it by name. He wants it to "dynamically update the row when the box is checked." What was your interpretation of that?

[Muddy_Funster]

Single quotes are valid markup, but it's a w3c recommendation to use double-quotes, as some SGML parsers don't recognize single-quotes. It's also a standard convention in every development team I've ever worked in over the last 15 years to use double-quotes for HTML, and single-quotes for JS. If you want to do it backwards, go for it... but other coders will hate you and refactor your code so it fits conventions.

No one will be changing my code, when did I even talk about my code? You have chosen to make this unnessiceraly personal - and so be it - when all I asked was that you justify your statement. It's not an unresonable thing to do when somone comes marching along throwing opinions about like they are hard facts. So it's a convention that you work to, and a recomendation, at best, outside that. Well backed up, I must say - perticulatly how you sited the recomendation and paid attention to the refference to handling markup generated thorugh php....oh wiat, you must have missed that bit when you got all pissy about things. It's good to know that all the employment you have had has made you so astute to the use of single quotes in html that you completly ignore the fact that the form element is missing all together, which would be, in my opinion, far more important for decent code than the single quotes thing...but hell, what do I know?

 

If you actually read what the OP wants his application to do, he wants AJAX, whether or not he specifically asked for it by name. He wants it to "dynamically update the row when the box is checked." What was your interpretation of that?

My interpretation of someone new to php, posting for help in a php forum, showing only php code, stating that it is for an internal script that only has to work and mentioning nothing what so ever about Javascript, JQuery or AJAX is that the OP is looking for a PHP solution NOT a JS one, as they very likely don't know or are not comfortable with javascript. However, rather than make a rash decission on the fact I preffer to get more information from people before throwing code at them that may in fact make things take longer to resolve, and end up teaching them nothing.

 

This is a help forum, not a personal critique of others practices. If you can't answer questions without getting agresivly personal with people when challenged then you should probably find something else to pass your time with.

[Psycho]

The OP's request has nothing to do about an AJAX solution. His request was about how to handle the dynamic number of tasks. AJAX does not, in and of itself, solve the question. It does work around some of the problems since the user would not be submitting the entire form. But, it adds complexity that is not needed and doesn't really help the OP in gaining better understanding of the basic logic that could be implemented.

 

As for HTML standards, I use single quotes a lot due to the ease of implementing them in double quotes echo statements (but I do prefer double quotes). But, I would highly suggest the OP stop using the FONT tags which have been deprecated for over a decade now.

 

@lewishowe,

 

Your post only had a single query to get ALL of the undone tasks in a table. I must assume that is only mock code since I would expect that you would only be getting the tasks for a specific email/message. And, aside from the two queries (which are identical) you didn't provide any details about your table structure. So, I'll provide an example of what your tables could look like and how you could implement what you need.

 

I expect you would need at least two tables for the email and tasks. There would obviously need to be others such as a users table, but those aren't needed as part of the functionality in question. The two tables and some of the fields needed would be something like:

 

messages:

message_id, message_date, user_id, message_text

 

tasks:

task_id, message_id (foreign key to messages table), task_description, done

 

Now, when querying the details for a message, if you need data from the messages table and the tasks table, you will want to JOIN the tasks table on the messages table. Then, when creating the form with the checkboxes for the tasks, use the value of done (0 or 1) to make the checkboxes checked or unchecked. Also, more importantly, create the checkbox names as an array and set the value of the checkboxes as the IDs of the tasks. When a form is submitted only checkboxes that are checked are included in the POST data. So, when the user submits the form, you can update all the tasks associated with the message with a single query based upon which ones were included in the POST data.

 

Note: none of the code below is tested, so there could be some typos.

 

Sample code for form creation script

<?php

if(!isset($_GET['message_id']))
{
   $output .= "No message selected";
}
else
{
   //User has selected to display a message and the tasks
   $messageID = intval($_GET['message_id']);

   //Get selected message and tasks for selected message ID
   $query = "SELECT m.message_id, m.message_text, m.message_date,
				 t.task_id, t.task_description, t.done			     
		  FROM message AS m
		  LEFT JOIN tasks AS t
		    ON t.message_id = m.message_id
		  WHERE m.message_id = '{$messageID}'";
   $result = mysql_query($query);

   if(!$result)
   {
    $output = "Error running query:<br>Query: {$query}<br>Error:" . mysql_error();
    exit();
   }
   elseif(!mysql_num_rows($result))
   {
    $output = "The selected message ID does not exist.";
   }
   else
   {
    while($row = mysql_fetch_assoc($result))
    {
	    if(!isset($output))
	    {
		    $displayDate = date('m-d-Y', strtotime($row['date']));
		    $output  = "<form action=\"\" method=\"post\">\n";
		    $output .= "<b>Date:</b> {$displayDate}<br>\n";
		    $output .= "<b>Message:</b> {$row['message_text']}<br><br>\n";
		    $output .= "<b>Tasks:</b><br>\n";
	    }
	    $checked = ($row['done']==1) ? ' checked="checked"' : '';
	    $output .= "<input type=\"checkbox\" name=\"tasks[]\" value=\"{$row['task_id']}\"{$checked} />\n";
	    $output .= "{$row['task_description']}<br>\n";
    }

    $output .= "</form>\n";
   }
}

?>
<html>
<body>
<?php echo $output; ?>
</body>
</html>

 

Sample code for form processing

<?php

if(!isset($_POST['message_id']))
{
   $output .= "No form submitted";
}
else
{
   //User submitted form
   $messageID = intval($_POST['message_id']);

   //Process the tasks submitted
   $taskIDsAry = array();
   if(isset($_POST['tasks'))
   {
    //Force to ints and remove NULL values
    $taskIDsAry = array_filter(array_map('intval', $_POST['tasks']));
   }
   //Put in comma separated list
   $taskIDsSQL = implode(', ', $taskIDsAry);

   //Run ONE query to update the 'done' field for all the tasks
   //Checked tasks (in POST data) will be set to 1
   //Unchecked tasks (not in POST data) will be set to 0
   $query = "UPDATE tasks
		  SET done = IF(task_id IN (), 1, $taskIDsSQL)
		  WHERE m.message_id = '{$messageID}'";
   $result = mysql_query($query);
}

?>

[Barand]

Do you really need the email that mentioned in your first post?

 

All you need is a page for the team to list outstanding tasks. They can then add new tasks or mark tasks as done.

 

When the next team comes along they repeat the process.

 

If you did have an email it would just say "Click here to see this page"

[Psycho]

I just noticed I forgot to include the $taskIDsSQL variable in the query in the processing script. And, in retrospect I would chang ethe name of that variable to $checkedTaskIDsSQL to be more descriptive of what it actually contains.

 

Here is the updated code for the relevant section:

    //Process the tasks submitted
   $taskIDsAry = array();
   if(isset($_POST['tasks'))
   {
    //Force to ints and remove NULL values
    $checkedTaskIDsAry = array_filter(array_map('intval', $_POST['tasks']));
   }
   //Put in comma separated list
   $checkedTaskIDsSQL = implode(', ', $taskIDsAry);

   //Run ONE query to update the 'done' field for all the tasks
   //Checked tasks (in POST data) will be set to 1
   //Unchecked tasks (not in POST data) will be set to 0
   $query = "UPDATE tasks
		  SET done = IF(task_id IN ({$checkedTaskIDsSQL}), 1, $taskIDsSQL)
		  WHERE m.message_id = '{$messageID}'";

[smoseley]

No one will be changing my code, when did I even talk about my code? You have chosen to make this unnessiceraly personal - and so be it - when all I asked was that you justify your statement.

 

A lot gets lost in translation on a forum... I didn't mean to make this personal. You did mention your code... you said when you do it, it passes W3C validation. I simply meant to respond to that and state the facts as I understand them.

 

There was no emotion in my post. There was no opinion. There was only fact. The fact is that it's a W3C recommendation to use double-quotes, and a common convention in every major web development shop in the world (that I'm aware of) to do so.

 

Hope you understand I'm just trying to share my experience and help.

 

Cheers.

Edited November 13, 2012 by smoseley

[smoseley]

The OP's request has nothing to do about an AJAX solution. His request was about how to handle the dynamic number of tasks. AJAX does not, in and of itself, solve the question. It does work around some of the problems since the user would not be submitting the entire form. But, it adds complexity that is not needed and doesn't really help the OP in gaining better understanding of the basic logic that could be implemented.

 

Maybe I misunderstood the request. I don't find that my suggestion was overly complex, though. AJAX isn't as difficult as people make it out to be.

 

As for HTML standards, I use single quotes a lot due to the ease of implementing them in double quotes echo statements (but I do prefer double quotes). But, I would highly suggest the OP stop using the FONT tags which have been deprecated for over a decade now.

 

I stopped using double-quotes to echo HTML for the reason of being difficult to generate conventional double-quoted HTML, though I still do on occasion.

 

My suggestions for echoing HTML, in order of preference (considering performance, and clarity of code):

 

// 1 - Concatenated single-quoted text, for high performance in an HTML-generating loop
echo '<div id="element">' . $dynamicContent . '</div>';

// 2 - Heredoc for multi-lined HTML
echo <<<HTML
<div id="element1">{$dynamicContent}</div>
<div id="element2">{$dynamicContent}</div>
<div id="element3">{$dynamicContent}</div>
HTML;

// 3 - HTML blocks with PHP echo short-tags for templating
?>
<div id="element"><?=$dynamicContent?></div>
<?php

// 4 - Double-quotes with escaped double-quoted HTML, if you absolutely insist on double-quoted text in PHP
echo "<div id=\"element\">{$dynamicContent}</div>";

Edited November 13, 2012 by smoseley

[Muddy_Funster]

A lot gets lost in translation on a forum... I didn't mean to make this personal. You did mention your code... you said when you do it, it passes W3C validation. I simply meant to respond to that and state the facts as I understand them.

 

There was no emotion in my post. There was no opinion. There was only fact. The fact is that it's a W3C recommendation to use double-quotes, and a common convention in every major web development shop in the world (that I'm aware of) to do so.

 

Hope you understand I'm just trying to share my experience and help.

 

Cheers.

That's fair enough then and I can accept that's how you ment it, and now that were all cleared up I suppose the polite thing to do would be for me to stop hijacking the thread and let the OP hopefully get back to us with how the provided solutions are shaping up for them.

 

p.s. I, personaly, would re-order your preffered method to

1=3

2=1

3=4

4=2

 

I hate dot concats. I used to use them all the time, but as soon as you take your eye off the ball it can be a royal pain to find that missing . or " now I just escape as I go if it's a single line. heredoc and nowdoc blocks seem to be PHP's dirty little secret. They are grossly overlooked from what I have seen, dispite being perfect for holding html and SQL code in a far more readable (and debug friendly) format.

 

Anyway....enough! back to the OP!......

Edited November 13, 2012 by Muddy_Funster

[lewishowe]

Had a lot to read, hence the delayed response.

 

Okay, I've had a look at all the suggestions and compiled something from it which I think (thought) would work best..

 

So the form now looks like (i'll make it prettier at another time):

 

print '<form id="handover" name="handover" method="post" action="newho.php?a=pt1&s=s">';
$toq= mysql_query("SELECT * FROM tasks WHERE done='0'");
while ($row = mysql_fetch_assoc($toq))
{
   printf('<label for="%d"><span class="labelsystem">%s</span><span class="labelregion">%s</span><span class="labeldescription">%s</span></label>' . PHP_EOL, $row['taskid'], $row['system'], $row['region'], $row['description']);
   printf('<input id="%d" type="checkbox" name="todo1[]" value="%d" />' . PHP_EOL, $row['taskid'], $row['taskid']);
}

 

Now the PHP code..

 

if (isset($_POST['todo1']) && is_array($_POST['todo1']))
{
   function isIntString($in)
   {
    $bResult = false;
    $int = (int)$in;
    if ((string)$int == $in)
    {
	    $bResult = true;
    }
    return $bResult;
   }
   $aTodo = array_filter($_POST['todo1'], 'isIntString');

   if (!empty($aTodo))
   {
    $sQry = 'UPDATE tasks SET done = 1 WHERE taskid IN (' . implode(', ', $aTodo) . ')';
   }
}

 

Problem.. it's not working. I'm probably missing something very obvious, but as I'm quite frankly 'inexperienced', I can't see it...

 

Any ideas?

 

Once again thanks for all your help so far.

 

P.S: I like the AJAX idea, but I'd rather not use AJAX for just the one function - Maybe implementing as a whole in the near future would be something to look at - But thanks anyway

[Muddy_Funster]

you know, something to work with that's a bit more informative than "it's not working" is always appreciated...

[lewishowe]

Okay, well there's no error messages...

 

The query just isn't running - When I tick the box, it doesnt update the done field to 1

[Muddy_Funster]

Could we see the full code for the form and the update pages?