Comparing data in mysql array

[InABoxPr]

Well i have been trying to get this to work for the past 30 minutes, and it just keeps mind fucking me over and over.

 

Heres how my mysql is basicly setup:

 

Table: Tasks

 

ID | Done

 

Table: Users

 

ID | Username

 

Now im the table "tasks" for the column "done" i want it to say like

 

"1,2,3,4,5" the number represents the Users ID.

 

Now i was to echo out each ID in the table tasks that does not contain the users ID

 

I was trying to put the data in the column "done" into a array, and use in_array to compare the user ID and the data, but it wasnt working.

[Alex]

Well, your real problem is that your database is structured poorly, so it's making a task like this not so easy to accomplish.

 

Anytime you're storing concatenated data in a field like that is a pretty good indication of poor database design.

 

If you remove the "Done" field from the Tasks table so that it's just a table to describe each task, then you can create another table of completed tasks. In this table you could have the id of a user, and the id of the task completed. So a record for each task each user has completed.

 

Then selecting the tasks that haven't been completed for a particular user is a simple, and much more efficient, task.

[InABoxPr]

Ah, i never thought about that.

 

So i would have something like this,

 

Table: Tasks

 

ID | Task

 

Table: Users

 

ID | Username

 

Table: Complete

UserID | TaskID

 

I would have the script check if the id of the user is inside the ID column of the Complete table, and if it is it will compare the task id to the task id inside the table task and i will echo out every row that isnt already complete by that user.

 

Im still sorta stuck on how to code this, i understand how to do it but i feel a bit confused when doing this.

[Alex]

You wouldn't require a PHP script to do any checking. You could have MySQL do all the work with a simple select query.

 

This can probably be done better, but roughly:

 

SELECT Tasks.ID from Tasks WHERE Tasks.ID NOT IN (SELECT Complete.TaskID FROM Complete WHERE Complete.UserID = 'some user id')

[InABoxPr]

And with that sql query, i would echo out the result for each thing that is returned.

 

So something like

 

$sql = mysql_query("SELECT Tasks.ID from Tasks WHERE Tasks.ID NOT IN (SELECT Complete.TaskID FROM Complete WHERE Complete.UserID = 'some user id')" or die(mysql_error());

while($fetch = mysql_fetch_array($sql))

{

echo "$fetch[task]";

}

 

[Alex]

Well, it depends on what you want to echo out. The TaskID? The "Task" field, or what?

 

You'll also have to specify which UserID we're dealing with here, don't forget that.

[InABoxPr]

Well im trying to echo out the task, and for now lets say the user id is "1" .

 

 

 

[Alex]

So then maybe something like this:

 

$sql = mysql_query("SELECT Tasks.Task as Task from Tasks WHERE Tasks.ID NOT IN (SELECT Complete.TaskID FROM Complete WHERE Complete.UserID = '1')" or die(mysql_error());
while($fetch = mysql_fetch_array($sql))
{
echo $fetch['Task'];
}

[InABoxPr]

Alright thanks, I will test it out now. I will reply shortly with the result.

[InABoxPr]

Alright it works now, i have 1 last question.

 

I made it:

 

echo $fetch['Task'] .">>";

 

instead of

 

echo $fetch['Task'] .">>";

 

so now it would say (if i have a task)

 

task>>

 

Im using the ">>" as a seperator for later use.

 

How can i make it delete the last / remove the last ">>" out of the echo'd out data.

 

For example if i have tasks:

 

ID | Task

1 | Hello

2 | Hi

3 | oHia

 

It would echo out like:

 

Hello>>Hi>>oHia>>

 

I want it to echo out like

 

Hello>>Hi>>oHia

 

 

[InABoxPr]

Sorry for tripple posting, i couldnt find out how to edit previous post.

 

I solved all my problems, can someone close this thread

[Alex]

You can mark it as solved yourself using the button on the bottom of the page.