SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict Phil-man's Avatar
    Join Date
    Nov 2000
    Posts
    291
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a series of articles in a MySQL database and each one is assigned to a category. I'm trying to use PHP to present them as follows (hope the formatting doesn't get messed up):

    CATEGORY A

    - Article One
    - Article Two

    CATEGORY B

    - Article Three
    - Article Four

    etc.

    I know how to do this using a DISTINCT selection statement (since some categories don't yet have articles in them) to first get the categories and then, using a loop, query the MySQL database for each category, but I'm trying to figure out a way to do it with just a single DB query. Any ideas? Thanks in advance for any help!

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How about using an array?

    $result = mysql_query("SELECT * from articletable");
    while($row = mysql_fetch_array($result)) {
    $catname = $row["catnamefield"];
    $articles[$catname][] = $row["articlenamefield"];
    }

    So now you have a big array whose key is the category and the value is an array of the articles in that category

    while(list($key,$val) = each($articles)) {
    print $key.'<br>';
    while(list($key,$val) = each($val)) {
    print $val."<br>";
    }
    }
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    SitePoint Addict Phil-man's Avatar
    Join Date
    Nov 2000
    Posts
    291
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. I think it will work, but I'm missing something. I'm not an expert, and I'm sure I'm just doing something really dumb. My revised actual code is below, and I get the following error message in my browser:

    "Warning: Variable passed to each() is not an array or object in... on line 68."

    Line 68 is the last "while" statement below.

    $result = mysql_query("SELECT Journals.ID, Title, Published, Category FROM Journals, Categories WHERE Published <= CURDATE() AND CID=Categories.ID ORDER BY Category, Published DESC");
    if (!$result) {
    echo("<p>Error performing query: " .
    mysql_error() . "</p>");
    exit();
    }
    while($row = mysql_fetch_array($result)) {
    $category = $row["Category"];
    $articles[$category][] = $row["Title"];
    }
    while(list($key,$val) = each($articles)) {
    print $key . "<br>";
    while(list($key,$val) = each($val)) {
    print $val . "<br>";
    }
    }

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My mistake it will rpobably break if there is only one category or one item in a category. Which line exactly is it?

    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    SitePoint Addict Phil-man's Avatar
    Join Date
    Nov 2000
    Posts
    291
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again. Here's the guilty line :-)

    while(list($key,$val) = each($val)) {

  6. #6
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $result = mysql_query("SELECT Journals.ID, Title, Published, Category FROM Journals, Categories WHERE Published <= CURDATE() AND CID=Categories.ID ORDER BY Category, Published DESC");
    if (!$result) {
    echo("<p>Error performing query: " .
    mysql_error() . "</p>");
    exit();
    }
    while($row = mysql_fetch_array($result)) {
    $category = $row["Category"];
    $articles[$category][] = $row["Title"];
    }
    while(list($key,$val) = each($articles)) {
    print $key . "<br>";
    if (is_array($val)) {
    while(list($key2,$val2) = each($val)) {
    print $val2 . "<br>";
    }
    }
    }
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  7. #7
    SitePoint Addict Phil-man's Avatar
    Join Date
    Nov 2000
    Posts
    291
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are a person of great talent and intellect. It works perfectly. THANK YOU. Now... don't kill me... but I forgot that there are two additional fields that I need in addition to the title: "ID" and "Published", the latter being a date field, not that it matters. :-) They are both in the same MySQL table as "Title".

    Does that royally mess this whole thing up? Or can I still use the same basic code structure to accomplish this? I'm not sure how the two additional fields fit into the whole "array within an array" thing, or the loop within a loop for that matter.

    To clarify, each "Title" serves as a hyperlink to the article (by passing "ID" in the query string). And "Published" appears in parentheses after the title.

    Can you help yet again? Thanks.

  8. #8
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This should work for you I haven't tested it anywhere but I am pretty sure it will work


    $result = mysql_query("SELECT Journals.ID, Title, Published, Category FROM Journals, Categories WHERE Published <= CURDATE() AND CID=Categories.ID ORDER BY Category, Published DESC");
    if (!$result) {
    echo("<p>Error performing query: " .
    mysql_error() . "</p>");
    exit();
    }
    while($row = mysql_fetch_array($result)) {
    $category = $row["Category"];
    $title = $row["Title"];
    $id = $row["ID"];
    $date = $row["Published"];
    $articles[$category][$id]["title"] = $title;
    $articles[$category][$id]["published"] = $date;
    }
    while(list($key,$val) = each($articles)) {
    print $key . "<br>";
    if (is_array($val)) {
    while(list($key2,$val2) = each($val)) {
    print '<a href="article.php?articleid='.$key2.'">'. $val2["title"] .'</a>'.$val2["date"];
    }
    }
    }
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  9. #9
    SitePoint Addict Phil-man's Avatar
    Join Date
    Nov 2000
    Posts
    291
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your continued help with this. I tried your suggestion. The date works as it should, but the ID still doesn't seem to want to work. Also, now only the first article within each category appears, instead of all articles.

    The frustrating thing is that PHP isn't entirely Greek to me. I PRETTY much understand how this thing is working... and yet I can't quite get over the hump to make this work!

    Any thoughts? I'll keep trying too. Thanks!!

  10. #10
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    See told you I didn't test it

    $result = mysql_query("SELECT Journals.ID, Title, Published, Category FROM Journals, Categories WHERE Published <= CURDATE() AND CID=Categories.ID ORDER BY Category, Published DESC");
    if (!$result) {
    echo("<p>Error performing query: " .
    mysql_error() . "</p>");
    exit();
    }
    while($row = mysql_fetch_array($result)) {
    $category = $row["Category"];
    $title = $row["Title"];
    $id = $row["ID"];
    $date = $row["Published"];
    $articles[$category][$id]["title"] = $title;
    $articles[$category][$id]["published"] = $date;
    }
    while(list($key,$val) = each($articles)) {
    print $key . "<br>";
    if (is_array($val)) {
    while(list($key2,$val2) = each($val)) {
    print '<a href="article.php?articleid='.$val[$key2].'">'. $val[$val2["title"]] .'</a>'.$val[$val2["published"]];
    }
    }
    }


    Please don't PM me with questions.
    Use the forums, that is what they are here for.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •