Question 20950 in Mysql Question posted in
The official MySQL documentation can be found here.

Loop fails to show the table correctly, if value in 1st or 2nd <td></td> = 0 – Mysql

If I have a column with different values called vStatus (member-staus with value 100 – 200 – 300 etc.) in MySQL with diferent values, and I want to place those users in 3 columns in a table like this:

<tr>
    <td><%=[user with value 200]%></td>
    <td><%=[user with value 300]%></td>
    <td><%=[user with value 400]%></td>
</tr>

it will work just fine. But if I loop through all users and place all users with the value 200 in the 1st column

<td><%=200%></td>

all users with the value 300 in the 2nd column

<td><%=300%></td>

and finally all users with the value 400 in the 3rd column

<td><%=400%></td>

it will look something like this:

<%
Set RScolumnA = Conn.Execute("SELECT username, firstname, middlename, lastname FROM users WHERE vStatus = 200 ORDER BY username ASC")
Set RScolumnB = Conn.Execute("SELECT username, firstname, middlename, lastname FROM users WHERE vStatus = 300 ORDER BY username ASC")
Set RScolumnC = Conn.Execute("SELECT username, firstname, middlename, lastname FROM users WHERE vStatus = 400 ORDER BY username ASC")

If Not RScolumnA.EOF OR RScolumnB.EOF OR RScolumnC.EOF Then
    Do Until RScolumnA.EOF AND RScolumnB.EOF AND RScolumnC.EOF
%>

<tr>
    <td><%=200%></td>
    <td><%=300%></td>
    <td><%=400%></td>
</tr>

<%
    If Not RScolumnA.EOF Then
        RScolumnA.MoveNext
    End If
    If Not RScolumnB.EOF Then
        RScolumnB.MoveNext
    End If
    If Not RScolumnC.EOF Then
        RScolumnC.MoveNext
    End If
    Loop
End If
%>

This works just fine, if there’s at least just one value in 1st AND in the 2nd column

But if I remove the only user with

vStatus = 200,

and there’s a user with

vStatus = 300

in the 2nd column, all of the

<tr>...</tr>

disapears.

If there’s at least a vStatus = 200 or vStatus = 300 – or more – no problem. But if there’s only 1 user with vStatus = 200, and only one user with vStatus = 300, and I remove one of them, everything disapears.

A friend of mine suggested using DB -> make an array -> place users in table with GetRows methods (res). But that doesn’t work.

I hope you understand my problem, and I hope to have some help. Thanks in advance

Tags:

2

Answers


  1. 0 
    This is a little bit clunky but does what you want as far as 
I can work out from your question. I used Sql Server as my database

<%
    
dim id, username, firstname, middlename, lastname, vStatus
Set RScolumn = Conn.Execute("SELECT [id],[username],[firstname],[middlename],[lastname],[vStatus] FROM [users] where vStatus in ( 100, 200, 300, 400, 500) order by vStatus, id asc")
%>
<table border="1" cellspacing="1" cellpadding="1">
<tr>
<% 
If Not RScolumn.EOF Then
    Do Until RScolumn.EOF
        id = RScolumn(0)
        username = RScolumn(1)
        firstname = RScolumn(2)
        middlename = RScolumn(3)
        lastname = RScolumn(4)
        vStatus = RScolumn(5)
%>
<td align="left" valign="top" nowrap>
<%=id & " " & username& " " & firstname& " " & middlename& " " & lastname& " " & vStatus  & " "%> 
</td>
<%
    If Not RScolumn.EOF Then
        RScolumn.MoveNext
    End If
    Loop
else%>
 <td align="left" valign="top" nowrap>No Data</td>
 <%
End If
%>
</tr>
</table>
    Login or Signup to reply.
  2. 0

    I added a getrows solution too if you needed that

    <%
Dim ARYRecords, cRecords, cFields, id, username, firstname, middlename, lastname, vStatus, vStatusSwapper

Set RScolumn = Conn.Execute("SELECT [id],[username],[firstname],[middlename],[lastname],[vStatus] FROM [users] where vStatus in (100, 200, 300, 400, 500) order by vStatus, id asc")

Response.Write "<table border=""1"" cellspacing=""1"" cellpadding=""1"">" & vbCrLf
Response.Write "<tr>" & vbCrLf
If Not RScolumn.EOF Then
    ARYRecords = RScolumn.GetRows()
    RScolumn.Close
    Conn.Close
    Set RScolumn = Nothing
    Set Conn = Nothing

    For cRecords = 0 to Ubound(ARYRecords, 2)
        id = ARYRecords(0, cRecords)
        username = ARYRecords(1, cRecords)
        firstname = ARYRecords(2, cRecords)
        middlename = ARYRecords(3, cRecords)
        lastname = ARYRecords(4, cRecords)
        vStatus = ARYRecords(5, cRecords)
        if vStatusSwapper = "" then 
            response.write  "<td nowrap>"
        elseif vStatusSwapper <> vStatus then
            response.write  "</td>" & vbCrLf & "<td nowrap>" 
        end if  
        response.write username & " " & firstname & " " & middlename & " " & lastname & " " 
        vStatusSwapper = vStatus
    Next
    response.write "</td>" & vbCrLf
    response.write "</tr>" & vbCrLf
Else
    Response.Write "<tr><td colspan=""6"" align=""center"">No Data</td></tr>" & vbCrLf
End If
Response.Write "</table>" & vbCrLf
%>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search