Training, Open Source computer languages
PerlPHPPythonMySQLApache / TomcatTclRubyJavaC and C++LinuxCSS 
Search for:
Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
 
For 2023 (and 2024 ...) - we are now fully retired from IT training.
We have made many, many friends over 25 years of teaching about Python, Tcl, Perl, PHP, Lua, Java, C and C++ - and MySQL, Linux and Solaris/SunOS too. Our training notes are now very much out of date, but due to upward compatability most of our examples remain operational and even relevant ad you are welcome to make us if them "as seen" and at your own risk.

Lisa and I (Graham) now live in what was our training centre in Melksham - happy to meet with former delegates here - but do check ahead before coming round. We are far from inactive - rather, enjoying the times that we are retired but still healthy enough in mind and body to be active!

I am also active in many other area and still look after a lot of web sites - you can find an index ((here))
Count links under a category

Posted by torriv (torriv), 16 December 2006
Hello,

I have tried now in several days to make a way of displaying how many link there is under each category on my link page.. i have tried to copy the php code from my myphpadmin page where i manage to write the correct mysql script, and got the result i wanted...
but when i try this in my page.. it doesn't work.. can anyone help me?



Posted by admin (Graham Ellis), 17 December 2006
Yes, I'm sure we can help you, though you don't give me very much code / example to know where to start ... and "doesn't work" is a term dreaded by anyone offering support - there's so much evidence about a problem in knowing how it fails.

Having said that, I was putting an example together for our cascading style sheets training ths morning and I wanted to add in some database stuff to show the whole thing together, so I added total lines.   Have a look at the following:

a) A demonstration showing how we have summariesed elements (reviews rather than links, but the principle is the same) on our database

b) The full source code of the example.

You'll see that I've kept a counter of the number of times each page has been reviewed in my database query loop, and I've produced extra output lines as follows:

1. A title line for a section when the count is zero

2. A count summary and link line when the review page (section in your case) has changed.   This count summary is done by a function as it has to be called up twice - there's an extra call to it after the review loop has dropped out so that the final count is generated, and a function avoids code duplication in these circumstances.



Posted by torriv (torriv), 17 December 2006
okey, i'm sorry... but i'm not that good in english, but i try my best!

Here is some code for you:

<?php
include "admin/connect.php";
include "admin/var.php";

if(isset($_GET['catid']))
{
 $ID=$_GET['catid'];
 $day=date("U");
 $deletedate=$day-3600*24*$formatdays;
 $deleteoldlinks="DELETE from ffal_links where time<'$deletedate'";
 mysql_query($deleteoldlinks) or die("Could not delete old links");
 $getcatlink="SELECT * from ffal_categories where ID='$ID'";
 $getcatlink2=mysql_query($getcatlink) or die("Could not get catname");
 $getcatlink3=mysql_fetch_array($getcatlink2);
 print "<A href='index.php'>Hjem</a>--><A href='index.php?catid=$getcatlink3[ID]'>$getcatlink3[catname]</a><br><br>";
 print "<center><h3> $getcatlink3[catname] </h3></center>";
 print "<center><A href='addlink.php'>Legg til en link</a></center><br><br>";
 $getlinks="SELECT * from ffal_links where catparent='$ID' limit $numlinks";
 $getlinks2=mysql_query($getlinks) or die(mysql_error());
 while($getlinks3=mysql_fetch_array($getlinks2))
 {
   $getlinks3[url]=htmlspecialchars($getlinks3[url]);
   $getlinks3[title]=htmlspecialchars($getlinks3[title]);
   $getlinks3[description]=htmlspecialchars($getlinks3[description]);
   $getlinks3[description]=wordwrap($getlinks3[description], 25, "\n", 1);
     print "<center>";
   print "<A href='$getlinks3[url]' target='_blank'>$getlinks3[title]</a>";
   if($allowdescription=="Yes"||$allowdescription=="yes")
     
   {
        print " $getlinks3[Description]<br>------------------------------------------------------------------- <br>";
   }
 }
 
}
else
{
 $count=0;
 $getcategories="SELECT * from ffal_categories";
 $getcategories2=mysql_query($getcategories) or die("Could not retrieve categories");
 print "<center>";
 print "<table border='0' cellspacing='20'>";
 print "<tr><td colspan='3'><center><h3>Kategorier</h3></center></td></tr>";
 while($getcategories3=mysql_fetch_array($getcategories2))
 {
    if($count%3==0)
    {
      print "<tr><td><A href='index.php?catid=$getcategories3[ID]'>$getcategories3[catname]</a></td>";
    }
    else if($count%3==1)
    {
      print "<td><A href='index.php?catid=$getcategories3[ID]'>$getcategories3[catname]</a></td>";
    }
    else
    {
      print "<td><A href='index.php?catid=$getcategories3[ID]'>$getcategories3[catname]</a></td></tr>";
    }
    $count++;
 }
 print "<tr><td colspan='3'><center><A href='addlink.php'>Legg til en link</a></td></tr>";
 print "</table>";
}
include 'critical.php';
?>

as u see... there is a table with categories... but the numebers of links is in a row/column(?) called catparent in a table called ffal_links..

print "<td><A href='index.php?catid=$getcategories3[ID]'>$getcategories3[catname]


it is after [catname] i wantet to display (number)....

Here is the adress of that page just how it look right know..
http://www.trivera.net/linker/

Posted by admin (Graham Ellis), 17 December 2006
I think you're going to have to do another query for each category to get the number of entries, unless you've got it stored in the category table.   You'll need to base it on the

$getlinks="SELECT * from ffal_links where catparent='$ID' limit $numlinks";

line, but without the limits, and you might do better to select count(catparent) rather than * so that you just get the count back.

I guess the example I wrote was off target

Posted by torriv (torriv), 17 December 2006
ok.. i will try it out.. and get back to you as soon as i have tried it.. and i will copy the code i have made.. thank u so long!
-----------------------------------------------------------------------

here we go again...

code:
<?php
include "admin/connect.php";
include "admin/var.php";
//main module for chipmunk FFAL script
if(isset($_GET['catid']))
{
 $ID=$_GET['catid'];
 $day=date("U");
 $deletedate=$day-3600*24*$formatdays;
 $deleteoldlinks="DELETE from ffal_links where time<'$deletedate'";
 mysql_query($deleteoldlinks) or die("Could not delete old links");
 $getcatlink="SELECT * from ffal_categories where ID='$ID'";
 $getcatlink2=mysql_query($getcatlink) or die("Could not get catname");
 $getcatlink3=mysql_fetch_array($getcatlink2);
 print "<A href='index.php'>Hjem</a>--><A href='index.php?catid=$getcatlink3[ID]'>$getcatlink3[catname]</a><br><br>";
 print "<center><h3> $getcatlink3[catname] </h3></center>";
 print "<center><A href='addlink.php'>Legg til en link</a></center><br><br>";
 $getlinks="SELECT * from ffal_links where catparent='$ID' limit $numlinks";
 $getlinks2=mysql_query($getlinks) or die(mysql_error());
 while($getlinks3=mysql_fetch_array($getlinks2))
 {
   $getlinks3[url]=htmlspecialchars($getlinks3[url]);
   $getlinks3[title]=htmlspecialchars($getlinks3[title]);
   $getlinks3[description]=htmlspecialchars($getlinks3[description]);
   $getlinks3[description]=wordwrap($getlinks3[description], 25, "\n", 1);
     print "<center>";
   print "<A href='$getlinks3[url]' target='_blank'>$getlinks3[title]</a>";
   if($allowdescription=="Yes"||$allowdescription=="yes")
     
   {
        print " $getlinks3[Description]<br>------------------------------------------------------------------- <br>";
   }
 }
 
}
else
{
 $ID=$_GET['catid'];
 $count=0;
 $getcategories="SELECT * from ffal_categories";
 $getcategories2=mysql_query($getcategories) or die("Could not retrieve categories");
 $number="SELECT COUNT(catparent) from ffal_links where catparent='$ID'";
 $numer2=mysql_query($number) or die(mysql_error());
 $number3=mysql_fetch_array($number2);
 print "<center>";
 print "<table border='0' cellspacing='20'>";
 print "<tr><td colspan='3'><center><h3>Kategorier</h3></center></td></tr>";
 while($getcategories3=mysql_fetch_array($getcategories2))
 {
    if($count%3==0)
    {
      print "<tr><td><A href='index.php?catid=$getcategories3[ID]'>$getcategories3[catname]</a>       $number3[catparent]</td>";
    }
    else if($count%3==1)
    {
      print "<td><A href='index.php?catid=$getcategories3[ID]'>$getcategories3[catname]</a>       $number3[catparent]</td>";
    }
    else
    {
      print "<td><A href='index.php?catid=$getcategories3[ID]'>$getcategories3[catname]</a>       $number3[catparent]</td></tr>";
    }
    $count++;
 }
 print "<tr><td colspan='3'><center><A href='addlink.php'>Legg til en link</a></td></tr>";
 print "</table>";
}
include 'critical.php';
?>

Adress to the page: http://www.trivera.net/linker/index1.php

as you can see.. something wrong
and.. just so it is said... this is my first week with php..

I'm, really happy that you really bother to help me!


Posted by torriv (torriv), 18 December 2006
and.. another thing.. i think this one is faster to solve...
I wona have this code to give me the date of the last updatet document in my homepage..

i know it gives me the last update document in that directory on the root, but not the subdirectories.. so i tried with a if/else if/else, but it doesn't give me the last updatet time on the subdir...here is the code:

<?php
$getLastModDir = filemtime("/home/triveran/public_html/.");
 if($getLastModDir>$getLastModDir)
 {
echo "Hjemmesiden ble sist oppdatert: " . date ("l, j. F Y, H:i:s", $getLastModDir);
 }
 else if($getLastModDir>$getLastModDir)
 {
 $getLastModDir = filemtime("/home/triveran/public_html/gjestebok/.");
echo "Hjemmesiden ble sist oppdatert: " . date ("l, j. F Y, H:i:s", $getLastModDir);
}
else if ($getLastModDir>$getLastModDir)
{
 $getLastModDir = filemtime("/home/triveran/public_html/gallery/.");
echo "Hjemmesiden ble sist oppdatert: " . date ("l, j. F Y, H:i:s", $getLastModDir);
 }
else if ($getLastModDir>$getLastModDir)
{
 $getLastModDir = filemtime("/home/triveran/public_html/linker/.");
echo "Hjemmesiden ble sist oppdatert: " . date ("l, j. F Y, H:i:s", $getLastModDir);
}
 else
 {
echo "Hjemmesiden ble sist oppdatert: " . date ("l, j. F Y, H:i:s", $getLastModDir);
 }
?>

Posted by admin (Graham Ellis), 18 December 2006
on 12/17/06 at 18:51:08, torriv wrote:
 $numer2=mysql_query($number) or die(mysql_error());
 $number3=mysql_fetch_array($number2);


Probably a spelling error - "numer2" v "number2".

The line number is really useful to you (I got that off the page you pointed me at) as it will usually bring the error down to just one or two lines - the one indicated, or something just above that sets up conditions for the line that had problems.



Posted by torriv (torriv), 18 December 2006
ok.. know i have fixed the spelling.. but as you can see know.. there is not coming any numberg.. i have at least 10 link under "personlige hjemmesider"....

Posted by admin (Graham Ellis), 18 December 2006
on 12/18/06 at 06:17:57, torriv wrote:
and.. another thing..


See separate answer ... I try to keep to one subject per thread as it helps for future indexing   and this one made a good blog entry!

Posted by admin (Graham Ellis), 18 December 2006
on 12/18/06 at 06:59:12, torriv wrote:
ok.. know i have fixed the spelling.. but as you can see know.. there is not coming any numberg.. i have at least 10 link under "personlige hjemmesider"....


"Logic error" ... you need to have the category count done for each category, so it needs to be within the while loop.   I note you 're new to PHP; if you've programmed before in another language, look nback to the principle you learnt there of conditionals, looping and so on and apply them here in just the same way.  If you're new to programming, then some more basic texts (or of course one of our courses   ) would be a good place to start; you'll have a lot to learn but it will be highly rewarding.

As an alternative / in parallel on the count side, you might like to play about with the example I wrote for you yesterday morning - the formatting and approach is different, but it does the same basic thing.

Posted by torriv (torriv), 18 December 2006
i'm sorry, it won't happen againt

Posted by torriv (torriv), 20 December 2006
Okey...

My last question..

As you can see, i have now manage to get the TOTAL of links, and have now tried to get just get the numbers under each category...
if i wrote this:

$catparent="select catparent from ffal_links WHERE catparent=1";

THEN i get the total numbers under category 1, but as u probably know, i wonna have this to happend automatically..


I did as you said, tried with a while sentence.. but then it took me several minutes to get into my page.. (i didn't get into my page at all, so i think the while loop never stopped, even if i did it identically like the other while sentence)

So.. are you willingly to give me that little snappit of code?

code:

 $ID=$_GET['catid'];
 $count=0;
 $getcategories="SELECT * from ffal_categories";
 $getcategories2=mysql_query($getcategories) or die("Could not retrieve categories");
 
$catparent="select catparent from ffal_links";
 $catparent2=mysql_query($catparent) or die("funker dårlig");
$catparent3=mysql_num_rows($catparent2);
 print "<center>";
 print "<table border='0' cellspacing='20'>";
 print "<tr><td colspan='3'><center><h3>Kategorier</h3></center></td></tr>";
while($getcategories3=mysql_fetch_array($getcategories2))
 {
    if($count%3==0)
    {
      print "<tr><td><A href='index.php?catid=$getcategories3[ID]'>$getcategories3[catname]</a>                                $catparent3</td>";
    }
    else if($count%3==1)
    {
      print "<td><A href='index.php?catid=$getcategories3[ID]'>$getcategories3[catname]</a>$catparent3       </td>";
    }
    else
    {
      print "<td><A href='index.php?catid=$getcategories3[ID]'>$getcategories3[catname]</a> $catparent3      </td></tr>";
    }
    $count++;
 }
 print "<tr><td colspan='3'><center><A href='addlink.php'>Legg til en link</a></td></tr>";
 print "</table>";
}

adress:
http://www.trivera.net/linker/index1.php

Posted by admin (Graham Ellis), 20 December 2006
Your count is outside the while loop so you're only doing in once. IOt needs to be inside the loop and also modified to count not ALL possible hits but only those that are applicable.

So this code ...

Code:
$catparent="select catparent from ffal_links";
$catparent2=mysql_query($catparent) or die("funker dårlig");
$catparent3=mysql_num_rows($catparent2);


moves after ...

Code:
while($getcategories3=mysql_fetch_array($getcategories2))
 {


and the select will have a where clause like

Code:
where catID = $getcategories3[ID]


By the way - I've had to guess at "catID" as I can't see any reference to what the category field is called in the ffal_links table.


Posted by torriv (torriv), 20 December 2006
Thank you SO VERY much!!

That was the thing!

i only change catid with the name catparent, since catparent was the reference to the category!

Now it works perfectly!

You can see the result here:

http://www.trivera.net/linker/index1.php

Merry Christmas and a happy new year!

Posted by torriv (torriv), 23 December 2006
As I said in my other post.. i had another problem, not a problem that is visible..

Because, in my mysql table, i have a row in my links table called "time",

And in that row, it's only several number.. like 1110908 (only random)
I know it is something about timestamp...
I have search the internet about it, and found some code that will convert that set of number to readable text like 23/12/2006...

But it seems to me that there isn't many that wonna have that displayed on the page.. but.. i wonna have it because i wonna have a date on each link, so people can see when the last link was added, or when that paticular link was added... if you can give me a sample of how to convert it, i will find out of the rest by myself.



Posted by admin (Graham Ellis), 23 December 2006
$datest = date("j/n/Y",111090;



This page is a thread posted to the opentalk forum at www.opentalk.org.uk and archived here for reference. To jump to the archive index please follow this link.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2024: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 793803 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho