FRIHOST • FORUMS • SEARCH • FAQ • TOS • BLOGS • COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


News system with mySQL database.





ranzon
Hi, i've created a news system with mysql and php.
but i have a problem, how can i get the news to print the latest news first.

here's the code:
Code:
<?php
$username="username";
$password="password";
$database="news";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM nyheter";
$result=mysql_query($query) or die(mysql_error());
$num = mysql_num_rows($result);
mysql_close();
echo "<b><center>Nyheter</center></b><br><br>";

$i=0;
while ($i < $num) {

$navn=mysql_result($result,$i,"navn");
$emne=mysql_result($result,$i,"emne");
$nyhet=mysql_result($result,$i,"nyhet");
$tid=mysql_result($result,$i,"tid");
$dato=mysql_result($result,$i,"dato");

?>
<center>
<table width="532" border="1">
<tr><td width="522" height="95" valign="top">
<table width="532">
<tr>
<td height="21" colspan="2" valign="top">Emne: <? echo("". $emne ."");?><hr /></td>
</tr>
<tr>
<td height="39" colspan="2" valign="top">Nyhet:<br>      <? echo("". $nyhet ."");?><hr /></td>
</tr>
<tr>
<td width="328" height="21" valign="top">Nyheten er skrevet av: <? echo("". $navn .""); ?></td>
<td width="250" valign="top">Klokken: <? echo("". $tid .""); ?> Den: <? echo("". $dato .""); ?></td>
</tr>
</table>
</td>
</tr></table>
  <br /><br />
</center>
<?

$i++;
}

?>


i hope you guys could help me..
Jamatu
By the looks of you already have an auto_incrementing id row 'tid' if not you should add it.

Change
Code:
$query="SELECT * FROM nyheter";

to
Code:
$query = "SELECT * FROM nyheter ORDER BY tid DESC LIMIT 0,5";


'ORDER BY nyheter_id DESC' - Orders the news from the latest entry first to the oldest entry last

'LIMIT 0,5' - Limits the amount of news articles that will be shown, if you don't want to limit the amount show just remove this part.
ranzon
but, the Tid = time, and dato = date..
so if i think right, if the news beeing sorted by tid (wich is time) the are beeing sorted by, the time the getting posted..
but if is it possible to sort them by an id? like every post have an unique id, wich beeing set automaticly.
BlackSkad
You can first sort on date, and then on time like this:
Code:
"SELECT * FROM nyheter ORDER BY dato,tid DESC LIMIT 0,5"


Or, like you said, sort on the unique id (assuming the field with the id has the name id):
Code:
"SELECT * FROM nyheter ORDER BY id DESC LIMIT 0,5"


BlackSkad
ranzon
Does the sql database set an id automaticly, or do i have to set it manually?
and two more things, how to put the current date into an post variable automaticly?
like the date beeing put into the $_POST['dato'];
and the current time beeing put into the $_POST['time'];
with this date format: Date.Month.Year
it's the same if it beeing done in the script who saves it to the database, or in the file where the form is written

and how too print just the last news, who is written?
Jamatu
ranzon wrote:
Does the sql database set an id automaticly, or do i have to set it manually?

The database will set an id automatically if you set it to auto_increment. The id will need to have it's own field though. Check out this example field


ranzon wrote:
and two more things, how to put the current date into an post variable automaticly?
like the date beeing put into the $_POST['dato'];
and the current time beeing put into the $_POST['time'];
with this date format: Date.Month.Year

You could use a hidden input in the form that you use to insert the news
Code:
<input type="hidden" name="dato" value="<? echo date('d.m.Y',mktime()) ?>" />
<input type="hidden" name="time" value="<? echo date('H:i:s',mktime()) ?>" />

ranzon wrote:

it's the same if it beeing done in the script who saves it to the database, or in the file where the form is written

Don't get what your trying to say there.
ranzon wrote:
and how too print just the last news, who is written?

How do you mean? With an id field or not?

With the id field
Code:
$query = "SELECT * FROM nyheter ORDER BY id DESC LIMIT 0,1";

Without the id field (BlackSkad's code modified)
Code:
$query =  "SELECT * FROM nyheter ORDER BY dato,tid DESC LIMIT 0,1"
ranzon
i just want to print the latest news.. 1 news..
Jamatu
ranzon wrote:
i just want to print the latest news.. 1 news..

You already asked that in your previous post, and more Razz

The part your wanting is
Code:
$query =  "SELECT * FROM nyheter ORDER BY dato,tid DESC LIMIT 0,1"
ranzon
Thank you, it worked very well..
roee
nice system but you need to work more about style and css
ranzon
i know. i'm working on css and style now..

www.fredrik.frih.net <- there is the site, placed. it's in norwegian now, i'm going to work on an english version soon.
ranzon
do'h... got a new problem with this script:
it allways echos this date and time:
time: 11:40:52 date: 21.04.2006

with these scripts:

this for adding the news in mysql database:
Code:
<?
$username="";
$password="";
$database="";

$navn = $_POST['navn'];
$emne = $_POST['emne'];
$nyhet = $_POST['nyhet'];
$tid = $_POST['tid'];
$dato = $_POST['dato'];

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die("Fitte database");

$query = "INSERT INTO `nyheter` (`id`, `navn`, `tid`, `dato`, `nyhet`) VALUES ('', '$navn', '$tid', '$dato', '$nyhet')";

$result=mysql_query($query);

mysql_close();
?>


and this is the form code:

Code:
<form method="post" action="writenews.php">
Navn:<input name="navn" type="text" size="50" maxlength="15" /><br>
Emne:<input name="emne" type="text" size="50" maxlength="50" /><br>
Nyhet:<br><textarea name="nyhet" cols="50" rows="10"></textarea><br>
<input type="hidden" name="dato" value="<? echo date('d.m.Y',mktime()) ?>" />
<input type="hidden" name="tid" value="<? echo date('H:i:s',mktime()) ?>" />
<input type="submit">


and i'm using this script to read the news:
Code:
<?php
$username="";
$password="";
$database="";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die("Jζvla fitte mySQL");
$query = "SELECT * FROM nyheter";
$result=mysql_query($query) or die(mysql_error());
$num = mysql_num_rows($result);
mysql_close();

$i=0;
while ($i < $num) {

$id=mysql_result($result,$i,"id");
$navn=mysql_result($result,$i,"navn");
$emne=mysql_result($result,$i,"emne");
$tid=mysql_result($result,$i,"tid");
$dato=mysql_result($result,$i,"dato");
$nyhet=mysql_result($result,$i,"nyhet");

?>
<hr />
Nyheten er skrevet av: <? echo("$navn"); ?><br />
Emne: <?php echo("$emne"); ?><br />
Skrevet kl: <?php echo("$tid"); ?> den: <?php echo("$dato"); ?><br />
<?php echo("$nyhet"); ?>
<?php

$i++;
}

?>


someone know what i have to do to fix that? or do i have to write time and date manually to get it right?
Related topics
Problem installing a news system
Need Help with mysql database of DragonflyCMS
News system
mysql database table structure
Connecting to 2 different Mysql database and tables?
Optimizing a mySQL database
Maximum MYSQL database size
Cron Job to Backup MySQL Database
Mysql database problem
PHPBB MYSQL Database Question.
MySQL database for a forum
Mysql database size?
Coranto - News system
Connect to MySql database
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.