Lookin for matches in DB using php

[KriBez]

Hello!

Finally I joined daniweb. Great stuff in here guys. Anyways lets get to the problem:

I have a page where a number of topics are displayed ( they are added by admin). And to get this list I`m using this script

// Query the database
$gettopic = mysql_query("SELECT * FROM topics ORDER BY id DESC") or die(mysql_query());
// List the topics
while ($row = mysql_fetch_assoc($gettopic))
{
//get data
$id = $row['id'];
$heading = $row['heading'];
$status = $row['status'];
$date_closing = $row['date_closing'];
if ($status==1)
$status_name='Closed';
else
$status_name='Open';
if($status_name=='Closed')
$participate = '-';
else
$participate = "<a href=\"paricipate.php?id=$id" >Participate</a>";
echo "
<tr>
<td>
<a href=\"content.php?id=$id" >$heading</a>
</td>
<td align='center'>
$status_name
</td>
<td align='center'>
$date_closing
</td>
<td align='center'>
$participate
</td>
</tr>
";
}

Everything works fine here. The page displays all the topics and Status thing works fine as well.

This all is being taken from topics table.

So there is another table called responses where is a list of users participating in selected topics.

| id | user_id | topic_id | content |

So the question is:
How can I make script close the topic ( as it does in the script with data taken from Status cell) in case user is already participating with his content?

But topics where he is not participating - still available for him unless he doesn`t publish his content in those. In other words - if user id is matching the topic id => script closes the topic for that user. How could I make it work?

Tried to experiment with JOINs and other while loops but no successful results.

Any ideas?

Thank you!

[diafol]

I can't make head nor tail of your explanation.

You want to change the status value?

UPDATE topics AS t,responses AS r SET t.status=0
WHERE t.id=r.topic_id;

REMEMBER to back up your data before experimenting with update and delete queries!

[KriBez]

Thanks for reply!

Yes I want to change the $status value in case user_id and topic_id ( in the same row) has been found on the 2nd table. Eg., user with ID=1 has published something in topic with ID=22 and because of that this user (ID=1) won`t be able to post anymore in this topic (ID=22). All others are still available for him.

[diafol]

//get $user_id
UPDATE topics AS t,responses AS r SET t.status=0
WHERE t.id=r.topic_id AND r.user_id='$user_id';

[KriBez]

Thanks. But isn`t this closing the whole topic for everyone?

The 1st database looks smth like this:

| id | heading | admin_content | status | date_closes

[diafol]

Yes of course it will because that's the way you've designed your tables.

id | heading | admin_content | status | date_closes

How do you suggest that you use 'status' to differentiate between users? You can't.

OK, so if I get this right - you want users to reply once and once only to an admin post. You want to block them from adding another entry?

If so, you don't need any messing with MySQL - Just use a select statement (using JOINS) - if the user has a reply - don't include a 'reply to this' link / reply form.

[KriBez]

Thanks for response arday!

Yes, I ment exactly that and it worked. All I needed was 3 lines of code and the thing works!!!

Thank you very much!