The reason that it's failing is that you are not using [MAN]mysql_fetch_array[/MAN] correctly. You are assuming that it's going to return 2,1,3 but actually, this command:
$poster_rows = mysql_fetch_array($poster_result);
is only obtaining the first of the three rows of data. You need to use a while statement to make that line execute over and over again until there are no more lines to return. So this should make your code work correctly (if you really mean to use mysql_fetch_array):
$poster_query = "SELECT userid from discussion WHERE id = 1";
$poster_result = mysql_query($poster_query) or die(mysql_error());
while ($poster_row = mysql_fetch_array($poster_result)) {
echo $poster_row[0];
echo "<br />\n";
}
Sorry, that's just how PHP works.
But I don't think you care whether or not you use that specific command - you just want to get the data... so I'd actually use this code to do what you're trying to do:
$poster_query = "SELECT userid from discussion WHERE id = 1";
$poster_result = mysql_query($poster_query) or die(mysql_error());
while (list($poster_row) = @mysql_fetch_row($poster_result)) {
echo $poster_row;
echo "<br />\n";
}
So I'm using mysql_fetch_row to obtain one row at a time, just like mysql_fetch_array is... but I'm extracting it into a simple variable instead of an array.
But all of that aside.... I think you're trying to do the right programming based on a bad foundation. It's good that you want to create a separate table that connects discussions with users but you're doing it based on having already made the IT_DISCUSSION table which has too much data.
Here's how I'd do it: First, Leave IT_DISCUSSION alone. It's doing just fine. You need the redundant data in there. (That is, you need each entry in that table to mention the userid so that you know which post was written by which user.)
Second, add this:
make a table, call it something like discussion_user_xref. That is, this table exists for the sole purpose of cross referencing disucssions and users.
Whenever someone makes a post to a certain discussion and you add the post to IT_DISCUSSION, then perform the following commands:
$query = "delete from discussion_user_xref where discussion='$discussion_id' and user='$user_id'";
$result = MYSQL_QUERY($query);
$query = "insert into discussion_user_xref (discussion_id,user_id) values ('$discussion_id','$user_id')";
$result = MYSQL_QUERY($query);
This way, if the user has already posted to this discussion, the first query will delete any previous entries in the cross reference table. Then the second command will add that user to the table. The two commands together will make sure that each user is only in the table once for that discussion.
Now when you want to send email to everyone involved in a certain discussion, you simply say:
select user_id from discussion_user_xref where discussion_id='$discussion_id'";
Actually, if you wanted to be really clever, you could get their email addresses from the other table like this:
select users.email from users,discussion_user_xref where discussion_user_xref.discussion_id='$discussion_id' and users.id=discussion_user_xref.user_id
This is called a join. You are sort of doing two queries at once. First you are saying, FIND all the user_id's of the people involved in this discussion... but GIVE ME the email address from the users table that is associated with the user_id's that you just got from the list of people involved in that discussion.
So to recap: There's no benefit to making the IT_DISCUSSION table and then having to repeatedly cull through it to make a new table that just connects users to discussions (especially if that table is going to list the same user more than once!) The correct way to do it is to maintain two tables simultaneously. Whenever someone makes a post to one table, you add their user_id to the other table (after you deleted any such entries first to make sure that each discussion/user entry is unique).
Then you get an A+
I've thrown a few different concepts at you but you sound like you know what you're doing so none of these are rocket science - it's just a cleaner methodology and it will pay off when you have 20,000 discussions and 50,000 users. Learning the right way to handle the big leagues now (when you're doing small sites) guarantees that you'll be able to get to the big leagues. (I'm mixing metaphores - sports and academics. It's been a long day).
