Last, quick MySQL question

I have several elements in a table with the same id number. If I try

PHP Code:

---

$result = mysql_query("SELECT * FROM comments WHERE post_id = '$post_id'");
    if(!$result) {
        die('Invalid query: ' . mysql_error());}
    $row = mysql_fetch_assoc($result); 


---

It looks like it only grabs one with that id number and no others. What do I need to do?

What output does this give you?

PHP Code:

---

echo mysql_num_rows($result); 


---

Also, is ID an integer? If so, you don't need to put quotes around it. If not, ignore me.

It outputs 2. I'm guessing there is something wrong with my loop because I need to display everything it finds and it displays my first entry twice and never the second.

PHP Code:

---

for ($i = 0; $i < mysql_num_rows($result); $i++)
    {
        if (!mysql_data_seek($result, $i)) {
            echo mysql_error();
          }
        print "<br /><p><b>Name: ".$row['name']."<br />\n</b>".$row['message']."</p><br />\n";
    } 


---

I feel stupid. I fixed it

I've never seen anyone loop records like that. I normally do something like:

Code:

---

$result = mysql_query("SELECT * FROM comments WHERE post_id = '$post_id'");

if ($res = mysql_fetch_array($result)) {
    do {
        echo $res['name'] . '<br />';
    } while ($res = mysql_fetch_array($result));
} else {
    echo 'No records found!';
}

---

Or, if you don't need to display a 'No records found' error, then:

Code:

---

$result = mysql_query("SELECT * FROM comments WHERE post_id = '$post_id'");

while ($res = mysql_fetch_array($result)) {
    echo $res['name'] . '<br />';
}

---

These seem much easier to me, but if you like your method better, then go for it.

Quote:

---

Originally Posted by kasracer

It outputs 2. I'm guessing there is something wrong with my loop because I need to display everything it finds and it displays my first entry twice and never the second.

PHP Code:

---

for ($i = 0; $i < mysql_num_rows($result); $i++)
    {
        if (!mysql_data_seek($result, $i)) {
            echo mysql_error();
          }
        print "<br /><p><b>Name: ".$row['name']."<br />\n</b>".$row['message']."</p><br />\n";
    } 


---

I feel stupid. I fixed it

---

If you only want two fields from the database, then only include two fields in your query.

Code:

---

SELECT name,message FROM WHERE post_id = 44

---

It is also much easier and a lot more readable to use the code posted by The Hobo as mysql_fetch_* functions return false when there are no more rows left.

Thanks for the help guys. You all have made learning PHP and MySQL easier for me. I'm getting the hang of it.

Right now my blog seems to be working decently. Now I'm just worried about security in posting comments and such. I made a script that turns '>', '<' and quotes into their HTML &lgl; things so they won't get executed but can display as text. I'm not sure whatelse to do in that area. I think that covers me and everything but the fetching of entries is done via POST. Just displaying an entry is done via GET so It seems fairly secure.

The htmlspecialchars() function handles all the conversion of angle brackets and other HTML meta characters. Also, if magic quotes is turned off, which you can test with the get_magic_quotes_gpc() function you must use the mysql_escape_string() function on your data before entering it into the database.

For integer, boolean and number types make sure you cast them appropriatly with (int), (bool), (float) etc. before putting them inside a query.