Sorting MySQL results by enum column

offnordberg

I am having a problem of MySQL/PHP logic hope someone can help me out.

Let's say I have a web page on which I want to list links to articles and each article falls into one of N main catagories (3-5 but I want the code to be flexible if N changes). I keep all of my article names, urls, timestamps, in a single MySQL table along with an enum column that specifies one of the N possible catagory names for each article.

If I want to pull all of the articles out of MySQL (with only one query of the database) and list them on the page, but grouped by catagory with catagory headers, how should I do that?

I am sure there is a way to sort and echo the array of the MySQL result by the N catagory values in the enum column, but I can't make it work. I am having trouble getting around how to specify a catagory ends and a new one begins when looping through the result array when I don't specify catagory names in the code (since there are N of them). Is there a way to count the N possible enum values in the result array before I actually begin printing array values?

Any ideas?

Thanks in advance,
Erik

rs232

It would be better to make two tables one with the article catagory and the other one with your articles.

You can't really get the catagory names and and the articles with one query. It will take two.

$sql = "SELECT SELECT DISTINCT catname FROM articles sort by catname";

$result = mysql_query($sql);
while ($r = mysql_fetch_array($result)) {
echo "$r[catname];
$sql = "select * from articles where catname='$r[catname]'";
$articles = mysql_query($sql);
while ($article = mysql_fetch_array($articles)) {
echo "$article[title] ";
echo $article[text];
echo whatever else blah blah blah;
}
}

Of course you will need to change the column names to match your database. and modify it to make it look prettier but that's basically what you need to do.

Hope that helps. email me and let me know how it turns out.

offnordberg

Thanks Steven, but I don't think this will work. The way you wrote it, for N possible catagories, there are actually 1+N database queries! (one for the array of categories and then one for articles in each catagory inside the first loop).

Right now I am fooling with the following code:

$query = "SELECT article_id, name, link, category FROM article ORDER BY category";
$result = DB_Query("content", $query);
// done once to figure out the first category returned
$temp_category = mysql_fetch_array($result[0][3]);
while ($r = mysql_fetch_array($result)) {
echo "$r[category]\n";
echo "<ul>\n";
if ($r['category'] == $temp_category) {
// this loop accounts for internal vs external articles
if ($r['link'] != "") {
echo "<li><a href=\"$r[link]\">$r[name]</a>\n";
} else {
echo "<li><a href=\"$PHP_SELF?article_id=$r[article_id]\">$r[name]</a>\n";
}
}
echo "</ul>\n";
$temp_category = $r['category'];
}

The line "$temp_category = mysql_fetch_array($result[0][3]);" does not work (I haven't figured out how to do this sort of thing yet) but I think that it should be able to work in one query.

If it HAS to be two queries to work, I suppose it would have to be done like you specified but get the listings of the catagories in one query, the full listings of everything in a second query and then use some sort of find-the-intersection-of-two-arrays function to print out the second array sorted by the first.

Thanks again for your help and for any other ideas you might have.
- Erik

offnordberg

in the code in my previous post, the: echo " followed by a line break and a \n"; is actually a >ul< to start the unordered list of links and the bullets that show up are just %gt;li%lt; code for each link.

Anon

Erik

If you have your query do a sort before returning the values you can then do a for loop to display.

$a = 0;
sql = select data where ??? order by 'cat'

$result = mysql_db_query()
while($query = mysql_fetch_row($result))
{
$cat[$a] = $urlquery[0];
$link[$a] = $urlquery[1];
$a = $a + 1;
}
$b = 0;
echo "$cat[$b] ";

for ($c = 0; $c <= $a; $c++)
{
if ($cat[$c] != $b)
{
echo "$cat[$b] ";
$b++;
}
echo "$link[$c] ";
}

Hope this helps

John