Need some help please

richarro1234 · May 16, 2015

Hey,

So, im creating like a checklist of items that need to be done.

Now, i have the list of items, but its the same list for every user (they have to login to see the list)

What i need is a way so that it stores just that users completed items (my list works at the moment with just 1 user. when i mark an item as complete it crosses it out so its done) but then if another user come sonline and looks at the list it will show (my) list and will say itesm are done if they havent done them.

So what i need to know is what is the best way to store the data and how do i do it?

i thought about adding rows to the user table for each of the items, but theres 60 on the list (and it might grow) so didnt think that would be very efficient.

the list is marked completed by ajax/jquery so needs to accommodate that too.

If anyone is confused by this (like me) then please let me know and i will try to explain better

Thanks

Rich

Ch0cu3r · May 16, 2015

So what i need to know is what is the best way to store the data and how do i do it?

I would say you would want to setup three tables in your database.

users table - stores user info such as user id, username, password etc...
items table - store the items in the checklist such as the item id, title, description etc...
item_completed table - in this table you only record the user id along with the id of the completed item.

You would use a join to know if the user has completed an item in the check list

If you are new to joins I recommend you have a look at here first http://www.sitepoint.com/understanding-sql-joins-mysql-database/. You will want to use a Left Join (query the items table and left join the items_completed table where the logged in user id and item id matches).

Edited May 16, 2015 by Ch0cu3r

richarro1234 · May 16, 2015

Hey, thanks for the reply.

How would i add that to this though?

<?php
if(isset($_POST['submit'])) {
	$addId = -1;

	if(isset($_POST['content'])) {
		foreach($_POST['content'] as $id => $content) {
			if(!is_int($id)) {
				header('HTTP/1.1 400 Bad Request');
				die('Invalid item ID.');
			}

			if($id === -1 && !empty($content)) {
				$query = $link->prepare('INSERT INTO todo(`done`, `user`, `text`) VALUES(0, :user, :text);');
				$query->execute(array(
						':user' => $user,
						':text' => $content
					));
				$addId = $link->lastInsertId();
			} else {
				$query = $link->prepare('UPDATE todo SET `text` = :text WHERE `id` = :id AND `user` = :user LIMIT 1;');
				$query->execute(array(
						':id' => (int)$id,
						':user' => $user,
						':text' => $content
					));
			}
		}
	}

	$query = $link->prepare('UPDATE todo SET `done` = 0 WHERE `user` = :user');
	$query->execute(array(':user' => $user));

	if(isset($_POST['done'])) {
		foreach($_POST['done'] as $done) {
			if(!ctype_digit($done) && $done !== '-1') {
				header('HTTP/1.1 400 Bad Request');
				die('Invalid item ID.');
			}

			if($done === '-1') {
				$done = $addId;
			}

			$query = $link->prepare('UPDATE todo SET `done` = 1 WHERE `id` = :id AND LIMIT 1;');
			$query->execute(array(':id' => (int)$done));
		}
	}
}

$query = $link->prepare('SELECT * FROM todo WHERE `user` = :user ORDER BY `id` ASC;');
$query->execute(array(':user' => $user));
?><!DOCTYPE html>

<html>
	<head>
		<!-- Page title -->
		<title>My list · TODO:</title>

		<!-- Stylesheets -->
		<link rel="stylesheet" type="text/css" href="stylesheets/default.css" />
		<link rel="stylesheet" type="text/css" href="stylesheets/todo.css" />

		<!-- Scripts -->
		<script type="text/javascript" src="scripts/json2.min.js"></script>
	</head>
	<body>
		<form method="POST" id="content">
			<h1>100% CheckList</h1>

			<ul id="todo-items">
				<?php while($row = $query->fetch()): ?>
					<li> <input type="checkbox" name="done[]" value="<?= $row->id ?>" class="done-box"<?php if($row->done): ?> checked="checked"<?php endif; ?> /> <span class="content"><?= htmlspecialchars($row->text) ?></span></li>
				<?php endwhile; ?>
			</ul>
		</form>

		<script type="text/javascript" src="scripts/todo.js" async="async"></script>
	</body>
</html>

without messing toooo much up? ive never used joins before and although im sure its fairly easy to most people.

Rich

Edited May 16, 2015 by richarro1234

Barand · May 17, 2015

Instead of an "items_completed" table you could just have a "date_completed" column (default NULL) in the items table and timestamp the item when completed.

richarro1234 · May 17, 2015

Hey thanks for the replys.

The problem is it's the same list for everyone.

It's a 100% checklist so it's the same list for every person but each person will have completed different things to everyone else so needs to be the same list unique to each person so it doesn't really matter about when it was done. It just needs to be a unique list for everyone who signs up without having a massive database.

Barand · May 17, 2015

In which case I concur with Ch0cU3r.

richarro1234 · May 17, 2015

Ok any ideas how I would go about doing that with the code I have? Don't want to mess too much up and if I start playing with bits I don't know then I'm sure to break it

Barand · May 17, 2015

Let's suppose you item and item completed tables contain

mysql> SELECT * FROM item;
+---------+--------------+
| item_id | item_descrip |
+---------+--------------+
|       1 | Item 1       |
|       2 | Item 2       |
|       3 | Item 3       |
|       4 | Item 4       |
|       5 | Item 5       |
|       6 | Item 6       |
|       7 | Item 7       |
|       8 | Item 8       |
|       9 | Item 9       |
|      10 | Item 10      |
+---------+--------------+

mysql> SELECT * FROM item_completed;
+---------+---------+---------------------+
| item_id | user_id | completed           |
+---------+---------+---------------------+
|       2 |       1 | 2015-05-14 10:59:15 |
|       3 |       2 | 2015-05-15 21:59:15 |
|       4 |       2 | 2015-05-16 11:59:15 |
|       5 |       1 | 2015-05-17 14:59:15 |
+---------+---------+---------------------+

User 1 has completed tasks 2 and 5.

If we run this query which matches the tasks against the completed tasks for user 1

SELECT
    i.item_id
  , i.item_descrip
  , c.user_id
FROM item i
LEFT JOIN item_completed c
    ON i.item_id = c.item_id AND c.user_id = 1
ORDER BY i.item_id

we get this, where the completed tasks are shown by a non-null value in the user_id column

+---------+--------------+---------+
| item_id | item_descrip | user_id |
+---------+--------------+---------+
|       1 | Item 1       |    NULL |
|       2 | Item 2       |       1 | <-- completed
|       3 | Item 3       |    NULL |  
|       4 | Item 4       |    NULL |
|       5 | Item 5       |       1 | <-- completed
|       6 | Item 6       |    NULL |
|       7 | Item 7       |    NULL |
|       8 | Item 8       |    NULL |
|       9 | Item 9       |    NULL |
|      10 | Item 10      |    NULL |
+---------+--------------+---------+

With this code you will get the output shown below

<?php
function showTasks(mysqli $db, $user)
{
    $sql = "SELECT
                i.item_id
              , i.item_descrip
              , c.user_id
            FROM item i
            LEFT JOIN item_completed c
                ON i.item_id = c.item_id AND c.user_id = ?
            ORDER BY i.item_id";
    $out = "<table>";
    $stmt = $db->prepare($sql);
    $stmt->bind_param('i', $user);
    $stmt->execute();
    $stmt->bind_result($id, $desc, $uid);
    while ($stmt->fetch()) {
        $cbox = $uid ? "&check;" : "<input type='checkbox' name='complete' value='$id' />";
        $out .= "<tr><td>$desc</td><td>$cbox</td></tr>\n";
    }
    $out .= "</table>\n";
    return $out;
}
?>
<html>
<body>
    <?=showTasks($db, $_SESSION['user'])?>
</body>
</html>

richarro1234 · May 18, 2015

thanks barand, still doesnt really solve the problem though.... (unless im just not understanding it)

ill try to explain a different way..

a user comes and joins this website, there is 1 list of items to complete (100% checklist) each user that joins has the same list of items to complete.

so the list needs to be made, every time a user joins the site and store only what they tick off.

E.G im on the site, i complete tasks 1-5, barand, you join the site and the same list (everything unticked) appears on your screen have done tasks 2-7 so you tick them off, then when you come back on the site, you tasks 2-7 are still ticked off and when i come on my 1-5 are ticked off.

so everyone has there own list of the same items which is unique, individual, personal only to that user.

Hope that cleared things up abit and if its still the same answer then fine, let me know

Thanks

Rich

Barand · May 18, 2015

That is exactly what my code is doing, showing all the items and which ones you ($user) have already ticked off.

If you run it for user 2 (who has completed 3 and 4) you get

richarro1234 · May 18, 2015

ooo right i see, ok thanks very much sorry for all the hassle

does date completed have to be a timestamp though or can it be a Boolean?

Edited May 18, 2015 by richarro1234

Barand · May 18, 2015

It doesn't have to be there at all since you would only write to that table when a user flags a task as completed. I thought the date would be useful should you ever need to know, say, who completed in the fastest/slowest time period or who has been doing it the longest as still hasn't finished (they may need help).

richarro1234 · May 18, 2015

hmmm, just been looking at the code and such like, but what happens if they then mark the same as completed, like say user 1 and 2 marks item 2 as completed, the way its set up doesnt look like it would work, surely it would replace the current number in user_id?

Barand · May 18, 2015

When user 1 completes item 2 you write a record for user 1 to the item_completed table. When user 2 completes it you do the same for user 2 so you get

+---------+---------+---------------------+
| item_id | user_id | completed           |
+---------+---------+---------------------+
|       2 |       1 | 2015-05-14 10:59:15 |
|       2 |       2 | 2015-05-15 21:59:15 |
+---------+---------+---------------------+

richarro1234 · May 18, 2015

ooo right i see, i think i get it now.

is that the best way of doing it though? as in if i have 10 users who have completed 59 of the 60 items each, thats 590 records in 1 db? surely that would get big quick, say if 100 people used it 5900 records thats a fairly big db??

Barand · May 18, 2015

That's a fairly small db.

Just because table is on the large side

mysql> select count(*) from result;
+----------+
| count(*) |
+----------+
|  2557024 |
+----------+

doesn't mean slow queries

mysql> select count(*), avg(analysisresult) as average
    -> from result
    -> where usercode = 1;
+----------+------------+
| count(*) | average    |
+----------+------------+
|    25570 | 12.9759093 |
+----------+------------+
1 row in set (0.16 sec)

richarro1234 · May 18, 2015

oh right ok, didnt even know you could do that.....thanks, i will hopefully leave you alone now, thanks for all the help.

Sign In

Need some help please

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information