[How to return a table's column names?]

I am attempting to get the microsoft SQL equivalent of "show columns from table" from MySQL.

HOW?

[PHP user Stranded in ASP world! HELP!!!]

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?)

[PHP user Stranded in ASP world! HELP!!!]

This is the simplest way to accomplish this task?

Never bothered with mysql_field_seek() =)

[PHP user Stranded in ASP world! HELP!!!]

[!--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

[PHP user Stranded in ASP world! HELP!!!]

[!--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?

[PHP user Stranded in ASP world! HELP!!!]

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.

[Multitable query syntax]

[!--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";

[Multitable query syntax]

[!--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.

 

 

[Multitable query syntax]

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.

[Multitable query syntax]

[!--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

[Multitable query syntax]

[!--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')

[Multitable query syntax]

[!--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!

 

 

[Multitable query syntax]

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*

[Multitable query syntax]

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.

[A fun multitable problem. Can't get syntax right.]

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].

[A fun multitable problem. Can't get syntax right.]

 

You know, I had a feeling that was the case. Thank you.

 

SELECT sites. * , categories.site_id, count( categories.category ) FROM categories, sites WHERE categories.site_id = sites.id and ( categories.category = 'bad') and not site_id in( select site_id from categories where category in ('good','bad') )

 

I get a syntax error at select 'site_id...'

 

 

[INSERT In between entries of a database table?]

Can you explain why you would want to do this?

 

Typically, this defeats the purpose of id's at all.

 

I'll show you:

 

Let's say you have a table 'animals'

 

0,cat

1,dog

2,rhino

 

you have another table 'pet owners'

 

tim,0 (cat)

tim,1 (dog)

shelly,2 (rhino)

 

Then you insert an animal into database after cat, lemur!

 

0,cat

1,lemur

2,dog

3,rhino

 

Now tim owns a cat and a lemur and shelley owns a dog!

[MySQL Questions]

Sure, not to mention piss off your system administrator.

 

Don't go query crazy I guess. If you make your queries smart, you'll get everything you need and everything you don't need in one fell swoop. You ought to post some of your practices.

 

[A fun multitable problem. Can't get syntax right.]

[!--quoteo(post=330189:date=Dec 24 2005, 02:14 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Dec 24 2005, 02:14 PM) 330189[/snapback][/div][div class=\'quotemain\'][!--quotec--]

Well, I don't really understand how your last query has any effect -- if you're already only including results "WHERE category IN ('ugly','good')", then the other ones would necessarily be excluded! Maybe you can shed some light on this.

 

 

User makes a list of sites heshe wants:

 

Include('good','bad','ugly')

 

And doesn't want:

 

Exclude('wonderful','hideous')

 

Category table goes like:

 

1,good

1,bad

2,good

2,wonderful

3,ugly

4,otherworldy

 

According to the user's include/exclude list, Site 2 has something they want but is excluded because it is also 'wonderful' which was excluded. Site 1 and 3 appear. Site 4 wasn't mentioned, and so it is not included either.

 

Howto?

[A fun multitable problem. Can't get syntax right.]

I have two tables.

 

Sites

 

id, site_info, etc.

 

Categories

 

site_id, category

 

Sites can be in multiple categories, new entry for each one.

 

1,Bad

1,Ugly

1,Obnoxious

2,Good

2,Wonderful

 

Say a user comes along and asks for all sites that are 'Ugly' and 'Obnoxious' but not 'Wonderful'' or 'Bad'

 

Now, for relevancy purposes I also get the number of times site_id appears in Categories, so that sites that match the most user included categories rise to the top.

 

First I used the following multitable query to get both the number of times site appears in category, but also the site info.

 

$q = "SELECT sites. * , categories.site_id, count( categories.category )

FROM categories, sites

WHERE categories.site_id = sites.id

GROUP BY categories.site_id, sites.id";

 

Then I thought: well why not get ONLY sites that share one or more of the categories that users want with this same query? Isn't it better just to get it all done now? So I...

 

$q = "SELECT sites. * , categories.site_id, count( categories.category )

FROM categories, sites

WHERE categories.site_id = sites.id and (category = "ugly" or category = "good")

GROUP BY categories.site_id, sites.id";

 

Then I thought why not just knock off the sites the user wants to exclude now? So I....

 

$q = "SELECT sites. * , categories.site_id, count( categories.category )

FROM categories, sites

WHERE categories.site_id = sites.id and (category = "ugly" or category = "good") and (category != "wonderful" and category !="hideous")

GROUP BY categories.site_id, sites.id";

 

--

 

My current problem: Sites that are wonderful and hideous are still showing up! Ideas?