I'm having trouble getting any information to display from this query. Anyone know where I'm going wrong?
Thank you!
$query = "SELECT * ".
"FROM comments, users ".
"WHERE comments.user_id = users.user_id ".
"ORDER BY comments.date DESC ".
"LIMIT 10";
$result = mysql_query[$query] or die[mysql_error[]];
while [$row = mysql_fetch_array[$result]] {
echo $row['users.user_id'];
echo $row['comments.comment'];
}
OMG Ponies
317k78 gold badges511 silver badges494 bronze badges
asked Oct 26, 2009 at 20:35
1
use mysql_fetch_assoc[] instead of mysql_fetch_array[]. In your loop use the column name as the array key:
while [$row = mysql_fetch_assoc[$result]] {
echo $row['column_name1'];
echo $row['column_name1'];
}
In your query try to be more specific on the select statement, try not to use *.
answered Oct 26, 2009 at 20:44
Hubert PerronHubert Perron
3,0125 gold badges33 silver badges28 bronze badges
You're probably getting the error because you are sorting [ORDER BY] on a field that does not exist in your query.
It would be best practice to not use the "SELECT *" querying. If all you need are specific values, specify them. This also helps when retrieving the data...
$query = "SELECT users.user_id, comments.comment, comments.date ".
"FROM comments, users ".
"WHERE comments.user_id = users.user_id ".
"ORDER BY comments.date DESC ".
"LIMIT 10";
$result = mysql_query[$query] or die[mysql_error[]];
while [$row = mysql_fetch_array[$result]] {
echo $row['user_id'];
echo $row['comment'];
echo $row['date'];
}
answered Oct 26, 2009 at 20:50
It is good practice to specify column names in a query rather than using * - on some DBs there is a performance impact and on all it prevents any unexpected behaviour cropping up from table changes.
In the example I think the issue is arsing from the array keys you are using - you don't need to include the table name in them, just the column name:
echo $row['user_id'];
echo $row['comment'];
answered Oct 26, 2009 at 20:51
MacrosMacros
7,0792 gold badges36 silver badges61 bronze badges
The better practice is to write only fields what you need in your sql query like this:
$query = "SELECT u.user_id uid, c.comment comment ".
"FROM comments c, users u ".
"WHERE comments.user_id = users.user_id ".
"ORDER BY comments.date DESC ".
"LIMIT 10";
Using so type of queries you reduce the time of executing your query and transmitting data from database server to your php script. After this modification your cycle transformed to:
while [$row = mysql_fetch_array[$result]] {
echo $row['uid'], $row['comment'];
}
answered Oct 26, 2009 at 20:54
I use PDO method but this can work for you too:
answered Feb 7, 2021 at 16:52