Converting ASP Classic Code to PHP

[mike3075]

I have only been working with PHP for about a week, so please be patient. I need to convert the following ASP Classic code to PHP. I have the SQL partially working.

<label>Select a bulletin:</label>
<select>
<option value="">Select One</option>
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$conn = new mysqli($servername, $username, $password);
if (!$conn) {
die('Could not connect: ' . mysql_error());
}
$sql = "SELECT bulletinid, bulletindate, bulletinlink, bulletintext FROM tblbulletins";
mysql_select_db(MyDB);
$retrieved = mysql_query( $sql, $conn );
if(! $retrieved ) {
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retrieved, MYSQL_ASSOC)) {
$bullentinid = $row['bulletinid'];
$bullentindate = $row['bulletindate'];
$bullentinlink = $row['bulletinlink'];
echo $link;
}
mysql_close($conn);
?>
</select>

 

The records that are retrieved are:

| 01 | 05/01/19 - 05/07/19 | link 1 | <embed src="bul/05-01.pdf#"> |
| 02 | 05/08/19 - 05/14/19 | link 2 | <embed src="bul/05-08.pdf#"> |
| 03 | 05/15/19 - 05/21/19 | link 3 | <embed src="bul/05-15.pdf#"> |

The problem I'm having is converting line 10 in ASP Classic to PHP:

01. <% Function bulletins %>
02. <% sSQL = "SELECT bulletinid, bulletindate, bulletinlink, bulletintext FROM tblbulletins ORDER BY bulletinid" %>
03. <% Set oRecordSet = adoCon.Execute(sSQL) %>
04. <% If Not oRecordSet.EOF Then %>
05. <% arrayResult1 = oRecordSet.GetRows() %>
06. <% End If %>
07. <% Set oRecordSet = Nothing %>
08. <% iRowNumber1 = ubound(arrayResult1,2) %>
09. <% For iCounter1= 0 to iRowNumber1 %>
10. <% Response.Write ("<option value="pg01.php?mode=view&ID=<%=arrayResult1(0,iCounter1)%>"><% = arrayResult1(1,iCounter1) %></option>")  
11. <% Next %>
12. <% End Function %>
13. <% 'arrayResult1(0,iCounter1) = bulletinid %>
14. <% 'arrayResult1(1,iCounter1) = bulletindate %>
15. <% 'arrayResult1(2,iCounter1) = bulletinlink %>
16. <% 'arrayResult1(3,iCounter1) = bulletintext %>
17. <label>Select bulletin:</label>
18. <select>
19. <% Call bulletins %>
20. </select>

Once the link was clicked, it would open pg01.php to the specific saint that was selected.  Here is the ASP Classic code on pg01.asp that needs to be converted to PHP:

<% IF Request.querystring("mode") = "view" THEN %>
<% Session("bulletinid") = Request.querystring("ID") %>
<% bulletinID = Clng(Session("bulletinID")) %>
<% sSQL = "SELECT * FROM tblbulletins WHERE bulletinid = " & bulletinID %>
<% Set oRecordSet = adoCon.Execute(sSQL) %>
<% IF Not oRecordSet.EOF THEN %>
<% arrayResult = oRecordSet.GetRows() %>
<% END IF %>
<% END IF %>
<% Set oRecordSet = Nothing %>
<div class="main">
<h4><% =arrayResult(1,0) %></h4>
<p><%=arrResultSet(4,0)%></p>
</div>

 

 

 

[Barand]

I am curious about what is "partially working". As far as I can see, none of it should.

• You create a mysqli connection but after that you use the now extinct mysql_xxx functions for your query and processing.
• Your only output is "echo $link" but $link does not appear to be defined anywhere in the code

You will find it more advantageous in the long run to learn to use PDO instead of mysqli for your database access.

[Barand]

I think this is what the ASP code was attempting to do

FORM PAGE

<?php
define("HOST",'localhost');                                                     //
define("USERNAME",'****');                                                      //
define("PASSWORD",'****');                                                      //
define("DATABASE", 'test');                                                     //   This code would normally
                                                                                //   be in an included file
$db = new PDO("mysql:host=".HOST.";dbname=".DATABASE, USERNAME, PASSWORD);      //   to avoid repetition
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);               //   on every page
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);          //
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);                       //

    
function bulletinOptions($db)
{
    $opts = "<option value=''>- select bulletin -</option>\n";
    $result = $db->query("SELECT bulletinid
                               , bulletindate
                          FROM tblbulletins 
                          ORDER BY bulletinid
                          ");
    foreach ($result as $r) {
        $opts .= "<option value='{$r['bulletinid']}'>{$r['bulletindate']}</option>\n";
    }
    return $opts;
}

?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Example</title>
</head>
<body>
<form method="GET" action="pg01.php">
    <input type="hidden" name="mode" value="view">
    <label>Bulletin: </label>
    <select name="ID">
        <?=bulletinOptions($db)?>
    </select>
    <input type="submit" name="btnSubmit" value="Submit">
</form>
</body>
</html>

PG01.PHP

<?php
include 'db_inc.php';   // contains connection code


$bulletin_date = "Invalid bulletin id";
$bulletin_text = '';

if (isset($_GET['ID'])) {
    $stmt = $db->prepare("SELECT DATE_FORMAT(bulletindate, '%m-%d-%Y') as date
                               , bulletintext
                          FROM tblbulletins
                          WHERE bulletinid = ?     
                         ");
    $stmt->execute( [ $_GET['ID'] ] );
    if ($row = $stmt->fetch() ) {
        $bulletin_date = $row['date'];
        $bulletin_text = $row['bulletintext'];
    }
}

?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Example</title>
</head>
<body>
    <h4><?=$bulletin_date?></h4>
    <p><?=$bulletin_text?>
</body>
</html>

 

Edited July 1, 2019 by Barand

[mike3075]

Thank you very much for the help.