Multiple Query From Different Rows

Warning: Unable to jump to row 1 on MySQL result

[brodie_r]

What i have been trying to do, is i have a page called service, and it displays a few details from the service column, in this table in each row it has a user_id, which links back to the users table.
===============
<?php
require_once ('mysql_connect.php');

$query ="SELECT * FROM service ORDER BY date_booked DESC";
$result = mysql_query($query);

if (!$result)
die(mysql_error());
while ($r = mysql_fetch_array($result)) {
extract($r);

$tech_name=mysql_result($result,$i,"tech_name");
$user_id2=mysql_result($result,$i,"user_id");
$fault=mysql_result($result,$i,"fault");
$resolution=mysql_result($result,$i,"resolution");
$charge=mysql_result($result,$i,"charge");
$status=mysql_result($result,$i,"status");
$note=mysql_result($result,$i,"note");
$date_complete=mysql_result($result,$i,"date_complete");
?>
<?php
require_once ('mysql_connect.php');

$query2 ="SELECT * FROM users WHERE user_id='$user_id2'";
$result2 = mysql_query($query2);

$user_id=mysql_result($result2,$i,"user_id");
$first_name=mysql_result($result2,$i,"first_name");
$last_name=mysql_result($result2,$i,"last_name");
$phone=mysql_result($result2,$i,"phone");
$mobile=mysql_result($result2,$i,"mobile");
$note=mysql_result($result2,$i,"note");
$email=mysql_result($result2,$i,"email");
?>
===============

So far my query Works for only the first results, displaying both the users first/last name, and the details corresponding to him, but it does not work for any of the other results. I recieve the error:

=======
Warning: Unable to jump to row 1 on MySQL result index 4 in /home/stormweb/public_html/x14/view_service3.php on line 41
=======
If anyone has any ideas? or know what i am doing wrong. If any other information is needed please just ask. I appreciate your help! Thanks

[ScriptReference]

Well, here are some things I noticed:

Your while loop isn't closed.
$i is used, but never declared or incremented.
$i is used to access the elements of both queries.
There's no point requiring once the same file twice.

Have you thought of a join? E.G.

SELECT s.*,u.* FROM service s INNER JOIN users u on u.user_id=s.user_id ORDER BY s.date_booked DESC

That should get all of the information in a single query. HOWEVER, watch out for extract if columns in the two tables have the same name.

[brodie_r]

Script Reference, thankyou very much i was able to get it working from the JOIN query. As for the loops, it is closed outside of that which i didnt paste. For others that need to reference to this:
======
<table border="0" cellspacing="3" cellpadding="2" align="left">
<tr>
<th><div align="left"><font face="Arial" size="2">Date Booked        </font></div></th>
<th><div align="left"><font face="Arial" size="2">Customer Name        </font></div></th>
<th><div align="left"><font face="Arial" size="2">Tech Name       </font></div></th>
<th><div align="left"><font face="Arial" size="2">Status       </font></div></th>
</tr>
<?php
require_once ('mysql_connect.php');

$query ="SELECT s.*,u.* FROM service s INNER JOIN users u on u.user_id=s.user_id ORDER BY s.date_booked DESC ";
$result = mysql_query($query);

if (!$result)
die(mysql_error());
while ($r = mysql_fetch_array($result)) {
extract($r);

$tech_name=mysql_result($result,$i,"tech_name");
$date_booked=mysql_result($result,$i,"date_booked");
$status=mysql_result($result,$i,"status");
$note=mysql_result($result,$i,"note");
$first_name=mysql_result($result,$i,"first_name");
$last_name=mysql_result($result,$i,"last_name");
?>
<span class="style3">
<tr>
<td><font face="Arial" size="1"><b><?php echo $date_booked;?></b></font></td>
<td><font face="Arial" size="1"><?php echo $last_name.", ".$first_name;?></font></td>
<td><font face="Arial" size="1"><?php echo $tech_name;?></font></td>
<td><font face="Arial" size="1"><?php echo $status;?></font></td>
<td><font face="Arial" size="1"><a href="more.php?id=<?php echo $user_id;?>">View Member</a>   </font></td>
<td><font face="Arial" size="1"><a href="more_service.php?id=<?php echo $service_id;?>">View Report</a>   </font></td>
<td><font face="Arial" size="1"><a href="print_service.php?id=<?php echo $service_id;?>">Print Report</a>   ¦</font></td>
<td><font face="Arial" size="1"><a href="amend_service.php?id=<?php echo $service_id;?>">Edit Report</a></font></td>
</tr>
</span>
<?php
$i++;
}
echo "</table>";
?>
======