Jump to content

Paging and MySQL Help

Gannuscio

By Gannuscio
in MySQL Help

 Share

Recommended Posts

Gannuscio Newbie

Gannuscio

Posted
Posted

Here is my problem.

 

I currently have a search engine that points to my DB and I want to be able to use paging incase a large amount of records are found.  Everything works fine... I get my results in the right order..

 

but only on the first page.

 

When hit nextpage, or previous page,

 

I get the following error 

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

[MySQL][ODBC 3.51 Driver][mysqld-5.0.45-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 'GROUP BY proddb.code ORDER BY AVG(rateit.stars) DESC' at line 1 

/search.asp, line 176

 

I can't figure out if it my my SQL STATEMENT or if it is the way I am paging the records.

 

Here is the rest of my code.  Please help!

 

Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3
Const adOpenStatic = 3
Const adCmdText = 1


' For Loop To Move Through Search String, Counting Spaces, and
' Incrementing WordCounter By One At Each Occuranceh

For i = 1 to len(SValue)
   If mid(SValue, i, 1) = " " Then 
     WordCounter = WordCounter + 1
   End If
Next

' Now, We Add One To Include The First Word

WordCounter = WordCounter + 1

' Next We Dim Word As An Array, With The Maximum Number Of Words
' To Allow - In This Case, 100

Dim Word(100)

' Now, We Fill The Array With The Words

CurrentWord = 1
For i = 1 to len(SValue)
     If mid(SValue, i, 1) = " " Then
CurrentWord = CurrentWord + 1
     Else
Word(CurrentWord) = Word(CurrentWord) + mid(SValue, i, 1)
     End If
Next

' Now Lets Build The SQL Statement Based On What Search Type (SType)
' Was Selected
Dim rsRecords
Set rsRecords = Server.CreateObject("ADODB.Recordset")
rsRecords.CursorLocation = 3
rsRecords.PageSize = 3
rsRecords.CacheSize = 50

' First Part Of SQL

SQL = " SELECT proddb.code, proddb.ProductName, proddb.ProductUrl, proddb.Description, proddb.ImageURL,  proddb.Category, AVG(rateit.stars) as rateTotal from gannuscio.proddb LEFT JOIN gannuscio.rateit ON proddb.code = rateit.pid WHERE "

' For Loop To Concatenate SQL String Together

For i = 1 to WordCounter
	If SType = "AllWords" Then
		If i <> WordCounter Then
			SQL = SQL & strFieldName & " LIKE '%" & Word(i) & "%' AND "
		ElseIf i = WordCounter Then
			SQL = SQL & strFieldName & " LIKE '%" & Word(i) & "%'"
		End If
	ElseIf SType = "AnyWord" Then
		If i <> WordCounter Then
			SQL = SQL & strFieldName & " LIKE '%" & Word(i) & "%' OR "
		ElseIf i = WordCounter Then
			SQL = SQL & strFieldName & " LIKE '%" & Word(i) & "%'"
		End If
	End If
Next

' Finishing Part Of SQL Statement.

      
SQL = SQL & " GROUP BY proddb.code "
SQL = SQL & " ORDER BY AVG(rateit.stars) DESC " 


rsRecords.Open SQL, oConn, adOpenForwardOnly, adLockReadOnly

		If Len(Request("pagenum")) = 0  Then
            	rsRecords.AbsolutePage = 1
				Else
            If CInt(Request("pagenum")) <= rsRecords.PageCount Then
                rsRecords.AbsolutePage = Request("pagenum")
        			Else
                rsRecords.AbsolutePage = 1
            End If
            End If
                                               
                                               Dim abspage, pagecnt
                                               abspage = rsRecords.AbsolutePage
                                               pagecnt = rsRecords.PageCount
Dim fldF, intRec

if not rsRecords.eof then
r = rsRecords.RecordCount
    	response.write("<div style=""border:#ff66cc 1px dashed;""><font size='2'>Your Search Returned: " & r & " Products<br></font></div><br>") 
Else 
 response.Write("<div style=""border:#ff66cc 1px dashed;""><font size='2'>Sorry we could not find any products to match your search!</font></div><br>")

End If
%>

</h2>
<% If Not rsRecords.EOF And Not rsRecords.BOF Then %>
        <table width="582" height="218" border="0">

	<% 
	Linkcount = 0
	Do While NOT rsRecords.EOF AND LinkCount < rsRecords.PageSize %>
          
          <tr>
            <td width="198" height="76" rowspan="3" align="left"><p align="center">
            <% Response.Write ("<a href='product_detail.asp?prod_description=" & rsRecords(Server.URLEncode("Code")) & "'><img src='" & rsRecords("ImageURL") & "'height='100px' border='0'>") %></p>            </td>
            <th width="374" height="20" align="right" valign="bottom"><div align="center"><% Response.Write "<font color='#000000' size='3'>" & rsRecords("ProductName") & "" %></div></th>
          </tr>
          <tr>
            <th height="29" align="center" valign="bottom"><div align="center"></div></th>
          </tr>
          <tr>
            <th width="374" height="29" align="center" valign="top"><div align="center">
              <% 
		If rsRecords("rateTotal") => 1 Then
                      response.write("<font size='1'>Average Rating: </font><font size='1'>"& Round(rsRecords("rateTotal")) &" out of 5</font><br>") 
														   						  							        
         		      Else
                      response.write ("<b><a class='searchfont'>Not Yet Rated</a></b></br><a class='productfont'><br>" & vbnewline)
                      End If
				  If rsRecords("rateTotal") > 4.5 Then
		Response.Write ("<img src='images/5star.gif' border='0' align='middle'> ")
		Else	
	If rsRecords("rateTotal")  => 3.6 AND rsRecords("rateTotal") <= 4.5 Then
		Response.Write ("<img src='images/4star.gif' border='0' align='middle'> ")
		Else
	If rsRecords("rateTotal")  => 2.6 AND rsRecords("rateTotal") <= 3.5 Then
		Response.Write ("<img src='images/3star.gif' border='0' align='middle'> ")
		Else
	If rsRecords("rateTotal")  => 1.6 AND rsRecords("rateTotal") <= 2.5 Then
		Response.Write ("<img src='images/2star.gif' border='0' align='middle'> ")
		Else
	If rsRecords("rateTotal")  => .5 AND rsRecords("rateTotal") <= 1 Then
		Response.Write ("<img src='images/1star.gif' border='0' align='middle'> ")
		Else
	If rsRecords("rateTotal") < 1 Then
		Response.Write("")
			End If		
	  			End If
			End If
		End If
		End If
	End IF	  %>
            </div></th>
          </tr>
          <tr>
            <td width="198" height="22" align="left"><div align="center">
              <% Response.Write "<center><a href='" & rsRecords("ProductURL") & "' target='_blank'><b><center><font color='#CC3300'></font></center></b><br><font color='#0000FF'><center><img src='images/buynow.gif' border='0' align='center'></center></font></a>" %>
            </div></td>
            <th height="22" align="right" valign="top"><div align="center">
              <% Response.Write "<a class='rate' href='product_detail.asp?prod_description=" & rsRecords(Server.URLEncode("Code")) & "'><img src='images/bluebtn.gif' border='0'></a> " %>
            </div></th>
          </tr>
          <tr>
            <td height="1" colspan="2" align="left"><div align="center"><%
Response.Write "<a class='descriptionfont2'><font color='#000000'>" & rsRecords("Description") & ""
%></div></td>
          </tr>
          <tr>
            <td height="9" colspan="2" align="left" bordercolor="#000000" bgcolor="#CCCCCC"><div align="center"></div></td>
          </tr>
	  <%
	  LinkCount = LinkCount + 1
	  rsRecords.MoveNext %>
	  <%	
Loop
%>
    <%

End If

%>
        </table>

        <table width="200" border="1">
          <tr>
            <td><% Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=1"">First Page</a>"
                                                               Response.Write " | "
                                                               
                                                               If abspage = 1 Then
                                                               Response.Write "<span style=""color:silver;"">Previous Page</span>"
                                                               Else
                                                               Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & abspage - 1 & """>Previous Page</a>"
                                                               End If
                                                               
                                                               Response.Write " | "
                                                               
                                                               If abspage < pagecnt Then
                                                               Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & abspage + 1 & """>Next Page</a>"
                                                               Else
                                                               Response.Write "<span style=""color:silver;"">Next Page</span>"
                                                               End If
                                                               Response.Write " | "
                                                               Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & pagecnt & """>Last Page</a>"
                                                              
                                               
                                               
rsRecords.close 
set rsRecords = nothing 
oConn.close
set oConn = nothing 
%> </td>
          </tr>
        </table>
        <p> </p>
        <p align="center">
      
        </p>
      </div>

      <div class="thirds">
        <div class="smallboxtop"></div>

        <div class="smallbox">
          <script type="text/javascript">
//<![CDATA[
<!--
google_ad_client = "pub-3818016923432290";
google_ad_width = 468;
google_ad_height = 60;
google_ad_format = "468x60_as";
google_ad_type = "text";
google_ad_channel = "";
google_color_border = "FFFFFF";
google_color_bg = "FFFFFF";
google_color_link = "0000FF";
google_color_text = "000000";
google_color_url = "9D1961";
//-->
//]]>
</script> <script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript">
</script>

Link to comment
https://forums.phpfreaks.com/topic/82690-paging-and-mysql-help/
Share on other sites
Gannuscio Newbie

Gannuscio

Posted
  • Author
Posted

Here is my SQL Statement

 

SQL = " SELECT proddb.code, proddb.ProductName, proddb.ProductUrl, proddb.Description, proddb.ImageURL,  proddb.Category, AVG(rateit.stars) as rateTotal from gannuscio.proddb LEFT JOIN gannuscio.rateit ON proddb.code = rateit.pid WHERE "

' For Loop To Concatenate SQL String Together

For i = 1 to WordCounter
	If SType = "AllWords" Then
		If i <> WordCounter Then
			SQL = SQL & strFieldName & " LIKE '%" & Word(i) & "%' AND "
		ElseIf i = WordCounter Then
			SQL = SQL & strFieldName & " LIKE '%" & Word(i) & "%'"
		End If
	ElseIf SType = "AnyWord" Then
		If i <> WordCounter Then
			SQL = SQL & strFieldName & " LIKE '%" & Word(i) & "%' OR "
		ElseIf i = WordCounter Then
			SQL = SQL & strFieldName & " LIKE '%" & Word(i) & "%'"
		End If
	End If
Next

' Finishing Part Of SQL Statement.

      
SQL = SQL & " GROUP BY proddb.code "
SQL = SQL & " ORDER BY AVG(rateit.stars) DESC " 


rsRecords.Open SQL, oConn, adOpenForwardOnly, adLockReadOnly

Link to comment
https://forums.phpfreaks.com/topic/82690-paging-and-mysql-help/#findComment-421017
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • 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.