Rewriting PHP - MySQL statement... I'm lost

[jazztie]

At this moment I have the following code:

$page = mysql_fetch_array (mysql_query("SELECT * FROM pages WHERE subcat=$subcatid"));

$rubr = split (",", $page["rubrieken"]);

print "<TABLE width='600' border=0>";
print "<TR>";

if (count($rubr) > 1) {

    for($i=0;$i<count($rubr);$i++) { 
           $tit=mysql_result(mysql("$dbname","SELECT titel FROM rubrieken WHERE id=$rubr[$i]"),0,0); 
           print "<TD valign='top'><B>$tit</B></TD>";
      }
      print "<TR>";

      for($i=0;$i<count($rubr);$i++) { 
         print "<TD valign='top'>\r";
            
           $result = mysql_query ("SELECT id,link FROM links WHERE rubrieken=$rubr[$i] ORDER BY id"); 
           while ($row = mysql_fetch_array ($result)) {
            print " ". $row ["link"] . "<BR>\r";
           }
              print "</TD>";
    }

     print "</TR></TABLE>\r\r";
}

?> 

Say that I have two Rubrieken (dutch for Sections) called
Rubriek 1 and Rubriek 2.

The first rubriek (section) has the Links: link 1, link 3, link 5
the second rubriek has the Links: link 2, link 4, link 6

This would produce the following output:
(without the dashes)

Rubriek 1---------Rubriek 2
--Link 1------------Link 2
--Link 3------------Link 4
--Link 5------------Link 6

But what I want is:

Rubriek 1
--Link 1
--Link 3
--Link 5

Rubriek 2
--Link 2
--Link 4
--Link 6

Could someone help me rewrite the code, so I get the output in the way that I want?

[freakysid]

Here is my go at it.

$page*=*mysql_fetch_array*(mysql_query("SELECT***FROM*pages*WHERE*subcat=$subcatid"));
$rubr*=*split*(",",*$page["rubrieken"]);

for($i=0;$i<count($rubr);$i++)*{
   $tit=mysql_result(mysql("$dbname","SELECT*titel*FROM*rubrieken*WHERE*id=$rubr[$i]"),0,0);*
   print*"<BR><B>$tit</B><BR>\r";
   
   $result*=*mysql_query*("SELECT*link*FROM*links*WHERE*rubrieken=$rubr[$i]*ORDER*BY*id");*
   while*($row*=*mysql_fetch_array*($result))*{
      print*$row*["link"]*.*'<BR>\r';
   }
} 

Sorry I didn't grapple with the table syntax. I'm sure you can work that out. However, the above logic should be what you want. Not too pretty, but as you explained in an earlier thread you have to use a database schema which isn't normalised

Note that the if statement if*(count($rubr)*>*1) is redundant (and 1 should have been 0 ). The for condition for($i=0;$i<count($rubr);$i++) is sufficient.

Hope that is of help

[jazztie]

Thanx a lot, Freakysid...
it's actually what I meant... and it works.

However... ...
Sometimes, there are no 'rubrieken'. In other words;
pages.rubrieken can be empty.

When I use your code:

$page = mysql_fetch_array (mysql_query("SELECT * FROM pages WHERE subcat=$subcatid"));
$rubr = split (",", $page["rubrieken"]);

print "<TABLE width='600' border=0>";
print "<TR>";

for($i=0;$i<count($rubr);$i++) {
   $tit=mysql_result(mysql("iclon","SELECT titel FROM rubrieken WHERE id=$rubr[$i]"),0,0); 
   print "<BR><B>$tit</B><BR>\r";
   
   $result4 = mysql_query ("SELECT link FROM links WHERE rubriek=$rubr[$i] ORDER BY id"); 
   while ($row = mysql_fetch_array ($result4)) {
      print $row ["link"] . '<BR>';
   }

} 

than I get the following error (if the pages.rubrieken is empty)
Warning: Supplied argument is not a valid MySQL result resource in C:\...\showrubr.php on line 91

Warning: Supplied argument is not a valid MySQL result resource in C:\...\showrubr.php on line 95

line 91:

   $tit=mysql_result(mysql("iclon","SELECT titel FROM rubrieken WHERE id=$rubr[$i]"),0,0); 

line 95:

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

Which is logical because he can't find any...
Any tips?

[freakysid]

Well that's why I enjoy hanging around this forum. I get to gain from your pain

I just went and read the manual on function split() as I have never used it. http://www.php.net/manual/en/function.split.php Here is the first user contributed comment on that page:

kietscia@yahoo.com
22-Mar-2000 03:49
Here's a little unexpected gem.

<?php
$x = split(":","");
print "There are " . count($x) . " items";
?>

Suprisingly enough (or maybe not), this prints:
There are 1 items
I would expect this to be 0 since the string is empty.

Personaly I would use the explode() function over the split() function because you are using a simple string (in fact just one char ";") as the delimiter, not a regular expression. But you might still have the same problem ?!?

So a fix could be (you would have to test this):

if $page["rubrieken"] { // test that the rubrieken field is not null
   $rubr*=*split*(",",*$page["rubrieken"]);
} 

That should (?) fix the problem at line 91 which also cascades through to cause the problem at line 95.

[jazztie]

It does work... !!!!

I added '(' and ')' otherwise the if-statement doesn't work.

if ($page["rubrieken"]){
   $rubr = split (",", $page["rubrieken"]);
} 

You are my savior...

gr. Jasper