Show a category using an array

[dropbop]

Hi, I have been working on a directory/cms script (first large script to build from scratch).

 

I am stuck on a small part of it where is shows users a list of the listings they have put in the directory.

 

Below is the code that generates the table that shows the list of listings which is pulled from the 'listings' table in my database.

 

<?php 
### List of listings user has posted ###
if(isset($_SESSION['userlogged'])) {
$user_id = $_SESSION['userlogged'];

$sqlCommand = "SELECT listing_id, cat_id, listing_title, listing_date, showing FROM listings WHERE showing='0' AND user_id='$user_id' ORDER BY listing_id ASC"; 
$query = mysql_query($sqlCommand,$con) or die (mysql_error()); 

$ListingDisplay = '';
while ($row = mysql_fetch_array($query)) { 
    $listing_id = $row["listing_id"];
$cat_id = $row['cat_id'];

if ($cat_id == '1') { 
		$cat_name = "Carpenters";
	}
	if ($cat_id == '2') {
		$cat_name = "Stone Masons";
	}
	if ($cat_id == '3') {
		$cat_name = "Plumbers";
	}
	if ($cat_id == '4') {
		$cat_name = "Landscapers";
	}

$listing_title = $row["listing_title"];
$listing_date = $row["listing_date"];

$ListingDisplay .= '
<tr class="listingaccount">
<td><a href="edit_listing.php?lid=' . $listing_id . '">' . $listing_id . '</a></td>
<td>' . $cat_name . '</td>
<td>' . $listing_title . '</td>
<td>' . $listing_date . '</td>
<td><a class="hoverBtn" href="edit_listing.php?lid=' . $listing_id . '"><img src="images/btnedit.png" border="0"></a></td>
<td><a class="hoverBtn" href="remove_listing.php?lid=' . $listing_id . '"><img src="images/btndelete.png" border="0"></a></td>
</tr>';

} 
mysql_free_result($query); 

?>

<table width="680" border="0" align="right" cellpadding="0" cellspacing="0">
  <tr>
    <td colspan="2"><h2>My Listings</h2></td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td><a href="account_add_listing.php">Add New Listing</a></td>
    <td> </td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td colspan="2" valign="top">
    
    
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td>Listing Code</td>
    <td>Category</td>
    <td>Title</td>
    <td>Date Entered</td>
    <td> </td>
  </tr>
  
    <?php echo $ListingDisplay; ?>
  
</table>
    
    
    
   </td>
  </tr>
</table>
<?php } ?>

 

That all works just fine, but I would like to be able to generate the categories automatically. I am thinking some sort of array, but im not quite sure how to go about it and was hoping maybe someone could give me a starting point I could work from.

 

My categories table is like this:

 

--------------------------

| cat_id | cat_name |

--------------------------

 

Many thanks

 

Eoin

 

[scootstah]

So you need to use a JOIN then.

 

$sqlCommand = "SELECT listing_id, listing_title, listing_date, showing, categories.cat_id, categories.cat_name FROM listings JOIN categories ON categories.cat_id = listings.cat_id WHERE showing='0' AND user_id='$user_id' ORDER BY listing_id ASC"; 

 

Then you can just do

while ($row = mysql_fetch_array($query)) { 
echo $row['cat_name'];
}

[dropbop]

So you need to use a JOIN then.

 

$sqlCommand = "SELECT listing_id, listing_title, listing_date, showing, categories.cat_id, categories.cat_name FROM listings JOIN categories ON categories.cat_id = listings.cat_id WHERE showing='0' AND user_id='$user_id' ORDER BY listing_id ASC"; 

 

Then you can just do

while ($row = mysql_fetch_array($query)) { 
echo $row['cat_name'];
}

 

That works perfectly! thank you very much... I was initially trying to use JOIN, but from looking at your one, mine was all wrong.. but now I know how to do it properly.

 

I did get an error though, which after googleing it, the error was saying there were 2 columns with 'showing',

 

Column 'showing' in field list is ambiguous

 

So just in case anyone else gets the same error after using a JOIN, it means there is a column in each table with the same name.

 

I haven't tried it yet, but to get around it, you can put the table name before the column name, something like

 

SELECT table1.showing, table2.showing......

 

please correct me if I'm wrong... I'm still learning myself.

 

Anyway, thanks again scootsha.. problem solved!

[scootstah]

So you need to use a JOIN then.

 

$sqlCommand = "SELECT listing_id, listing_title, listing_date, showing, categories.cat_id, categories.cat_name FROM listings JOIN categories ON categories.cat_id = listings.cat_id WHERE showing='0' AND user_id='$user_id' ORDER BY listing_id ASC"; 

 

Then you can just do

while ($row = mysql_fetch_array($query)) { 
echo $row['cat_name'];
}

 

That works perfectly! thank you very much... I was initially trying to use JOIN, but from looking at your one, mine was all wrong.. but now I know how to do it properly.

 

I did get an error though, which after googleing it, the error was saying there were 2 columns with 'showing',

 

Column 'showing' in field list is ambiguous

 

So just in case anyone else gets the same error after using a JOIN, it means there is a column in each table with the same name.

 

I haven't tried it yet, but to get around it, you can put the table name before the column name, something like

 

SELECT table1.showing, table2.showing......

 

please correct me if I'm wrong... I'm still learning myself.

 

Anyway, thanks again scootsha.. problem solved!

 

That's correct. You can also use aliases on the tables so you don't have to type the whole table name out every time.

 

SELECT t1.showing, t2.showing FROM table1 AS t1 JOIN table2 AS t2 ON .....