[SOLVED] Query with multiple WHERE statements?

[Darkmatter5]

I need to have a query run inside a while loop that joins data from two tables depending on what the the current item_id is.

 

Table examples:

Cabinets

 

cabinet_id

 

cab_name

 

1

 

investments

 

2

 

documents

 

Folders

 

folder_id

 

fol_name

 

cabinet_id

 

1

 

stocks

 

1

 

2

 

word docs

 

2

 

3

 

spreadsheets

 

2

 

Items

 

item_id

 

item_name

 

folder_id

 

1

 

Stock 1

 

1

 

2

 

letter

 

2

 

3

 

letter 2

 

2

 

4

 

budget

 

3

 

Now here's my function code:

function show_filing() {
  include 'library/config.inc.php';
  $conn=mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql');
  mysql_select_db($dbnamemain);
            
  $query=mysql_query("SELECT *
        FROM items
        ") or die(mysql_error());
  echo "<p>
        <table width='100%' border='0' cellspacing='1' cellpadding='1'>
        <th width='200'>PATH</th>
        <th width='200'>NAME</th>
        <th>DESCRIPTION</th>
        <th width='70'>ACTION</th>";
            
  while($row=mysql_fetch_array($query)) {
        $folcab_query=mysql_query("SELECT folders.fol_name, cabinets.cab_name
            FROM folders, cabinets
            WHERE folders.cabinet_id=cabinets.cabinet_id
            ") or die(mysql_error());
        $row1=mysql_fetch_array($folcab_query);
        echo "<tr>
            <td>$row1[cab_name] -> $row1[fol_name] -> </td>
            <td>" .substr($row['item_name'],0,100). "</td>
            <td>$row[item_description]</td>
            <td align='center'>Delete | Edit</td>
            </tr>";
  }
  echo "</table>";
            
  mysql_close($conn);
}

 

I need for example if the item the while loop is on is the first item I need it to get

 

item_id: 1

item_name: Stock 1

folder_id: 1

 

And then also with the query inside the while statement to get

 

fol_name: stocks

cab_name: investments

 

The first time it runs it gets the correct fol_name and cab_name, but the fol_name and cab_name variable don't change whith the item_id.  How can I make the second run through result in

 

item_id: 2

item_name: letter

folder_id: 2

fol_name: word docs

cab_name: documents

 

then third, fourth, etc...?

 

Thanks!

[xtopolis]

It's hard to understand what you want based on your description.  From what it seems like, you want to display a path listing , item name, description:    based off of looking up all the items in your table.

 

IF THIS IS NOT WHAT YOU WANT, explain more clearly and ignore the rest.  Otherwise read on.

 

Otherwise, try this code, and see the results.  (I left out item_description because it was not in your supplied tables)

<?php
/*
Your config stuff here
*/

  $query=mysql_query("SELECT c.cab_name as cabinet, 
f.fol_name as folder, 
i.item_name as item
FROM items i
JOIN folders f ON(i.folder_id=f.folder_id)
JOIN cabinets c ON(c.cabinet_id=f.cabinet_id)
") or die(mysql_error());
  echo "<p>
        <table width='100%' border='0' cellspacing='1' cellpadding='1'>
        <th width='200'>PATH</th>
        <th width='200'>NAME</th>
        <th>DESCRIPTION</th>
        <th width='70'>ACTION</th>";

        while(list($cab,$fol,$item)=mysql_fetch_array($query)){
        echo "<tr>
            <td>$cab -> $fol -> </td>
            <td>" .substr($item,0,100). "</td>
            <td>Descr</td>
            <td align='center'>Delete | Edit</td>
            </tr>";
       }
  echo "</table>";
?>

Outputs: (something similar, but not as nice looking)

[pre]

PATH                            NAME   DESCRIPTION ACTION     

investments -> stocks ->        Stock 1  Descr          Delete | Edit

documents -> word docs ->        letter    Descr          Delete | Edit

documents -> word docs ->        letter 2  Descr          Delete | Edit

documents -> spreadsheets ->    budget    Descr          Delete | Edit

[/pre]

[Darkmatter5]

That worked perfectly! Thanks! Infact it made me understand joining and queries alot better.  I never knew you could alias the field names.

[xtopolis]

Glad it worked

Topic Solved this bad boy.

[Darkmatter5]

How do I mark it as solved?

[xtopolis]

should be a button at the bottom left for you.