[RESOLVED] Accessing Query Data From Query Against Multiple Tables

[rsmith]

Hey all, here's my question. I have a query that gets info from two tables in my database. How do I access the data from the query? I know how to use the mysql_fetch_assoc() when the query in question only pulls information from a single table. How do I do the same thing for multiple tables and then insert the necessary data into my page? 😕

Here's my query

$query = "SELECT * FROM customers, service WHERE customers.id = service.cust_id AND service.active = 1 ORDER BY service.id DESC";
	    	$result = mysql_query($query);

I have some fields that appear in both tables but are specific to that table. I still need to be able to access all the information for each table independently. Here are the structures for each:

Service:
id, cust_id, make, model, equipment, issue, tnotes, tcomments, status, lcost, pcost, entered, estimate, date_needed, modified, modified_by, active

Customers
id, fname, lname, address, city, state, zip, category, business, hphone, wphone, mphone, email, entered, modified, modified_by

Thanks in advance!

[CanMike]

Here a small piece of code from a family website that as you can see requires data from two tables 'rights' and 'family_rank' then uses the results from the 'SELECT' statement to modify (via an 'UPDATE' statement) the rights table.

The variable '$fid' is retrieved from a HTML <form>.

       $query = "SELECT registered AS reg,
                     cname      AS name
                FROM rights, family_rank
               WHERE rights_id = '$fid'
                 AND family_id = '$fid'";

$result = mysql_query($query);

if (mysql_num_rows($result) != 1)
{
  $error = 'Invalid Family Id!<br />';
}
elseif (mysql_num_rows($result) == 1)
{
  $row = mysql_fetch_array($result, MYSQL_ASSOC);
  if ($row['reg'] == 'n')
  {
    $db_name = $row['name'];
    $query= "UPDATE rights
             SET registered='y', password=PASSWORD('$p'), email='$e'
             WHERE rights_id = $fid";

    $result = @mysql_query ($query); // Run the query.

    if ($result)
    {
      // set cookie to expire in 5 years
      $_SESSION['logged'] = 'yes';
      $_SESSION['logged_name'] = $db_name;

      $body ="Thank-you for registering with the family site.!  \n
              Your family id is           '{$_POST['family_id']}'. \n
              Your registered password is '{$_POST['password1']}'.\n
              Your registered email is    '{$_POST['email1']}'.\n
 

Does this help?