[RESOLVED] how to do this?

bull

hello,

<?
$SQL = "SELECT * from news ORDER BY id DESC"; 
$result = @mysql_query( $SQL );
while( $row = @mysql_fetch_array( $result ) ) {
	echo "<h1>$row[title]</h1><p>$row[text]</p>"; 
}
?>

how to change $SQL query, if i want also print category name for every title? i've heard about JOIN but i have no idea how to work with it.

bogu

SELECT * FROM news n
LEFT JOIN categories c ON n.category_id = c.id
ORDER BY n.id DESC

bull

thanks, i'll try this.

bull

it work okey. only i have one problem, when i need to echo id of news article script prints id of category, not news id.

how to solve this.

HeyRay2

In the code bogu wrote...

SELECT * FROM news n 
LEFT JOIN categories c ON n.category_id = c.id 
ORDER BY n.id DESC

... c.id would be the category id and n.id would be the new post id, so the code would be...

$SQL = "SELECT * FROM news n 
LEFT JOIN categories c ON n.category_id = c.id 
ORDER BY n.id DESC";
$result = @mysql_query( $SQL ); 
while( $row = @mysql_fetch_array( $result ) ) { 
    echo "<h1>".$row[title]."</h1><p>".$row[text]."</p><p>News ID: ".$row[n.id]."</p>"; 
} 
?>

bull

i dont get why, but this isnt working, it prints nothing (as id) .. any ideas?

bogu

SELECT n.id, c.id FROM news n 
LEFT JOIN categories c ON n.category_id = c.id 
ORDER BY n.id DESC

extracts the news ids and the category ids if u want to extract fileds from news (I create an alias for the news table name wich is n) add n.field_name, if u want to extract some fields from category (I create an alias for the category table name wich is c) add c.field_name (Ex. c.title for category title ...)

bull

<?
$SQL = "SELECT n.id, c.id FROM news n
LEFT JOIN categories c ON n.categories_id = c.id
ORDER BY n.id DESC"; 
$result = @mysql_query( $SQL );
while( $row = @mysql_fetch_array( $result ) ) {
	echo "<h1>".$row[title]."</h1><p>".$row[text]."</p><p>News ID: ".$row[n.id]."</p>"; 
}
?>

its how i use it. and it is not working. i dont understand why..

bogu

Create aliases for the fields name:

SELECT n.id as news_id, c.id as categ_id FROM ...

and when u echo them use the alias ...

echo $row['news_id'];
echo $row['categ_id'];

u should use a third party script like phpmyadmin to see how your sql looks like and to understand it better ...

HeyRay2

My mistake. I forgot to change the SQL statement to explicitly assign n.id and c.id to a variable. Thank you bogu for pointing that out.

This should work...

SQL = "SELECT n.id as news_id, c.id as cat_id, n.title as news_title, 
n.fulltext as news_text, c.title as cat_title FROM news n 
LEFT JOIN categories c ON n.categories_id = c.id 
ORDER BY n.id DESC"; 
$result = @mysql_query( $SQL ); 
while( $row = @mysql_fetch_array( $result ) ) { 
    echo "<h1>".$row[news_title]."</h1><p>".$row[news_text]."</p><p>News ID: ".$row[news_id]."</p>"; 
} 
?>

The above code will make the following database information available to you:

$row[news_id] // the ID number of the news entry
$row[news_title] // the title of the news entry
$row[news_text] // the full text of the news entry
$row[cat_id] // the ID number of the category
$row[cat_title] // the title of the category

After performing a JOIN, you have to explicity assign the database values you want available to your PHP script, because pulling information from multiple database tables can cause conflicts between values in the tables.

Let me know if that code worked.

bull

this work's okey. thanks for help - bogu and HeyRay2.