Don't know where to begin. Please Help!!

[adamjones]

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!

[adamjones]

-Bump.

 

Anyone?

[okamosy]

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

[keeB]

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 "";
			}
			?>