Jump to content

Archived

This topic is now archived and is closed to further replies.

dogdaynoon

php loop for changing mssql results

Recommended Posts

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;
          }
      }
}

 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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>';
}

Share this post


Link to post
Share on other sites

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 />';
}

Share this post


Link to post
Share on other sites

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