SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Location
    Leeds, UK
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    newbie code problem

    I'm trying to create my first database driven site with php/MySQL and have a problem with the news section. Here's a link to how I want the finished site to look and here's a link to where I've got up to so far.

    The problem is that I need to split the database output so that the article titles are each displayed in a separate table as in the first design example. Below is the code that calls the first 5 article titles (or 'slugs') from the DB:
    PHP Code:
     // generate and execute query 
    $query "SELECT id, slug, timestamp FROM news ORDER BY timestamp DESC LIMIT 0, 5"
    $result mysql_query($query) or die ("Error in query: $query. " mysql_error()); 

    // if records present 
    if (mysql_num_rows($result) > 0


    // iterate through resultset
    // print article titles 
    while($row mysql_fetch_object($result)) { 
    I think this is just a case of simplifying the code so that it pulls a specific entry from the DB rather than iterating thru 5 entries. The problem is I'm quite new to this and am not sure of the syntax to do this. Could anyone help me out with a solution please?

    Thanks
    Last edited by prendo; Apr 1, 2003 at 13:59.

  2. #2
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You probably don't want to do one query at a time, if you can get the information all at once.

    Keep in mind that when you are dynamically creating a page all you are doing is asking the programming language (like PHP) to create the text that you would have typed in manually.

    So lets say that you reproduce those squares in the middle of the page with this code:
    Code:
    <table>
    content
    </table>
    Alright, I dramatically oversimplified everything, but it is just to make the following example easier to understand:
    PHP Code:
     // generate and execute query 
    $query "SELECT id, slug, timestamp FROM news ORDER BY timestamp DESC LIMIT 0, 5"
    $result mysql_query($query) or die ("Error in query: $query. " mysql_error()); 

    // if records present 
    if (mysql_num_rows($result) > 0


    // iterate through resultset
    // print article titles 
    while($row mysql_fetch_object($result)) { 

       
    // each time this loop runs it will do whatever you put between the braces,
       // including outputting any html you want
       
    echo('<table>');
       echo(
    $row['slug']);
       echo(
    '</table>');

    So basically...
    Step 1: Figure out how you want the html to look once the page gets produced by php
    Step 2: Integrate the text that produces that look into your php
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Location
    Leeds, UK
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Samsm,

    Step 1: Figure out how you want the html to look once the page gets produced by php
    Step 2: Integrate the text that produces that look into your php

    That all seemed to make sense to me but I can't quite get it to work yet.

    I replaced this code:

    PHP Code:
    // each time this loop runs it will do whatever you put between the braces,
    // including outputting any html you want
    echo('<table>');
    echo(
    $row['slug']);
    echo(
    '</table>');


    ..with this:

    PHP Code:
       // each time this loop runs it will do whatever you put between the  braces,
       // including outputting any html you want
       
    echo('<table border="0px" cellpadding="0px" cellspacing="0px" align="center">
            <tr height="2px"> 
              <td background="images/TL.gif"></td>
              <td background="images/T_Fill.gif"></td>
              <td background="images/TR.gif" width="5px"></td>
            </tr>
            <tr> 
              <td  background="images/L_Fill.gif" width="2px"></td>
              <td style="padding:3px;" bgcolor="#FFFFFF"> 
                <!-- START CONTENT -->
                '
    );

       echo(
    $row['0']);

       echo(
    ' <!-- FINISH CONTENT -->
              </td>
              <td  background="images/R_Fill.gif" width="5px" valign="top"> 
                <!-- (Floating DIV tag inside right hand side cell column) -->
                <div valign="bottom"><img src="images/det2.gif" /></div></td>
            </tr>
            <tr height="5px"> 
              <td background="images/BL.gif"></td>
              <td background="images/B_Fill.gif"> 
                <!-- (Floating DIV tag inside bottom cell column) -->
                <div align="left"><img src="images/det1.gif" /></div></td>
              <td background="images/BR.gif" width="5px"></td>
            </tr>
          </table>'
    );
    }
     
     
    ?> 
    Sorry, it's a bit messy but all these tables with rounded corners tend to do that! I've also had to employ various spacer type objects for alignment purposes, I suppose I should have removed these for clarity purposes, sorry.

    Here's a link to show you how the page comes out...obviously something is going wrong. It seems to be creating three empty tables below the first table (which I left for some intro text). I'm not sure why it has created 'three' tables or why there is no content or error messages showing.

    Could you tell me whether I've followed your instructions correctly samsm, or where I've gone wrong??

  4. #4
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First off, I think you have the general idea, which is good.

    One thing first:
    PHP Code:
    // not this: echo($row['0']); but this:
    echo($row[0]); 
    Notice the lack of quotes. Quotes are unnecessary when indicating a position in an array (like here). In fact, they are actually a little bit of a liability, although I believe PHP will make the array still work. You only need quotes for a using a string to indicate position in an array (ex: $array['id']).

    Alright, that technicality aside, let's look at what rendered. I was eyeballing the code from a view source window so I might have missed the correct spot, but it appears that you have this:
    PHP Code:
    <!-- START CONTENT -->
    <
    img src="" alt="" name="temp" width="363" height="102" id="temp" /> 
    <!-- 
    FINISH CONTENT --> 
    ... followed by three completely blank sections where echo($row['0']) did not output a thing.

    So check up on what you are retrieving. I like to do it like this:
    PHP Code:
    // replace echo($row[0]); with this:
    echo('<pre>');
    print_r($row);
    echo(
    '</pre>'); 
    That will quickly show you what is (or isn't!) in each array.
    Also, print_r will show you what to put in $row['here']... I think you'll be able to see how, if all goes well.

    Hope that helps
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  5. #5
    Sidewalking anode's Avatar
    Join Date
    Mar 2001
    Location
    Philadelphia, US
    Posts
    2,205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $row[0] or anything with array syntax isn't going to work for mysql_fetch_object()

    Try mysql_fetch_assoc() (to access it as $row['slug']), or use $row->slug if you want to keep using mysql_fetch_object().
    TuitionFree a free library for the self-taught
    Anode Says... Blogging For Your Pleasure

  6. #6
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by anode
    $row[0] or anything with array syntax isn't going to work for mysql_fetch_object()

    Try mysql_fetch_assoc() (to access it as $row['slug']), or use $row->slug if you want to keep using mysql_fetch_object().
    Good call! I'm so used to seeing that query/fetch_array combo I looked right over that.

    print_r would have revealed that eventually, but nice to speed things along
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  7. #7
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Location
    Leeds, UK
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, thanks for your help, I feel like I'm getting somewhere with this.

    First of all, I removed the quotes from the array as samsm said. I felt I should ignore this part though samsm:
    Quote Originally Posted by samsm
    Alright, that technicality aside, let's look at what rendered. I was eyeballing the code from a view source window so I might have missed the correct spot, but it appears that you have this:
    PHP: <!-- START CONTENT -->
    <
    img src="" alt="" name="temp" width="363" height="102" id="temp" />
    <!--
    FINISH CONTENT -->
    ... followed by three completely blank sections where echo($row['0']) did not output a thing.
    ..cos it looks like you may have been looking at the code for the table above where the php actually starts (forgive me if I'm wrong there). This code is just a table with a placeholder; it's there to hold a gif of the page title ie NEWS and a little bit of descriptive text, nothing dynamic.

    Then I changed mysql_fetch_object() to mysql_fetch_assoc() as per anode. I also tried a couple of variations on this as mentioned in your post anode.

    Here's a link to the result. It looks pretty much the same as before so I also tried changing:
    PHP Code:
    echo($row[0]); 
    with:
    PHP Code:
    echo('<pre>'); 
    print_r($row); 
    echo(
    '</pre>'); 
    ..and this link shows the result.

    This is quite interesting because it shows me that the slugs are definitely being received in the browser but are not being displayed properly. (and neither is the html for the table designs)

    Could you tell me if I'm going in the right direction here please guys?
    Last edited by prendo; Apr 2, 2003 at 01:56.

  8. #8
    SitePoint Wizard
    Join Date
    Oct 2001
    Posts
    2,686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,
    The reason why you don't get the data from db displayed is that;
    When using mysql_fetch_assoc() you need to access the result using the column name as key in the $row array. Example:
    PHP Code:
    echo $row['id'];
    echo 
    $row['slug'];
    echo 
    $row['timestamp']; 
    If you want to use numeric indices ($row[0]), then use mysql_fetch_row().

    One last option is to use mysql_fetch_array() which "combine" mysql_fetch_row() and mysql_fetch_assoc(). Then you can choose which method you want to use.

    You also have mysql_fetch_object as Anode explained.

    HTH

    -Helge

  9. #9
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Location
    Leeds, UK
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Helge
    The reason why you don't get the data from db displayed is that;
    When using mysql_fetch_assoc() you need to access the result using the column name as key in the $row array. Example:[php]echo $row['id'];
    echo $row['slug'];
    echo $row['timestamp'

    Thanks Helge, that seems to have got the php working. Here's the link.

    Now I need to figure out how to get the html to display the boxes like I want them to look. ie like this. Is this just a case of working through my html code now? I'm still a bit confused because I only added the html code to make one table, which should appear below the one at the top of the main centre column of my layout. It doesn't really seem to be rendering any of the table code though.

    I've used style sheets in certain places but not sure if this is effecting the php code. Could anyone please give me some pointers about where I should look to get the tables rendered correctly?

    Thanks in advance.

  10. #10
    SitePoint Wizard
    Join Date
    Oct 2001
    Posts
    2,686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi
    Glad you got something printed to screen

    About the HTML. If you post the HTML code of one of the tables (boxes) that you repeat in your working layout (with the text), I'll try to put the PHP code into it for you.

    -Helge

  11. #11
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Location
    Leeds, UK
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Helge
    If you post the HTML code of one of the tables (boxes) that you repeat in your working layout (with the text), I'll try to put the PHP code into it for you.

    -Helge
    Thanks a lot for the offer Helge. I managed to get the html sorted out. It was a simple matter in the end. Some of the image links were broken.

    I'm now in the position where the data is displaying in the boxes as I wanted but something has gone slightly wrong with the slug display in the process.

    Here's a link to how the data was originally presented, and here's a link to how it looks now.

    I can see that this is because the php/html code for the link and the datestamp are outside the loop; I'm just not sure how to adapt the code so that they fall within the loop. I'm sure this must be something simple but I haven't had any success with it yet. I'll keep trying things but any further assistance will be greatly appreciated. [img]images/smilies/smile.gif[/img]

    here's the main code:
    PHP Code:
    // generate and execute query
    $query = "SELECT id, slug, timestamp FROM news ORDER BY timestamp DESC LIMIT 0, 5";
    $result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
    // if records present
    if (mysql_num_rows($result) > 0)
    {
    // iterate through resultset
    // print article titles
    while($row = mysql_fetch_assoc($result)) {

    // each time this loop runs it will do whatever you put between the braces,
    // including outputting any html you want
    echo('<table border="0px" cellpadding="0px" cellspacing="0px" align="center">
    <tr height="2px"> 
    <td background="../images/TL.gif"></td>
    <td background="../images/T_Fill.gif" width="363"></td>
    <td background="../images/TR.gif" width="5px"></td>
    </tr>
    <tr> 
    <td background="../images/L_Fill.gif" width="2px" height="102"></td>
    <td style="padding:3px;" bgcolor="#FFFFFF"> 
    <!-- START CONTENT -->
    ');
    echo $row['id'];
    echo $row['slug'];
    echo $row['timestamp'];
    echo(' <!-- FINISH CONTENT -->
    </td>
    <td background="../images/R_Fill.gif" width="5px" valign="top"> 
    <!-- (Floating DIV tag inside right hand side cell column) -->
    <div valign="bottom"><img src="../images/det2.gif" /></div></td>
    </tr>
    <tr height="5px"> 
    <td background="../images/BL.gif"></td>
    <td background="../images/B_Fill.gif"> 
    <!-- (Floating DIV tag inside bottom cell column) -->
    <div align="left"><img src="../images/det1.gif" /></div></td>
    <td background="../images/BR.gif" width="5px"></td>
    </tr>
    </table>');
    }

    ?>

    //Create a link from the slug to newsstory.php
    <li><a href="../newsstory.php?id=<? echo $row->id?>"><? echo $row->slug?></a> 
     
    //Display the date of the story
    <br>
    <? echo formatDate($row->timestamp); ?> 

    <p> 
    <?
    }
    // if no records present
    // display message
    else
    {
    ?>
    No press releases currently available 
    <?
    }
    // close database connection
    mysql_close($connection);
    ?>
    Thanks!
    Last edited by prendo; Apr 3, 2003 at 03:00.

  12. #12
    Sidewalking anode's Avatar
    Join Date
    Mar 2001
    Location
    Philadelphia, US
    Posts
    2,205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You'll want to replace
    PHP Code:
    echo $row['id'];
    echo 
    $row['slug'];
    echo 
    $row['timestamp']; 
    with
    PHP Code:
    ?>
    <li><a href="../newsstory.php?id=<?php echo $row['id']; ?>"><?php echo $row['slug']; ?></a>

    //Display the date of the story
    <br>
    <? echo formatDate($row['timestamp']);
    TuitionFree a free library for the self-taught
    Anode Says... Blogging For Your Pleasure

  13. #13
    SitePoint Wizard
    Join Date
    Oct 2001
    Posts
    2,686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem

    Now that the table/HTML problem is solved, you only need to concentrate on the formatting between the <!-- START CONTENT --> and <!-- FINISH CONTENT --> tags.

    The reason why the data is presented in one line is because of me . I didn't have the energy to format the code when I responded earlier.

    How do you want the data to presented?
    If you just want a linebreak, do this
    PHP Code:
    echo $row['id'];
    echo 
    '<br>';
    echo 
    $row['slug'];
    echo 
    '<br>';
    echo 
    $row['timestamp'];
    echo 
    '<br>'
    If you tell how you want it, I'll try to help out.

    -Helge

    Edit: I didn't notice the line with the link. Do to get it working, do as Anode suggested.

  14. #14
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Location
    Leeds, UK
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much guys. I can't explain how much I've learned from this. Probably more than on any other forum post I've ever made; on any forum.

    THIS page makes me very happy right now.

    Hope I can be as helpful someday!

  15. #15
    SitePoint Wizard
    Join Date
    Oct 2001
    Posts
    2,686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Glad you got it working

    Nice layout btw.

    -Helge

  16. #16
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Location
    Leeds, UK
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Helge

    Nice layout btw.

    -Helge
    Thanks Helge; glad you like it!

    Just one more question if I may

    I'd like to add a teaser to each article underneath the slug. This would basically be the first sentence or so of the news article; 50 characters would probably be enough.

    I know if I did the following:

    PHP Code:
    // each time this loop runs it will do whatever you put between the braces,
    // including outputting any html you want
       echo('<table>      
                <!-- START CONTENT -->
                ');
    ?>
          <a href="""newsstory.php?id=<?php echo $row['id']; <?php echo $row['slug']; ?></a> 
          <br>
    <?php echo $row['content']; ?>
       <br>
          <? echo formatDate($row['timestamp']);
       echo(
    ' <!-- FINISH CONTENT -->
              </table>'
    );
    }
     
     
    ?>
    ..it would display the whole article but how would I delimit this to the first 50 characters only? Would I need to use the count function?

  17. #17
    SitePoint Wizard
    Join Date
    Oct 2001
    Posts
    2,686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,
    You could just do a substr(), but that would just cut the sentence after 50 characters and that could be in the middle of a word.

    But in the user contributed notes in the manual, there is a solution to this. I've just it customized to your needs.
    Replace this line
    PHP Code:
    <?php echo $row['content']; ?>
    with
    PHP Code:
    <?php
    $str 
    $row['content'];
    if(
    strlen($str) > 50 ) {
        
    $str substr(trim($str) , 050); 
        
    $str substr($str0strlen($str)-strpos(strrev($str ), ' '));
        
    $str $str '...';
    }
    echo 
    $str;
    ?>
    That should do the trick.

    -Helge

  18. #18
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Location
    Leeds, UK
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Helge
    in the user contributed notes in the manual, there is a solution to this. I've just it customized to your needs.
    Thanks Helge; I did as you said but nothing seems to be outputting to screen. I also tried to check what's being retrieved by swapping:
    PHP Code:
     echo $str
    with
    PHP Code:
    echo('<pre>');
    print_r($str);
    echo(
    '</pre>'); 
    but this didn't display anything either.


    I have also just tried the substring method you mentioned to check if this works and I'm getting no output again:

    PHP Code:
    <a href="newsstory.php?id=<?php echo $row['id']; ?>">
    <?php echo $row['slug']; ?></a> 
    <br>
    <?php
    // display the first 50 characters of the story
    echo substr($row['content'], 050); ?>
    <br>
    <? echo formatDate($row['timestamp']);
    Any ideas what could be wrong? ..I suspect it could be something embarrassingly obvious but I just can't spot it!!
    Last edited by prendo; Apr 4, 2003 at 03:00.

  19. #19
    SitePoint Wizard
    Join Date
    Oct 2001
    Posts
    2,686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try to just print out $row['content'] without any substr().
    Does that print anything?

    -Helge

  20. #20
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Location
    Leeds, UK
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you mean like this:

    PHP Code:
    <?php
       
    // display the whole story
           
    echo $row['content'?>
    ..because this on it's own didn't produce anything.

    The code used to print the content field on the news article page is:

    PHP Code:
    // print details
    if ($row)
    {
    ?>
                       
    <p> <b><? echo $row->slug?></b> 
    <p> <? echo nl2br($row->content); ?>

    Should I try something like this instead?

  21. #21
    SitePoint Wizard
    Join Date
    Oct 2001
    Posts
    2,686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by prendo
    Should I try something like this instead?
    Probably not. That depends on what mysql_fetch_*() you're using.

    Do you actually select that coulm in your query, like this:
    Code:
    $query = "SELECT id, slug, content, timestamp FROM news ORDER BY timestamp DESC LIMIT 0, 5";
    -Helge

  22. #22
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Location
    Leeds, UK
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Helge
    Do you actually select that coulm in your query, like this:
    Code:
    $query = "SELECT id, slug, content, timestamp FROM news ORDER BY timestamp DESC LIMIT 0, 5";
    -Helge

    OMG!

    I'm so sorry for wasting your time like that Helge..

    That was such a noob mistake to make!!

    IT WORKS!!!

    Thanks for your patience..Hope I can repay you someday!


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
  •