close ad
WARNING: Do Not Install the DREAMWEAVER CC 2017 or 2018 Update »
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Need to display database field by DESC but it's not working

Thread began 12/27/2009 10:18 pm by ijk76319396 | Last modified 1/11/2010 2:55 pm by Eric Mittman | 1151 views | 8 replies |

ijk76319396

Need to display database field by DESC but it's not working

I am trying to order pages by there average rating and it is working however I am also trying to have the latest page rated be the one displayed, however the entry that is being displayed for my "pageURL" is always the first one in the database no matter what I have put in. This is the code for the recordset...

$query_rsRATINGS = "SELECT ARTISTusername, pageURL, FORMAT(AVG(rating), 2) AS averageRating FROM rating GROUP BY ARTISTusername ORDER BY averageRating DESC, pageURL DESC";
$rsRATINGS = mysql_query($query_rsRATINGS, $con2com) or die(mysql_error());
$row_rsRATINGS = mysql_fetch_assoc($rsRATINGS);
$totalRows_rsRATINGS = mysql_num_rows($rsRATINGS);



So how can I have the latest "pageURL" uploaded be the one that is displayed? I have also tried removing the pageURL DESC command but it does the same thing.

Sign in to reply to this post

Jimmy Wu

If you want the sort to first sort on the pageURL, then you should put pageURL first in the sort. The way you have the query, it looks like it should order by the rating first and then if there are two results with the same rating then it will sort by pageURL.

Sign in to reply to this post

ijk76319396

That gives me the same exact results. I think I didn't explain what I am trying to do well enough, if you look at the example at indexalt.php you can see at the top of the page the ratings and the pages I am trying to display underneath the ratings. However the image displayed is the first one that was entered into the database for the comic being rated. I would like to have it so every time someone rates a page of a comic it changes the image on the indexalt page to the latest page rated. It works in the database as far as being uploaded with the proper URL but I can only get the first page URL entered into the database IE the first page rated to be the one that is getting displayed. Everything else is working fine the way I want it to.

Sign in to reply to this post

Jimmy Wu

I'm not sure if this is what you're asking for, but if you want the last rated to be displayed at the top, you can insert the time it was rated each time a comic is rated and then use that to sort the results and get the correct comic to display that way.

Not entirely sure how you set up your database. If that doesn't work for you, could you explain that more clearly?

Sign in to reply to this post

ijk76319396

I will let you know exactly how the database is and give you a quick example and see if that helps. A user is looking at a comic by "MEMBER-1" they are looking specifically at "PAGE#1". They click on a 5 to rate the comic.The fields that go into the database are ARTIST="MEMBER-1" PAGE="PAGE#1" and rating="5" as well as a hidden field capturing the users IP and a auto-increment field that gives the message a unique numerical ID. So the fields that matter are the first three as I use the rating with the average rating function which is working fine. The ARTIST field is used to sort by group by specific artist/comic also works fine. Now the problem is that the page being displayed is always going to be PAGE#1... when the user goes to page two and rates a 3 for example the following is correctly being entered into the database ARTIST="MEMBER-1" PAGE="PAGE#2" and rating="3"now on the indexalt.php page I posted above I would like instead of "PAGE#1" being displayed for it to display "PAGE#2" of the same comic, with the rating and group still working the way it does. Hope this isn't too confusing but that's the best I can do to describe what I am attempting.

Sign in to reply to this post

Eric Mittman

The results you are getting seem correct based on the query that you are using. You would like the last rated page to be displayed but how do you determine what the last rated page was? Without some further filtering of your recordset to indicate what record should be displayed you will always see the first record from this recordset which will not be the last rated page.

In order to filter the query to get the last rated page you must have some sort of criteria that you use to determine what the last rated page was. As I understand it now you are not recording anything in your database to indicate the order that the pages were ranked.

If the id column for the table is auto incrementing then the largest value for this column should be the last rated page. If this is the case then you should be selecting the id column and ordering the query by this id column in DESC order.

Sign in to reply to this post

ijk76319396

I am auto incrementing with the ID, however if I order by ID then that defeats the purpose of the average rating as I am trying to display the top five rated comics in order from highest to lowest. The URL for the image is the last entry in the databse for that column. Do I need to add an additional database and somehow link the two together to get the results I need?

Sign in to reply to this post

gruant2000381341

I use this to get an echo of most recent log in my database.
it echos the timedate stamp but you could alter that to what you want to see

<?php

require_once "connect_to_mysql.php";

//execute the SQL query and return records
$res = mysql_query("SELECT * FROM lastsent ORDER BY id DESC LIMIT 1");

//fetch tha data from the database
while ( $row = mysql_fetch_array($res) ) {
echo date("g:i a F j, Y ", strtotime($row["dateTime"])) . "<br />";
}
//close the connection
mysql_close();

?>

Sign in to reply to this post

Eric Mittman

Since you have one criteria already "I am trying to display the top five rated comics in order from highest to lowest", you just need to add to this to order them from the most recent out of these top rated.

This is something you can do but you will need to determine what order these top rated ones were rated in. To do this you must have some more data in the records. For example, if you recorded the time of the rating as a timestamp you could organize your result set by this timestamp field in DESC. This would give you your top rated results, but ordered by the time of the review.

This is the part that you are missing though, you need to have this extra info about when the rating occurs stored for each record, otherwise you will have a very difficult time determining when the reviews occurred and what order you should list them. I think the timestamp way is the best, you can just add a column to your table to hold the timestamp. Then when you insert or update the record you just use the current timestamp for this column value.

Sign in to reply to this post

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...