Building a dynamic SQL statement based on contents of an array

[emozley]

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:

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...

$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:

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

 

[ChrisHirst]

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

Chris.

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

 

[gandalf458]

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

 

[ChrisHirst]

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