Need to create tables from querry

[Tom101]

I am trying to create seperate tables from this. Maybe a div class will work. I want each cat variable 'catname' to be in seperate tables at the top of each table with the subcats for that cat in 1 or 2 rows under the cat. I want to be able to also choose whether to use 1 or 2 table rows for the subcats under the cat. any ideas? Will appreciate your help.

 

 

 

// Categories
$sql = "SELECT catid, catname AS catname FROM $t_cats WHERE enabled = '1' $sortcatsql";
$rescats = mysql_query($sql) or die(mysql_error());
$catcount = @mysql_num_rows($rescats);

$percol_short = floor($catcount/$dir_cols);
$percol_long = $percol_short+1;
$longcols = $catcount%$dir_cols;

$i = 0;
$j = 0;
$col = 0;
$thiscolcats = 0;

while($rowcat=mysql_fetch_array($rescats))
{
if ($j >= $thiscolcats)
{
	$col++;
	$thiscolcats = ($col > $longcols) ? $percol_short : $percol_long;
	$j = 0;

	echo "<td valign=\"top\" width=\"$cell_width%\">";
}

$i++;
$j++;


    $catlink = buildURL("ads", array($xcityid, $rowcat['catid'], $rowcat['catname']));


$adcount = 0+$catadcounts[$rowcat['catid']];

?>

<table border="0" cellspacing="0" cellpadding="0" width="100%" class="dir_cat">
<tr>
<th width="25" valign="top"><img src="images/category.gif" border="0" align="absmiddle"></th>
<th><a href="<?php echo $catlink; ?>"><?php echo $rowcat['catname']; ?></a>
<?php if($show_cat_adcount) { ?><span class="count">(<?php echo $adcount; ?>)</span><?php } ?>
</th>
</tr>

<?php

$sql = "SELECT scat.subcatid, scat.subcatname AS subcatname
FROM $t_subcats scat
WHERE scat.catid = $rowcat[catid]
	AND scat.enabled = '1'
$sortsubcatsql";

$ressubcats = mysql_query($sql) or die(mysql_error()."<br>$sql");

$subcatcount = mysql_num_rows($ressubcats);


while ($rowsubcat = mysql_fetch_array($ressubcats))
{

    if ($shortcut_categories && $subcatcount == 1 
            && $rowsubcat['subcatname'] == $rowcat['catname']) {
        continue;
    }

    
	$adcount = 0+$subcatadcounts[$rowsubcat['subcatid']];


        $subcat_url = buildURL("ads", array($xcityid, $rowcat['catid'], $rowcat['catname'], 
            $rowsubcat['subcatid'], $rowsubcat['subcatname']));


?>
	<tr>
	<td> </td>
	<td>
	<a href="<?php echo $subcat_url; ?>"><?php echo $rowsubcat['subcatname']; ?></a>
	<?php if($show_subcat_adcount) { ?><span class="count">(<?php echo $adcount; ?>)</span><?php } ?>
	<br>
	</td>
	</tr>

<?php

}

?>

</table>
<br>

<?php

if($j==$thiscolcats || $i==$catcount) echo "</td>";

}


?>

</tr></table>

 

 

[Psycho]

It's late so I am not going to read through all of your code. But, I think you are making this more difficult than it needs to be. First, and foremost, you only need to run one query with a JOIN to get the data you need. Running queries in loops is a bad idea.

 

You don't state "how" you want to select if there are two rows for the subcategories or not - either specifically set or dynamically determined.

 

Here is some pseudo code to help you on your way

//Function to display each category with the subcats
function createTable($categoryName, $subCatAry, $subCatRows=1)
{
    echo "<table>\n";
    echo "<tr><th>{$categoryName}</th></tr>\n";

    if($subCatRows==1)
    {
        //Display subcategories in one row
echo "<td>" . implode(', ', $subCatAry) . "</td>\n";
    }
    else
    {
        //Display subcategories in multiple rows
        $subCatAry = array_chunk($subCatAry, ceil(count($subCatAry)/$subCatRows) );
        foreach($subCatAry as $subCatRow)
        {
    echo "<td>" . implode(', ', $subCatRow). "</td>\n";
        }
    }
    echo "</table>\n";
}

//Create and run query
$query = "SELECT c.catid, c.catname, sc.subcatid, sc.subcatname
          FROM $t_cats as c
          JOIN $t_subcats as sc ON c.catid = sc.catid
          WHERE c.enabled = '1'
            AND sc.enabled = '1'
          $sortcatsql";
$result = mysql_query($query);
  
//Process the results
$category = false;
while($row = mysql_fetch_assoc($result))
{
    //Check for new category
    if($category != $row['catname'])
    {
        //New category
        if($category!==false)
        {
            //Display last category data
            createTable($category, $subCats2);
        }
        $subCats = array();
        $category = $row['catname'];
    }
    $subCats[] = $row['subcatname'];
}
//Display last categoiry data
createTable($category, $subCats2);

[Tom101]

I want to be able to choose between one or 2 rows wide on the subcatagories. The subcatagories would look like one or two columns under the catagory. I guess it would be need to be specifically set as I don't want the number of subcatagories to determine which way it goes.

The ideal solution would to be to have it specifically set but manually adjustable by altering numbers in the code.

[Psycho]

I want to be able to choose between one or 2 rows wide on the subcatagories.

 

"Two rows wide"? A row is a row. You mean you want two COLUMNS per row. the code I provided should still do what you want - just modify the display function to display the records in multiple columns.

 

//Function to display each category with the subcats
function createTable($categoryName, $subCatAry, $subCatCols=1)
{
    //Prepare the subcategory array
    if($subCatCols==1)
    {
        //Modify to a into multidimensional array with one element
        $subCatCells = array(0 => $subCatAry);
    }
    else
    {
        //Break array into multidimensional parts
        $subCatCells = array_chunk($subCatAry, ceil(count($subCatAry)/$subCatCols) );
    }

    //Output the results
    echo "<table>\n";
    echo "<tr><th colspan=\"$subCatCols\">{$categoryName}</th></tr>\n";
    foreach($subCatCells as $subCatCell)
    {
        echo "<td>" . implode("<br />", $subCatCell). "</td>\n";
    }    	
    echo "</table>\n";
}

[Tom101]

Yes, I meant to say two COLUMNS wide. I wasn't able to get the code to work.

[Tom101]

Maybe the tables used for categories and subcatories will be of some help.

 

 

Table for categories

 

CREATE TABLE IF NOT EXISTS `cats` (

  `catid` smallint(5) unsigned NOT NULL auto_increment,

  `catname` varchar(50) NOT NULL default '',

  `pos` smallint(5) unsigned NOT NULL default '0',

  `enabled` enum('0','1') NOT NULL default '0',

  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

  PRIMARY KEY  (`catid`),

  KEY `enabled` (`enabled`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

 

Table for subcategories

 

CREATE TABLE IF NOT EXISTS `subcats` (

  `subcatid` smallint(5) unsigned NOT NULL auto_increment,

  `subcatname` varchar(50) NOT NULL default '',

  `catid` smallint(5) unsigned NOT NULL default '0',

  `hasprice` enum('0','1') NOT NULL default '0',

  `pricelabel` varchar(25) NOT NULL default '',

  `expireafter` smallint(5) unsigned NOT NULL default '100',

  `enabled` enum('0','1') NOT NULL default '0',

  `pos` smallint(5) unsigned NOT NULL default '0',

  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

  PRIMARY KEY  (`subcatid`),

  KEY `catid` (`catid`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

 

[Psycho]

I wasn't able to get the code to work.

Could you provide more information? I go to the trouble of writing code for you and you simply state you couldn't get it to work. You don't mention if there were errors, if you validated the data retrieved from the query or offer anything that would help me to help you.

 

I stated it was pseudo code - not meant to be actual working code for you to just copy/paste. It was meant to guide you on the logic you could implement to get the result you want.

[Tom101]

I wasn't able to get the code to work.

Could you provide more information? I go to the trouble of writing code for you and you simply state you couldn't get it to work. You don't mention if there were errors, if you validated the data retrieved from the query or offer anything that would help me to help you.

 

I stated it was pseudo code - not meant to be actual working code for you to just copy/paste. It was meant to guide you on the logic you could implement to get the result you want.

 

 

Sorry but I am not a php programmer. I normally know how to manipulate php code & variables to do what I need but didn't see where in this script to use the code & variables you wrote. I was stumped on this one which is why I came to this forum. I have looked around on this forum and see I probably should have posted my question in "Third Party PHP Scripts" since I didn't write the code.

Thanks for trying to help.