We help IT Professionals succeed at work.

How do I do this in php?

Loganathan Natarajan
on
253 Views
Last Modified: 2010-04-21
I have table containing data with news details, first i want to take latest 10 news with priority order basis. Here, I

have written sql statement (in postgres) something like,

SELECT news_id, news_title, news_url,  news_position, news_order, news_status  FROM "tblNews" ORDER BY news_id DESC, news_order ASC LIMIT 10 OFFSET 0

But this sql does not give the correct results as it takes only latest 10 records but it is not in the priority basis also.

How do i handle this in php atleast getting the latest 10 records and get the priority order basis

I have something in my mind to implement this, but i am looking for the best way ...
Comment
Watch Question

SELECT news_id, news_title, news_url,  news_position, news_order, news_status  FROM "tblNews" ORDER BY news_id DESC, news_order ASC LIMIT 10 OFFSET 0

Stick to doing only a single ordering (either news_id or news_order).

Example:

SELECT news_id, news_title, news_url,  news_position, news_order, news_status  FROM "tblNews" ORDER BY news_order DESC LIMIT 10 OFFSET 0

Commented:
You will have to specify how you want to handle situations where there are more that 10 entries.
ie 50 entries of each priority, all entered so that newness spans all priorities.
Do you want just all NEW, just the priority ones (even though there are newer ones).
or
Do you want 10 New ones, which are then ordered by priority?
Loganathan NatarajanLAMP Developer
CERTIFIED EXPERT

Author

Commented:
waygood:
>> Do you want 10 New ones, which are then ordered by priority?

yes, i want this way,... only 10 news (latest ... by putting news_id DESC) as welll as these 10 news should be ordered the priority also...

for example,

last 10 news

news_id       news_title                         news_priority
17                   This is latest news2             1
16                  This is latest news2           3
15                  This is latest news2           3
14                  This is latest news2           5
12                  This is latest news2          5
10                  This is latest news2          6
9                  This is latest news2          7

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
You mean allways sorting out the latest ten, but by priority as second choice?
Then ahmad2121 is spot on with his example. That gets the highest numbered news. Then sort those by priority, with those lowest first.

SELECT news_id, news_title, news_url,  news_position, news_order, news_status  FROM "tblNews" ORDER BY news_id DESC, news_order ASC LIMIT 10,0

Did you get a problem using that query?

Regards,
Andreas
Loganathan NatarajanLAMP Developer
CERTIFIED EXPERT

Author

Commented:
>> Take the result set with first query and sort it programatically.

can you give some more details,?
Loganathan NatarajanLAMP Developer
CERTIFIED EXPERT

Author

Commented:
HeroGuran:

>>
SELECT news_id, news_title, news_url,  news_position, news_order, news_status  FROM "tblNews" ORDER BY news_id DESC, news_order ASC LIMIT 10,0

I am getting exact results till news_id DESC ... i am not getting news_order in ASC order.. thatz the problem (i.e first priority comes first...  1, 2,3,4,5 etc) ...

 this applies only the latest 10 records what we select in the news_id DESC

am i making sense please?
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Loganathan NatarajanLAMP Developer
CERTIFIED EXPERT

Author

Commented:
hi HeroGuran.. thanks for the answers..
Loganathan NatarajanLAMP Developer
CERTIFIED EXPERT

Author

Commented:
can anyone help, i could not get it correctly.,
$i = 0;
while($row=mysql_fetch_array($result))
{
   $array[$i] = array($row["news_id"],$row["news_priority"]);
   $i++;
}

**************
may you tell how do i sort or get the exact result.,
Hey!
I'm sorry it didn't work out for you. Normally you should start a new question, but I guess you have reason for posting here so I'll try to help anyways. The attached code to my previous post is the easiest to work from so that's where I'll start.

//Get the 10 latest news
$sql = 'SELECT news_id, news_title, news_url,  news_position, news_order, news_status FROM "tblNews"  ORDER BY news_id ASC LIMIT 10,0';
//Iterate the latest ten news to use in secondary query
$addSQL = "";
while($row=mysql_fetch_array($result))
{
   $addSQL = "AND news_id = '".$row["news_id"]."' ";
}
$sql = 'SELECT news_id, news_title, news_url,  news_position, news_order, news_status FROM "tblNews" WHERE 1=1 '.$addSQL.' ORDER BY news_order ASC LIMIT 10,0';
//This should result in the latest ten news, ordered by "news_order", which I assume is your "priority"

Or did you try that allready?

Regards,
Andreas
Loganathan NatarajanLAMP Developer
CERTIFIED EXPERT

Author

Commented:
i did not try that..i am trying.. this.,
The last SQL should come out something like ..
Oh, hey, you must use OR.. not AND. That's a big mistake by me.
There must also be a punctuation at the addSQL variable for building on values, not replacing like now. I have corrected this below.

Regards,
Andreas
//Get the 10 latest news
$sql = 'SELECT news_id, news_title, news_url,  news_position, news_order, news_status FROM "tblNews"  ORDER BY news_id ASC LIMIT 10,0';
//Iterate the latest ten news to use in secondary query
$addSQL = "";
while($row=mysql_fetch_array($result))
{
   if($addSQL)
      $addSQL .= "news_id = '".$row["news_id"]."' ";
   else
   $addSQL .= "OR news_id = '".$row["news_id"]."' ";
}
$sql = 'SELECT news_id, news_title, news_url,  news_position, news_order, news_status FROM "tblNews" WHERE 1=1 AND ('.$addSQL.') ORDER BY news_order ASC';
//This should result in the latest ten news, ordered by "news_order", which I assume is your "priority"

Open in new window

Sheesh, monday.. I turned the if-statement around, should be:
//Get the 10 latest news
$sql = 'SELECT news_id, news_title, news_url,  news_position, news_order, news_status FROM "tblNews"  ORDER BY news_id ASC LIMIT 10,0';
//Iterate the latest ten news to use in secondary query
$addSQL = "";
while($row=mysql_fetch_array($result))
{
   if($addSQL)
      $addSQL .= "OR news_id = '".$row["news_id"]."' ";
   else
      $addSQL .= "news_id = '".$row["news_id"]."' ";
}
$sql = 'SELECT news_id, news_title, news_url,  news_position, news_order, news_status FROM "tblNews" WHERE 1=1 AND ('.$addSQL.') ORDER BY news_order ASC';
//This should result in the latest ten news, ordered by "news_order", which I assume is your "priority"

Open in new window

Loganathan NatarajanLAMP Developer
CERTIFIED EXPERT

Author

Commented:
Yes, it works now., i was trying without OR as well concatenation of $addSQL.. anyhow it works welll... thanks again.., anyhow i don't have option again to assign points.. it is something great to come and discussed this point.,

but i was trying some SORTING technique ... did not work that//

thanks HeroGuran;

Commented:
you could always use subquery

SELECT * FROM (SELECT * FROM table ORDER BY newest LIMIT 10) ORDER BY priority

I couldn't get it to work through phpMyAdmin, but this is the basic syntax
http://dev.mysql.com/doc/refman/4.1/en/unnamed-views.html

Loganathan NatarajanLAMP Developer
CERTIFIED EXPERT

Author

Commented:
:: waygood
>> excellent .... i have also got the results through like this sub query ...., anyhow thanks for posting your answer here....  so i have used like this also and got the correct result.. thanks

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.