Page 1 of 1

MySQL code problem

Posted: Sun Mar 22, 2009 6:48 pm
by cap2cap10
Greeting php technorati. Ok this time, I am trying to check a database for messages for a simple message board. What I need the code to do is to:
1-check if there is a message for a user (mess_id)
if there is no message, the code should return "no messages" in html table.
2-if there are messages, the code should return name, message, date and the option to delete message.

But my code does not work.

Here is the code:

Code: Select all

// Connect to server and select databse.
mysql_connect('****************', '****', '*****************') or die(mysql_error());
mysql_select_db('js_info') or die(mysql_error());
 
if(isset($_GET['mess_id']))
{
    $id = $_GET['mess_id'];
 
    $query = "DELETE * FROM js_messages WHERE mess_id = '$mess_id'";
    mysql_query($query) or die(mysql_error());
 
    $text .= 'Message successfully deleted!<br>';
    $text .= '<a href="'.$_SERVER['PHP_SELF'].'">Back to Inbox.</a>';
}
else
{
    $userID = $_SESSION['js_login']['userID'];
 
    $text .= '<table border="0" align="left" width="980">';
    $text .= '<tr><td><fieldset><legend><span class="style12"><img src="images/inbox.jpg" width="26" height="26" border="0">In-box:</span></legend>';
 
    $text .= '<P><table border="0" width="940" bgcolor="#FDE7A2" cellspacing="0" cellpadding="0">';
    $text .= '<tr><td align="center" colspan="5" bgcolor="#FFFFFF"><a href="http://www.creditcards.com"><img src="http://partners.creditcards.com/images/NGOB46860_02-07-06.gif"  alt="CreditCards.com" border="1"></td></tr>';
    $text .= '<tr><td colspan="5"><table border="0" cellspacing="0" cellpadding="0" height="25" width="940" background="images/menu_bg.gif">';
    $text .= '<tr><td class="menus" style="padding-left: 18px;" width="150"><span class="menu">FROM:</span></td>';
    $text .= '<td width="4" height="24" align="left" valign="top"><img src="images/menu_separater.gif" width="3" height="24"></td>';
    $text .= '<td width="640" height="24" align="left" valign="middle"><span class="menu">MESSAGE:</span></td>';
    $text .= '<td width="4" height="24" align="left" valign="middle"><img src="images/menu_separater.gif" width="3" height="24"></td>';
    $text .= '<td width="150" height="24" align="left" valign="middle"><span class="menu">DATE POSTED:</span></td>';
    $text .= '<td width="4" height="24" align="left" valign="middle"><img src="images/menu_separater.gif" width="3" height="24"></td>';
    $text .= '<td width="50" height="24" align="left" valign="middle"><span class="menu">DELETE:</span></td>';
    $text .= '</tr></table>';
    $text .= '</td></tr>';
 
    $count = '0';
 
    $query = "SELECT * FROM js_messages WHERE userID = '$userID'";
    $result = mysql_query($query) or die(mysql_error());
 
    while($row = mysql_fetch_assoc($result))
    {
        if ($count % 2 == '0')
        {
        $text .= '<tr><td align="center" width="100%"><span class="style1">No New Messages!</td></tr>';
        }
        elseif ($count % 2 > '0')
        {
 
        $text .= '<tr width="100%" bgcolor="#F89858">';
        $text .= '<td style="padding-left: 18px;" width="150"><span class="style1">'.$row['company'].'</span></td>';
        $text .= '<td width="640" height="24" align="left" valign="middle"><span class="style1"style="padding-left: 45px;">'.$row['messages'].'</span></td>';
        $text .= '<td width="100" height="24" align="left" valign="middle"><span class="style1"style="padding-left: 45px;">'.$row['date_posted'].'</span></td>';
        $text .= '<td width="50" height="24" align="left" valign="middle"><span class="style1"style="padding-left: 45px;"><a href="'.$_SERVER['PHP_SELF'].'?mess_id='.$row['mess_id'].'"><img src="images/delete.jpg" width="25" height="26" border="0"></a></span></td>';
        $text .= '</tr><tr width="100%" bgcolor="#F89858">';
        $text .= '<td style="padding-left: 18px;" width="150"><span class="style1">'.$row['company'].'</span></td>';
        $text .= '<td width="640" height="24" align="left" valign="middle"><span class="style1"style="padding-left: 45px;">'.$row['messages'].'</span></td>';
        $text .= '<td width="100" height="24" align="left" valign="middle"><span class="style1"style="padding-left: 45px;">'.$row['date_posted'].'</span></td>';
        $text .= '<td width="50" height="24" align="left" valign="middle"><span class="style1"style="padding-left: 45px;"><a href="'.$_SERVER['PHP_SELF'].'?mess_id='.$row['mess_id'].'"><img src="images/delete.jpg" width="25" height="26" border="0"></a></span></td></tr>';
 
        }
 
        $count++;
      }
 
    $text .= '</table></fieldset></td></tr></table><p>';
 
}
 
echo $text;

:banghead: this code only returns no messages even if there are messages in the database.
Can someone enlighten me as to where are the errors in my code?

thanks in advance,

Batoe :drunk:

Re: MySQL code problem

Posted: Sun Mar 22, 2009 6:58 pm
by jayshields
Quite a weird bit of code you've got there. Here are some debugging tips.

- Check that $userID actually contains what you think it does.
- If the userID field in your table is an interger based field, take the quotes off of $userID in the query.
- Your usage of the $count variable is confusing. It seems like you want to use it to count the amount of rows and use it as a current row pointer at the same time! Looks like you've tried to adapt the code from some code which did zebra striping on the rows. For now, get rid of $count completely and the if/elseif conditions and the increment. Put an if around the while block and check mysql_num_rows($result) is more than 0, if not, show your no messages row, if so, go into your while block.

On a side note, don't put a $_SESSION variable straight into an SQL query. Use mysql_real_escape_string() on the variable first.