RE: [PHP] Selecting a special row from the database
On Fri, May 4, 2007 9:26 am, Edward Kay wrote:
>> 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";
Also, forget the where clause and change your ORDER BY to be:
ORDER BY auto_id = $newsId desc, auto_id desc
This will FIRST check the ID to see if it's the one you want from the
one story, then the rest in id descending format.
And you probably should not use "auto_id desc" but "postdate desc" or
whatever date field you are using to date the stories...
Technically speaking, you should NOT rely on the fact that mysql will
be adding 1 to each id to get the next one.
That happens to be how it works, and it's really unlikely to change,
but if you ever get to where you have a humongous site and a zillion
news stories in federated databases, you suddenly have this buglet
where your ID is *not* the thing you wanted to sort by.
And, in theory, the MySQL guys could change the implementation of
auto_increment out from under you, tho that's even less likely than
needing a federated db setup... :-)
--
Some people have a "gift" link here.
Know what I want?
I want you to buy a CD from some indie artist.
http://cdbaby.com/browse/from/lynch
Yeah, I get a buck. So?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Selecting a special row from the database
On Fri, May 4, 2007 8:36 am, Marcelo Wolfgang wrote:
> 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;
SQL injection attack alert:
You *really* need to sanitize this input.
http://phpsec.org/
> } 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";
Using select * is usually a bad idea anyway, but you can also add:
$newsId = (int) $newsId;
select *, auto_id = $newsId as requested from ...
This then gives PHP a way to tell if this is a story they ASKED FOR by
id, or just a story, as you have an "extra" filed called 'requested'
This assumes that you never ever have 0 as an ID in the database, as
the (int) typecast will force it to 0 if you don't have a $newsId, but
you almost for sure won't have a 0 for auto_id, as it's an
auto_increment field that starts at 1 and goes up to over 2 billion.
--
Some people have a "gift" link here.
Know what I want?
I want you to buy a CD from some indie artist.
http://cdbaby.com/browse/from/lynch
Yeah, I get a buck. So?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Selecting a special row from the database
Edward Kay a écrit : -Original Message- From: Fredrik Thunberg [mailto:[EMAIL PROTECTED] 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:[EMAIL PROTECTED] 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! 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Selecting a special row from the database
> -Original Message-
> From: Fredrik Thunberg [mailto:[EMAIL PROTECTED]
> 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:[EMAIL PROTECTED]
> >> 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.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Selecting a special row from the database
Edward Kay skrev: -Original Message- From: Marcelo Wolfgang [mailto:[EMAIL PROTECTED] 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! 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; -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Selecting a special row from the database
> -Original Message-
> From: Alister Bulman [mailto:[EMAIL PROTECTED]
> Sent: 04 May 2007 15:07
> To: php-general@lists.php.net
> Subject: Re: [PHP] Selecting a special row from the database
>
>
> On 04/05/07, Marcelo Wolfgang <[EMAIL PROTECTED]> wrote:
>
> > 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
>
> > 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";
>
> Yep, thats pretty classic.
> One thing I would do - assuming $newsId should always be an integer
>
> $whereClause = '';
> if (isset($_GET['id']) {
> $newsId = intval($_GET['id']);
> if ($newsId) // if not 0
> $whereClause = 'WHERE auto_id ='.$newsId;
> }
> .
>
>
> Alister
>
This doesn't solve the problem. If ID is set, you'll only get one result.
Several results are always required, just with a different ordering. See my
other reply.
Edward
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Selecting a special row from the database
> -Original Message-
> From: Marcelo Wolfgang [mailto:[EMAIL PROTECTED]
> 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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Selecting a special row from the database
On 04/05/07, Marcelo Wolfgang <[EMAIL PROTECTED]> wrote:
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
Here is the code I have so far, I hope it serve as a better explanation
than mine!
Yep, thats pretty classic.
One thing I would do - assuming $newsId should always be an integer
$whereClause = '';
if (isset($_GET['id']) {
$newsId = intval($_GET['id']);
if ($newsId) // if not 0
$whereClause = 'WHERE auto_id ='.$newsId;
}
.
Alister
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[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! TIA Marcelo Wolfgang -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
