Trouble with running 2 queries

[blasterstudios]

OK. Here's what i'm trying to do:
I have 2 tables in my database. One is Messages, the other is Followups.

What I'm trying to do is pull all the rows in Messages that have associated rows in the Followups table that the 'read' column is marked "N". I'm running 2 queries for this, and somewhere inside them it's not working.

My first query pulls all the rows from the Followups table that are:
1. Owned by the currently logged in User
2. Show a "N" in the Read field.
3. I believe my problem lies somewhere in the SELECT DISTINCT statement, but i'm not sure how to go about doign this. Here is the query:

$colname_pullunreadfollowups = "1";
if (isset($_SESSION['owneruser'])) {
$colname_pullunreadfollowups = (get_magic_quotes_gpc()) ? $_SESSION['owneruser'] : addslashes($_SESSION['owneruser']);
}
mysql_select_db($database_bsaccounts, $bsaccounts);
$query_pullunreadfollowups = sprintf("SELECT DISTINCT messageid, owneruser, `read` FROM followups WHERE owneruser = '%s' AND `read` = 'N'", $colname_pullunreadfollowups);
$pullunreadfollowups = mysql_query($query_pullunreadfollowups, $bsaccounts) or die(mysql_error());
$row_pullunreadfollowups = mysql_fetch_assoc($pullunreadfollowups);
$totalRows_pullunreadfollowups = mysql_num_rows($pullunreadfollowups);

Then, I'm using this code, i don't know why, i found it somewhere on this forum, and it may be part of the problem:

while ($row_pullunreadfollowups) {
$msgid[] = $row['messageid'];
}
$msgid = array($msgid);

Lastly, my 2nd query pulls the topics from the Messages table that match the messageids found with unread followups:

$colname_pullunreads = "1";
if (isset($_SERVER['msgid'])) {
$colname_pullunreads = (get_magic_quotes_gpc()) ? $_SERVER['msgid'] : addslashes($_SERVER['msgid']);
}
mysql_select_db($database_bsaccounts, $bsaccounts);
$query_pullunreads = sprintf("SELECT messageid, `date`, subject FROM messages WHERE messageid = %s", $colname_pullunreads);
$pullunreads = mysql_query($query_pullunreads, $bsaccounts) or die(mysql_error());
$row_pullunreads = mysql_fetch_assoc($pullunreads);
$totalRows_pullunreads = mysql_num_rows($pullunreads);

In case you didn't figure it out, I'm using Dreamweaver.

At what point is this not working? My result shows up as only 1 message. I know there is 2 in database (there is 1 unread Followup for 2 different Messages).

Thanks for the help.

 

[DRJ478]

This code only checks if the row is set.

while ($row_pullunreadfollowups) {
  $msgid[] = $row['messageid'];
}

The logic is flawed. The first row is retrieved by

$row_pullunreadfollowups = mysql_fetch_assoc($pullunreadfollowups);

That is not necessary. What you want to do is to retrieve the rows in the loop;

while ($row_pullunreadfollowups = mysql_fetch_assoc($pullunreadfollowups)){
   $msgid[] = $row_pillunreadfollowups['messageid'];
}

That will retrieve all rows.

 

[blasterstudios]

so what you're saying is, replace this text:

while ($row_pullunreadfollowups) {
  $msgid[] = $row['messageid'];
}

with this text:

while ($row_pullunreadfollowups = mysql_fetch_assoc($pullunreadfollowups)){
   $msgid[] = $row_pillunreadfollowups['messageid'];
}

and get rid of this text:

$row_pullunreadfollowups = mysql_fetch_assoc($pullunreadfollowups);

Sorry, I don't understand what you're telling me in this post.

 

[DRJ478]

A MySQL query return a "resource identifier", which is an identifier that allows for retrieval of the individual rows of the result set.
In your code the next line (after mysql_query()) already retrieves the first of the rows - prematurely.
The next line checks out how many rows altogether were found.
Further down your code has a while loop which has a statement that is always true (or false depending on the query outcome) and an assignment in the loop that assigns something from a variable ($row) that isn't even initialized.

Solution:
Forget Dreamweaver, start writing your own code. It is at least wasteful trying to iron out the kinks this convoluted code has. Don't blindly believe in the technology - it is important that you understand what is going on and how it works (or in thi s case doesn't).

mysql_select_db($database_bsaccounts, $bsaccounts) OR die("Select DB error: ".mysql_error());
$query_pullunreadfollowups = sprintf("SELECT DISTINCT messageid, owneruser, `read` FROM followups WHERE owneruser = '%s' AND `read` = 'N'", $colname_pullunreadfollowups);
# Execute the query
$pullunreadfollowups = mysql_query($query_pullunreadfollowups, $bsaccounts) or die(mysql_error());
# look how many rows retrieved (superfluous)
$totalRows_pullunreadfollowups = mysql_num_rows($pullunreadfollowups);
# iterate the result set and gather the messageids
while ($row_pullunreadfollowups = mysql_fetch_assoc($pullunreadfollowups);
) {
  $msgid[] = $ow_pullunreadfollowups['messageid'];
}

You will end up with an array $msgid that holds all the id's retrieved through the first query.
The second query should iterate the array or concatenate the values to retrieve the rows.

However, it seems to me since the two tables are linked by a foreign key (the messageid) you could use a LEFT join to retrieve the needed information in a single query.