Printing results from query

results sorted by categories, ones after anothers

[Amenhotep]

I have some problems which is not surprise as I'm a beginner in PHP.

Here is my problem.

I created database and table named base and news.
In table news I created column: id, title, text, all_text, date... and category.

I want to display latest news by id and category. I used this to print latest news:

------------------------------------
while($row = mysql_fetch_row($result)) {
$title[$i]=$row[1];
$text[$i]=$row[2];
$date[$i]=$row[4];
$category[$i]=$row[6];
...........

$i++; }
------------

After that I printed certain rows in defined places in table like:
------
<tr>
<td width="2" height="22" bgcolor="#FFFFFF"></td>
<td width="202" valign="top" bgcolor="#F3F2F1"><div class="titles"><span><? print $title[3]?></span></div></td>
<td width="219" valign="top" bgcolor="#FAF9F5"><div class="tites"><span><? print $title[4]?></span></div></td>
</tr>
<tr>

---------------------

Well, this would print news descending as they were written.

But, I want to print news from certain category, I used this:

-----------------------------------
for($i=0;$i<sizeof($category);$i++) {
if($category[$i]=="Art")

$title2[$j]=$title[$i];
$txt2[$j]=$text[$i];
$date2[$j]=$date[$i];
......

$j++; }
-----------------------------------
It worked well printing news from "ART".
But after this, I want to print news from category SPORTS.

How I'll do this? Please someone

[Amenhotep]

I'm sorry, I made a mistake.

It wouldnt print news just from certain category. I just noticed it printes ALL news.

How to print news from one category, then print news from another?

[jatar_k]

Welcome to WebmasterWorld Amenhotep,

what is the query you are using? You could do this kind of grouping using a GROUP BY clause

[Amenhotep]

$query = "SELECT * FROM news ORDER BY id DESC";

[Amenhotep]

Let me put this in few lines.

I have database and table that contains some news.
I column named category, in form that fills it I predefined 4 categories: art, sports, Lifestyle and politics.

I want, on my site, to organize showing news by those categories.

1. 5 latest news form category politics
2. then 5 latest news from category art
... and so on.

I dont know how to do this. I now how to print generaly latest news from DB but not in this manner i explained.

Hope someone will assist. I know how you pplare busy and tired of these kind of quest... but...

[jatar_k]

a wild guess

$query = "SELECT * FROM news GROUP BY category ORDER BY id DESC";

I have a tendency to get my GROUP and my ORDER messed up though so you may have to play with it a bit to get it to work, but the general idea is there

the GROUP BY clause is what you need

[smatts9]

If you are looking to print the last 5 then try:

$query = "SELECT * FROM news GROUP BY category ORDER BY id DESC LIMIT 5";

But I am not sure it will work, It may just return the 5 most recent news articles from all the categories.

You may end up having to query each category by itself?

Like:

$query1 = "SELECT * FROM news WHERE category='arts' ORDER BY id DESC LIMIT 5";
$query2 = "SELECT * FROM news WHERE category='politics' ORDER BY id DESC LIMIT 5";
$query3 = "SELECT * FROM news WHERE category='cat3' ORDER BY id DESC LIMIT 5";
$query4 = "SELECT * FROM news WHERE category='cat4' ORDER BY id DESC LIMIT 5";

If you are going to be adding more categories later on then this wont be the best, I am not the best when using all the mysql commands like LIMIT, GROUP BY, etc.

[Amenhotep]

Thanks man!

This will do the thing! I predefined categories so there will not be no new in near future ;)

Result is realy nice looking;)

I'll go now on security issues and design ;)