Jump to content


Photo

php loop for changing mssql results


  • Please log in to reply
4 replies to this topic

#1 dogdaynoon

dogdaynoon

    Newbie

  • New Members
  • Pip
  • 7 posts

Posted 19 August 2013 - 03:07 PM

Hello, i am running the following:
MS Server 2008 R2, SQL Express 2012, IIS 7, PHP 5

I have a user input form that allows users to select what fields in the database that they would like to search with a supplied search term, (supplied by them).


This is working well. My problem is that since the sqlsrv_fetch_array() statement can return a varrying number of items I am having a hard time looping through each row returned and showing all the selected results. Let me explain more.

Example table looks like this:
ID | FIRSTNAME | LASTNAME | AGE | CITY | MAIDENNAME | STATE | AUTOMOBILE
---------------------------------------------------------------------------------------------------
1 | BILLY | FRANKLIN | 37 | RIO | CONNER | WISCONSON | GEO METRO
2 | ******** ******* ** *** ****** *************** ***********

A user can select check boxes for any of the columns that they wish to search, so the search results could return any combination of the column names above.
So a user may enter search term Sam and select FIRSTNAME and MAIDENNAME to search
so my code to loop through this data would be:

 
while($row = sqlsrv_fetch_array($stmt) {
echo($row['FIRSTNAME'] . " - " . $row['MAIDENNAME'] . "<hr />");
}

This works fine and dandy until a user decides to search AGE and CITY and FIRSTNAME: 
now my echo statement will need to reflect that it is $row['AGE'], $row['CITY'], and $row['FIRSTNAME'].


How do write that echo statement to reflect the ever changing selection of the user for what columns or fields they want to search?


Thanks in advance,
dogdaynoon

 

 

here are a couple of examples of what i have tried...
 

while($row = sqlsrv_fetch_array($stmt)) {
             $rslt = "";
            for($i=0; $i>=count($row);$i++){
               $rslt .= $row[$i] . " "; 
               $i++
             }
           echo($rslt);
         }


I am sad to say that i don't have a very logistical thought process so i am all about trial and error.

 


$i = 0;
$rslt = "";
while($row = sqlsrv_fetch_array($stmt)){
      while($i<count($row)){
         $rslt = $rlst . " " . $row[$i] . " ";
         $i++;
         if($i>=count($row)){
            $rslt .= "<hr />";
            echo($rslt);
            $i++;
            break;
          }
      }
}

 



#2 _EmilsM

_EmilsM

    Member

  • Members
  • PipPip
  • 14 posts

Posted 19 August 2013 - 03:49 PM

Hello. Could you please post the "$stmt" variable content?



#3 dogdaynoon

dogdaynoon

    Newbie

  • New Members
  • Pip
  • 7 posts

Posted 19 August 2013 - 04:04 PM

Thank you for your response.

here is a sample of the select statement:

SELECT xml_test_table.idCred, xml_test_table.datacred, dbo.assoc_files.associated_file
FROM py_testing.dbo.xml_test_table 
INNER JOIN py_testing.dbo.assoc_files
ON py_testing.dbo.xml_test_table.full_xml_path=py_testing.dbo.assoc_files.full_xml_path 
WHERE idCred LIKE '%james%' OR datacred LIKE '%james%' 

This is a statement built with variables so the part that reads xml_test_table.idCred, xml_test_table.datacred, dbo.assoc_files.associated_file is built from user inputed check boxes and the part that says

idCred LIKE '%james%' OR datacred LIKE '%james%' is built from the user input as well as the user text input for a search term

Then end resulting $stmt is as follows:

$tsql = "SELECT $slctTerm 
        FROM py_testing.dbo.xml_test_table 
	INNER JOIN py_testing.dbo.assoc_files
	ON py_testing.dbo.xml_test_table.full_xml_path=py_testing.dbo.assoc_files.full_xml_path
	WHERE $likeTerm";

the $slctTerm and the $likeTerm are built on the fly.

is this what you were looking for?


Edited by dogdaynoon, 19 August 2013 - 04:07 PM.


#4 kicken

kicken

    Wiser? Not exactly.

  • Gurus
  • 2,568 posts
  • LocationBonita, FL

Posted 19 August 2013 - 04:05 PM

Use a foreach loop over the return value from sqlsrv_fetch_array.
 
while($row = sqlsrv_fetch_array($stmt)) {
   foreach ($row as $column=>$value){
      echo $column.": ".$value."<br>";
   }
   echo '<hr>';
}

Edited by kicken, 19 August 2013 - 04:06 PM.

Recycle your old CD's, don't trash them!
Did I help you out?  Feeling generous? I accept tips via Paypal or Bitcoin @ 14mDxaob8Jgdg52scDbvf3uaeR61tB2yC7

#5 dogdaynoon

dogdaynoon

    Newbie

  • New Members
  • Pip
  • 7 posts

Posted 19 August 2013 - 04:13 PM

that worked great! however it doesn't stop.

infinite...

I will look at that and get back here once I see why that is happening... :)

 

 

THIS DID IT!!! thank you very much. I had been staring at this for days! I didn't know you could put the $column=>$value in the foreach. that is great!

while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){
   foreach ($row as $column=>$value){
      echo $column.": ".$value."<br />";
   }
   echo '<hr />';
}

Edited by dogdaynoon, 19 August 2013 - 04:16 PM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com