PHP/MySQL: Need help getting variable

[Gibbs]

I have a basic Private Message system working on my site. However i'm having problems with PHP getting the mail_id information from MySQL.

If I do this I can get the mail ID:
[code]$result=mysql_query("select * from $table where UserTo='$cookie' ORDER BY SentDate DESC") or die ("ERROR: PLEASE REPORT THIS");
while ($row=mysql_fetch_array($result))
{
echo $row[mail_id];
[/code]

However i'm trying to get the mail_id in a different part and am finding it difficult. When somebody selects a link this is used:
[code]$result =mysql_query("select * from $table where UserTo='$cookie' and mail_id='[mail_id]'") or die ("cant do it1");
//echo mysql_num_rows($result);
$row = mysql_fetch_array($result);
if($row[UserTo]==$cookie) [/code]

However the mail_id isn't recognised like previously. Is there a way to use the code at the top to get the mail_id to work here?

Any help appreciated. Thanks

[paul2463]

if you are pulling the mail_id from the first query and using it in the second query then you have to assign it to a variable and use it that way such as
[code]
<?php
$result=mysql_query("select * from $table where UserTo='$cookie' ORDER BY SentDate DESC") or die ("ERROR: PLEASE REPORT THIS");
$row=mysql_fetch_array($result);
$mailId  = $row[mail_id];
$result =mysql_query("select * from $table where UserTo='$cookie' and mail_id='$mailId'") or die ("cant do it1");
//echo mysql_num_rows($result);
$row = mysql_fetch_array($result);
if($row[UserTo]==$cookie)
?>
[/code]

[Gibbs]

I'm still confused. I've tried this (i'll post the entire part as it may make more sense).

[code=php:0]$result=mysql_query("select * from $table where UserTo='$cookie' ORDER BY SentDate DESC") or die ("ERROR: PLEASE REPORT THIS");
echo "<table cellpadding=2 cellspacing=1 width=400 valign=top>";
$row = mysql_fetch_array($result);

$messageid = $row[mail_id]; // mail_id

while ($row=mysql_fetch_array($result))
{
echo "<tr><td width=30><p>Mail:</td><td><a href=pm.php?action=view&mail_id=$row[mail_id]>
$row[Subject]
</a></td><td width=50> <a href=pm.php?action=delete&id=$row[mail_id]>
<center>Delete
</a><br></td></tr>";
}
echo "</table>";
}


if($action==view)
{
$result = mysql_query("select * from $table where UserTo='$cookie' and mail_id='$messageid'") or die ("cant do it1");
$row = mysql_fetch_array($result);
if($row[UserTo]==$cookie)
{
}
else
{
echo "<p class=\"error\"><b>This isn't your mail!</b></p>" .$row;
exit;[/code]

[paul2463]

so if they click on view they are sent away and back to this page by the
[code]
<?php
echo "<tr><td width=30><p>Mail:</td><td><a href=pm.php?action=view&mail_id=$row[mail_id]>
$row[Subject]
</a></td><td width=50> <a href=pm.php?action=delete&id=$row[mail_id]>
<center>Delete
</a><br></td></tr>";
?>
[/code]
then you can use the mail_id in the url for the second query
you would need

[code]
<?php
if($action==view)
{
if(isset($_GET['mail_id'])) { $mailId = $_GET['mail_id']; }
$result = mysql_query("select * from $table where UserTo='$cookie' and mail_id='$mailId'") or die ("cant do it1");
$row = mysql_fetch_array($result);
if($row[UserTo]!=$cookie)
{
echo "<p class=\"error\"><b>This isn't your mail!</b></p>" .$row;
exit;
}
?>
[/code]

[paul2463]

are you also getting the $action variable set from the URL??
such as
[code]
if(isset($_GET['action'])) { $action = $_GET['action']; }
if ($action = "view")
{
blah
[/code]

[Gibbs]

It's all done in the same page. I'm getting action using

[code=php:0]<?php
$action = $_GET['action'];[/code]

[paul2463]

then you can get the mail_id for the second bit by the same method as you pass the mail_id in the URL as well
<php
$mailId = $_GET['mail_id'];
?>

then use that in the query

[Gibbs]

So simple! I can't believe that  ::)

Thanks for your help! :P