Re: Selecting a special row from the database

Edward Kay a écrit :

-----Original Message----- From: Fredrik Thunberg [mailto:thunis@webguidepartner.com] Sent: 04 May 2007 15:31 To: Edward Kay Cc: php-general@lists.php.net Subject: Re: [PHP] Selecting a special row from the database Edward Kay skrev:

-----Original Message----- From: Marcelo Wolfgang [mailto:marcelo@w3ol.com.br] Sent: 04 May 2007 14:37 To: php-general@lists.php.net Subject: [PHP] Selecting a special row from the database Hi all, I'm building a news display page for a website, and since the

user has 2

ways to arrive there, I want to know if this is possible: 1) the user arrive at news.php I will run a query at the db, and get the latest news to be

the main one

(full display) and display the others news in a list 2) the user arrived from a link to a specific news to news.php?id=10 It should display the news with id = 10 as the main news and get the latest ones to display in a list of other news I've so far was able to add a dinamic WHERE to my query ( if I have or not the id GET parameter ) and if I don't have it, I'm able to display the latest result as the main news, but when I have an id as a GET parameter, I have a where clause in my query and it will

return only the

main news and not build up the news list what I want is to separate the news that the user want to see ( the id=XX one ) from the others rows, can someone advice me ? Here is the code I have so far, I hope it serve as a better explanation than mine! <? $newsId = $_GET['id']; if (isset($newsID)){ $whereClause = 'WHERE auto_id ='.$newsId; } else { $whereClause = ''; } mysql_connect("localhost",$user,$pass) or die (mysql_error()); mysql_select_db ($db_table); $SQL = "SELECT * FROM tb_noticias $whereClause ORDER BY auto_id DESC"; $news_Query = mysql_query($SQL); $recordCount = mysql_numrows($news_Query); mysql_close(); ?> TIA Marcelo Wolfgang

If id is set, query the DB for this news article and put as the first element in an array. Then perform a second query WHERE ID != id

and append

the results to the array. If id isn't set, just perform the one query without any where clause. If you want to do it all in one query, if id is provided use

SELECT... WHERE

ID = id UNION (SELECT...WHERE ID != id ORDER BY id DESC) to get

the ordering

you want. If id isn't set just use SELECT ... ORDER BY id DESC. I'm not sure if there would be much difference in performance as I still think the database would perform two queries internally. Edward

Maybe SELECT ..., if (id = my_id,1,0) as foo ... ORDER BY foo DESC;

Good idea! Just tried it out and it works really well.

Hello, If you're looking for performance, why do you select everything for articles that are not in full display ? Isn't it better if you SELECT * just for the article you want in full display and make another query for other titles ? LIMIT would also help performance. Cheers -- Emmanuel