Start Free Trial

asked on

Split function then convert varchar to integer

I have a list of numbers in a mysql database that are type varchar. I need to split the numbers and compare each to another msql record. The split is working and I get the type mismatch error. I have tried converting to an integer in the mysql statement but still get the same error. Below is the part of the code that is causing the problem:

sql="SELECT topFriendCount, topFriendList FROM users WHERE userID=" & request.QueryString("userID") & ""
      set rsTopCount=Conn.Execute(sql)

      topCount=rsTopCount("topFriendCount")
      topFriends=rsTopCount("topFriendList")

      Dim strAryWords
Dim strValue

      strValue = topFriends
strAryWords = Split(strValue, " ")

      For i = 0 to Ubound(strAryWords)
            sql="SELECT * FROM friends WHERE (userID=" & request.QueryString("userID") & " OR friendUserID=" & request.QueryString("userID") & ") friendUserID=" & strAryWords & " AND status='approved' ORDER BY friendID"
            set rs=Conn.Execute(sql)

Hi judo2000,

Should be
sql="SELECT * FROM friends WHERE (userID=" & request.QueryString("userID") & " OR friendUserID=" & request.QueryString("userID") & ") friendUserID=" & strAryWords(i) & " AND status='approved' ORDER BY friendID"

Also watch out for empty strings, if it is possible you might want to add

If strAryWords(i) <> "" Then

Cheers,
NicksonKoh

Guy Hengel [angelIII / a3]

sql="SELECT topFriendCount, topFriendList FROM users WHERE userID=" & request.QueryString("userID") & ""
set rsTopCount=Conn.Execute(sql)

topCount=rsTopCount("topFriendCount")
topFriends=rsTopCount("topFriendList")

Dim strAryWords
Dim strValue

strValue = topFriends
strAryWords = Split(strValue, " ")

'NO NEED TO LOOP, ACTUALLY:
'For i = 0 to Ubound(strAryWords)

'if friendUserID is numerical data:

sql="SELECT * FROM friends WHERE (userID=" & request.QueryString("userID") & " OR friendUserID=" & request.QueryString("userID") & ") friendUserID IN (" & join(strAryWords, ",") & " , null) AND status='approved' ORDER BY friendID"
set rs=Conn.Execute(sql)

'if friendUserID is string data:

sql="SELECT * FROM friends WHERE (userID=" & request.QueryString("userID") & " OR friendUserID=" & request.QueryString("userID") & ") friendUserID IN ('" & join(strAryWords, "','") & "') AND status='approved' ORDER BY friendID"
set rs=Conn.Execute(sql)

ASKER

Hey,
Sorry it took so long but I tried both suggestions and NicksonKoh's worked but when I list the other friends on that page who are not top friends it puts the top friends at the bottom when I go to page 2. I want it to work like the top friends on myspace and I even have the sourse myspace used but they must have some modification I don't have access

I tried angelIII's code and got the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-4.1.20-max-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'friendUserID IN ('248','249','251','252','253','254','255','256','257') AND stat' at line 1

/topFriends.asp, line 223

Here is the entire code where it lists the friends not in the top friends:

<ul id="boxes">
                                    <%
                                          sql="SELECT topFriendCount, topFriendList FROM users WHERE userID=" & request.QueryString("userID") & ""
                                          set rsTopCount=Conn.Execute(sql)

                                          topCount=rsTopCount("topFriendCount")
                                          topFriends=rsTopCount("topFriendList")

                                          Dim strAryWords
                                          Dim strValue

                                          strValue = topFriends
                                          strAryWords = Split(strValue, " ")

                                          For i = 0 to Ubound(strAryWords)
                                                'top friend start
                                                      sql="SELECT * FROM friends WHERE (userID=" & request.QueryString("userID") & " OR friendUserID=" & request.QueryString("userID") & ") AND friendUserID=" & strAryWords(i) & " AND status='approved' ORDER BY friendID"
                                                      set rs=Conn.Execute(sql)

                                                      DO WHILE NOT rs.EOF
                                                            sql="SELECT * FROM users WHERE (userID=" & rs("friendUserID") & " OR userID=" & rs("userID") & ") AND userID !=" & request.QueryString("userID") & ""
                                                            set rsUser=Conn.Execute(sql)
                                                            'response.Write("cnt " & cnt & "<br>")
                                                            'response.Write(topFriendCount)
                                                      %>
                                                      <li class="boxTop" id="<%=rsUser("userID")%>">
                                                      <%
                                                            sql="SELECT * FROM pics WHERE (userID=" & rs("friendUserID") & " OR userID=" & rs("userID") & ") AND userID !=" & userID & " AND picDefault='yes'"
                                                            set rsFriend=Conn.Execute(sql)

                                                            if NOT rsFriend.EOF THEN
                                                                  if NOT rsFriend("picName")="" THEN
                                                            %>
                                                            <img src="http://www.firedigs.com/images/userPics/<%=rsUser("url")%>/<%=rsFriend("picName")%>" width="100" border="0" alt="<%=rsUser("displayName")%>" onload="maxImage(this);"><br>
                                                            <%
                                                                  ELSE
                                                            %>
                                                            <img src="http://www.firedigs.com/images/blankPic.gif" width="100" height="100" border="0" alt="<%=rsUser("displayName")%>" onload="maxImage(this);"><br>
                                                            <%
                                                                  END if
                                                                  ELSE
                                                            %>
                                                            <img src="http://www.firedigs.com/images/blankPic.gif" width="100" height="100" border="0" alt="<%=rsUser("displayName")%>" onload="maxImage(this);"><br>
                                                            <%
                                                                  END if
                                                            response.Write(rsUser("displayName"))
                                                      %>
                                                      <br>

                                                      </li>
                                                      <%
                                                            rs.MoveNext
                                                      Loop
                                                'top friends end
                                          Next

                                          DIM intPageRecords, intRecords, intRecordCount, intCurrentPage
                                          DIM intNumberOfPages, intDisplayPage

                                                sql="SELECT * FROM friends WHERE (userID=" & request.QueryString("userID") & " OR friendUserID=" & request.QueryString("userID") & ") AND status='approved'"
                                                For i = 0 to Ubound(strAryWords)
                                                      sql=sql & " AND friendUserID<>" & strAryWords(i) & ""
                                                Next
                                                sql=sql & " ORDER BY friendID"
                                                set rsCount=Conn.Execute(sql)

                                                if NOT rsCount.EOF THEN
                                                      recordCount=0
                                                      DO WHILE NOT rsCount.EOF
                                                            recordCount=recordCount+1
                                                            rsCount.MoveNext
                                                      Loop

                                                      sql="SELECT * FROM friends WHERE (userID=" & request.QueryString("userID") & " OR friendUserID=" & request.QueryString("userID") & ")"
                                                      For i = 0 to Ubound(strAryWords)
                                                            sql=sql & " AND friendUserID<>" & strAryWords(i) & ""
                                                      Next
                                                      sql=sql & " AND status='approved' ORDER BY friendID"
                                                      set rs=Conn.Execute(sql)
                                                %>
                                                <br><br>
                                                <%
                                                      intPageRecords = Request.Querystring("page")
                                                      IF intPageRecords = "" THEN intPageRecords = 1 : intRecords = 1
                                                      intRecords = intPageRecords
                                                      intPageRecords = ((intPageRecords - 1) * 20) +1
                                                      intRecordCount = 0

                                                      rs.Move (intPageRecords - 1)

                                                      cnt=0
                                                      DO WHILE intRecordCount < 20 and NOT rs.EOF
                                                            sql="SELECT * FROM users WHERE (userID=" & rs("friendUserID") & " OR userID=" & rs("userID") & ") AND userID !=" & request.QueryString("userID") & ""
                                                            For i = 0 to Ubound(strAryWords)
                                                                  sql=sql & " AND userID<>" & strAryWords(i) & ""
                                                            Next
                                                            set rsUser=Conn.Execute(sql)
                                                            'response.Write("cnt " & cnt & "<br>")
                                                            'response.Write(topFriendCount)
                                                      %>
                                                      <li class="box" id="<%=rsUser("userID")%>">
                                                      <%
                                                            sql="SELECT * FROM pics WHERE (userID=" & rs("friendUserID") & " OR userID=" & rs("userID") & ") AND userID !=" & userID & ""
                                                            For i = 0 to Ubound(strAryWords)
                                                                  sql=sql & " AND userID<>" & strAryWords(i) & ""
                                                            Next
                                                            sql=sql & " AND picDefault='yes'"
                                                            set rsFriend=Conn.Execute(sql)

                                                            if NOT rsFriend.EOF THEN
                                                                  if NOT rsFriend("picName")="" THEN
                                                            %>
                                                            <img src="http://www.firedigs.com/images/userPics/<%=rsUser("url")%>/<%=rsFriend("picName")%>" width="100" border="0" alt="<%=rsUser("displayName")%>"><br>
                                                            <%
                                                                  ELSE
                                                            %>
                                                            <img src="http://www.firedigs.com/images/blankPic.gif" width="100" height="100" border="0" alt="<%=rsUser("displayName")%>"><br>
                                                            <%
                                                                  END if
                                                                  ELSE
                                                            %>
                                                            <img src="http://www.firedigs.com/images/blankPic.gif" width="100" height="100" border="0" alt="<%=rsUser("displayName")%>"><br>
                                                            <%
                                                                  END if
                                                            response.Write(rsUser("displayName"))
                                                      %>
                                                      <br>

                                                      </li>
                                                      <%
                                                            cnt=cnt+1
                                                            rs.MoveNext
                                                            intRecordCount = intRecordCount +1
                                                      Loop
                                                %>
                                          </ul>
                                          <div style="clear: both;"><br /></div>
                                          <br><br>
                                          </td>
                                    </tr>
                                    <tr>
                                          <td>
                                                <b><%=intPageRecords%> - <%=intPageRecords+(intRecordCount-1)%> of <%=(recordCount)%> Fire Fighters</b>
                                                <br><br>
                                                <%
                                                      intCurrentPage = Request.Querystring("page")
                                                      IF intCurrentPage = "" THEN intCurrentPage = 1
                                                      intNumberOfPages = int(recordCount \ 20)
                                                      IF recordCount MOD 20<> 0 THEN intNumberOfPages = intNumberOfPages + 1
                                                      Response.Write("Pages: [")
                                                      FOR intDisplayPage = 1 TO intNumberOfPages
                                                            IF Cint(intDisplayPage) = Cint(intCurrentPage) THEN
                                                                  Response.Write " <b>" & intDisplayPage & "</b> "
                                                            ELSE
                                                                  Response.Write " <a href=""topFriends.asp?page=" & intDisplayPage & "&userID=" & request.QueryString("userID") & """ class='pgNav'>" & intDisplayPage &_
                                                                  "</a> "
                                                            END IF
                                                      NEXT
                                                      Response.Write ("]")

ASKER

To see the page go to:
http://friends.firedigs.com/topFriends.asp?userID=150

You don't have to be logged in right now to see it the userID is mine.
It looks really jacked up in IE but looks right in Firefox, I'll worry about the layout once all the other stuff is working correctly.

Thanks

Hi,

To order the list properly, you can simply tweak the SQL as shown below

<%
sql="SELECT topFriendCount, topFriendList FROM users WHERE userID=" & request.QueryString("userID") & ""
set rsTopCount=Conn.Execute(sql)

topCount=rsTopCount("topFriendCount")
topFriends=rsTopCount("topFriendList")

strValue=Replace(topFriends, " ", ", ")
sql="SELECT a.*, 1 as IsTopFriends FROM friends a "
sql=sql & " WHERE userID IN (" & strValue & ") AND a.status='approved'"
sql=sql & " UNION "
sql=sql & " SELECT b.*, 0 as IsTopFriends FROM friends b "
sql=sql & " WHERE (userID=" & request.QueryString("userID") & " OR friendUserID=" & request.QueryString("userID") & ") AND b.status='approved'"
sql=sql & " ORDER BY IsTopFriends desc, friendID"
set rs=Conn.Execute(sql)
%>

ASKER

I get the following error when I use that sql statement:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-4.1.20-max-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND a.status='approved' UNION SELECT b.*, 0 as IsTopFriends FROM friends b W' at line 1

/topFriends.asp, line 228

ASKER

Ok, I change:
sql="SELECT a.*, 1 as IsTopFriends FROM friends a "
sql=sql & " WHERE userID IN (" & strValue & ") AND a.status='approved'"
sql=sql & " UNION "
sql=sql & " SELECT b.*, 0 as IsTopFriends FROM friends b "
sql=sql & " WHERE (userID=" & request.QueryString("userID") & " OR friendUserID=" & request.QueryString("userID") & ") AND b.status='approved'"
sql=sql & " ORDER BY IsTopFriends desc, friendID"

to:
sql="SELECT a.*, 1 as IsTopFriends FROM friends a "
sql=sql & " WHERE userID IN ('" & strValue & "') AND a.status='approved'"
sql=sql & " UNION "
sql=sql & " SELECT b.*, 0 as IsTopFriends FROM friends b "
sql=sql & " WHERE (userID=" & request.QueryString("userID") & " OR friendUserID=" & request.QueryString("userID") & ") AND b.status='approved'"
sql=sql & " ORDER BY IsTopFriends desc, friendID"

But now it lists all my friends not just the top friends

Thanks

Hi, Isn't that what u need? To have top friends on top and the other friends below.

'This part of the SQL list the top friends
sql="SELECT a.*, 1 as IsTopFriends FROM friends a "
sql=sql & " WHERE userID IN ('" & strValue & "') AND a.status='approved'"

sql=sql & " UNION "

'This part of the SQL list the other friends (note I added an additional condition to make sure this part of the SQL does not pull in the Top Friends)
sql=sql & " SELECT b.*, 0 as IsTopFriends FROM friends b "
sql=sql & " WHERE (userID=" & request.QueryString("userID") & " OR friendUserID=" & request.QueryString("userID") & ") AND userID NOT IN ('" & strValue & "') AND b.status='approved' "
sql=sql & " ORDER BY IsTopFriends desc, friendID"

ASKER

The problem I'm having is how to display them afterwards. I want to only display 20 per page but it was displaying all my friends starting with the top friends then all 200 of my friends on one page then it would display the top friends again but I think that is in the rest of my code. I'll try to redo that part and let you know, I think because it does them both at the same time it just make the rest of the code not work but I should be able to redo the rest to make it work.

Thanks

judo2000, are the placing a for loop in the query?? You shouldn't. You should simply loop through the recordsets.

<%
sql="SELECT topFriendCount, topFriendList FROM users WHERE userID=" & request.QueryString("userID") & ""
set rsTopCount=Conn.Execute(sql)

topCount=rsTopCount("topFriendCount")
topFriends=rsTopCount("topFriendList")

strValue=Replace(topFriends, " ", ", ")

sql = ....

set rs=Conn.Execute(sql)

DO WHILE NOT rs.EOF
%>
... Code to write the result to HTML ...
<%
Loop

ASKER

Ok, I think I may have it figured out but how can I determine if the user is a top friend or not in an if statement. I need the top friends photos to have a different background color around them.

ASKER

I was using a for loop but took it out. I think I have it working except for my previous comment and need to rework it so only 20 + the top friends show up per page. I'm getting closer to that too.

rst.Fields("IsTopFriends") will tell if the record is a top friend.

1 = Yes
0 = Not

ASKER

I tried and it lists everything as 0. I did a test:

DO WHILE NOT rs.EOF
response.Write(rs("IsTopFriends") & "<br>")
rs.MoveNext
Loop

I tried it with rs("IsTopFriends") and rs.Fields("IsTopFriends")

Thanks

Can show your code

ASKER

I changed it a little and it works except it now shows members of the top friends multiple times. I reversed userID and friendUserID. Here is what I have now.

<ul id="boxes">
                                    <%
                                          sql="SELECT topFriendCount, topFriendList FROM users WHERE userID=" & request.QueryString("userID") & ""
                                          set rsTopCount=Conn.Execute(sql)

                                          topCount=rsTopCount("topFriendCount")
                                          topFriends=rsTopCount("topFriendList")

                                          Dim strAryWords
                                          Dim strValue

                                          topCount=rsTopCount("topFriendCount")
                                          topFriends=rsTopCount("topFriendList")

                                          strValue=Replace(topFriends, " ", ", ")

                                          sql="SELECT a.*, 1 as IsTopFriends FROM friends a "
                                          sql=sql & " WHERE friendUserID IN (" & strValue & ") AND a.status='approved'"
                                          sql=sql & " UNION "
                                          sql=sql & " SELECT b.*, 0 as IsTopFriends FROM friends b "
                                          sql=sql & " WHERE userID=" & request.QueryString("userID") & " AND b.status='approved'"
                                          sql=sql & " ORDER BY IsTopFriends desc, friendID"
                                          set rs=Conn.Execute(sql)

                                          DO WHILE NOT rs.EOF
                                                sql="SELECT * FROM users WHERE (userID=" & rs("friendUserID") & " OR userID=" & rs("userID") & ") AND userID !=" & request.QueryString("userID") & ""
                                                set rsUser=Conn.Execute(sql)
                                                if rs.Fields("IsTopFriends")="1" THEN
                                                %>
                                                <li class="boxTop" id="<%=rsUser("userID")%>">
                                                <%
                                                      ELSE
                                                %>
                                                <li class="box" id="<%=rsUser("userID")%>">
                                                <%
                                                      END if
                                                      sql="SELECT * FROM pics WHERE (userID=" & rs("friendUserID") & " OR userID=" & rs("userID") & ") AND userID !=" & userID & " AND picDefault='yes'"
                                                      set rsFriend=Conn.Execute(sql)

                                                      if NOT rsFriend.EOF THEN
                                                            if NOT rsFriend("picName")="" THEN
                                                      %>
                                                      <img src="http://www.firedigs.com/images/userPics/<%=rsUser("url")%>/<%=rsFriend("picName")%>" width="100" border="0" alt="<%=rsUser("displayName")%>" onload="maxImage(this);"><br>
                                                      <%
                                                            ELSE
                                                      %>
                                                      <img src="http://www.firedigs.com/images/blankPic.gif" width="100" height="100" border="0" alt="<%=rsUser("displayName")%>" onload="maxImage(this);"><br>
                                                      <%
                                                            END if
                                                            ELSE
                                                      %>
                                                      <img src="http://www.firedigs.com/images/blankPic.gif" width="100" height="100" border="0" alt="<%=rsUser("displayName")%>" onload="maxImage(this);"><br>
                                                      <%
                                                            END if
                                                      response.Write(rsUser("displayName"))
                                                %>
                                                <br>

                                                </li>
                                                <%
                                                      rs.MoveNext
                                                Loop
                                                %>
                                          </ul>
                                          <div style="clear: both;"><br /></div>

ASKER

I may have gotten it. I changed the sql statment to:
sql="SELECT a.*, 1 as IsTopFriends FROM friends a "
                                          sql=sql & " WHERE friendUserID IN (" & strValue & ") AND a.status='approved' LIMIT " & topCount & ""
                                          sql=sql & " UNION "
                                          sql=sql & " SELECT b.*, 0 as IsTopFriends FROM friends b "
                                          sql=sql & " WHERE (userID=" & request.QueryString("userID") & " OR frienduserID=" & request.QueryString("userID") & ") AND b.status='approved'"
                                          sql=sql & " ORDER BY IsTopFriends desc, friendID"

Added limit " & topCount & "" in the first part of the statement. I'll let you know.

ASKER

Ok, well it almost works. For some reason it is showing the first 8 of 10 top friends then it repeats 2 of them rather than showing the last 2.

Any ideas?

Thanks

Hi judos,

To debug the SQL, use Response.Write sql and then test the SQL in mySQL.
Also, run each part of the SQL separately to determine why the SQL is not retrieving the results as required.

sql="SELECT a.*, 1 as IsTopFriends FROM friends a "
sql=sql & " WHERE friendUserID IN (" & strValue & ") AND a.status='approved' LIMIT " & topCount & ""
sql=sql & " UNION "
sql=sql & " SELECT b.*, 0 as IsTopFriends FROM friends b "
sql=sql & " WHERE (userID=" & request.QueryString("userID") & " OR frienduserID=" & request.QueryString("userID") & ") AND b.status='approved'"
sql=sql & " ORDER BY IsTopFriends desc, friendID"
Response.Write SQL
Response.End

ASKER

Here is what I got when I did that. As far as I can tell it looks correct but still repeating some of the people:

SELECT a.*, 1 as IsTopFriends FROM friends a WHERE friendUserID IN (248, 249, 251, 252, 253, 254, 255, 256, 257, 506) AND a.status='approved' LIMIT 10 UNION SELECT b.*, 0 as IsTopFriends FROM friends b WHERE userID=150 AND b.status='approved' ORDER BY IsTopFriends desc, friendID

Maybe it's my other sql statments that are causing the problem?

Thanks

ASKER

You can see the page at:

http://friends.firedigs.com/topFriends.asp?userID=150

To confirm that the SQL is returning the result as expected, did you run query directly in MySQL?

ASKER

I did and it lists them the same way, it lists some of them then repeats 2 instead of posting the last 2

ASKER

I ran it in mySQL and got the same output but when I clicked to order them by friendUserID I got the following error:

SQL query: Documentation

SELECT a. * , 1 AS IsTopFriends
FROM friends a
WHERE friendUserID
IN ( 248, 249, 251, 252, 253, 254, 255, 256, 257, 506 )
AND a.status = 'approved'
ORDER BY `friendUserID` ASC
LIMIT 10
UNION SELECT b. * , 0 AS IsTopFriends
FROM friends b
WHERE userID =150
AND b.status = 'approved'
LIMIT 0 , 30

MySQL said: Documentation
#1221 - Incorrect usage of UNION and ORDER BY

ASKER

Never mind on the last comment, I took out the limit 10 and it didn't give me the error but now it shows all of the top friends listed twice. That is weird.

SELECT * FROM (
SELECT a. * , 1 AS IsTopFriends
FROM friends a
WHERE friendUserID
IN ( 248, 249, 251, 252, 253, 254, 255, 256, 257, 506 )
AND a.status = 'approved'
LIMIT 10
UNION SELECT b. * , 0 AS IsTopFriends
FROM friends b
WHERE userID =150
AND b.status = 'approved'
LIMIT 0 , 30)
ORDER BY 'IsTopFriends' DESC, `friendUserID` ASC

ASKER

Error

SQL query: Documentation

SELECT *
FROM (

SELECT a. * , 1 AS IsTopFriends
FROM friends a
WHERE friendUserID
IN ( 248, 249, 251, 252, 253, 254, 255, 256, 257, 506 )
AND a.status = 'approved'
LIMIT 10
UNION SELECT b. * , 0 AS IsTopFriends
FROM friends b
WHERE userID =150
AND b.status = 'approved'
LIMIT 0 , 30
)
ORDER BY 'IsTopFriends' DESC , `friendUserID` ASC
LIMIT 0 , 30

MySQL said: Documentation
#1248 - Every derived table must have its own alias

Try

SELECT * FROM (
SELECT a. * , 1 AS IsTopFriends
FROM friends a
WHERE friendUserID IN ( 248, 249, 251, 252, 253, 254, 255, 256, 257, 506 )
AND a.status = 'approved'
LIMIT 10
UNION
SELECT b. * , 0 AS IsTopFriends
FROM friends b
WHERE userID = 150
and friendUserID NOT IN ( 248, 249, 251, 252, 253, 254, 255, 256, 257, 506 )
AND b.status = 'approved')
ORDER BY 'IsTopFriends' DESC, `friendUserID` ASC

SELECT c.* FROM (
SELECT a. * , 1 AS IsTopFriends
FROM friends a
WHERE friendUserID IN ( 248, 249, 251, 252, 253, 254, 255, 256, 257, 506 )
AND a.status = 'approved'
LIMIT 10
UNION
SELECT b. * , 0 AS IsTopFriends
FROM friends b
WHERE userID = 150
and friendUserID NOT IN ( 248, 249, 251, 252, 253, 254, 255, 256, 257, 506 )
AND b.status = 'approved') c
ORDER BY 'IsTopFriends' DESC, `friendUserID` ASC

ASKER

Same error

ASKER

Ok in the last you posted I didn't get an error when I took limit 10 out but it repeats 253 several times.

SELECT c.* FROM (
SELECT a. * , 1 AS IsTopFriends
FROM friends a
WHERE userID = 150
AND a.friendUserID IN ( 248, 249, 251, 252, 253, 254, 255, 256, 257, 506 )
AND a.status = 'approved'
UNION
SELECT b. * , 0 AS IsTopFriends
FROM friends b
WHERE b.userID = 150
and b.friendUserID NOT IN ( 248, 249, 251, 252, 253, 254, 255, 256, 257, 506 )
AND b.status = 'approved') c
ORDER BY 'IsTopFriends' DESC, `friendUserID` ASC

ASKER

that is very close. When the page is loading it looks correct then it shifts the first 6 to the very end of the list and the last 2 are at the top and I think number 9 is not highlighted as a top friend. When I run it in mySQL it looks correct.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Awesome! That works, I had to add another query to get the url for the user picture but it works. I have another question about how to make paging work with this setup but I'll post another questions.

Thanks so much for your help. I would never gotten to the sql statement we finished with. I appreciate your help.