[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Split function then convert varchar to integer

Posted on 2007-10-21
35
Medium Priority
?
3,478 Views
Last Modified: 2012-05-05
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)
0
Comment
Question by:judo2000
  • 21
  • 13
35 Comments
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 20120737
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
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20120803
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)
0
 

Author Comment

by:judo2000
ID: 20167842
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 ("]")
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Author Comment

by:judo2000
ID: 20167853
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
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 20167897
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)
%>
0
 

Author Comment

by:judo2000
ID: 20170893
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
0
 

Author Comment

by:judo2000
ID: 20171047
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
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 20174215
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"
0
 

Author Comment

by:judo2000
ID: 20174679
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
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 20174710
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
0
 

Author Comment

by:judo2000
ID: 20174826
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.  
0
 

Author Comment

by:judo2000
ID: 20174833
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.
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 20174886
rst.Fields("IsTopFriends") will tell if the record is a top friend.

1 = Yes
0 = Not
0
 

Author Comment

by:judo2000
ID: 20182595
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
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 20182605
Can show your code
0
 

Author Comment

by:judo2000
ID: 20182770
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>
0
 

Author Comment

by:judo2000
ID: 20182853
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.
0
 

Author Comment

by:judo2000
ID: 20182943
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
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 20183029
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

0
 

Author Comment

by:judo2000
ID: 20183092
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
0
 

Author Comment

by:judo2000
ID: 20183093
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 20183124
To confirm that the SQL is returning the result as expected, did you run query directly in MySQL?
0
 

Author Comment

by:judo2000
ID: 20183145
I did and it lists them the same way, it lists some of them then repeats 2 instead of posting the last 2
0
 

Author Comment

by:judo2000
ID: 20183151
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
0
 

Author Comment

by:judo2000
ID: 20183155
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.
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 20183159
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
0
 

Author Comment

by:judo2000
ID: 20183165
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
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 20183171
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
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 20183175
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
0
 

Author Comment

by:judo2000
ID: 20183177
Same error
0
 

Author Comment

by:judo2000
ID: 20183188
Ok in the last  you posted I didn't get an error when I took limit 10 out but it repeats 253 several times.
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 20183199
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
0
 

Author Comment

by:judo2000
ID: 20183213
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.

0
 
LVL 17

Accepted Solution

by:
NicksonKoh earned 1200 total points
ID: 20183261
I modified your previous code and used the above correct union SQL. The pic SQL is also incorrect since you are going to just pick the picture of the friend.

<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 c.* FROM ("
      sql=sql & "SELECT a.*, 1 as IsTopFriends FROM friends a "
      sql=sql & " WHERE userID=" & request.QueryString("userID") & " AND 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 friendUserID NOT IN (" & strValue & ")  AND a.status='approved' "
      sql=sql & " ) ORDER BY 'IsTopFriends' DESC, `friendUserID` ASC"
      
      set rs=Conn.Execute(sql)
      
      DO WHILE NOT rs.EOF
            If rs("IsTopFriends")="1" THEN strClass = "boxTop" Else strClass = "Top"
      %>
  <li class="<%= strClass %>" id="<%=rsUser("userID")%>">
    <%
                  sql="SELECT  * FROM pic WHERE userID=" & rs("friendUserID")
                  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>
back to top
0
 

Author Comment

by:judo2000
ID: 20187022
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.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . It goes without saying that technology has transformed society and the very nature of how we live, work, and communicate in ways that would’ve been incomprehensible 5 ye…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question