Description:
The problem is with the SQL. In tbl_comment.comment_mreference, I have made this column varchar(60). It can either contain an integer value, or an alphanumeric value. If it's an integer value, an admin would be logged in and it would have inserted there auto_increment member number into tbl_comment.comment_mreference. If it's a alphanumeric value, it will mean that a guest is posting a comment.
Purpose:
I'm using this for my blog. For the commenting system.
At the moment:
I'm getting the following output:
---------------
one by test
Posted by,
two by rezo
Posted by, reZo
three by rezo
Posted by, reZo
four by kube
Posted by, kube
---------------
Now, this is all correct apart from the top entry. This entry has "test" as the value of it's tbl_comment.comment_mreference row. Therefore, it does not need to match any member ID into the tbl_register.register_number row for the corresponding member ID.
I can't seem to figure out a way to check if the value in tbl_comment.comment_mreference is alphnumeric or has an integer value.
Some of tbl_comment:
mysql> SELECT comment_mreference, comment_heading FROM tbl_comment;
+--------------------+-----------------+
| comment_mreference | comment_heading |
+--------------------+-----------------+
| test | one by test |
| 1 | two by rezo |
| 1 | three by rezo |
| 2 | four by kube |
+--------------------+-----------------+
4 rows in set (0.39 sec)
Some of tbl_register:
mysql> SELECT register_number, register_username FROM tbl_register;
+-----------------+-------------------+
| register_number | register_username |
+-----------------+-------------------+
| 1 | reZo |
| 2 | kube |
+-----------------+-------------------+
2 rows in set (0.23 sec)
I hope you understood what I was trying to explain. If you could help me solve this problem, or direct me to the functions which I could use. Or even tell me, and explain the reason why my version wouldn't work, and yours would work. I would be great full.
Thanks, reZo.
The Code:
<?php
require_once '../important/configuration.php';
$query = "SELECT tbl_comment.comment_heading AS comment_heading, tbl_comment.comment_content AS comment_content, tbl_comment.comment_mreference AS register_username, tbl_register.register_username AS register_username
FROM tbl_comment
LEFT JOIN
tbl_register
ON tbl_register.register_number = tbl_comment.comment_mreference";
/*ON tbl_comment.comment_mreference = IF (tbl_comment.comment_mreference REGEXP '^[0-9]{1,5}$',
tbl_register.register_number, tbl_comment.comment_mreference)";*/
$result = $mysqli->query($query) or die ($mysqli->error);
if( is_object($result) )
{
while( $row = $result->fetch_assoc() )
{
echo '<h1 title="' . $row['comment_heading'] . '">' . $row['comment_heading'] . '</h1>';
echo '<p>' . $row['comment_content'] . '</p>';
echo '<p>Posted by, ' . $row['register_username'] . '</p>';
}
}
?>