can't display data from mysql

i am having trouble with my php code where i want to display all the topics from mysql database but it don't display anything and just echo's he posts could not be displayed, please try again later. could someone advise me about what i am doing wrong here thanks.

<?php
include 'mysql.php';
include 'header.php';
//retrieving basic data from topic
$sql = "SELECT
            id,
            subject
        FROM
            topics
        WHERE
            id = ". mysql_real_escape_string($_GET['id']);
           
$result = mysql_query($sql);
if(!$result)
{
    echo 'The topic could not be displayed, please try again later.' . mysql_error(); 
}
else
{
    if(mysql_num_rows($result) == 0)
    {
        echo 'This topic dose not exist.';
    }
    else
    {
        while($row = mysql_fetch_assoc($result))
        {
            //display post data
            echo '<table class="topic" border="1">
                    <tr>
                        <th colspan="2">' . $row['subject'] . '</th>
                    </tr>';
        }
            //fetch the posts from the database on post.postby = users.id tells us that these are both the same through left join.
            $sql = "SELECT
                        users.id,
                        post.content,
                        post.date,                       
                        post.topic,
                        post.postby,
                        users.username
                    FROM
                        post
                    LEFT JOIN
                        users
                    ON
                        post.postby = users.id
                    WHERE
                        post.topic = " . mysql_real_escape_string($_GET['id']);           
            $result = mysql_query($sql);       
            if(!$result)
            {
                echo '<tr><td>The posts could not be displayed, please try again later.</tr></td></table>';
            }
            else
            {
                while($row = mysql_fetch_assoc($result))
                {
                    echo '<tr class="topic-post">
                            <td class="user-post">' . $row['username'] . '<br/>' . date('d-m-Y H:i', strtotime($row['date'])) . '</td>
                            <td class="post-content">' . htmlentities(stripslashes($row['content'])) . '</td>
                          </tr>';
                }
            }
           
            if(!$_SESSION['loggedIn'])
            {
                echo '<tr><td colspan=2>You must be <a href="signin.php">signed in</a> to reply. You can also <a href="signup.php">sign up</a> for an account.';
            }
            else
            {
                //show reply box
                echo '<tr><td colspan="2"><h2>Reply:</h2><br />
                    <form method="post" action="reply.php?id=' . $row['id'] . '">
                        <textarea name="reply-content"></textarea><br /><br />
                        <input type="submit" value="Submit reply" />
                    </form></td></tr>';
            }
           
            //finish the table
            echo '</table>';
        }
    }
include 'footer.php';
?>

PHP:

 

the tables are the following with the following fields

users
user_id username password email university user_date user_level

post

id content date topic
postby


topics

id subject date cat topby

 

Did you try to run mysql query directly in phpmyadmin or mysql console?

 

$id = mysql_real_escape_string($_GET['id'];

$query = "SELECT id,subject FROM topics WHERE id='$id'";

 

Your query is invalid. Put the value of the id in quotes like this:
$sql="SELECT id, subject FROM topics WHERE id = '".mysql_real_escape_string($_GET['id'])."';";

 

I'm assuming the ID is a number, so you don't need quotes. You don't even need mysql_real_escape_string(),... I would just do (int) $_GET['id'].

Just echo mysql_error() and it'll tell you what's wrong.

And use PDO instead of the mysql_* functions. They're old, deprecated, and very insecure.

www.php.net/pdo

 

Actually he needs the quotes, because if it is $_GET['id'] then that could be anything not just a number. Moreover even if he verifies that $_GET['id] is a number the quotes are still a good practice, because the value might be null.

And it is definitely a good idea to include mysql_real_escape_string() when handling $_GET variables, because anyone can manually change the value of those.

 

Not if you cast the GET variables to an integer like I did above: (int) $_GET['id'] will always be an integer, so it's safe to put it in your query, even without quotes. For anything that's not supposed to be an integer, I agree... every user defined variable should be escaped properly.

 

Very first thing to check (if you're not using a debugger, so you can just look at the value):

<?php
echo '"'.$_GET['id'].'"';
?>

PHP:

See if there's anything between the quotes. (I suspect that you're not sending the code any ID value, so the SQL query is returning no rows. It's looking for an ID of a blank numeric value.)

(BTW, quoting numeric values is a bad idea. WHERE id = '5' won't match if WHERE is an integer field, because you can't have '5' in a numeric field. ['5' isn't 5, it's a string with a single character whose ASCII value is 53].)

 

If I have understood you correctly what you are saying is simply not true. From what I have experienced it doesn't really matter whether the numeric value is in quotes or not. And of course you should have a numeric field when it comes to ids.

It's true that you can omit the quotes for numeric values in queries but if your value is null your query will fail. So unless you type cast it like nico_swd above did, your other choices are to check for null values or to just include those little quotes. I usually include the quotes because it is the shortest way of the three. I feel a little cautious when it comes to type casting. I've heard bad stories about this when it comes to other languages like Java and C++ but after all this is PHP.

 

Change this line in your code.
$result = mysql_query($sql);
to
$result = mysql_query($sql) or die(mysql_error());
And display error whatever you got. Additionally, you didn't put a semicolon in your sql statement. Some versions may cause an error for it.

 

Using (int) $_GET['id'] is without quotes like nico said is what I would do if the id is always numeric.

Additionally MySQL treats numbers/floats like strings:

mysql> SELECT 1 = 0;
        -> 0
mysql> SELECT '0' = 0;
        -> 1
mysql> SELECT '0.0' = 0;
        -> 1
mysql> SELECT '0.01' = 0;
        -> 0
mysql> SELECT '.01' = 0.01;
        -> 1

Code (markup):