×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Building a dynamic SQL statement based on contents of an array

Building a dynamic SQL statement based on contents of an array

Building a dynamic SQL statement based on contents of an array

(OP)
Hi,

After years and years of procrastinating I have finally decided to bite the bullet and convert my ASP classic code to PHP. Some bits are relatively straightforward but I am having real problems understanding how best to work with the results of sql queries. In ASP classic I can dump the recordet into an array with GetRows() but in php it seems you can't do that and have to work with the recordset.

My ASP code:

CODE

TBL.Open "SELECT RB_ResourceCats.CatID, RB_ResourceCats.CatName " &_
           "FROM RB_ResourceCats " &_
		   "WHERE RB_ResourceCats.Visible='True' " &_
		   "ORDER BY RB_ResourceCats.OrderOnPage", DB
		   
  If Not TBL.EOF Then
    CatArray=TBL.GetRows()
	ValidCatArray=True
  End If
  
  TBL.Close
  
  strSQL = "SELECT RB_Resources.ResourceID, RB_Resources.ResourceName, RB_Resources.ColHeadName, RB_Resources.AdminBook, " &_
           "RB_Resources.CatID " &_
		   "FROM RB_Resources " &_
		   "LEFT JOIN RB_ResourceCats ON RB_Resources.CatID=RB_ResourceCats.CatID " 

  TrueValues=0
  If ValidCatArray=True Then
    For i=0 To UBOUND(CatArray, 2)
	  ShowCat=Request.QueryString("Cat"& CatArray(0, i))
	  If ShowCat="" Then ShowCat="true"
	  If ShowCat="true" Then
	    If TrueValues=0 Then strSQL = strSQL & "WHERE "  
	    If TrueValues>0 And i< UBOUND(CatArray, 2)+1 Then
  	      strSQL = strSQL & "OR " 
	    End If
		strSQL = strSQL & "RB_Resources.CatID=" & CatArray(0, i) & " "
		TrueValues=TrueValues+1
	  End If
	Next
  End If 

My PHP code so far...

CODE

$sql          = "SELECT id, name FROM rb_resourcecats WHERE visible=true order by orderonpage";
$resourceCats = $conn->query($sql);

$sql = "SELECT rb_resources.id, rb_resources.name, rb_resources.colheadname, rb_resources.adminbook, " 
     . "rb_resources.category " 
	 . "FROM rb_resources " 
	 . "LEFT JOIN rb_resourcecats ON rb_resources.category=rb_resourcecats.id ";

$TrueValues=0;

if ($resourceCats->num_rows > 0) {
    // output data of each row
    while ($row = $resourceCats->fetch_assoc()) {
      $ShowCat=$_GET["Cat" . $row["id"]];
      if ($ShowCat() == "") {    $ShowCat="true"; }
      
      if ($ShowCat == "true") {
          if ($TrueValues() == 0) {
              $sql.="WHERE ";
          }
      }
    }
} 

So the bit I am having trouble converting is:

CODE

If TrueValues>0 And i< UBOUND(CatArray, 2)+1 Then
  strSQL = strSQL & "OR " 
End If 

So rather than working with an array called CatArray I am working with a recordset called $resourceCats and what I want to say (in pseudocode) is

If TrueValues > 0 And I am not on the last row of $resourceCats then add "OR to $sql

Any help gratefully received!

Thanks very much

Ed

RE: Building a dynamic SQL statement based on contents of an array


CODE --> PHP

<?php
if (condition) {
write something to the string or do nothing
else {
write "OR" to the string followed by something else
}
} ?>

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

RE: Building a dynamic SQL statement based on contents of an array

Hi Chris, should that be:

<?php
if (condition) {
write something to the string or do nothing
} else {
write "OR" to the string followed by something else
}
?>

I'm not a number, I'm a free man

RE: Building a dynamic SQL statement based on contents of an array

Yep the additional } is a typo

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close