displaying data from sql on webpage

Liquid_Penguin

What I am trying to do is list the 2 most recent entries in a database side by side. I have tried repeated regions but that isnt working or I have done it wrong. Is there a tutorial I can reference to help acheive this??

TheDefender

Post the code you have and we can help you tweak it. You need an ORDER BY and LIMIT clause in your MySQL statement to order your results by the date of the entry and restricting it to the 2 latest. From there, it's a matter of simple HTML to display it. Without seeing your code, I can't be more specific in helping you though.

You don't really need a tutorial. You need basic MySQL, HTML and PHP understanding. From there, you can use the MySQL and PHP Manuals to do it. (They both have examples that can help a ton)

Liquid_Penguin

I believe this is what you need. I used repeat regions but what I want to do is limit it to the last 2 records ordered by group type put into the database. I also want them to display in different columns instead of rows.

$query_Recordset1 = "SELECT * FROM feature";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $insidexsi) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {
  $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
  $all_Recordset1 = mysql_query($query_Recordset1);
  $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

<table width="831" id="featable" >
  <?php do { ?>
  <tr>
    <td width="400"><img src="<?php echo $row_Recordset1['story_ImgURL']; ?>"></td>
    <td width="15">&nbsp;</td>
    <td width="400">&nbsp;</td>
  </tr>
  <tr>
    <td id="featext"><?php echo $row_Recordset1['story_teaser']; ?></td>
    <td>&nbsp;</td>
    <td id="featext"></td>
  </tr>
  <tr>
    <td id="more"><a href="story_detail.php?story_ID=<?php echo $row_Recordset1['story_ID']; ?>">Read More</a> </td>
    <td>&nbsp;</td>
    <td id="more">&nbsp;</td>
  </tr>
  <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>

TheDefender

I may be missing something, but it seems like you are overcomplicating things here.

<?php
$query_Recordset1 = mysql_query("SELECT * FROM feature ORDER BY table_id DESC LIMIT 2");  //Change table_id to the field you want to do your sorting on.

echo "<table><tr>";
while($row_Recordset1 = mysql_fetch_array($query_Recordset1)){
    echo "<td>";
    echo "<img src=\"". $row_Recordset1['story_ImgURL'] ."\" /><br />";
    echo $row_Recordset1['story_teaser'] ."<br />";
    echo "<a href=\"story_detail.php?story_ID=". $row_Recordset1['story_ID'] ."\">Read More</a>";
    echo "</td>";
}
echo "</tr></table>";
?>

Liquid_Penguin

That is working so far a little. Thank you very much.

I need to show that image but all I get now is the link to it and the story_ID is to be a link to the main story and all I get is the ID number.

leatherback

Hi Penguin,

the code that defender posted, was to show you that your code to retrieve records was very complex. you can now just add the HTML coding you need, around the code he gave you. That would be add the ,img src=\"\"> etcetc.

Liquid_Penguin

Here is my modified version but it isn't executing it when I add the HTML code.

<?php
echo "<table><tr>"; 
while($row_Recordset1 = mysql_fetch_array($query_Recordset1)){ 
    echo "<td>"; 
    <img src="echo $row_Recordset1['story_ImgURL'] ."<br />";">
    echo $row_Recordset1['story_teaser'] ."<br />"; 
    <a href="story_detail.php?story_ID= echo $row_Recordset1['story_ID'];">Read More</a> 
    echo "</td>"; 
} 
echo "</tr></table>"; 
?>

I think I have something wrong in there but can't find it.

TheDefender

I modified my original code for you...

Liquid_Penguin

Thank you!!! That is what I needed.

I see the code is within the PHP. Does formatting the table have to be within PHP also?? (i.e. Text color and spacing)

TheDefender

Different people have differing thoughts and tests to show different methods work better.

Some people argue that you should always bounce in and out of PHP within your HTML code. Others recommend the use of HEREDOC syntax. Still others always combine the php and HTML like I did here.

In this case, I personally would use a linked stylesheet, and add the applicable class definitions to the various table elements. The reason I wrote it the way I did was because the table row was generated within a PHP while loop, and you lose some speed (albeit negligible on small tables) when you bounce in and out of PHP. It's a combination of personal preferences and application needs.

Liquid_Penguin

When I tried to bounce in and out it wasn't executing. I'm new to php and I think jumping right in a little too far but I appreciate your help. I did have that table named as well as the td's. I tried to name them again and it won't execute. I guess I am not coding it right for PHP to understand but this is what I did.

<?php 
echo "<table id="featable"><tr>"; 
while($row_Recordset1 = mysql_fetch_array($query_Recordset1)){ 
    echo "<td>"; 
    echo "<img src=\"". $row_Recordset1['story_ImgURL'] ."\" /><br />"; 
    echo $row_Recordset1['story_teaser'] ."<br />"; 
    echo "<a href=\"story_detail.php?story_ID=". $row_Recordset1['story_ID'] ."\">Read More</a>"; 
    echo "</td>"; 
} 
echo "</tr></table>"; 
?>

I have 2 different td id's...One was for the link styling and the other for the text of the teaser text but I see where you put one td only.

Another thing was I am ordering them by the ID. I have 5 different sections that I want on this page that will just show 2 or 3 items from each group. There is one table with each entry having a group_type identification but all types of stories going in there. I am not sure how to ask this but each section will be a group_type and I want the two most recent stories to show. I can now code the table but how do I get each one to recognize only the 2 most recent from the group I want in that section?

leatherback

Hi,

Your problem is in the double quotes.

Every time you place an echo statemen, ONLY have the double quotes without a backslash if it is the start of the end of the echo statement.

Your code would be:

echo "<table id=\"featable\"><tr>";

As for the other part:

Two options:

you can either first select which sections are there, and do a query to your database for each section, with a limit 0, 2 added to the end (That will limit the number of records retrieved). Or you can select all records, ordered by section and insertion date, afte which you test in a loop whether you have already displayed two records for that class.

$result = mysql_query("select * from table order by section asc, insertdate desc");
while($row = mysql_fetch_array($result))
  {
  if($thissection == $row['section'])
    {
     if($displayed <= 1)
       {
        // show record
         $displayed++;
        }
      }
   else
      {
       $thissection =  $row['section']);
        $displayed = 1;

    // show record
 }
}

Liquid_Penguin

So the \" seperates different elements of a tag or any other tag as in if I was to add a border I would add it as such?

echo "<table id=\"featable\"border=1\"><tr>";

leatherback

Hope,

Maybe you should have a look at general HTML elements too..

All you are trying to do with PhP is generate valid HTML/DHTML/... code.
In html tags, you often need " " around values. But since PhP interprets these as 'text identifiers', you need to show PhP that these are to be seen as an integral part of the string. Therefor you add a \, resulting in \". So you get

echo "<table id=\"featable\" border=\"1\"><tr>";

BUT you would be better f using stylesheets to define borders etc. This because if you later on decide to change the layout, you only change hte class layout, instead of all your codes..

Liquid_Penguin

I understand to use style sheets I was just trying to understand how that line code is working and what the \" mean. I am understanding them as " used in normal HTML markup now. Am I right to believe that?

echo "<table id=\"featable\"border=\"1\"><tr>";

<table id="featable" border="1"><tr> in html

" opens and closes an echo and \" acts as " when coding php to process html elements??

So if I wantd to define the td I would code it as such....

echo "<td id=\"more\">";

Liquid_Penguin

The second question of mine you replied with

$result = mysql_query("select * from table order by section asc, insertdate desc"); 
while($row = mysql_fetch_array($result)) 
  { 
  if($thissection == $row['section']) 
    { 
     if($displayed <= 1) 
       { 
        // show record 
         $displayed++; 
        } 
      } 
   else 
      { 
       $thissection =  $row['section']); 
        $displayed = 1; 

    // show record 
 } 
}

Edited for my use would be this?

$result = mysql_query("select * from table order by group_ID DESC LIMIT 2"); 
while($row = mysql_fetch_array($result)) 
  { 
  if($thissection == $row['group_ID']) 
    { 
     if($displayed <= 1) 
       { 
        // show record 
         $displayed++; 
        } 
      } 
   else 
      { 
       $thissection =  $row['group_ID']); 
        $displayed = 1; 

    // show record 
 } 
}

As far as I can understand it it will ask if displayed is less then or equal to 1 it will show it and if not it will show 1? How would I show 2's or 3's?? Just change the numbers?

//edit After looking it over more and trying to understand it...I have no clue what that does or where it would goto. I need a little more thorough explanation of it if that is possible. I have tried reading it and placing it but I can't figure it out. 🙁

Maybe I explained it wrong. There are 5 types of news. 5 different tables I want to display each type in. I am currently displaying the last 2 entries in the database in one table. I want each table to have one type of news and to only grab the last 2 entries for that type from the database. I am using dreamweaver cause my knowledge of php is very nil but the basics. Dreamweaver is what created that very complex php earlier that was simplied. If possible cause I want to learn this and not just get code would like it explained to me in the simplest of forms so I can build off what you guys show me. I understand the code I got earlier but the latest code for sorting is a loss to me as I have no clue what it does. Any help in the code and learning it would be greatly appreciated.

Code so far:

<?php require_once('folder/file.php'); ?>
<?php
mysql_select_db($database_insidexsi, $insidexsi);
$query_stories = "SELECT * FROM feature";
$stories = mysql_query($query_stories, $insidexsi) or die(mysql_error());
$row_stories = mysql_fetch_assoc($stories);
$totalRows_stories = mysql_num_rows($stories);
$query_stories = mysql_query("SELECT * FROM feature ORDER BY story_ID DESC LIMIT 2");  

?>

Displaying results PHP:

<?php 
echo "<table id=\"featable\"><tr>";  

while($row_stories = mysql_fetch_array($query_stories)){ 
	echo "<td>"; 
    echo "<img src=\"". $row_stories['story_ImgURL'] ."\" /><br />"; 
    echo $row_stories['story_teaser'] ."<br />"; 
    echo "<a href=\"story_detail.php?story_ID=". $row_stories['story_ID'] ."\">Read More</a>"; 
    echo "</td>"; 
} 
echo "</tr></table>"; 
?>

I understand the first one a little bit and the second one I understand now after reading it over.

Roger_Ramjet

The simplest way to do this would be to run 5 queries, one for each type. Add WHERE type='....' to the query you now have to achieve this.

You would then use the code you have to display a table for each set of query results.

You can reduce the 5 queries to one with a UNION QUERY, which would make that part more efficient but the display code more complex.

Liquid_Penguin

Ok I got it. Thank you.

This is what I did. Is this the right way??

$query_stories = mysql_query("SELECT * FROM articles WHERE group_type='Feature' ORDER BY story_ID  DESC LIMIT 2");   

$query_stories1 = mysql_query("SELECT * FROM articles WHERE group_type='Industry' ORDER BY story_ID  DESC LIMIT 2");
$query_stories2 = mysql_query("SELECT * FROM articles WHERE group_type='Site' ORDER BY story_ID  DESC LIMIT 2");
$query_stories3 = mysql_query("SELECT * FROM articles WHERE group_type='Jobs' ORDER BY story_ID  DESC LIMIT 2");
$query_stories4 = mysql_query("SELECT * FROM articles WHERE group_type='Tutorials' ORDER BY story_ID  DESC LIMIT 2");

<?php 
echo "<table id=\"featable\"><tr>";  

while($row_stories = mysql_fetch_array($query_stories)){ 
	echo "<td>"; 
    echo "<img src=\"". $row_stories['story_ImgURL'] ."\" /><br />"; 
    echo $row_stories['story_teaser'] ."<br />"; 
    echo "<a href=\"story_detail.php?story_ID=". $row_stories['story_ID'] ."\">Read More</a>"; 
    echo "</td>"; 
} 
echo "</tr></table>"; 
?> 
<?php 
echo "<table id=\"featable\"><tr>";  

while($row_stories = mysql_fetch_array($query_stories2)){ 
	echo "<td>"; 
    echo "<img src=\"". $row_stories['story_ImgURL'] ."\" /><br />"; 
    echo $row_stories['story_teaser'] ."<br />"; 
    echo "<a href=\"story_detail.php?story_ID=". $row_stories['story_ID'] ."\">Read More</a>"; 
    echo "</td>"; 
} 
echo "</tr></table>"; 
?>

Roger_Ramjet

The only 'right way' is one that works for you.

Having got this working you can now move on to modify it so that it works with 1 query.

All 5 of your current queries can be run at once as a UNION query

SELECT * FROM articles WHERE group_type='Feature' ORDER BY story_ID  DESC LIMIT 2
UNION
SELECT * FROM articles WHERE group_type='Industry' ORDER BY story_ID  DESC LIMIT 2
UNION

// etc

This will return 1 resultset with the 10 rows in the same order as the original 5 queries.

What you would then need to do is to wrap your table generation routine into an outer loop with a loop count or a test for group_type changes. It would then create a seperate table for each type as it ran through the 1 resultset.

Give it a try, but keep a copy of the script you have now that works.

Liquid_Penguin

Thanks everyone for all your help.

Is UNION only used for joining multiple queries?

What you would then need to do is to wrap your table generation routine into an outer loop with a loop count or a test for group_type changes. It would then create a seperate table for each type as it ran through the 1 resultset.

Roger. Do you have a test script I can dissect and try to put that second part together and to also learn it? I have no clue what you meant being new to PHP.

I tried that UNION and can't figure out how to get it to work.

$query_stories = mysql_query ("SELECT * FROM articles WHERE group_type='Feature' ORDER BY story_ID  DESC LIMIT 2");
UNION 
mysql_query("SELECT * FROM articles WHERE group_type='Industry' ORDER BY story_ID  DESC LIMIT 2");
UNION 
mysql_query("SELECT * FROM articles WHERE group_type='Site' ORDER BY story_ID  DESC LIMIT 2");
UNION 
mysql_query("SELECT * FROM articles WHERE group_type='Jobs' ORDER BY story_ID  DESC LIMIT 2");
UNION 
mysql_query("SELECT * FROM articles WHERE group_type='Tutorials' ORDER BY story_ID  DESC LIMIT 2");

I tried it with and without the quotes, slashes, mysql_query and can't figure out how it should be done.