Jump to content

How to show news by time it was submitted

Russia

By Russia
in PHP Coding Help

 Share

Recommended Posts

Russia Regular Member

Russia

Posted
Posted

hey guys im having trouble showing the news by the date it was submitted, I want to also seperate it into 3 seperate parts,

 

first section will have the Main news which is only 1 row, it will have the value 1 in the column named type (is this news main or recent). The date on it doesn't matter because i want to show it even tho there might be news that is ahead of it.

 

The next section will have 3 rows of recent news parts. I want to show the 3 most recent news that there are in the database with the value 2 in the column named type. I want it to show the recent news by the date submitted.

 

Okay the last part will have the old news. There I want to show 3 rows of news that is after the 3 rows of news in recent news. That value will be 2 in the column named type.

 

Here is my current code.

 

<?php
session_start();
if(isset($_SESSION['username']))
{

mysql_connect("localhost","root","");
mysql_select_db("chat");


        $result = mysql_query("SELECT * FROM `news` WHERE `type`='1' ORDER BY `news`.`newsid` DESC LIMIT 1");
        $query = mysql_query("SELECT * FROM news WHERE `type`='1' ");
        //$ifadmin_result = mysql_query("SELECT * FROM users WHERE `username`='$loggedin' ");

if(mysql_num_rows($query)!=0)
{
$loggedin = $_SESSION['username'];
    $myrow = mysql_fetch_array($result);
       echo "<b>Title: ";
       echo $myrow['title'];
       echo "</b><br>On: <i>";
       echo $myrow['dtime'];
       echo "</i><hr align=left width=160>";
       echo $myrow['text1'];
       
       
       //check if admin
       $ifadmin_result = mysql_query("SELECT * FROM users WHERE `username`='$loggedin'");
       $ifadmin = mysql_fetch_array($ifadmin_result);
       if ($ifadmin['admin'] == 1) { 
       echo "<br><a href=\"read_more.php?newsid=$myrow[newsid]\">Read More...</a>
        || <a href=\"edit_news.php?newsid=$myrow[newsid]\">Edit</a>
         || <a href=\"delete_news.php?newsid=$myrow[newsid]\">Delete</a><BR><BR>";
            }  
                else {
                echo "<br><a href=\"read_more.php?newsid=$myrow[newsid]\">Read More...</a><BR>";
            }
         //check if admin
         
       echo "<hr align=left width=500>";
       }
       else
       {
       echo "no rows<br>";
       }

        $iCount=0;
        $var=false;
        //RECENT
        $result = mysql_query("SELECT * FROM `news` WHERE `type`='2' ORDER BY `news`.`newsid` DESC LIMIT 6");
        //lets make a loop and get all news from the database
       while($myrow = mysql_fetch_array($result))
             {//begin of loop
               //now print the results:
               $iCount = $iCount+1;
                if($iCount >= 4) {
                    if($var == false) {
                    $var=true;
                           echo "<hr align=left width=500>";
                    echo '<div> More News</div';
                    }
                    //Generate your refcent news here echo the title etc.ma
                   echo "<div><b>Title: ";
                   echo $myrow['title'];
                   echo "</b> <i>On:" . $myrow['dtime'] . '</i></div>';
                   echo "<a href=\"read_more.php?newsid=$myrow[newsid]\">Read More...</a>";
                } 
                else
                {
               echo "<b>Title: ";
               echo $myrow['title'];
               echo "</b><br>On: <i>";
               echo $myrow['dtime'];
               echo "</i><br>";
               //echo "<hr align=left width=160>";
               echo $myrow['text1'];
               // Now print the options to (Read,Edit & Delete the news)
               
               
                              $ifadmin_result = mysql_query("SELECT * FROM users WHERE `username`='$loggedin'");

       $ifadmin = mysql_fetch_array($ifadmin_result);
       if ($ifadmin['admin'] == 1) 
            { 
       echo "<br><a href=\"read_more.php?newsid=$myrow[newsid]\">Read More...</a>
        || <a href=\"edit_news.php?newsid=$myrow[newsid]\">Edit</a>
         || <a href=\"delete_news.php?newsid=$myrow[newsid]\">Delete</a><BR><hr align=\"left\" width=\"160\">";
            }  
                else
            {
                echo "<br><a href=\"read_more.php?newsid=$myrow[newsid]\">Read More...</a><hr align=\"left\" width=\"160\">";
            }
               
               
               
               
               
               
               
               
               /*
               echo "<br><a href=\"read_more.php?newsid=$myrow[newsid]\">Read More...</a>
                || <a href=\"edit_news.php?newsid=$myrow[newsid]\">Edit</a>
                 || <a href=\"delete_news.php?newsid=$myrow[newsid]\">Delete</a><br><hr align=\"left\" width=\"160\">";
                 */
                }
                
                
             }//end of loop
             
}
else
{

mysql_connect("localhost","root","");
mysql_select_db("chat");

$result = mysql_query("SELECT * FROM `news` WHERE `type`='1' ORDER BY `news`.`newsid` DESC LIMIT 1");
        $query = mysql_query("SELECT * FROM news WHERE `type`='1' ");
if(mysql_num_rows($query)!=0)
{
    $myrow = mysql_fetch_array($result);
       echo "<b>Title: ";
       echo $myrow['title'];
       echo "</b><br>On: <i>";
       echo $myrow['dtime'];
       echo "</i><hr align=left width=160>";
       echo $myrow['text1'];
       echo "<br><a href=\"read_more.php?newsid=$myrow[newsid]\">Read More...</a><BR>";
       echo "<hr align=left width=500>";
       } else {
       echo "no rows<br>";
       }
        $iCount=0;
        $var=false;
        //RECENT
        $result = mysql_query("SELECT * FROM `news` WHERE `type`='2' ORDER BY `news`.`newsid` DESC LIMIT 6");
        //lets make a loop and get all news from the database
       while($myrow = mysql_fetch_array($result)) {
       //begin of loop
               //now print the results:
               $iCount = $iCount+1;
                if($iCount >= 4) {
                    if($var == false) {
                    $var=true;
                           echo "<hr align=left width=500>";
                    echo '<div> More News</div';
                    }
                    //Generate your refcent news here echo the title etc.ma
                   echo "<div><b>Title: ";
                   echo $myrow['title'];
                   echo "</b> <i>On:" . $myrow['dtime'] . '</i></div>';
                   echo "<a href=\"read_more.php?newsid=$myrow[newsid]\">Read More...</a>";
                } else {
               echo "<b>Title: ";
               echo $myrow['title'];
               echo "</b><br>On: <i>";
               echo $myrow['dtime'];
               echo "</i><br>";
               //echo "<hr align=left width=160>";
               echo $myrow['text1'];
               // Now print the options to (Read,Edit & Delete the news)
               echo "<br><a href=\"read_more.php?newsid=$myrow[newsid]\">Read More...</a><hr align=left width=160>"; 
                }
                                                    }//end of loop        





}             
?>

<br>
<hr align=left width=500>
<!--
<hr align=left width=500>
<br><br>
<a href=index.php>Home</a> 

<a href=add_news.php>Add News</a>
-->

 

Here is how my database table looks like

U24vf.png

 

Dont mind the number of rows it is, I want it to show the first I guess 7 rows of news that is dated to the earliest time from present.

There soon will be hundreds of rows.

 

Here is how I made it fill in the row with the most recent time it was posted.

NOW()

in

$result = mysql_query("INSERT INTO news (title, dtime, text1, text2, type) VALUES ('$title',NOW(),'$text1','$text2','$type')");

 

Basically I want it to show the most recent rows based on the time they were posted.

 

 

 

Also, incase you need more information please post and il give me information.

 

 

 

 

Link to comment
Share on other sites
Russia Regular Member

Russia

Posted
  • Author
Posted

An individual told me to do this:

 

ORDER BY `news`.`newsid`

That's what sorts the results. Change it to use the date and copy it into the other queries.

 

How would I do it so it shows it by the date?

 

Also, wont it think that the values in the database are just random numbers, how do I make it recognise that it is time and date values?

 

Here is something I found online.

 

DATE_FORMAT(postdate, '%Y-%m-%d') as date

 

from

 

$query = "SELECT *," .
"DATE_FORMAT(postdate, '%Y-%m-%d') as date " .
"FROM news ORDER BY id DESC LIMIT $news_limit";

 

How do I actually get that to work in my current code.

 

Link to comment
Share on other sites
litebearer Member

litebearer

Posted
Posted

Perhaps something like this...

/* create query for first item */
$query1 = "SELECT * FROM news WHERE type = 1 limit 1";
/* execute the query */
$result1 = mysql_query($query1);
/* put the returned data into an array */
$row = mysql_fetch_array($result1);
echo $row['dtime'] . " - " .  $row['title'] . "<br/>";
echo $row['text1'] . "<br/><br/>";
echo $row['text2'] . "<hr>";

/* create query for next group */
$query2 = "SELECT * FROM news WHERE type = 2 ORDER BY dtime LIMIT 6";
/* execute the query /*
$result2 = mysql_query($query2);
/* loop thru the results for the 3 most recent news - they will be 0,1,2 */
$w_group=0;
while($row2 = mysql_fetch_array($result2)){
if($w_group<3){
	echo $row2['dtime'] . " - " .  $row2['title'] . "<br/>";
	echo $row2['text1'] . "<br/><br/>";
	echo $row2['text2'] . "<hr>";
}else{
	echo $row2['dtime'] . " - " .  $row2['title'] . "<br/>";
	echo $row2['text1'] . "<br/><br/>";
	echo $row2['text2'] . "<hr>";
}
$w_group ++;
}

Link to comment
Share on other sites
Russia Regular Member

Russia

Posted
  • Author
Posted

Okay, I added another row of news that says Newest recent news and it doesnt show up...

 

Its show the same thing and doesnt move the news down to the newest row that there is.

 

Im thinking of just making it so it seperates it into 3 different types, So there are 3 options, 1,2, or 3.

Link to comment
Share on other sites
Russia Regular Member

Russia

Posted
  • Author
Posted

Here you go mate, I added in my own css but dont mind it.

 

here:

 

<div class="section">
<div class="sectionBody">
<div class="sectionHeight">
<?php
if(isset($_SESSION['username']))
{



	$result = mysql_query("SELECT * FROM `news` WHERE `type`='1' ORDER BY `news`.`newsid` DESC LIMIT 1");
	$query = mysql_query("SELECT * FROM news WHERE `type`='1' ");
	//$ifadmin_result = mysql_query("SELECT * FROM users WHERE `username`='$loggedin' ");

if(mysql_num_rows($query)!=0)
{
$loggedin = $_SESSION['username'];
$myrow = mysql_fetch_array($result);
			echo "<div class=\"newsTitle\"><h3>".$myrow['title']."</h3><span>".$myrow['dtime']."</span></div>";
			echo "<a class=\"newsImage\" href=\"news.php?newsid=".$myrow['newsid']."\"><img src=\"img/missing_my_mummy.jpg\"/><br /></a>";
   
echo "
<div class=\"newsDesc\">
<p>
".$myrow['text1']."
</p>
</div>
<a href=\"news.php?newsid=".$myrow['newsid']."\">Read more...</a>
"; 
   
   
   //echo "<b>Title: ";
   //echo $myrow['title'];
   //echo "</b><br>On: <i>";
   //echo $myrow['dtime'];
   //echo "</i>";
   
   
   
   
   
   
   //check if admin
   $ifadmin_result = mysql_query("SELECT * FROM users WHERE `username`='$loggedin'");
   $ifadmin = mysql_fetch_array($ifadmin_result);
   if ($ifadmin['admin'] == 1) { 
   echo "|| <a href=\"edit_news.php?newsid=$myrow[newsid]\">Edit</a>
	 || <a href=\"delete_news.php?newsid=$myrow[newsid]\">Delete</a><BR><BR>";
		}  
			else {
			//echo "<br><a href=\"read_more.php?newsid=$myrow[newsid]\">Read More...</a><BR>";
		}




	 //check if admin
	 	   }
   else
   {
   echo "no rows<br>";
   }



}

else
{


$result_main = mysql_query("SELECT * FROM `news` WHERE `type`='1' ORDER BY `news`.`newsid` DESC LIMIT 1");
	$query_main = mysql_query("SELECT * FROM news WHERE `type`='1' ");
if(mysql_num_rows($query_main)!=0)
{
$myrow_main = mysql_fetch_array($result_main);

echo "<div class=\"newsTitle\"><h3>".$myrow_main['title']."</h3><span>".$myrow_main['dtime']."</span></div>";
echo "<a class=\"newsImage\" href=\"news.php?newsid=".$myrow_main['newsid']."\"><img src=\"img/missing_my_mummy.jpg\"/><br /></a>";
echo "
<div class=\"newsDesc\">
<p>
".$myrow_main['text1']."
</p>
</div>
<a href=\"news.php?newsid=".$myrow_main['newsid']."\">Read more...</a>
";	  
   } else {
   echo "no rows<br>";
   }
//end of loop		
}			 
?>
</div>
</div>
</div>



</div>
<div id="recentnews">
<div class="sectionHeader">
<div class="left">
<div class="right">
<div class="plaque">
<img src="img/recentnews.png" alt="Recent News" />
</div>
</div>
</div>
</div>
<div class="section">





<?php

//$iCount=10;
	$var=false;
	//RECENT
        $result = mysql_query("SELECT * FROM `news` WHERE `type`='2' ORDER BY `dtime` LIMIT 3,3");
        //lets make a loop and get all news from the database
   while($myrow = mysql_fetch_array($result))
             {
	$ifadmin_result = mysql_query("SELECT * FROM users WHERE `username`='$loggedin'");
	$ifadmin = mysql_fetch_array($ifadmin_result);

echo "<div class=\"sectionBody\"><div class=\"recentNews\"><div class=\"newsTitle\">";
echo "<h3>".$myrow['title']."</h3>";
echo "<span>".$myrow['dtime']."</span>";
echo "</div><img src=\"img/duelarena.jpg\" />";
echo "<p>".$myrow['text1']."</p>";

if ($ifadmin['admin'] == 1) 
		{ 
   echo "<br><a href=\"news.php?newsid=$myrow[newsid]\">Read More...</a>
	|| <a href=\"edit_news.php?newsid=$myrow[newsid]\">Edit</a>
	 || <a href=\"delete_news.php?newsid=$myrow[newsid]\">Delete</a>";
		}  
			else {
			echo "<br><a href=\"news.php?newsid=$myrow[newsid]\">Read More...</a>";
		}
echo "</div>";
echo "</div>";		
             }//end of loop
?>


</div>
</div>

<div id="morenews">
<div class="sectionHeader">
<div class="left">
<div class="right">
<div class="plaque">
<img src="img/morenews.png" alt="More News" />
</div>
</div>
</div>
</div>
<div class="section">
<div class="sectionBody">

<?php

	//RECENT
        $result_oldest = mysql_query("SELECT * FROM `news` WHERE `type`='2' ORDER BY `dtime` LIMIT 3");
        //lets make a loop and get all news from the database
   while($myrowoldest = mysql_fetch_array($result_oldest)) {
   //begin of loop

			   echo "<div class=\"more\">";
			   echo "<div class=\"moreTitle\">".$myrowoldest['title']."</div>";
			   echo "<div class=\"moreMore\"><a href=\"news.php?newsid=$myrowoldest[newsid]\">Read more...</a></div>";
			   echo "<div class=\"moreDate\">".$myrowoldest['dtime']."</div>";
			   echo "<br class=\"clear\" /></div>";

												}
?>








</div>
<div class="moreArchive">
<?php
	$ifadmin_result = mysql_query("SELECT * FROM users WHERE `username`='$loggedin'");
	$ifadmin = mysql_fetch_array($ifadmin_result);
if ($ifadmin['admin'] == 1) 
		{ 
   echo "<a href=\"add_news.php\">Add News</a>";
		} 
		?>

 

Do you want me to update the datbase in a 1,2,3,4,5,6,7 format so you understand what I am trying to do, then edit the page im trying to show it on with 1,2,3,4,5,6,7 in seperate format?

 

Also, I dont want it to be like hardcoded into the mysql as row 3.

 

Soon I will have hundreds of rows of news with a main news and a recent/oldest news.

 

 

Link to comment
Share on other sites
Russia Regular Member

Russia

Posted
  • Author
Posted

I managed to change the script around and it seems to work great now.

 

Now im trying to do one more thing, that if I make another row with the value 2 for the type of news it is, I want to automatically turn the oldest news with the value 2 in the type column to '3'.

 

Is there any kind of query to update the row to '3' if a row is found with the same value and update the oldest news that was posted with the value 2. Also, I want it to work that if only there are 3 rows in there with the same value.

 

Id be happy if I could get some help.

 

Is there anything that can do it like that? or can that not be created?

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.