Can you help me with this?? PHP MYSQL

[Vini]

Sorry for the vague title, but I'm going to have all on explaining this.

I've a list of things (to do) and I want to send this list out to X,Y,Z (people) and have them pick/choose the task they wish to do.

It's a bit like this;

# | Task | Person
1 | Fish | Do this!
2 | Cook | Do this!
3 | Talk | Do this!


I want X,Y,Z to click Do this! and be assigned to the task, on a first come first served basis.

How in the hell would I go about doing this?

[malfunction]

Originally Posted by Vini

Sorry for the vague title, but I'm going to have all on explaining this.

I've a list of things (to do) and I want to send this list out to X,Y,Z (people) and have them pick/choose the task they wish to do.

It's a bit like this;

# | Task | Person
1 | Fish | Do this!
2 | Cook | Do this!
3 | Talk | Do this!


I want X,Y,Z to click Do this! and be assigned to the task, on a first come first served basis.

How in the hell would I go about doing this?

Short answer:

You need some kind of flag or method to determine which tasks are still open and which ones have already been allocated

Long answer:

You need some kind of flag or method to determine which tasks are still open and which ones have already been allocated.

I would use either a distinct field (e.g. a flag) in a single "tasks" table...

or

Multiple tables

e.g. "new_tasks", "wip_tasks" and "closed_tasks". Essentially this way you'd be forming a queue (or workflow) using separate tables (which make it easy to see outstanding work, work in progress and a history of completed work - e.g. user X performed 100 tasks in October whereas user Y performed 150).

If you go the multiple table route (which implies more complex requirements) you might also want to consider having a 'template' or workflow definition table(s) that specify the sub-tasks required for each distinct activity - which would be used to populate the "new_tasks" table as each instance of the overall task (such as "process application") is created. If you're not familiar with workflow the classic example is making a cup of tea - fill kettle, switch it on, get cup, sugar, tea bag, etc - e.g. the distinct tasks required to perform the overall goal - some of which may be optional (milk or sugar?), some mandatory (fill kettle, boil, get cup), some must be performed in sequence (can't pour the water until the kettle has been filled and the water boiled), some may be performed in parallel (you can get the cug, sugar and milk while the kettle is boiling).

You'll also need to think about synchronisation / concurrency - the easiest way would be to give the person a work list / pick list of open tasks to choose from. When the person accepts some or all of these tasks you check the table again to ensure that the chosen tasks are still open (which, depending on the DB engine you're using may required explicit locks / transactions to be opened and closed). If there's any mismatch assign the person the tasks that are still open, ignore any that have already been assigned and return a message to indicate what happened - e.g. "task #2 and #6 allocated to you (user x), task #4 already allocated (to user y)"

[Vini]

Thanks for understanding my query, but unfortunately I don't understand your answer

It's a little above me lol! I'll sort something (bodge)

[Steve]

Simplest way would be to have two tables, a task table and a person table.

The task table would contain a column for the ID of the user doing the task. If NULL then it would be unowned. When somebody clicks "Do this" the task row is updated with their user ID.

That make sense?

[malfunction]

Originally Posted by Vini

Thanks for understanding my query, but unfortunately I don't understand your answer

It's a little above me lol! I'll sort something (bodge)

Well I had been drinking at the time so maybe it was me.

1) Essentially you can generate a list of open tasks by looking at anything that hasn't been allocated. I'd prefer a distinct flag but as per Steve's reply you could simply use a null user id, e.g.

select * from tasks where user_id is null

2) When you come to allocate tasks to a user you'll need to pass the task IDs and user ID into an update query, e.g.

update tasks set user_id = 666 where task_id in (1, 2, 3) and user_id is null

You could also use a loop and allocate each task one at a time instead of using "in (1, 2, 3)". You need to check the user id again to avoid re-allocating tasks that have already been assigned to someone (as you're going to have multiple people using the system at the same time)

3) Then you need to confirm which tasks have been successfully allocated to the user, e.g.

select * from tasks where task_id in (1, 2, 3) and user_id = 666

Because, as above, if the same task list will be open for multiple users to select from at the same time you might find that user X selects task 2 (only) while user Y is still looking at the full task list. User Y then selects tasks 1, 2 and 3 but you should only allocate tasks 1 and 3 to user Y because task 2 has already been taken. It would probably be nice to tell the user that task 2 was already taken (possibly giving the user name of the person that took it, possibly not depending on who your users are and any privacy concerns).

Still with me?

I'd also use a field to indicate whether or not a task has been completed. You could simply delete tasks that have been completed though I'd say keeping a history is better. This is why I went on about multiple tables above. Instead of using 1 table with flags used to indicate the state of each task (allocated? completed?) you could use separate tables, e.g.

"new_tasks" - to store details of all tasks that haven't as yet been allocated to anyone

"open_tasks" - to store details of all task that have been allocated to someone but aren't as yet complete

"closed_tasks" - to store details of all tasks that have been allocated to someone and have been completed (e.g. a history of what each user did, very useful and possibly legally required depending on what the system in question is doing)

This is without going into any detail in terms of tables that might be required outside of this, e.g. a "person" (or "user") table might be used to store user details (user id, username, full name and password perhaps) which then lets you use only the user id as a foreign key in the tasks table, a "task_type" table that might define the types of task (and perhaps an associated table - "user_task_type" that would let you restrict certain tasks to certain users - e.g. if some of the tasks should only be given to 'advanced users' you could accomodate this by giving the task a type or level)...

Without knowing more it's hard to say what you need to do - is this homework or a pet project at work or are you being paid as a full time developer and being given woolly requirements?

[Vini]

Cheers guys, and malfunction for the revisit!

I've played with this; http://www.spoono.com/php/tutorials/tutorial.php?id=23

and it's close, but I can't get it to write back

[Steve]

That's a terrible tutorial unfortunately, particularly because it doesn't nothing to avoid SQL injection attacks.

If this is going to be web accessible or accessible by anyone you don't completely trust, I'd learn a bit more first.

[Vini]

Originally Posted by Steve

That's a terrible tutorial unfortunately, particularly because it doesn't nothing to avoid SQL injection attacks.

If this is going to be web accessible or accessible by anyone you don't completely trust, I'd learn a bit more first.

It doesn't need to be secure. It's open to me and my family.

[Vini]

This is what I'm playing with.

[scaryjim]

Originally Posted by Vini

It doesn't need to be secure. It's open to me and my family.

Hate to disagree, but if it's on a publically facing website, even if you're using some form of authentication, then your code *still* needs to be secure. And frankly, even if that wasn't the case then you really shouldn't be learning bad practices now that you'll need to get out of later. Plus, there's no telling what even trusted members of your family will do to your website when you're not watching them (accidentally, maliciously *or* just because they feel like messing about "to see what happened")! You will have a much easier time of it if you learn the secure, safe method of doing things now.

[Vini]

Originally Posted by scaryjim

Hate to disagree, but if it's on a publically facing website, even if you're using some form of authentication, then your code *still* needs to be secure. And frankly, even if that wasn't the case then you really shouldn't be learning bad practices now that you'll need to get out of later. Plus, there's no telling what even trusted members of your family will do to your website when you're not watching them (accidentally, maliciously *or* just because they feel like messing about "to see what happened")! You will have a much easier time of it if you learn the secure, safe method of doing things now.

Whilst I understand there's a perfectly good reason for doing things properly. It's only on a publicly facing website, for now.

I'm not into coding and as you can see it's hardly earth shattering what I'm trying to do.

[Steve]

Post the code that isn't working and we'll see where it's going wrong.

[Vini]

<html>
<head>
<title>James' Birthday/Xmas List</title>
<base target="_blank">

    <style  TYPE="text/css">
		body {
		background: #f0f0f0
		}

		table {
		text-transform:capitalize;
		border: 3px solid #000;
		font-family: verdana;
		font-size: 14px;
		}

		th {
		background: #bbb;
		border: 0;
		}

		td {
		background: #d0d0d0;
		border: 0px;
		}
    </style>

</head>
<body>

<table border cellpadding=3>
<tr><th align=left width=400>Item (<a href="admin" target="_self">add</a>)</th><th align=left width=145>Shops to try...</th><th align=left width=145>Who</th></tr>


<?
//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("localhost","root","pw");

//select which database you want to edit
mysql_select_db("dbname");

//If cmd has not been initialized
if(!isset($cmd))
{
   //display all the news
   $result = mysql_query("select * from table WHERE occasion > '' ORDER BY id DESC");

  //run the while loop that grabs all the news scripts
   while($r=mysql_fetch_array($result))
   {

      //grab the title and the ID of the news
      $name=$r["name"];//take out the title
      $id=$r["id"];//take out the id
      $who=$r["who"];//take out who

	 //make the title a link

      echo "<tr><td>$name</td>";
      echo "<td>";
		  echo "<a href='http://www.amazon.co.uk/s/ref=nb_ss_w_h_?url=search-alias%3Daps&field-keywords=$name'><img border='0' src='icons/amazon.gif'></a>&nbsp;";
		  echo "<a href='http://www.play.com/Search.aspx?searchtype=allproducts&searchstring=$name&page=search&pa=search&go.x=0&go.y=0'><img border='0' src='icons/play.gif'></a>&nbsp;";
          echo "<a href='http://www.argos.co.uk/webapp/wcs/stores/servlet/Search?storeId=10001&langId=-1&searchTerms=$name'><img border='0' src='icons/argos.gif'></a>&nbsp;";
          echo "<a href='http://www.ebuyer.com/search?q=$name'><img border='0' src='icons/ebuyer.gif'></a>&nbsp;";
          echo "<a href='http://www.gamestracker.com/search.asp?title=$name&sp=on&pid39=on'><img border='0' src='icons/gamestracker.gif'></a>&nbsp;";
          echo "<a href='http://www.firebox.com/firebox/search?searchstring=$name'><img border='0' src='icons/firebox.gif'></a>&nbsp;";
          echo "<a href='http://www.google.co.uk/products?q=$name&scoring=p'><img border='0' src='icons/google.gif'></a>&nbsp;";
      echo "</td>";
      echo "<td>$who <a target='_self' href='index.php?cmd=edit&id=$id'><img border='0' src='icons/gift.png'></a></td></tr>";

    }
}
?>

</table></div><br />

<?
if($_GET["cmd"]=="edit" || $_POST["cmd"]=="edit")
{
   if (!isset($_POST["submit"]))
   {
      $id = $_GET["id"];
      $sql = "SELECT * FROM table WHERE id=$id";
      $result = mysql_query($sql);
      $myrow = mysql_fetch_array($result);
      ?>

      <form action="index.php" method="post" target="_self">
      <input type=hidden name="id" value="<?php echo $myrow["id"] ?>">

      Who: <INPUT TYPE="TEXT" NAME="who" VALUE="<?php echo $myrow["who"] ?>" SIZE=30> <input type="hidden" name="cmd" value="edit">

      <input type="submit" name="submit" value="submit">

      </form>

<? } ?>
<?
   if ($_POST["$submit"])
   {
	  $who = $_POST["who"];

	  $sql = "UPDATE table SET who='$who' WHERE id=$id";
      //replace news with your table name above
      $result = mysql_query($sql);
      echo "Thank you! Information updated.";
	}
}
?>

</body>
</html>

[Steve]

You've got $_POST["$submit"] when it should be $_POST["submit"] I guess.

Also the update query uses $id but I don't think $id gets set for that particular path through the code.

Further, you should make input variables to sql queries safe by using, say intval($var) for integers and mysql_real_escape_string($var) for strings. There are cleverer ways of doing it, but these should avoid injection issues, although don't do much in the way of validating the input.

[Vini]

Originally Posted by Steve

You've got $_POST["$submit"] when it should be $_POST["submit"] I guess.

That's done it for me! Cheers guys!

[Steve]

Good stuff.

Now read this: http://www.tizag.com/mysqlTutorial/m...-injection.php

and do it.

At some point in your life you'll thank us for it