MYSQL/PHP related question.

[nitation]

@ Everyone

 

I have two tables in my database, the structure looks like this;

 

Main_table

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

ID primary key

name

title

 

Submain_table

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

ID primary key

name

title

MainTableID

 

The problem is, i am trying to populate data from the both tables into an HTML table.

 

I can successfully retrieve from the Main_table. The problem lies with the Submain_table. The value of "MainTableID" on the submain_table is the value of "name" on the main_table.

 

So how do i display an information in the HTML table where Submain_table.MainTableID=Main_table.name

 

Thanks in advance.

[akitchin]

you've just basically answered the question yourself:

 

SELECT main.ID, main.name, main.title, sub.ID, sub.name, sub.title FROM Main_table AS main, Submain_table AS sub WHERE sub.MainTableID=main.ID

 

this assumes that for every main table entry, there is a submain table entry.  if not, you will want to research JOINs in google for MySQL, there's a multitude of tutorials about them.

[nitation]

@ akitchin

 

Please view the attached image to get the idea on what am trying to achieve.

 

Regards

 

[attachment deleted by admin]

[akitchin]

have you tried running that query?  it SHOULD, in theory, pull up every entry in the main table along with its corresponding entry in the submain table.  the rest is just a matter of HTML formatting.  have i missed something?  is the query not pulling up what you need?

[nitation]

You got it right. The HTML formatting.

 

See this:

 

Ok, Lets say in my html table. I have the title(head) like this;

 

Data from Main_Table ->      Business            Computer          School          Hospital

 

How do i add the information containing Business, Computer, School and Hospital respectively from my Submain_Table.

 

Look Below

 

Business                       Computer                    School                           Hospital

 

data from submain_table          data from submain_table        data from submain_table            data from submain_table

[akitchin]

do you have multiple submain entries for each individual main entry?  if so, use one query to echo the headers, followed by a second query to retrieve the submain entries in the same order as the main query:

 

SELECT stuff FROM maintable ORDER BY ID ASC

 

followed by:

 

SELECT stuff FROM submaintable GROUP BY maintableID ORDER BY maintableID ASC

[nitation]

@akitchin

 

I developed the system like this.

 

I insert record in the maintable, then on the submaintable form there is a select option that list all the ID from maintable. I insert record to the submaintable by selecting from the select option the ID that the submaintable was meant for.

 

so what i need now is how to format my html in such a way that, if the ID on the submaintable is "business" it should provide the information under record.

 

Thanks in advance

 

 

[akitchin]

i'm not sure i understand - have you actually tried either of the methods i've suggested?  are you having specific trouble with any code that you HAVE tried?  if so, what code are you using and what's the output?

 

as far as i can tell, either of my methods should work just fine.

[nitation]

your codes are just fine. Cos i understand what is going on here. I am only stucked with the HTML format.

 

Have a look at my code:

 

<?php $Sqlcat=mysql_query("select * from southplinks_main order by id ") or die (mysql_error());
		  if(!empty($Sqlcat)){
		  while($row=mysql_fetch_array($Sqlcat)){
		  $linkname=$row["name"];
		  $id=$row["id"];

		    ?>

<td width="550" height="21" valign="top"><b><font color="#A21C04"><?php print $linkname; ?></font></b>   </td>
<?php } } ?>

 

Confirm for me the above code.

[akitchin]

Confirm for me the above code.

 

.. confirmed.  try using the above suggested methods for outputting the subentries - it looks like you haven't tried either of them.

[nitation]

I have used your code

 

<?php 
              $subcat=mysql_query("SELECT * FROM southplinks_sub GROUP BY ownerID ORDER BY ownerID ASC ") or die (mysql_error());
		  if(!empty($subcat)){
		  while($row=mysql_fetch_array($subcat)){
		  $ownerID=$row["ownerID"];
		  $name=$row["name"];
		  

		    ?>
			<td width="550" height="21" valign="top"><b><font color="#000000"><?php print $name; ?></font></b>   </td>
<?php } } ?>

 

 

 

 

[nitation]

@Akitchin,

 

Please check this for the last time.

 

I will explain my question maybe u will get it.

 

The aim is to have a Main category and a sub category. I have a table for the both categories and i can insert data onto it.

 

This is the main category structure:

 

CREATE TABLE `southplinks_main` (

  `id` int(32) NOT NULL auto_increment,

  `name` varchar(255) collate latin1_general_ci NOT NULL,

  `title` varchar(255) collate latin1_general_ci NOT NULL,

  `DataInsertDate` date NOT NULL,

  `DataUpdateDate` date NOT NULL,

  `status` int( NOT NULL,

  PRIMARY KEY  (`id`)

 

The subcategory looks like this:

 

CREATE TABLE `southplinks_sub` (

  `id` int(32) NOT NULL auto_increment,

  `name` varchar(255) collate latin1_general_ci NOT NULL,

  `title` varchar(255) collate latin1_general_ci NOT NULL,

  `DataInsertDate` date NOT NULL,

  `DataUpdateDate` date NOT NULL,

  `status` int( NOT NULL,

  `ownerID` varchar(255) collate latin1_general_ci NOT NULL,

  PRIMARY KEY  (`id`)

 

As i said earlier i can add the main category and it works fine. To add the subcategory, i created a select option which populate the main category name. Then i insert it to the database. "Observe the ownerID in subcategory table".

 

Finally, i want to display each subcategory that belong to the main category using HTML TABLES.

[akitchin]

this code assumes you have several subentries for each main entry:

 

// setup the column order
$cols = array();

// start the header row
echo '<tr>';

// start getting the main entries
$main_resource = mysql_query('SELECT id, name, title FROM southplinks_main ORDER BY name ASC') or die(mysql_error());
while ($main_entry = mysql_fetch_assoc($main_resource))
{
  // set this column's ID
  $cols[1] = $main_entry['id'];
  
  // echo the header cell here
}

// end the header row, open the subentries row
echo '</tr><tr>';

// go through and get the subentries for each column
foreach ($cols AS $main_id)
{
  $sub_resource = mysql_query("SELECT id, name, title FROM southplinks_sub WHERE ownerID='$main_id' ORDER BY name ASC") or die(mysql_error());
  while ($sub_entry = mysql_fetch_assoc($sub_resource))
  {
    // echo the subentry within the cell, which should line up with the appropriate header
  }
}

// end the subentry row
echo '</tr>';

 

that should get you started.  it will get each of the main entries and let you echo a header cell for each one (just like in the code you posted above).  it stores the order of the cells just to make sure your subentries line up properly.  it then goes through each of the columns and grabs the appropriate subentries.

[nitation]

I have implemented the piece of code you provided.

 

Not working..

 

<?php
include ("connect.php");
// setup the column order
$cols = array();

// start the header row
echo '<tr>';

// start getting the main entries
$main_resource = mysql_query('SELECT id, name, title FROM southplinks_main ORDER BY name ASC') or die(mysql_error());
while ($main_entry = mysql_fetch_assoc($main_resource))
{
  // set this column's ID
  $cols[1] = $main_entry['id'];
  
  // echo the header cell here
           $Sqlcat=mysql_query("select * from southplinks_main order by id ") or die (mysql_error());
		  if(!empty($Sqlcat)){
		  while($row=mysql_fetch_array($Sqlcat)){
		  $linkname=$row["name"];
		  $id=$row["id"];


echo"<td width=\"550\" height=\"21\" valign=\"top\"><b><font color=\"#A21C04\">$linkname</font></b>    </td>";
  } } 
}

// end the header row, open the subentries row
echo '</tr><tr>';

// go through and get the subentries for each column
foreach ($cols AS $main_id)
{
  $sub_resource = mysql_query("SELECT id, name, title FROM southplinks_sub WHERE ownerID='$main_id' ORDER BY name ASC") or die(mysql_error());
  while ($sub_entry = mysql_fetch_assoc($sub_resource))
  {
    // echo the subentry within the cell, which should line up with the appropriate header

              $subcat=mysql_query("SELECT * FROM southplinks_sub GROUP BY ownerID ORDER BY ownerID ASC ") or die (mysql_error());
		  if(!empty($subcat)){
		  while($row=mysql_fetch_array($subcat)){
		  $ownerID=$row["ownerID"];
		  $name=$row["name"];
		  
			echo"<td width=\"550\" height=\"21\" valign=\"top\"><b><font color=\"#000000\">$name</font></b>   </td>";
  } }

  }
}

// end the subentry row
echo '</tr>';
?>

[nitation]

@akitchin

 

I thought of combining the tables by using the structure of the subcategory:

 

CREATE TABLE `southplinks_sub` (
  `id` int(32) NOT NULL auto_increment,
  `name` varchar(255) collate latin1_general_ci NOT NULL,
  `title` varchar(255) collate latin1_general_ci NOT NULL,
  `DataInsertDate` date NOT NULL,
  `DataUpdateDate` date NOT NULL,
  `status` int(Cool NOT NULL,
  `ownerID` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)

 

by changing ownerID to an integer value. This should refer back to the id field of the same table.  Top-level categories that have no parent (owner) will have an ownerID of 0.

 

To display, you would use a query like this:

 

SELECT * FROM MyTable ORDER BY ownerID,`name`.

 

Finally, when displaying, I can use something like shown below.  Note: This code pulls the entire resultset into an array first for easy sorting.  The same goal can be reached without that step by checking for a change in ownerID instead.

 

<?
$query = "SELECT * FROM MyTable ORDER BY ownerID,`name`";
if (!($result=mysql_query($query))) {
  // query failed.  report and...
  die();
}
$catlist=array();
while ($row=mysql_fetch_assoc($result)) {
  $catlist[$row['ownerID']][$row['id']]=$row;
}

// print each category
// I'll assume 5 columns for the table:
// name, title, DataInsertDate, DataUpdateDate, status
// $catlist[0] is all the top-level categories
echo "<table>";
foreach ($catlist[0] as $catid=>$val) {
  // $catid holds the id field
  // $val holds the rows
  // first, print the category name as an entire row
  echo "<tr><td colspan=\"5\">{$val['title']}</td></tr>";
  // check for sub-categories
  if (count($catlist[$catid])>0) {
    foreach ($catlist[$catid] as $subid=>$val2) {
      // $subid holds the id field
      // $val2 holds the row
      echo "<tr>",
           "<td>{$val2['name']}</td>",
           "<td>{$val2['title']}</td>",
           "<td>{$val2['DataInsertDate']}</td>",
           "<td>{$val2['DataUpdateDate']}</td>",
           "<td>{$val2['status']}</td>",
           "</tr>";
    }
  } else {
    echo "<tr><td colspan=\"5\">No sub-categories</td></tr>";
  }
  // blank row for a separator
  echo "<tr><td colspan=\"5\"> </td></tr>";
}
echo "</table>";
?>

 

What am stucked with is, how to insert the mainCategory and SubCategory into one table.