adamjones Posted April 15, 2009 Share Posted April 15, 2009 Hi. I'm trying to just do something really basic, and echo a message, depending on weather a user has an unread message in the database. I have this table layout; CREATE TABLE IF NOT EXISTS `useralerts` ( `id` int(11) NOT NULL auto_increment, `user` varchar(100) NOT NULL default '', `read` varchar(100) NOT NULL default 'no', `message` longtext NOT NULL, `datetime` varchar(100) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; When a user posts a comment in another users guestbook, it is added to the database, and then added to 'useralerts' too; <?php session_start(); ....DATABASE INFO.... $user=$_GET['user']; $from=$_GET['from']; $message=$_POST['message']; $datetime=date("d/m/y H:i:s"); $sql="INSERT INTO $tbl_name(`from`, `too`, `message`, `datetime`)VALUES('$from', '$user', '$message', '$datetime')"; $result=mysql_query($sql) or die(mysql_error()); if($result){ $sql2="INSERT INTO useralerts(`user`, `message`, `datetime`)VALUES('$user, 'You have a new guestbook entry from $from', '$datetime')"; $result2=mysql_query($sql2) or die(mysql_error()); if($result2){ header("location: http://www.mysite.co.uk/home/".$user.""); } } mysql_close(); ?> I'm just confused as to how I would write a code, that checks the table 'useralerts' for a message, and echo something like "you have a guestbook entry", when 'read' is set to 'unread' for their username. I tried to make something that would do that; <?php if (isset($_SESSION['name'])) { echo "<ul class='login'> <li class='left'> </li> <li>Welcome back ".$_SESSION['user']."!</li>"; mysql_connect("localhost", "domainey_habhub", "pass") or die(mysql_error()); mysql_select_db("domainey_habhub") or die(mysql_error()); $result = mysql_query("SELECT * FROM useralerts WHERE user='".$_SESSION[user]."' AND read='no'") or die(mysql_error()); while($row = mysql_fetch_assoc($result)) { if($row['read'] == no) { echo "You have a questbook entry"; } else { echo ""; } ?> But that clearly wouldn't work. I just don't know how to do it. Please help! Quote Link to comment Share on other sites More sharing options...
adamjones Posted April 15, 2009 Author Share Posted April 15, 2009 -Bump. Anyone? Quote Link to comment Share on other sites More sharing options...
okamosy Posted April 15, 2009 Share Posted April 15, 2009 Unless you plan on adding more types of messages to the useralerts table, it seems a bit over the top. You might instead just add a flag to the messages table to indicate whether a message is read or not. That way, when a user signs in, the query would be this: $unread = mysql_num_rows(mysql_query("SELECT count(*) FROM $tbl_name WHERE user = $_SESSION['user'] AND read = 0")); if($unread) { echo "You have $unread new message(s)"; } Then when the user reads the message, you can just set the read flag to 1 Quote Link to comment Share on other sites More sharing options...
keeB Posted April 16, 2009 Share Posted April 16, 2009 I'll teach you a secret that will soon make you a happy developer. Use print! And print_r! They're your friends. I have no flippin idea what's wrong with your code and you can be sure I'm not going to spend the time creating a temp db, and try to debug your script. Let us know the result of this page: <?php if (isset($_SESSION['name'])) { echo "<ul class='login'> <li class='left'> </li> <li>Welcome back ".$_SESSION['user']."!</li>"; mysql_connect("localhost", "domainey_habhub", "pass") or die(mysql_error()); mysql_select_db("domainey_habhub") or die(mysql_error()); $q = "SELECT * FROM useralerts WHERE user='" . $_SESSION[user] . "' AND read='no' "; print $q; // added $result = mysql_query() or die(mysql_error()); print_r($_SESSION); //added while($row = mysql_fetch_assoc($result)) { print_r($row); //added if($row['read'] == no) { echo "You have a questbook entry"; } else { echo ""; } ?> 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.