Russia Posted March 16, 2011 Share Posted March 16, 2011 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 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. Quote Link to comment Share on other sites More sharing options...
Russia Posted March 16, 2011 Author Share Posted March 16, 2011 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. Quote Link to comment Share on other sites More sharing options...
litebearer Posted March 16, 2011 Share Posted March 16, 2011 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 ++; } Quote Link to comment Share on other sites More sharing options...
Russia Posted March 16, 2011 Author Share Posted March 16, 2011 For some reason its not listing it correctly, its listing the news incorrecting, it repeated the oldest rows thing. I need to instead get get 3 rows made before the oldest news. Here is the picture. Quote Link to comment Share on other sites More sharing options...
Russia Posted March 16, 2011 Author Share Posted March 16, 2011 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. Quote Link to comment Share on other sites More sharing options...
litebearer Posted March 16, 2011 Share Posted March 16, 2011 Show your most recent code Quote Link to comment Share on other sites More sharing options...
Russia Posted March 17, 2011 Author Share Posted March 17, 2011 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. Quote Link to comment Share on other sites More sharing options...
Russia Posted March 17, 2011 Author Share Posted March 17, 2011 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? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.