Jump to content

yeago

Members
  • Posts

    20
  • Joined

  • Last visited

    Never

Contact Methods

  • Website URL
    http://www.yeago.net

Profile Information

  • Gender
    Not Telling

yeago's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. I am attempting to get the microsoft SQL equivalent of "show columns from table" from MySQL. HOW?
  2. By that do you mean go back through the records? Also, what's this mean: <% if tempCounty <> 0 Then %> <OPTION VALUE = "<%=tempCounty%>"><%=tempCountyValue%></OPTION> <% end if %> Or more importantly. Why would it be there (perhaps its just machine generated junk?)
  3. This is the simplest way to accomplish this task? Never bothered with mysql_field_seek() =)
  4. [!--quoteo(post=355624:date=Mar 16 2006, 06:56 PM:name=ober)--][div class=\'quotetop\']QUOTE(ober @ Mar 16 2006, 06:56 PM) [snapback]355624[/snapback][/div][div class=\'quotemain\'][!--quotec--] The if statement basically says "if the cursor (which is nothing more than a pointer in the recordset array) can use the MoveFirst call, do it.... otherwise run the query again to reset it at record 0". [/quote] Break this down once more? =) I'm very familiar with MySQL/PHP. Put in into those terms? Thanks
  5. [!--quoteo(post=355618:date=Mar 16 2006, 06:40 PM:name=ober)--][div class=\'quotetop\']QUOTE(ober @ Mar 16 2006, 06:40 PM) [snapback]355618[/snapback][/div][div class=\'quotemain\'][!--quotec--] ASP doesn't have a foreach() type function. What's wrong with the code as-is? [/quote] It does in 10 lines what PHP does in 5? Can you explain to me "Wend" and everything thereafter?
  6. Perhaps someone familiar with both ASP and PHP can help me understand some things about how ASP interacts with a SQL2000 database, as well as some syntax translations. First problem: [code] <% if tempCounty <> 0  Then %> <OPTION VALUE = "<%=tempCounty%>"><%=tempCountyValue%></OPTION> <%   end if   While (NOT rscCountyList.EOF) %> <OPTION VALUE="<%=(rscCountyList.Fields.Item("CountyID").Value)%>"><%=(rscCountyList.Fields.Item("County").Value)%></OPTION> <% rscCountyList.MoveNext() Wend If (rscCountyList.CursorType > 0) Then     rscCountyList.MoveFirst Else    rscCountyList.Requery   End If   %> [/code] Please, tell me how to make this a simple foreach() in ASP.
  7. [!--quoteo(post=334148:date=Jan 6 2006, 11:22 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 6 2006, 11:22 PM) 334148[/snapback][/div][div class=\'quotemain\'][!--quotec--] Where is the part of the query that deal with the categories table? Well, actually there are two queries, one that deals with them and one that doesn't, considering that the user may not have specified categories to add or exclude from search. Here it is anyway: I removed reference to the rating for now since I can't get it right. CREATE TEMPORARY TABLE Results SELECT s.*, count( c.category ) as relev FROM sites as s LEFT JOIN categories as c on (c.site_id = s.id) WHERE ($includes) and c.site_id = s.id GROUP BY s.id,c.site_id";
  8. [!--quoteo(post=333812:date=Jan 6 2006, 05:39 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 6 2006, 05:39 AM) 333812[/snapback][/div][div class=\'quotemain\'][!--quotec--] Now that you've changed the question, I have no idea what the problem is anymore. Post your current query -- because I don't see how your COUNT() could be wrong if you're GROUPing BY site_id. Ok here goes: Table 'sites' (id, url, description) Table 'categories' (site_id, category) <-- site_id will appear twice if site has been placed into two diff categories Table 'ratings' (site_id, rating) <-- site_id will appear twice if the site has been rated twice Main condition: I must get this done in one fell swoop using a temporary table because results must be sortable and I'm choosing MySQL rather than php Array_sort() to sort them. Comment on wisdom of this? What I need: I need a table that looks like id, url, description, count(categories) as relev, avg(rating) as rating CREATE TEMPORARY TABLE Results select s.*,avg(r.rating) as rating from sites as s LEFT JOIN ratings as r on (r.site_id = s.id) where r.site_id = s.id group by site_id Problem with this: Sites that have been rated twice are simply having their ratings added together, not averaged. Sites that have been rated once are having their ratings doubled.
  9. I have changed the way I'm going to do it. I'm just going to have users rate sites by 1-5, no different categories. I have a problem, however. I find that sites that have been rated more than once are receiving double 'relev' from count('category.categories') Also, I still need to do all this within one query (because I am using MySQL to sort them) so I need to get the average rating in one shot, and then call it 'rating' within my temp table.
  10. [!--quoteo(post=333046:date=Jan 4 2006, 06:44 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 4 2006, 06:44 AM) 333046[/snapback][/div][div class=\'quotemain\'][!--quotec--] First, I meant to write AVG( ( .... / 4 ) ), with the division _inside_ the average function. Second, if that doesn't produce the desired output, just post an example of a site_id with multiple ratings -- just those rows -- and I will show you what I mean. Didn't change anything. example: site_id,usability,features, etc 4, 5, 3, 2 4, 4, 1, 2 4, 5, 2, 2 Should be (site_id) 4, 4.75, 2, 2
  11. [!--quoteo(post=332804:date=Jan 3 2006, 06:28 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 3 2006, 06:28 PM) 332804[/snapback][/div][div class=\'quotemain\'][!--quotec--] Now that doesn't make any sense... try running the AVG() function on a few rows, and you'll see how it's supposed to work. I don't know specifically why you're getting a different output. Remember, this is an average across all of the sites' rating, so I don't understand why you're posting just a single site rating record in your example. I need this: Site name, url, description, relevancy (number of times it appears in 'categories') rating (average rating divided by the number of times it appears in 'ratings')
  12. [!--quoteo(post=332698:date=Jan 3 2006, 09:39 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 3 2006, 09:39 AM) 332698[/snapback][/div][div class=\'quotemain\'][!--quotec--] Sounds like you want the average rating; try AVG(....)/4 instead. BTW, you don't need to GROUP BY id at the end, since by definition, it's unique. I tried that.... AVG(attractiveness + watchability + originality + variety)/4 as rating Sites that have this rating: site id blah blah blah blah blah 6 3 3 3 3 3 Return rating=6!
  13. Perfect except for one thing (which I didn't account for when I posted). I can't simply take the ratings and divide them by 4 because a site may have been rated by more than one person. How can I take the ratings (attractiveness, originality, etc...) and divide them by the number of entries which a given id appears? Instead of: (a + b + c)/4 I want (a+b+c)/4/*number of times site has been reviewed*
  14. CREATE TEMPORARY TABLE Results SELECT (attractiveness + originality + variety + video_quality)/4 as rating, sites. * , categories.site_id, count( categories.category ) as relev FROM categories, sites, ratings WHERE categories.site_id = sites.id AND sites.id = ratings.site_id and ($includes) GROUP BY site_id,id"; This query takes a site, gets the number of categories it matches (to determine relevance) and then goes to the ratings table and averages the fields, giving a number (to determing ranking). Problem: Sites that haven't been ranked are not appearing. Yet I must say where sites.id = ratings.site_id or else I get very strange results, such as all ratings appearing the same for all sites.
  15. Gonna do it using temp table I guess since MySQL version is only 4.0.25 . I don't know how efficient it will be. One thing: how can I make count(categories.category) more friendly? Do I use 'AS'? Instead of $r[categories.category] I'd prefer $r[relevancy].
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.