Page 1 of 1

Special formatting for data retrieved from a MySQL table

Posted: Mon Aug 21, 2006 8:15 am
by Rovas
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I have two tables one with the different product' s categories  and the other with subcategories of a category. The second one is linked to the first one by a foreign to the first. The query uses the foreign key to link the names of the two tables togheter.
   I want to display the name of a category  on top of his subcategories and the same to different category. Something like this:

Code: Select all

-----------------------------  
  |    Category 1              |
  |-------------------------- - |
  |    Subcategory 1.1      |
  |    Subcategory  1.2     |
  | ----------------------------|
  |---------------------------- |
  |  Category 2                 |
   -------------------------------
   |   Subcategory 2.1      |
   |  Subcategory 2.2       |
   -------------------------------
  ----------------------------------
     Category 3
   --------------------------------
  ---------------------------------
My ideea to achieve this is putting the result from the query in two dimensional array. I tried using mysql_fetch_array (object, row) and using then next() and current() but I got the error message that the arrays I was storing the data from the table aren' t arrays (someone told me that mysql_fetch_* returns a reference to the field of data than the actual field). I tried to return a object and his properties to be displayed the way I wanted but the array remembered only the last entry in MySQL field.

Here is the code :

Code: Select all

connect(); //function which connects to the database
	$sql = 'SELECT DISTINCT CATEGORIES.NumCat AS Category, SUBCATEGORIES.NumSub as Subcategory
	    	  FROM CATEGORIES INNER JOIN SUBCATEGORIES
		      ON CATEGORIES.C= SUBCATEGORIES.Cat 
			ORDER BY NumeCat, NumSub';
	$rs = mysql_query($sql) or die(mysql_error());
	$n=mysql_num_rows($rs); 
         $i=1;
	while($rand=mysql_fetch_object($rs) && Si<$n)
	{
	  $cat[$i]=$rand -> Categorie;
	  $subcat[$i]=$rand -> Subcategorie;
	
	}
	if (current($cat["NumCat"]) != next($cat["NumCat"]))
		{
			if (!$first)
			{
				echo '</ul>';
			}
			echo $cat["NumCat"] .'<ul>';
			$prev = $row["NumCat"];
		}
		echo '<li>' .$row["NumSub"] .'</li>';
		$first = false;
	}
	echo '</ul>';*/

    do 
    {       
       echo "<tr>";
      if ($cat[$i]!= $cat[$j])
      {        
        echo "<td>" .$cat[$i];
        if ( $subcat[$i] != '') 
        {}
          echo "<td>"$subcat[$i] ."</td>";
          echo   "</tr>";
          
        else 
        {
          if ( $subcat[$i] != '') 
          {
            echo "<td>";
            echo   $subcat[$i]) ."</td>"; 
            echo "</tr>";         
          }  
        }
     $j=$i;
     $i=$i+1;       
    }
    while($i<$n && $j<$n-1 );  
    echo "</table>";   
    mysql_free_result();
  }

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Tue Aug 22, 2006 12:47 am
by RobertGonzalez
Two queries. Query one fetches the categories. Query two fetches the subcategories. Make both queries into arrays, then loop them. While you are looping the category array, inside each iteration, loop the subcategory array and for each iteration of the subcategory array, check to see if the join field of the subcat array matches the current loop value of the cat array loop. If it does match, so the subcat loop array values.

Posted: Tue Aug 22, 2006 3:03 am
by Rovas
JayBird | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I tried that after putting this post but it doesn' t display anything , the PHP parser doesn' t give a single notice or error (. 
  Can you guys tell what' s the error I can' t find it .

Code: Select all

//function for displaying the subcategories
      	function col($qid=0)
	{
    	$int="SELECT NameSub FROM CATEGORIES INNER JOIN SUBCATEGORIES ON SUBCATEGORIES.Cat= " .$qid ." GROUP BY NameSub";
	    $r=mysql_query($int) or die (mysql_error());
	    echo "<ul>";
	    while($rez=mysql_fetch_array($r))
    	{
	    	echo "<li>" .$rez["NameSub"] ."</li>"; 
		}	
		echo "</ul>";
	}
	//function which displays the categories and their subcategories
	function categ ()
	{
	  $vp=0; //previous C field value (The field C is the primary key of the table CATEGORIES)
	  $vu=0; // current value of C field
	  $int="SELECT * FROM CATEGORIES GROUP BY C"; //grouping denies the repeating of the elements
	  $rez=mysql_query($int) or die (mysql_error());
	  echo "<ul>";
	  while ($cat=mysql_fetch_array($rez))
	  {
	  	$vu=$rez["C"];  // assigning the current value of the C field
		if ($vu != $vp) // checks tp see if it s a different category by 
		{
			echo "<li>" .$rez["NameCat"] ."</li>"; //lthe category it' s displayed
			col($vu); // calling for it s subcategories 
	  	}
		$vp=$vu; // gives the current value to $vp
	  }
	  echo "</ul>";
	}

JayBird | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Tue Aug 22, 2006 3:24 am
by Rovas
:oops: :oops: If you hurry like me your bound to make stupid mistakes.
replace

Code: Select all

$rez["NameCate"]; 
   if($vu!=$vp){....}
with

Code: Select all

$vu=$cat["C"];
      echo "<li>" .$cat["NameCat"] "</li>";
      col($vu);
And will work fine

Posted: Tue Aug 22, 2006 8:35 am
by RobertGonzalez
FYI, 9 times out of 10 a blank screen is a mismatched bracket/parenthesis or a missing semicolon in conjunction with the php directive 'diplay_errors' set to 'Off'.

But I am gkad that you got it worked out.