[SOLVED] PHP/MySQL Sorted Output

[Stuart]

Hello everyone,
I have a new problem that I'm sure you'll be able to help me with. On my site, there is a page on which a php script calls a mysql database and outputs a table, which is called "files". It's all working fine, but now I want to add a few more features. Above the php script, there are three links that can be used to sort the data in different ways. The links look like this:

Code: Select all

Sort by date | Sort by title of file | Sort by author's name

Now, by default, the table is sorted by date, since that's the hardest to achieve with php. I know how to sort the data:

Code: Select all

SELECT * FROM files order by title;

But now, here's the problem. This data needs to be within several different divs. Like this:

HTML: Select all

<div class="file oddsection">
   <div class="title"><a href="file.doc">Some File</a></div>
   <div class="type">Microsoft Word Document</div>
   <div class="author">Some Person</div>
   <div class="date">Some Date</div>
   <div class="download"><a href="zip/file.zip">Download</a></div>
</div>
<div class="file evensection">
   <div class="title"><a href="file2.doc">Another File</a></div>
   <div class="type">Microsoft Word Document</div>
   <div class="author">Another Person</div>
   <div class="date">Another Date</div>
   <div class="download"><a href="zip/file2.zip">Download</a></div>
</div>

Notice that the styles "oddsection" and "evensection" change above. This is because of my stylesheet and needs to stay that way! Now, the mysql data is inserted into the divs up there. This also includes the href of the links, which change from file to file.

So, the problem is, I can't seem to find a way to display all the data with one php script, in a way that "oddsection" and "evensection" alternate! I have the following php, but it's not very far:

PHP: Select all

<?php
$user = "username";
$pswd = "password";
$dbname = "database";
mysql_connect(localhost, $user, $pswd);
mysql_select_db($dbname);

$order = $_GET['order'];

if ($order == "title"){
    $sql = 'SELECT * FROM files WHERE pos = "1";';
    $query = mysql_query($sql);
    while($row = mysql_fetch_array($query)) {
    echo '<div class="file '.$row['style']."\">\n";
    echo '<div class="title"><a href="'.$row['url'].'">'.$row['title']."</a></div>\n";
    echo '<div class="type">'.$row['type']."</div>\n";
    echo '<div class="author">'.$row['author']."</div>\n";
    echo '<div class="date">'.$row['date']."</div>\n";
    echo '<div class="download"><a href="'.$row['zip']."\">Download</a></div>\n";
    echo "</div>\n";
    }

    $sql = 'SELECT * FROM files WHERE pos = "2";';
    $query = mysql_query($sql);
    while($row = mysql_fetch_array($query)) {
    echo '<div class="file '.$row['style']."\">\n";
    echo '<div class="title"><a href="'.$row['url'].'">'.$row['title']."</a></div>\n";
    echo '<div class="type">'.$row['type']."</div>\n";
    echo '<div class="author">'.$row['author']."</div>\n";
    echo '<div class="date">'.$row['date']."</div>\n";
    echo '<div class="download"><a href="'.$row['zip']."\">Download</a></div>\n";
    echo "</div>\n";
    }

    $sql = 'SELECT * FROM files WHERE pos = "3";';
    $query = mysql_query($sql);
    while($row = mysql_fetch_array($query)) {
    echo '<div class="file '.$row['style']."\">\n";
    echo '<div class="title"><a href="'.$row['url'].'">'.$row['title']."</a></div>\n";
    echo '<div class="type">'.$row['type']."</div>\n";
    echo '<div class="author">'.$row['author']."</div>\n";
    echo '<div class="date">'.$row['date']."</div>\n";
    echo '<div class="download"><a href="'.$row['zip']."\">Download</a></div>\n";
    echo "</div>\n";
    }
}
?>

Crazy, right? The 'where pos = "1"' stuff is my cheap way of selecting only one row of the table. I don't want that! I would rather have one code that displays all the data, in the correct order. The order is determined by the "if" statement at the top, which corresponds to a variable in the URL. Like this:

http://www.domain.com/index.php?order=title

And "title" would change to "author" if the user selected the 'sort by author' link above. "date" would not be used since it is the default and will be displayed in that order when this variable is not present in the url. So, like this in the php script:

PHP: Select all

else {
// code to sort by date
} 

Do you see what I mean? It's most likely really confusing... Sorry!
The main idea is, the script places the data from the database into html code, and sorts the data according to the variable in the URL.

Any help would be great!
Thanks,
Stuart

[c010depunkk]

I would let MySQL to the sorting and then just output what the query returns:

PHP: Select all

$sort_key=mysql_escape_string($_GET['order']);
$query=mysql_query("SELECT * FROM files WHERE pos = '1' ORDER BY $sort_key;");
while($row=mysql_fetch_object($query)) {
   // output
} 

See if you can do something with that...

[Stuart]

OK, all set. Now to the other problem. How to I make the php script output the data in the database in a way that the class element on the div alternates. Like this:

HTML: Select all

<div class="file oddsection"></div>
<div class="file evensection"></div>

That's the only issue right now. How do I do this? I want to tell the php script to output the data, and alternate the classes "oddsection" and "evensection" so that they look like above, no matter how many divs there are.

Thanks

[c010depunkk]

See if you can understand this. I hope my programming logic is right, I haven't tested this....:

PHP: Select all

$sort_key=mysql_escape_string($_GET['order']);
$query=mysql_query("SELECT * FROM files WHERE pos = '1' ORDER BY $sort_key;");
$i=0;
while($row=mysql_fetch_object($query)) {
   echo('<div class="file '.(($i%2)==0?'odd':'even').'section"></div>');
   $i++;
}
if(($i%2)!=0) { // make sure we come out on a even number
    // if not, output one more div
    echo('<div class="file oddsection"></div>');
} 

I might have my "odds" and "evens" mixed up.

See if you can do something with this code. Feel free to ask questions.

[maspick]

You could set a variable to zero; if the variable is zero, set the class to even, else, set the class to odd; increment it with each "file"; if the variable equals 2, set it to zero.

Code: Select all

// outside while loop
$alt = 0;
// inside while loop
echo "<div class=\"file ";
if ($alt == 0) echo "even";
else echo "odd";
echo "section\">";
$alt ++;
if ($alt == 2) $alt = 0;

That's my 2 cents :^{>

[Stuart]

Great! I adjusted it a bit and I got what I wanted...
Thanks for all your help!

Thread Solved