News Managament system, 99% complete! HELP!

problems with News Management system PHP/MySQL

[cyberjunky]

Hi people, i made this news management system for my homepage of my site but im having some problems with it, im new to PHP keep in mind so if i dont everything the wrong or long winded way then my apologies, the thing is i got problems with the order the articles are displayed in they appear in random order, the same way as they are written to the database. i tried to use auto_increment and stuff but that didnt work in another way, so its like eliminating one problems brings another, my db connection and stuff is all done in a config, im only showing you the reletiv code to keep my site safe, if you need to know more then just let me know by PM'ming me. the problems arent very specific so if you could just help me fix any found problems and make this run real nice like then id be greatful, also this is like for everyone so feel free to use the script on your own sites if you like.

if ($nav == "edit") {
include ("inc_header.inc.php");
if ($_SESSION['Logged_in'] == "1") {
$area=$_GET['area'];
if ($_SESSION['Auth'] == "Moderator" ¦¦ $_SESSION['Auth'] == "Administrator") {
if ($area == "home") {
$id=$_GET['item'];
$mode=$_GET['mode'];
$query="SELECT Message FROM News WHERE ID='$id'";
$result=mysql_query($query);
$num=mysql_numrows($result);
$message=mysql_result($result,"Message");
$query="SELECT Topic FROM News WHERE ID='$id'";
$result=mysql_query($query);
$num=mysql_numrows($result);
$topic=mysql_result($result,"Topic");
// if mode = 1 then EDIT dialog should be shown
if ($mode == "1") {
if ($_SESSION['Auth'] == "Moderator" ¦¦ $_SESSION['Auth'] == "Administrator") {
if ($_SESSION['Username'] == $author ¦¦ $_SESSION['Auth'] == "Administrator") {
print "<form action='index.php?nav=edit&area=home&mode=2&item=$id' method='POST'><table class='sub'><tr><td class='header' colspan='2'>Edit A News Item.</td></tr><tr><td class='content'>&nbsp;Topic:&nbsp;</td><td class='content'>&nbsp; $topic &nbsp;</td></tr><tr>
<td class='content'>&nbsp;Message:&nbsp;</td><td class='content'>&nbsp;<textarea name='message' cols='75' rows='8' wrap='virtual' class='text'>$message</textarea>&nbsp;</td></tr><tr><td class='content' colspan='2'><center><input type='submit' name='Submit' value='Submit' class='submit'>&nbsp;&nbsp;&nbsp;<input type='reset' name='reset' value='Clear Form' class='submit'></center></td></tr></table></form>";
} else {
print "<table class='sub'><tr><td clas='header'>Encountered Problem</td></tr><tr><td class='content'>This area Exceeds your Authorisation, you do not have access to this feature, please contact your administrator if you need to use this feature.</td></tr></table>";
}
}
}
// if mode = 2 then post the changes and show success page
if ($mode == "2") {
// Get all data before rewritting the Record.
$topic=mysql_result($result,"Topic");
$author=mysql_result($result,"Author");
$date=mysql_result($result,"Date");
$ip=mysql_result($result,"IP");
// Get message from variable before deleting old message
$message=$_POST['message'];
// Update Record.
$query="UPDATE News SET Message='$message' WHERE ID='$id'";
mysql_query($query);
// Show completion screen
print "<table class='sub'><tr><td class='header'>Success</td></tr><tr><td class='content'>The News Item was successfully edited on item $id.</td></tr></table>";
}
}
} else {
print "<table class='sub'><tr><td clas='header'>Encountered Problem</td></tr><tr><td class='content'>This area Exceeds your Authorisation, you do not have access to this feature, please contact your administrator if you need to use this feature.</td></tr></table>";
}
} else {
print "<table class='sub'><tr><td class='header'> Encountered Problem </td></tr><tr><td class='content'>You are not logged in, You must be logged in to use this feature.</td></tr></table>";
}
include ("inc_footer.inc.php"); mysql_close(); exit();
}
if ($nav == "delete") {
include ("inc_header.inc.php");
if ($_SESSION['Logged_in'] == "1") {
$area=$_GET['area'];
$id=$_GET['item'];
// Get Records Author to determin whether Delete action is allowed
$query="SELECT Author FROM News WHERE ID='$id'";
$result=mysql_query($query);
$num=mysql_numrows($result);
$author=mysql_result($result,"Author");
if ($_SESSION['Username'] == $author ¦¦ $_SESSION['Auth'] == "Administrator") {
if ($area == "home") {
$query="DELETE FROM News WHERE ID='$id'";
mysql_query($query);
// after deleting the record all of the ID fields after the record need to be shifted
// ---- STEP 1 Get all records, so find the number of records
$query="SELECT * FROM News";
$result=mysql_query($query);
$num=mysql_numrows($result);
// ---- STEP 2
if (!$id == "0") {
while ($id < $num) {
$val=$id;
$query="UPDATE News SET ID='$val' WHERE ID='$id'";
mysql_query($query);
$id++;
}
}
print "<table class='sub'><tr><td class='header'>Success</td></tr><tr><td class='content'>The News Item was successfully deleted.</td></tr></table>";
}
} else {
print "<table class='sub'><tr><td class='header'>Encountered Problem</td></tr><tr><td class='content'>This area Exceeds your Authorisation, you do not have access to this feature, please contact your administrator if you need to use this feature.</td></tr></table>";
}
} else {
print "<table class='sub'><tr><td class='header'> Encountered Problem </td></tr><tr><td class='content'>You are not logged in, You must be logged in to use this feature.</td></tr></table>";
}
include ("inc_footer.inc.php"); mysql_close(); exit();
}
if ($nav == "post") {
$area=$_GET['area'];
if ($area == "home") {
if ($_SESSION['Logged_in'] == "1") {
if ($_SESSION['Auth'] == "Moderator" ¦¦ $_SESSION['Auth'] == "Administrator") {
// Get the data from the form
$topic=$_POST['topic'];
$message=$_POST['message'];
// Convert to session variables and date into normal variables
$username=$_SESSION['Username'];
$date=date("D dS M, Y");
// Ensure that if the topic is the first then reset the ID number
$query="SELECT * FROM News";
$result=mysql_query($query);
$num=mysql_numrows($result);
if ($num == "0") {
$query="INSERT INTO News VALUES('1','$topic','$username','$date','$message','$ip')";
} else {
$num=$num+"1";
$query="INSERT INTO News VALUES('$num','$topic','$username','$date','$message','$ip') AFTER ID='$num-1'";
}
mysql_query($query) or die ("Unable to Write to the Database Using:<br>ID:$num<br>". mysql_error() );
// Show completion screen
include ("inc_header.inc.php");
print "<table class='sub'><tr><td class='header'> Success </td></tr><tr><td class='content'>Your News Item has been posted successfully, click on Home to view your message.</td></tr></table>";
include ("inc_footer.inc.php"); mysql_close(); exit();
} else {
// warn that authorisation does not permit this action
include ("inc_header.inc.php");
print "<table class='sub'><tr><td class='header'> Encountered Problem </td></tr><tr><td class='content'>This area Exceeds your Authorisation, you do not have access to this feature, please contact your administrator if you need to use this feature.</td></tr></table>";
include ("inc_footer.inc.php"); mysql_close(); exit();
}
} else {
// warn user that he/she is not logged in
include ("inc_header.inc.php");
print "<table class='sub'><tr><td class='header'> Encountered Problem </td></tr><tr><td class='content'>You are not logged in, You must be logged in to use this feature.</td></tr></table>";
include ("inc_footer.inc.php"); mysql_close(); exit();
}
}
}
if ($nav == "home" ¦¦ $nav == "") {
include ("inc_header.inc.php");
print "<table class='sub'><tr><td class='header'> News </td></tr><tr><td class='content'>";
// Get the News items from the database
$query="SELECT * FROM News";
$result=mysql_query($query);
$num=mysql_numrows($result);
// start repeat loop to print the news articles
$i=0;
while ($i < $num) {
// get values from array into variables
$id=mysql_result($result,$i,"ID");
$topic=mysql_result($result,$i,"Topic");
$author=mysql_result($result,$i,"Author");
$date=mysql_result($result,$i,"Date");
$message=mysql_result($result,$i,"Message");
$ip=mysql_result($result,$i,"IP");
// Get avatar from User table
$Aquery="SELECT Avatar FROM Users WHERE Username='$author'";
$Aresult=mysql_query($Aquery);
$Anum=mysql_numrows($Aresult);
$avatar=mysql_result($Aresult,"Avatar");
// Get Signature from User table
$Bquery="SELECT Signature FROM Users WHERE Username='$author'";
$Bresult=mysql_query($Bquery);
$Bnum=mysql_numrows($Bresult);
$signature=mysql_result($Bresult,"Signature");
// Get Authors User type
$Cquery="SELECT Auth FROM Users WHERE Username='$author'";
$Cresult=mysql_query($Cquery);
$Cnum=mysql_numrows($Cresult);
$user_auth=mysql_result($Cresult,"Auth");
print "<table class='news'><tr><td class='info' valign='top' colspan='2'>Message Posted on $date by <a href='index.php?nav=profile&user=$author'>$author</a> ";
if ($user_auth == "u" ¦¦ $user_auth == "U") {
print "<font color='$User'>[USER]</font>";
}
if ($user_auth == "m" ¦¦ $user_auth == "M") {
print "<font color='$Moderator'>[MODERATOR]</font>";
}
if ($user_auth == "a" ¦¦ $user_auth == "A") {
print "<font color='$Administrator'>[ADMINISTRATOR]</font>";
}
print "<a href='index.php?nav=profile&user=$author'><img class='icon' src='chimage.php?image=Profile.gif' alt='View This Persons Profile'></a>";
// If logged and are an admin or moderator add delete option
if ($_SESSION['Logged_in'] == "1") {
if ($_SESSION['Auth'] == "Administrator" ¦¦ $_SESSION['Username'] == $author) {
print "<a href='index.php?nav=delete&area=home&item=$id'><img class='icon' src='chimage.php?image=Delete.gif' alt='Delete This News Item...?'></a>";
}
if ($_SESSION['Auth'] == "Administrator" ¦¦ $_SESSION['Username'] == $author) {
print "<a href='index.php?nav=edit&area=home&mode=1&item=$id'><img class='icon' src='chimage.php?image=Edit.gif' alt='Edit This News Item...?'></a>";
}
}
// Now close table
print "</td></tr><tr><td class='avatar' valign='top' rowspan='2'><a href='#top'>Top</a></td><td class='message' valign='top'><table class='topic'><td>$topic</td></table>$message</tr><tr><td class='message' valign='top'>$signature</td></tr></table>";
$i++;
}
print "</td></tr></table>";
// check if the user is logged in and if so then if user is Admin or Moderator then add message submit box
if ($_SESSION['Logged_in'] == "1") {
if ($_SESSION['Auth'] == "Moderator" ¦¦ $_SESSION['Auth'] == "Administrator") {
print "<form action='index.php?nav=post&area=home' method='POST'><table class='sub'><tr><td class='header' colspan='2'> Post a News item. </td></tr><tr><td class='content'>&nbsp;Topic:&nbsp;</td><td class='content'>&nbsp;<input type='text' name='topic' size='25' maxlength='40' class='text'>&nbsp;</td></tr><tr>
<td class='content'>&nbsp;Message:&nbsp;</td><td class='content'>&nbsp;<textarea name='message' cols='75' rows='8' wrap='virtual' class='text'></textarea>&nbsp;</td></tr><tr><td class='content' colspan='2'><center><input type='submit' name='Submit' value='Submit' class='submit'>&nbsp;&nbsp;&nbsp;<input type='reset' name='reset' value='Clear Form' class='submit'></center></td></tr></table></form>";
}
}
include ("inc_footer.inc.php"); mysql_close(); exit();
}

and the "News" table looks like this:
ID int(100) No 0
Topic varchar(50) No
Author varchar(50) No
Date date No 0000-00-00
Message blob No
IP varchar(18) No

thnx in advance Cyberjunky

[dkin]

I dont know exactly what your problem is but it may be your sql. try something like order by $var. A timestamp for instance, order by $date Asc.

Hope it helps.

Cheers.

[cyberjunky]

not really, but if you look where the posting piece is youl notice i have added a Date collection point but everytime i post a message using this the date is blank it just uses the default value 0000:00:00

BTW:removed the AFTER ID='$id-1'"; piece, which fixes one problem but leaves another million, lol.

the sites not complete so giving you them details wont comprimise anything, go ahead and test it and youl see what i mean. As i say though the problems arent really specific so if you log on and try it out for yourself you will be able to see the problems for yourself

i have had to remove the URL because of mod, but if you want to test it out and log in PM or Email me and ill give you the URL and user and pass.

[dkin]

and just so you know, posting small pieces of code is favored much more than pages. Many people will not even look at this much code.

For the future.

Cheers.

[pete_m]

There are a couple of things I'd do here. Rather than create the date in PHP when inserting a record, you can do it directly in SQL.
e.g.

 $query="INSERT INTO News VALUES('1','$topic','$username',NOW(),'$message','$ip')";

Then, when you are getting the news items, make sure you order them by date:

$query="SELECT * FROM News order by Date DESC";

[cyberjunky]

thnx the date now works properly, but what about the delete function, the piece i added to shift all the ID numbers in order to maintain a constant flow of number so that when the script reads from the database it doesnt look like there is a missing record.

like if i deleted a record like say record 6 from a table of eight records then it would read 1,2,3,4,5,7,8 because record 6 doesnt exist aynmore so i want all the records after the one deleted to have their IDs updated..can you help me out plz...?

[dkin]

I do not believe updating all the ids is the answer here, that just doesn't seem logical to me. If you are talking about say a numbered list from the database, would counting rows and displaying the row number work, this way when something is deleted all numbers would be updated.

[cyberjunky]

thnx guys, i appreciate your help the date system works fine no, thnx for that, but against your advice ive added a renumbering script despite you saying not to, i know a lot of forums say this but i dont see any other way, im a newb and just started making my site the second i finished reading a PHP turial for 1 day, so i pretty mch jumped into it because i didnt want to wait any longer, btw i used this to update my ID field:

$query="SELECT * FROM News";
$result=mysql_query($query);
$num=mysql_numrows($result);
$a='1';
while ($a <= $num) {
$id=mysql_result($result,$a,ID);
$query="UPDATE News SET ID='$a' WHERE ID='$id'";
mysql_query($query);
$a++;
}

i wanted to take your advice and leave it as it is but it is causing numerous problems so for now ill just leave it like that. thnx guys!

[dkin]

either way glad you got it working, good job. :o)