Reprogram an ASP page to PHP

JodyFischer
JodyFischer used Ask the Experts™
on
I am trying to learn php. I have been programming in asp for a long time and thought it was time to learn a new language. I am having trouble understanding the connection and output from the database.

Can you look at this basic asp code and tell me how to make it work in php. This is connecting to a SQL 2008 database.

Set connection = Server.CreateObject("ADODB.Connection")
connection.open "sa", "456", 123"

Set Recordsx = CreateObject("ADODB.Recordset")
sqlString = "SELECT * FROM projects WHERE id=" & 12330
Recordsx.Open sqlString, Connection, 2, 3

<b>Project Name:</b><br>
<%= recordsx("project_name") %>
<p>
<b>Project Description:</b><br>
<%= recordsx("project_desc") %>
<p>

<% If recordsx("available") = 1 then %>
      <a href="<%= recordsx("url") %>" target="<%= recordsx("id") %>">
      <%
      Select Case Recordsx("type_of_project")
      
      Case "video":
             %>Play Video</a><%
      Case Else:
            %>View Site</a><%
            
      End Select
       %>
      <p>
<% End If %>
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
What version of PHP, where is your database located, and is your PHP running on IIS on Windows?

Author

Commented:
PHP - 5.3.1
Database is local, I am using a DSN connection
Yes, IIS on Windows
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
Ok, you need to download and install the Microsoft SQL Driver for PHP http://www.microsoft.com/download/en/details.aspx?id=20098 and the SQL Native Client which is required for the PHP driver to work.  It is linked on that page but it looks like there are two versions.  I have SQLNCLI 10 for SQL Server 2005 but you may need SQLNCLI 11 or 12 for SQL Server 2008 and 2012.

Author

Commented:
I installed SQLSRV30.EXE to the ext directory of PHP. You mention a second one, but I don't see it. I also use an Oracle database for some applications. Will I need to to do something simialr for that?
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
The SQL Native Client which is required for the PHP driver to work.  Yes, Oracle requires it's own drivers too.

Here is a PHP MS SQL demo that works on my computer:

<?php 
// MS SQL server 2005 using PHP => 5.2.4 or PHP 5.3 and php_sqlsrv_5x_ts_vc6.dll driver
// DB configuration
$dbtable = "websitelist";  // the table we're using
/* Specify the server and connection string attributes. */
$serverName = "WINXP\SQLEXPRESS";    // Your database server
/* Get UID and PWD from application-specific files.  */
$uid = "username";      // Your db username
$pwd = "password";      // Your db password
$connectionInfo = array( "UID"=>$uid,
                         "PWD"=>$pwd,
                         "Database"=>"TestDB");
/* Connect using SQL Server Authentication. */
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
		{
     echo "Unable to connect.</br>";
     die( print_r( sqlsrv_errors(), true));
		 }
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
 "http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
<title>sqlsrv demo</title>
</head>
<body>
<table border="0" cellpadding="0" cellspacing="0" summary="">
<tr valign="top">
<td>
<h2 align="center" style="margin: 1px;">sqlsrv demo - PHP/MS-SQL</h2>
<div align="center">MS SQL server 2005 using PHP => 5.2.4 or PHP 5.3</div>
<table border="0" cellpadding="0" cellspacing="1" summary="local people" width="960px" bgcolor="#336699" style="font-family: Arial; font-size: 11pt;">
<tbody>
<?php
if ($conn != "") { 
// Formulate Query
$pgsize = 20;
/* Assign literal parameter values. */
$params = array( 5, 10);
sqlsrv_query( $conn,"SET ROWCOUNT $pgsize");
// --------- Get data from table ---------------
$dquery = "SELECT TOP ($pgsize) ent_num, DisplayName, Sortname, WebSite, Descript, Cat, Approved FROM $dbtable";
$dquery .= " ORDER BY ent_num";
// TEST PRINT!!
//echo $dquery."<br>";
/* Execute the query. */
if($result = sqlsrv_query( $conn, $dquery, $params, array( "Scrollable" => SQLSRV_CURSOR_KEYSET ))) {
  //echo "Statement executed.\n";
	} 
else {
      echo "Error in statement execution.\n";
      die( print_r( sqlsrv_errors(), true));
	}
$nrows = sqlsrv_num_rows($result);

while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_NUMERIC)) {
echo '<tr bgcolor="#ffffff">';
echo '<td>&nbsp;'.$row[0].'</td>';
echo '<td>&nbsp;'.$row[1].'</td>';
echo '<td>&nbsp;'.$row[3].'</td>';
echo '<td>&nbsp;'.$row[4].'</td>';
echo '<td>&nbsp;'.$row[5].'</td>';
echo '<td>&nbsp;'.$row[6].'</td>';
echo '</td></tr>';
echo "\r\n";
}
}
?>
</tbody> 
</table>

</td>
</tr>
</table>

</body>
</html>

Open in new window


You should be able to adapt this page to your database by adding or eliminating rows in the output table and entering the specifics for your database.

Author

Commented:
Is there a way to take the code that I put in and show me how this corresponds to PHP? It's confusing since I know what I need to do, I just can't figure out how to do it.

Author

Commented:
I am trying to do a DSN connection which should be simple

$conn = odbc_connect('contract_assessment', "sa", "!123");

A simple sql statement

$sqlsrv = "SELECT * FROM projects";

I can't figure out the correct execute statement. Everything I try gives me

Fatal error: Call to undefined function sqlsrv_connect() in C:\inetpub\wwwroot\test.php on line 14
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
Here is an even simpler page using ODBC.  Note that the SQL Native Client is still used.  Here is the doc page for it: http://us3.php.net/manual/en/function.odbc-connect.php

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
 "http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
<title>PHP ODBC</title>
</head>
<body>
<h1>PHP ODBC</h1>
<?php
// MS SQL server 2005 using PHP 5.2 or lower and php_odbc.dll driver
// DB configuration
$serverName = "DIBSW2K\SQLEXPRESS";     // Your database server
$dbuser = "dibtest";      // Your db username
$dbpass = "Test4dib";      // Your db password
$dbname = "TestDB";      // Your database name
$dbtable = "websitelist";  // the table we're using

$conn = odbc_connect("Driver={SQL Server Native Client 10.0};Server=$serverName;Database=$dbname;", $dbuser, $dbpass);
	
if($conn == 0)
	 {
	 die('Unable to connect to the ODBC datasource.');
	 }
 
$idresult = odbc_exec($conn,"SELECT DisplayName, ent_num FROM $dbtable WHERE ent_num = 7");
$idrows = odbc_num_rows($idresult);
$arr = odbc_fetch_array($idresult);
echo $serverName." - ";
echo $arr['DisplayName'];
	 
// Close the ODBC connection.
odbc_close($conn);
?>

</body>
</html>

Open in new window

Author

Commented:
Thank you. I am able to connect to the database and see results. Can you give me the steps I need to do the same with an Oracle Database?
Fixer of Problems
Most Valuable Expert 2014
Commented:
I can show you an old page I used with Oracle XE but I don't have it installed anymore.  http://www.php.net/manual/en/book.oci8.php

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
 "http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
<title>Oracle hr connect</title>
</head>
<body>
<h1>Oracle hr connect</h1>
<?php

// Connects to the XE service (i.e. database) on the "localhost" machine
$conn = oci_connect('hr', 'hr', 'localhost/XE');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

$stid = oci_parse($conn, 'SELECT * FROM employees');
oci_execute($stid);

echo "<table border='1'>\n";
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
    echo "<tr>\n";
    foreach ($row as $item) {
        echo "    <td>" . ($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;") . "</td>\n";
    }
    echo "</tr>\n";
}
echo "</table>\n";

?>
<pre>

CREATE table "EMPLOYEES" (
    "ID"         NUMBER(11) NOT NULL,
    "FNAME"      VARCHAR2(4000),
    "LNAME"      VARCHAR2(4000),
    "TITLE"      VARCHAR2(4000),
    constraint  "EMPLOYEES_PK" primary key ("ID")
)
/

CREATE sequence "EMPLOYEES_SEQ" 
/

CREATE trigger "BI_EMPLOYEES"  
  before insert on "EMPLOYEES"              
  for each row 
begin  
    select "EMPLOYEES_SEQ".nextval into :NEW.ID from dual;
end;
/   
</pre>
</body>
</html>

Open in new window

Author

Commented:
Thank you
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
You're welcome.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial