Hi all,

I have been trying to google this for an hour with no luck. I want to use multiple mysql_query 's to grab data so I can output it into a table.

I have had a look at Union, but I am unsure of how to use it? I have also looked at joins but they do not apply to my situation.

Basically I have three columns in a html table I want to populate.

ID Category Subcategory

1 Heading List of subcategories from table subcategorya
2 Heading List of subcategories from table subcategoryb

etc

Category and heading come from the same table called Category. But the subcategory's come from four different tables labeled:

  • subcategorya

  • subcategoryb

  • subcategoryc

  • subcategoryd

So basically I want to select data from all these tables including the category table and then make a while loop out of it to display the data.

How do i do this!? It's driving me mad! 😃

Thanks!

    what do you mean by:

    1 Heading List of subcategories from table subcategorya
    2 Heading List of subcategories from table subcategoryb

    If I understand correctly you only have a table with three fields that you want to populate. Furthermore, you need to tell us how these different tables relate to each other, if you were to simply paste the DDL (sql script source code) here, that would help. But more importantly you have to know what you are doing at the time you plan your tables, you must think about how you plan to relate and present the data those tables will eventually contain. Then you must apply the principles of database normalization. In other words, database queries can't perform miracles on a poorly designed database. Take your time to build a solid database that meets your business requirements first, then you can turn to joins.

      Thanks for the reply. I have had a quick look over the link. I am using ID's in each mysql table.

      Basically for every field in the mysql category table, I want to display all results from another field in a different mysql table. And I want to do this four times.

      My site is a review site. I have four main categories, and subcategories for the main ones. My whole site, add review forms, navigation etc links to one php file called "cats.php" which defines the categories by variables. And cat.php is included into the navigation page, the form page etc.

      To manage cats.php I want to put the categories into the database base, define whats in the DB as variables for cats.php and make a management page which I can view/edit/delete/add categories. The management page is what I am struggling with, as I want to build a html table to view all the categories.

      In mysql I have a mysql table for category (the main categories). And four seperate mysql tables for sub categories.

      So when I build my html table, I want to display all the contents of one subcategory table to every category field. Here is what I have so far...

      


<html>
<body>

<table border="1" align="center">
	<tr>
	    <td>ID</td>
			<td>Category</td>
			<td>Sub cats</td>
			<td>Edit</td>
	</tr>

<?php

include 'mysql.php';

$category = mysql_query("select * from category") or die (mysql()); 

while ($row = mysql_fetch_array($category))
{

  $id = $row['id'];  
      
	$edit = "editcats.php?id=$id";

?>

	<tr>
	<td>
	<?php echo $id; ?>
	</td>
<td>
   <?php echo $row['category']; ?>
</td> 
<td>
<?php
if($id == "1")
{
echo 'contents of subcategorya';
}
else if($id == "2")
{
echo 'contents of subcategoryb';
}
?>
  </td>
		<td>
			<center>
			<form action="<?php echo $edit; ?>" method="POST">
      <input type="image" src="edit.gif">
      </center>
    </form>		
    </td>
	</tr>



<?php

    }


mysql_free_result($database);

include 'closemysql.php';

?>
</table>
</body>
</html>

      Thanks 🙂

        I now understand what I need to do, after doing some reading on indexes.

        I have adjusted my mysql database as follows. I have one category table...

        
id      category

1       example 1
2       example 2
3       example 3
4       example 4

        I have one subcategory table...

        cat_id    subcategory

1           sub 1
1           sub 2
3           sub 3
4           sub 4
4           sub 5
4           sub 6

        So....

        Subs 1 + 2 are sub categories of example 1.

        Sub 3 is a sub category of example 3.

        Subs 4, 5 + 6 are sub categories of example 4 and so on.

        cat_id and id relate to each other. I have called this in the database and displayed the results...

        

<html>
<body>

<table border="1" align="center">
	<tr>
	    <td>ID</td>
			<td>Category</td>
			<td>Subcategorys</td>
			<td>Edit</td>
	</tr>

<?php

include 'mysql.php';

$category = mysql_query("select * from category, subcategory where category.id = subcategory.cat_id ORDER BY id") or die (mysql()); 


while ($row = mysql_fetch_array($category))
{

$id = $row['id'];
$edit = "editcats.php?id=$id";

?>

	<tr>
	<td>
	<?php echo $id; ?>
	</td>
<td>
<?php echo $row['category']; ?>
</td> 
<td>
<?php echo $row['subcategory']; ?>
  </td>
		<td>
			<center>
			<form action="<?php echo $edit; ?>" method="POST">
      <input type="image" src="edit.gif">
      </center>
    </form>		
    </td>
	</tr>



<?php

    }


mysql_free_result($database);

include 'closemysql.php';

?>
</table>
</body>
</html>

        This works like a charm 🙂 But using this code, it lists everything. Using the example above and my code this will result in something like this...

        
------------------------------------
category    subcategory
------------------------------------
example 1    sub 1
------------------------------------
example 1    sub 2
------------------------------------
example 3    sub 3
------------------------------------
example 4    sub 4
------------------------------------
example 4    sub 5
------------------------------------
example 4    sub 6
------------------------------------

        etc

        How do I make it so it will just show one heading from category and list the subcategories? like this...

        
----------------------------------
category     subcategory
--------------------------------
example 1       sub 1
                       sub 2
----------------------------------
example 2      No subcategories
----------------------------------
example 3       sub 3
----------------------------------
example 4       sub 4
                       sub 5
                       sub 6
---------------------------------

        Hope it makes sense! :p

          Write a Reply...