PHP, MSSQL + Stored Procedure = help!

[ronrattie]

I have Mac-mini running a small corporate intranet site, I have installed PHP 5 with FreeTDS so that I can access MSSQL databases that run on other company servers.

My problem is that I want I have a search form that queries a database fine, but I have a stored procedure that modifies the input on one field to bounce it off of a list of known abbreviations (ex: OS = Operating System). The stored procedure takes the input of the field and then outputs a string like db.field LIKE %Operating% OR db.field LIKE %System% OR db.field LIKE %OS%

That string needs to be put into the existing database query.

$sql = mssql_query("SELECT * FROM DBTable WHERE $storedprocedure ORDER BY x DESC");

However when I try to drop my string into that query it always complains about syntax error before ORDER.

????

[Sxooter]

can you show us what one of the queries looks like before you run it?

[ronrattie]

So if you look below I'm trying to pass $LongNameClause into the SELECT statement right after WHERE.

It works if I put some static info in those quotes, but I want the $LongNameClause from the Stored Procedure part to be passed into that. I am by a Web Designer by training and have been picking up more and more complex PHP/SQL jobs lately from my design clients. SO please excuse my ignorance. I'm using the Phakt extension from Interakt (bought by Adobe) to connect my PHP to a MSSQL server, but the extension does not support Stored Procedures(natively).

<?php
//Connection statement
require_once('../Connections/Vision.php');

//Aditional Functions
require_once('../includes/functions.inc.php');

// begin Recordset
$server = "database";
$user = "username";
$passwd = "password";
$db_name = "Main";
$table_name = "JSA_Jobs";
$conn = mssql_connect($server,$user,$passwd) or die ("I cannah make da connektion to $server kaptain!");

if ($conn) { 
    mssql_select_db($db_name,$conn); 

$stmt=mssql_init("jsaspGetLongNameClause"); 
//$RC="";
//	mssql_bind($stmt,"@RC",stripslashes($RC),SQLINT2); 

$NameList=""; 
$FieldName=""; 
$AndOr=""; 
$LongNameClause=""; 


mssql_bind($stmt,"@NameList",stripslashes($NameList),SQLVARCHAR,FALSE,500);    
mssql_bind($stmt,"@FieldName",stripslashes($FieldName),SQLVARCHAR,FALSE, 30); 
mssql_bind($stmt,"@AndOr",stripslashes($AndOr),SQLVARCHAR,FALSE, 3);
mssql_bind($stmt,"@LongNameClause",stripslashes($LongNameClause),SQLTEXT,FALSE,TRUE, 8000); 

$result = mssql_execute($stmt); 
//while(mssql_next_recordset($result));
//	echo "$result";



 //   print ("NameList = $NameList ; FieldName = $FieldName ; AndOr = $AndOr"); 
   //$query = "SELECT * From JSA_Jobs WHERE $LongNameClause ORDER BY LongName ASC";
   //$result = mssql_query($query);

mssql_close($conn); 
} 
// end Recordset


// Load the common classes
require_once('../includes/common/KT_common.php');

// Load the common classes
require_once('../includes/jaxon/widgets/dtable/dtable.php');



$dtable_DWAjaxTable1 = new dtable($Vision, 'rsJSA_Jobs1', 'dtable_DWAjaxTable1', 'JSA_Jobs');
$dtable_DWAjaxTable1->setUrl(KT_getFullUri());
$dtable_DWAjaxTable1->setMaxRows(50);
$dtable_DWAjaxTable1->addColumn("ProjectID", "STRING_TYPE", "JSA_Jobs.ProjectID", "%");
$dtable_DWAjaxTable1->addColumn("PrimesClient", "STRING_TYPE", "$LongNameClause");
$dtable_DWAjaxTable1->addColumn("LongName", "STRING_TYPE", "JSA_Jobs.LongName", "%");
$dtable_DWAjaxTable1->addColumn("GeneralDesc", "STRING_TYPE", "JSA_Jobs.GeneralDesc", "%");
$dtable_DWAjaxTable1->addColumn("City", "STRING_TYPE", "JSA_Jobs.City", "%");
$dtable_DWAjaxTable1->addColumn("State", "STRING_TYPE", "JSA_Jobs.State", "%");
$dtable_DWAjaxTable1->addColumn("County", "STRING_TYPE", "JSA_Jobs.County", "%");
$dtable_DWAjaxTable1->addColumn("StartDate", "DATE_TYPE", "JSA_Jobs.StartDate,JSA_Jobs.ProfServicesComplDate", "=");
$dtable_DWAjaxTable1->addColumn("FirmCost", "NUMBERIC_TYPE", "JSA_Jobs.FirmCost,JSA_Jobs.TotalProjectCost", "=");
$dtable_DWAjaxTable1->addColumn("MainServiceType", "STRING_TYPE", "JSA_Jobs.MainServiceType", "=");
$dtable_DWAjaxTable1->addColumn("Sector", "STRING_TYPE", "JSA_Jobs.Sector", "=");
$dtable_DWAjaxTable1->setPK("ProjectID", "NUMERIC_TYPE");
$dtable_DWAjaxTable1->setDefaultSortColumn('LongName', 'DESC');
$dtable_DWAjaxTable1->Execute();

$dtable_DWAjaxTable1_filter_sql = $dtable_DWAjaxTable1->getFilter();
$dtable_DWAjaxTable1_order_sql = $dtable_DWAjaxTable1->getSorter();


//maybe this will work.....

// Begin List Recordset
$maxRows_rsJSA_Jobs1 = $dtable_DWAjaxTable1->getMaxRows();
$pageNum_rsJSA_Jobs1 = 0;
if (isset($_GET['pageNum_rsJSA_Jobs1'])) {
  $pageNum_rsJSA_Jobs1 = $_GET['pageNum_rsJSA_Jobs1'];
}
$startRow_rsJSA_Jobs1 = $pageNum_rsJSA_Jobs1 * $maxRows_rsJSA_Jobs1;
// Defining List Recordset variable

$NXTSPinput__rsJSA_Jobs1 = "";  //<--what should go here? 
if (isset($LongNameClause)) {
  $NXTFilter__rsJSA_Jobs1 = $LongNameClause;
}
$NXTFilter__rsJSA_Jobs1 = "1=1";
if (isset($dtable_DWAjaxTable1_filter_sql)) {
  $NXTFilter__rsJSA_Jobs1 = $dtable_DWAjaxTable1_filter_sql;
}
// Defining List Recordset variable
$NXTSort__rsJSA_Jobs1 = "LongName DESC";
if (isset($dtable_DWAjaxTable1_order_sql)) {
  $NXTSort__rsJSA_Jobs1 = $dtable_DWAjaxTable1_order_sql;
}
$query_rsJSA_Jobs1 = "SELECT JSA_Jobs.PrimesClient, JSA_Jobs.LongName, JSA_Jobs.Name, JSA_Jobs.GeneralDesc, JSA_Jobs.City, JSA_Jobs.State, JSA_Jobs.County, JSA_Jobs.StartDate, JSA_Jobs.ProfServicesComplDate, JSA_Jobs.TotalProjectCost, JSA_Jobs.FirmCost, JSA_Jobs.MainServiceType, JSA_Jobs.Sector, JSA_Jobs.ProjectID FROM JSA_Jobs WHERE {$NXTSPinput__rsJSA_Jobs1} AND {$NXTFilter__rsJSA_Jobs1} ORDER BY {$NXTSort__rsJSA_Jobs1}";
$rsJSA_Jobs1 = $Vision->SelectLimit($query_rsJSA_Jobs1, $maxRows_rsJSA_Jobs1, $startRow_rsJSA_Jobs1) or die($Vision->ErrorMsg());
if (isset($_GET['totalRows_rsJSA_Jobs1'])) {
  $totalRows_rsJSA_Jobs1 = $_GET['totalRows_rsJSA_Jobs1'];
} else {
  $all_rsJSA_Jobs1 = $Vision->SelectLimit($query_rsJSA_Jobs1) or die($Vision->ErrorMsg());
  $totalRows_rsJSA_Jobs1 = $all_rsJSA_Jobs1->RecordCount();
}
$totalPages_rsJSA_Jobs1 = (int)(($totalRows_rsJSA_Jobs1-1)/$maxRows_rsJSA_Jobs1);
// End List Recordset

// begin Recordset
$query_VisionData = sprintf("SELECT * FROM JSA_Jobs");
$VisionData = $Vision->SelectLimit($query_VisionData) or die($Vision->ErrorMsg());
$totalRows_VisionData = $VisionData->RecordCount();
// end Recordset

// AJAX Dynamic Table statistics
$dtable_DWAjaxTable1->setStartRow($startRow_rsJSA_Jobs1);
$dtable_DWAjaxTable1->setPageNum($pageNum_rsJSA_Jobs1);
$dtable_DWAjaxTable1->setTotalRows($totalRows_rsJSA_Jobs1);
$dtable_DWAjaxTable1->setTotalPages($totalPages_rsJSA_Jobs1);
?>

[Sxooter]

Wait, is the database complaining about syntax, or php?

What's the exact error message.

If it's a database error, can you echo out the query from php and post just that?

[ronrattie]

If nothing is put in the quotes

$NXTSPinput__rsJSA_Jobs1 = "";  //<--what should go here? 
if (isset($LongNameClause)) {
  $NXTFilter__rsJSA_Jobs1 = $LongNameClause;
}

Then it gives a syntax error before AND

Because {$NXTSPinputrsJSA_Jobs1} is before the AND in the query.

$query_rsJSA_Jobs1 = "SELECT JSA_Jobs.PrimesClient, ....<SNIP> JSA_Jobs.ProjectID FROM JSA_Jobs WHERE {$NXTSPinput__rsJSA_Jobs1} AND {$NXTFilter__rsJSA_Jobs1} ORDER BY {$NXTSort__rsJSA_Jobs1}";

So I'm trying to pass the output of

mssql_bind($stmt,"@LongNameClause",stripslashes($LongNameClause),SQLTEXT,FALSE,TRUE, 8000); 

into the SELECT statement.

(the input for the SP is coming from a field in a form)

[Roger_Ramjet]

You are telling mysql_bind that @LongNameClause is NOT an OUTPUT parameter so it is treated as an input parameter, which may or may not upset the sp.

mssql_bind($stmt,"@LongNameClause",stripslashes($LongNameClause),SQLTEXT,FALSE,TRUE, 8000);

$LongNameClause is therefore not set so the WHERE clause in the next query will be empty and will throw an SQL syntax error.

While you are at it, I would start using PDO since you are using PHP5, well worth it just for the prepared statement functionality.

[ronrattie]

Okay well, it seems your advice (Ramjet) seems to have helped some. I have to wait for someone else to get back from vacation now to alter the stored procedure a bit for me. (designer/developers not allowed to do that) and then I'll know for sure if all is well.

[ronrattie]

Okay well, it seems your advice (Ramjet) seems to have helped some. I have to wait for someone else to get back from vacation now to alter the stored procedure a bit for me. (designer/developers not allowed to do that) and then I'll know for sure if all is well.

[ronrattie]

Okay... time has passed and the whole page has been pretty much re-written in the meantime.

Now we are using one stored procedure to process the entire output of the form.

I can connect to the database and the stored procedure does run.
But when my form inputs are not being sent to the stored procedure, the procedure
always appears with all null values.

The code is below, any help would really be appreciated.

<?php
// Link Identifier
$conn = mssql_connect("dbase_server_name", "username", "pwd");
if(! $conn){
  print "A connect to the SQL Server was not made.";
}

// Select Database Name
$db = mssql_select_db("dbase_name", $conn);
if(! $db){
  print "The database was not found.";
}

// Create Query for Stored Procedure In Code
$stmt = mssql_init("jsaSearchJobList",$conn);
if(! $stmt){
	print "The stored procedure cannot be found.";
}

$SQL_Keyword = $Keyword;
$SQL_BusinessSector = $BusinessSector;
$SQL_ServiceType = $ServiceType;
$SQL_ThruCost = $ThruCost;
$SQL_FromCost = $FromCost;
$SQL_ThruDate = $ThruDate;
$SQL_FromDate = $FromDate;
$SQL_State = $State;
$SQL_County = $County;
$SQL_LongName = "city";
$SQL_ClientAndName = $ClientAndName;
$SQL_ProjectID = $ProjectID;



// Bind Peramaters To Send To Stored Proc.
mssql_bind($stmt, "@pSrch_ProjectID", $SQL_ProjectID, SQLVARCHAR, 20);
mssql_bind($stmt, "@pSrch_ClientAndName", $SQL_ClientAndName, SQLVARCHAR, FALSE, 500);
mssql_bind($stmt, "@pSrch_LongName", $SQL_LongName, SQLVARCHAR, FALSE, 300);
mssql_bind($stmt, "@pSrch_County", $SQL_County, SQLVARCHAR, FALSE, 30);
mssql_bind($stmt, "@pSrch_State", $SQL_State, SQLVARCHAR, FALSE, 30);
mssql_bind($stmt, "@pSrch_FromDate", $SQL_FromDate, SQLVARCHAR, FALSE, 30);
mssql_bind($stmt, "@pSrch_ThruDate", $SQL_ThruDate, SQLVARCHAR, FALSE, 30);
mssql_bind($stmt, "@pSrch_FromCost", $SQL_FromCost, SQLVARCHAR, FALSE, 30);
mssql_bind($stmt, "@pSrch_ThruCost", $SQL_ThruCost, SQLVARCHAR, FALSE, 30);
mssql_bind($stmt, "@pSrch_ServiceType", $SQL_ServiceType, SQLVARCHAR, FALSE, 50);
mssql_bind($stmt, "@pSrch_BusinessSector", $SQL_BusinessSector, SQLVARCHAR, FALSE, 50);
mssql_bind($stmt, "@pSrch_Keyword", $SQL_Keyword, SQLVARCHAR, FALSE, 200);



// Execute SQL Statement & Capture Results
$result = mssql_execute($stmt) or die("No results from that query."); 

//query
//$Query = mssql_query($db, $conn);

// Fetch Return Data And Place Into Array For Access
$rsJSA_Jobs = mssql_fetch_assoc($result); 
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Project Search</title>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">

<link href="../includes/jaxon/widgets/dtable/css/dtable.css" rel="stylesheet" type="text/css" />


<style type="text/css">
<!--
.style1 {color: #FFFFFF}
-->
</style>
<script type="text/JavaScript">
<!--
function MM_openBrWindow(theURL,winName,features) { //v2.0
  window.open(theURL,winName,features);
}
//-->
</script>
</head>


<body> 

<form action="index.php" method="GET" enctype="multipart/form-data" class="dtable" id="jsaSearchJobList">

  <table width="100%" border="0" cellpadding="0" cellspacing="0" class="filter">

    <thead>
      <tr class="filter">
        <th width="25">&nbsp;</th>
	 <th width="146"><div align="right" class="style1" title="Enter the full project number or a series of numbers. 
e.g. 01478.01 or 478.01">Project No.&nbsp;</div></th>

        <th colspan="2" class="filter"><div align="left">
          <input type="text" name="ProjectID" size="10" maxlength="10" />
&nbsp;</div></th>
           //////////////////////////////////SNIP////////////////////////////////////////////////////////////////////
		  <th width="146"><div align="right" class="style1">Client&nbsp;</div></th>


        <th colspan="5" class="filter"><div align="left">
          <input name="ClientAndName" type="text" id="ClientAndName" onclick="javascript:this.value=this.value.toLowerCase()" size="30" maxlength="70" />
          &nbsp; </div></th>
      </tr>
      //////////////////////////////////SNIP////////////////////////////////////////////////////////////////////
      <tr class="filter">
        <th width="25">&nbsp;</th>
	  <th width="146"><div align="right" class="style1">State&nbsp;</div></th>


        <th colspan="5" class="filter"><div align="left"><select name="State"> 
<option value="" selected="selected">Select a State</option> 
<option value="AL">Alabama</option> 
//////////////////////////////////SNIP////////////////////////////////////////////////////////////////////
<option value="WI">Wisconsin</option> 
<option value="WY">Wyoming</option>
</select>
              </div></th>            

		  </tr>


      <tr class="filter">
        <th width="25">&nbsp;</th>
	  <th width="146"><div align="right" class="style1" title="Enter a year or use < and > to indicate before and after. 
e.g. 2003 or <2003 (before 2003) or >2003 (after 2003)">Year&nbsp;</div></th>

        <th colspan="5" class="filter"><div align="left">
          <input type="text" name="FromDate" size="10" maxlength="22" /> 
          - 
          <input type="text" name="ThruDate" size="10" maxlength="22" />
      </div>          </tr>
      <tr class="filter">
        <th width="25">&nbsp;</th>
	  <th width="146"><div align="right" class="style1">Costs&nbsp;</div></th>

        <th colspan="5" class="filter"><div align="left">
          <input type="text" name="FromCost" size="10" maxlength="22" />
          - 
          <input type="text" name="ThruCost" size="10" maxlength="22" />
      &nbsp; </div>          </tr>
      <tr class="filter">
        <th width="25">&nbsp;</th>
	  <th width="146"><div align="right" class="style1">Main Service Type&nbsp;</div></th>

        <th colspan="5" class="filter"><div align="left"><select name="MainServiceType">
          <option value="" selected="selected">Select a Type</option>
          //////////////////////////////////SNIP////////////////////////////////////////////////////////////////////
          <option value="Water Supply Planning">Water Supply Planning</option>
          <option value="WQ/Wasterwater/TMDL">WQ/Wasterwater/TMDL</option> 
        </select>
      </div>              </tr>
      <tr class="filter">
        <th width="25">&nbsp;</th>
	  <th width="146"><div align="right" class="style1">Business Sector&nbsp;</div></th>

        <th colspan="5" class="filter"><div align="left"><select name="Sector">
          <option value="" selected="selected">Select a Sector</option>
//////////////////////////////////SNIP////////////////////////////////////////////////////////////////////
              <option value="transportation">Transportation</option>
              <option value="water resources">Water Resources</option> 

        </select>
      </div>              </tr>
      <tr class="filter">
        <th>&nbsp;</th>
        <th>&nbsp;</th>
        <th colspan="5" class="filter">&nbsp;</th>
      </tr>
      <tr class="filter">
        <th width="25">&nbsp;</th>
	  <th width="146"><div align="right" class="style1" title="This field searches titles and available project descriptions. 
Enter part of a word, a word, or a sequence of 2-3 words. 
e.g. restor or restoration or restoration plan">Keyword&nbsp;</div></th>

        <th colspan="5" class="filter"><div align="left">
          <input type="text" name="keyword" size="30" maxlength="50" />
          &nbsp; </div></th>
      </tr>
      <tr class="filter">
         <th colspan="7">&nbsp;</th>
      </tr>


      <tr class="filter">
        <th align="left" valign="top"></th>
        <th align="left" valign="top"></th>
        <th width="80" align="left" valign="top"><input class="filterButton" type="submit" name="jsaSearchJobList" value="Search"/> &nbsp;</th>
        <th width="145" align="left" valign="top"><a class="reset" href="index.php" target="_self">
        <input class="filterButton" type="reset" name="Reset" value="Reset"/></a></th>
        <th colspan="3" align="left" valign="top"><div align="center"><span class="style1">This is being worked on by Ron at the moment, many functions are not going to work.</span> </div></th>
      </tr>
      <tr class="filter">
         <th colspan="7">&nbsp;</th>
      </tr>
	  <tr>
	    <td>            
      </tbody>
</table>  

<table width="100%" cellpadding="0" cellspacing="0" class="filter">
  <tr class="filter">
    <td width="13%" align="center" valign="middle" bgcolor="#818baf" class="style1" scope="col">Project No. </td>
    <td width="87%" align="center" valign="middle" bgcolor="#818baf" class="style1" scope="col">Client - Title</td>
  </tr>

  <tr class="filter">
    <td bgcolor="#CCCCCC" class="filter" scope="col"><?php echo $rsJSA_Jobs->Fields['ProjectID']; ?></td>
    <td width="87%" bgcolor="#CCCCCC" class="filter" scope="col"><?php echo $rsJSA_Jobs->Fields('LongName'); ?></td>
  </tr>
  <tr class="filter">
    <td colspan="3" bgcolor="#818baf" class="filter" scope="col">&nbsp;</td>
  </tr>
</table>
</form>
</body>
</html>