retrieving messages

[droidus]

i am trying to retrieve messages with some code.  It seems to be a bit more complicated that i thought it would be.  so here it goes:

 

$query = "SELECT * FROM memberMail WHERE userIDFrom='92' AND unread='1'";  
$result = mysql_query($query) or die(mysql_error());
if (mysql_num_rows($result) > 0) {
$row = mysql_fetch_array($result) or die(mysql_error());

$num_rows = mysql_num_rows($result);

echo "You have (" . $num_rows . ") unread message(s).";

$i = 0;
for($i; $i<$num_rows; ++$i) {
	if($row[$i[unread]] == "1") {
		echo $row[$i[message]] . "<p>";
	}
}

}

 

what i am trying to do, is print out the messages from the records that are unread, and are from a certain user.  i don't think this for statement will pick up the record numbers that got matches though.  and i am not entirely sure about the $row statements in the for statement either.

[Psycho]

$query = "SELECT message FROM memberMail WHERE userIDFrom='92' AND unread='1'";  
$result = mysql_query($query) or die(mysql_error());

//Display number of unread messages
$num_rows = mysql_num_rows($result);
echo "You have ($num_rows) unread message(s).";

//If there are unread messages, display them
if (mysql_num_rows($result) > 0)
{
while($row = mysql_fetch_assoc($result))
{
        echo "<p>{$row['message']}<p><br>\n";
    }
}

[droidus]

ok; thanks!

[droidus]

if i wanted to add some more details to the message, would the code look like:

 

if (mysql_num_rows($result) > 0)
{

echo "<table width='50%' border='1'>";

while($row = mysql_fetch_assoc($result))
{
	echo "
	<tr>
		<td>{$row['sent']}</td>
		<td>{$row['message']}</td>
	</tr>";
}

echo "</table>";

}

[skwap]

if i wanted to add some more details to the message, would the code look like:

 

if (mysql_num_rows($result) > 0)
{

echo "<table width='50%' border='1'>";

while($row = mysql_fetch_assoc($result))
{
	echo "
	<tr>
		<td>{$row['sent']}</td>
		<td>{$row['message']}</td>
	</tr>";
}

echo "</table>";

}

 

then use this

 

$query = "SELECT * FROM memberMail WHERE userIDFrom='92' AND unread='1'";  
$result = mysql_query($query) or die(mysql_error());
//Display number of unread messages
$num_rows = mysql_num_rows($result);
echo "You have ($num_rows) unread message(s).";

//If there are unread messages, display them
if (mysql_num_rows($result) > 0)
{
while($row = mysql_fetch_array($result))
{
        echo "<p>{$row['sent']}<p><br>\n";
     echo "<p>{$row['message']}<p><br>\n";

    }
}

[Psycho]

Two things:

 

1) You first have to add the fields to the SELECT part of the query that you want to use. I would advise NOT using '*'. There are good reasons not to use it unless you really do need it.

 

2. Don't create the table IN the loop. Create the table outside the loop and create the rows inside it.

 

$query = "SELECT sent, message
          FROM memberMail
          WHERE userIDFrom='92' AND unread='1'";  
$result = mysql_query($query) or die(mysql_error());

//Display number of unread messages
$num_rows = mysql_num_rows($result);
echo "You have ($num_rows) unread message(s).";

//If there are unread messages, display them
if (mysql_num_rows($result) > 0)
{
    //Open table and create headers
    echo "<table border=\"1\">\n";
    echo "  <tr>\n";
    echo "    <th>Sent</th>\n";
    echo "    <th>Message</th>\n";
    echo "  </tr>\n";
    //Show messages
    while($row = mysql_fetch_assoc($result))
    {
        echo "  <tr>\n":
        echo "    <td>{$row['sent']}</td>\n";
        echo "    <td>{$row['message']}</td>\n";
        echo "  <tr>\n":
    }
    //Close table
    echo "</table>\n";
}

[droidus]

ok, thanks again!

 

so why would you not want to select all data?  can a hacker retrieve all of that information, so you want to keep as little information being transmitted?

 

lastly, what is wrong with this?:

 

$query = "SELECT userIDTo, received, read, unread, sent, message

          FROM memberMail

          WHERE userIDFrom='92' AND unread='1'"; 

$result = mysql_query($query) or die(mysql_error());

[skwap]

Why moderator sir ?

what is the problem with * in sql statement. Its better to select all fields in a table ??

[Psycho]

lastly, what is wrong with this?:

 

$query = "SELECT userIDTo, received, read, unread, sent, message

          FROM memberMail

          WHERE userIDFrom='92' AND unread='1'"; 

$result = mysql_query($query) or die(mysql_error());

 

I don't see anything wrong with that.

 

Why moderator sir ?

what is the problem with * in sql statement. Its better to select all fields in a table ??

 

I am not a moderator. I am merely someone who has participated on this board for many years and showed the ability to provide "good" solutions.

 

There are many reason not to use '*' and I really didn't want to go into them in a forum post. But, since you both ask:

 

Using the asterisk in your select query will return ALL the rows from the selected tables. If you aren't using all the rows there is no need to hav them returned in the query. It just eats up resources. Especially if you are JOINing tables, you could be pulling much, much more information than you really need. And, when you do JOIN tables you can run into errors if you have fields with the same name in different tables. For example if you need to JOIN products with categories and both tables have a field called 'name'. If you tried to do an ORDER BY on "name" you would get an error because it doesn't know which name field to order on.

 

can a hacker retrieve all of that information

As long as you have protected your database transactions from infiltration, no. But, lets say you DID have a gap in your code. Then you now have data that may be sensitive that could be exposed.

 

Here is another reason. I never use the numeric index to reference fields from a database query, In fact, you may notice I changed your use of mysql_fetch_array() (which returns results indexed by their field names as well as numerically- i.e. everything is duplicated) to mysql_fetch_assoc() (which only returns field indexed by field name). The reason I do that is I would never make a mistake of referencing the wrong field. But, let's say you, or someone else, has some code that references fields by their numeric index. If the query is only pulling five specific fields then there will always be five fields in the results and they can always be referenced using 0-4. But, if the query uses *, then the developer needs to specify the correct fields by their position in the results (e.g. 2, 3, 6, 7, 9).

 

Ok, so once they have that working and are referencing the right fields everything is good, right? Wrong! A couple months down the road you realize you need a new column in that table to support some other functionality. So, you add that field and when doing so decide to insert it between some other fields because its value is tied to another field. Now, that code above that was returning all fields and referencing them by numeric index will be displaying the wrong data. If you were not changing that page as part of your modification you might not even notice that change before putting the changes into production. And, you could be exposing sensitive data such as SSN, birth-date, etc.

[droidus]

i get the error, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read, unread, sent, message FROM memberMail WHERE userIDFrom' at line 1".  then when i insert "SELECT sent, message" instead, it works.

 

here is my full code for the query:

require_once('../Connections/uploader.php'); 
mysql_select_db($database_uploader, $uploader);
$query = "SELECT userIDTo, received, read, unread, sent, message
          FROM memberMail
          WHERE userIDFrom='92' AND unread='1'";  
$result = mysql_query($query) or die(mysql_error());

[skwap]

Try this.

 

require_once('../Connections/uploader.php'); 
mysql_select_db($database_uploader,$uploader);
$query = "SELECT userIDTo,received,read,unread,sent,message FROM `memberMail` WHERE `userIDFrom` = '92' AND `unread` = '1'";  
$result = mysql_query($query) or die(mysql_error());

[Psycho]

That won't work either as the ones you backquoted are not the problem. I checked the list of MySQL reserved words: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

 

It seems "read" is a MySQL reserved word. So, you will need to enclose that field name in backquotes But, let's just be really safe and do that to all of the field names

$query = "SELECT `userIDTo`, `received`, `read`, `unread`, `sent`, `message`
          FROM `memberMail`
          WHERE `userIDFrom` = '92' AND `unread` = '1'";

[xyph]

You also don't want to use SELECT * because first, the MySQL engine must poll the table to get a list of columns, and then it can select that data.

 

It's potentially doubling the execution time of a simple query.

[droidus]

hm, didn't know that was a reserved word... thanks!!  i wonder what read is for?

 

and thanks, xyph! that is good to know... will have to change that in my other forms!