Escape from MySQHELL - Changing code from MySQL to MsSql

[roryfear]

I've got the code, It works great, but now we're moving on to MsSQL Server from MySQL and my lovely code won't work.

I've tried to figure it out but I'm no genius. Whilst I can muddle through with PHP & MySQL, when it comes to MsSQL I turn into a jibbering wreck.

All though is not lost, I'm trying to learn and with your help (hopefully!) I'll get this blasted thing to work!

Heres the code for the search page....

    <form action="basic_results.php" method="post">
    <table width="50%" border="0">
  <tr>
      <td> Choose Search Type:</td>
    <td><select name="search_type_1" tabindex="1" title="Search Type 1">
          <option value="Tenant_Surname">Tenant Surname</option>
          <option value="Road">Road</option>
          <option value="Area">Area</option>
          <option value="Post_Code">Post Code</option>
          <option value="Telephone">Telephone</option>
          <option value="Status_ID">Status</option>
        </select></td>
  </tr>
  <tr>
      <td>Enter Search Term: </td>
    <td><input name="search_term_1" type="text" tabindex="2" title="Search Term 1" /></td>
  </tr>  <tr>
      <td> Choose Search Type:</td>
    <td><select name="search_type_2" tabindex="3" title="Search Type 2">
          <option value="Tenant_Surname">Tenant Surname</option>
          <option value="Road">Road</option>
          <option value="Area">Area</option>
          <option value="Post_Code">Post Code</option>
          <option value="Telephone">Telephone</option>
          <option value="Status_ID">Status</option>
        </select></td>
  </tr>
  <tr>
      <td>Enter Search Term: </td>
    <td><input name="search_term_2" type="text" tabindex="4" title="Search Term 2" /></td>
  </tr>
</table>
    <input name="submit" type="submit" value="Search" tabindex="5" title="Search"  />
     &nbsp;|&nbsp; 
    <input type="reset" value="Reset" name="Reset" tabindex="6" title="Reset" />
 </form>

And heres where I want it to spit out the results...

<?

trim($search_term_1);
if (!$search_type_1 || !$search_term_1)
{
echo "<br />You have not specified your 1st search term.<br />";
}

$search_type_1 = addslashes($search_type_1);
$search_term_1 = addslashes($search_term_1);

trim($search_term_2);
if (!$search_type_2 || !$search_term_2)
{
echo "<br />You have not specified a 2nd search term.<br />";
}

$search_type_2 = addslashes($search_type_2);
$search_term_2 = addslashes($search_term_2);

include("../admin/dbinfo.inc.php");
@ $db = sql_connect("$dsn", "$username", "$password");

if (!$db)
{
echo "Error : could not connect to database.";
}

sql_select_db("$database");
$sql_query = "select * from Property where ".$search_type_1." LIKE '%".$search_term_1."%' AND ".$search_type_2." LIKE '%".$search_term_2."%'"; 
$sql_result = sqlquery($query);
$sql_num_results = sql_num_rows($result);

echo "<p><br /><b>Number of records found: ".$sql_num_results."</b></p>";?>
  <table border="0" cellspacing="2" cellpadding="2" width="100%">
    <tr> 
      <td width="24%">Address</td>
      <td width="6%">Post Code</td>
      <td width="10%">Tenant Name</td>
      <td width="3%">IE</td>
      <td width="13%">Inspection Date</td>
      <td width="3%">WE</td>
      <td width="13%">Works Date</td>
      <td width="4%">Status</td>
      <td width="24%"></td>
    </tr>
    <tr> 
      <td colspan="9">&nbsp;</td>
    </tr>
    <?
for ($i=0; $i <$sql_num_results; $i++)
{
$row = sql_fetch_array($result);?>
    <tr> 
      <td><? echo $row["House_No"]; ?>&nbsp;<? echo $row["Road"]; ?>&nbsp;<? echo $row["Address_Line_2"]; ?>&nbsp;<? echo $row["Address_Line_3"]; ?></td>
      <td><? echo $row["Post_Code"]; ?></td>
      <td><? echo $row["Tenant_Title"]; ?>&nbsp;<? echo $row["Tenant_Initials"]; ?>&nbsp;<? echo $row["Tenant_Surname"]; ?></td>
      <td><? echo $row["Inspecting_Engineer"]; ?></td>
      <td><? echo $row["Inspection_Date"]; ?></td>
      <td><? echo $row["Works_Engineer"]; ?></td>
      <td><? echo $row["Works_Date"]; ?></td>
      <td><? echo $row["Status_ID"]; ?></td>
      <td><a href="../admin/update.php?Property_ID=<? echo $row["Property_ID"]; ?>" title="View Property" class="links">View 
        Property</a></td>
      <?
}
?>

Any help muchly appreciated,

Yours an increasingly bald Rory!

[MarkR]

Please post in what way it doesn't work. This will include
- Expected behaviour
- Observed behaviour
- Any error messages displayed or written to the PHP error log.

You do have error_reporting(E_ALL) enabled, right?

addslashes() is not a correct way of escaping strings in mysql or mssql, so that could be where you're going wrong.

Mark

[Sxooter]

And if you're escaping from MySQL, why not try PostgreSQL or Oracle. Just kidding about the Oracle bit... But Firebird might be a nice choice as well. Both are generally better DBs than MSSQL server. Although MSSQL is getting better, it's way overpriced for its load handling abilities.

[roryfear]

In reply to MarkR,

• Expected behaviour

I expected it to extract data from the Property table in the database based on what was selected on the search page. For example if i select the following in the search page...

search_type_1 = Tenant Surname
search_term_1 = Fear
search_type_2 = Road
search_term_2 = (Left Blank)

Then I would expect the results page to return something like...

You have not specified a 2nd search term.

Number of records found: 2

Address Post Code Tenant Name IE Inspection Date WE Works Date Status

31 Long Road Longford Huddersfax HD1 1UP Mr I Fear 0 2005-12-13 0 0000-00-00 1 View Property
50 Cow Head Longford Huddersfax HD1 1UP Mrs K Fear 0 0000-00-00 0 0000-00-00 0 View Property

If I put...

search_type_1 = Tenant Surname
search_term_1 = Fear
search_type_2 = Road
search_term_2 = Long Road

Then ....

Number of records found: 1

Address Post Code Tenant Name IE Inspection Date WE Works Date Status

31 Long Road Longford Huddersfax HD1 1UP Mr I Fear 0 2005-12-13 0 0000-00-00 1 View Property

These are the results that I was getting whilst running on Mysql, but on MS Sql no matter what I select on the search page I'm getting...

• Observed behaviour

You have not specified your 1st search term.

You have not specified a 2nd search term.

• Any error messages displayed or written to the PHP error log.

I've not had any errors reported on screen, and i've got error_reporting(E_ALL)
enabled.

Hope you can understand this :o

Thanks

[roryfear]

In reply to Sxooter,

Ha! Tell that to the guy I work with.

I generally don't like MS products full stop, but for some reason he's always used MSSql and always will. I was quite happy to stick to MySql, but there was no way that was going to happen. I'm just a minion, my opinion counts for NULL