?
Solved

PHP version of this ASP code

Posted on 2011-05-09
5
Medium Priority
?
284 Views
Last Modified: 2012-05-11
Over the years, I've come to use a pretty standard version of this ASP code.  It connects to a database (be it MSSQL or Access) and loops through records and displays them then closes the DB connection.

I'm moving to a PHP environment and wondered what the PHP equivalent of this code snippet might look like.

Let's assume the database is "localhost" as it's MySQL.
<% 
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Application("features") (this uses a global.asa with a path to the MSSQL db)
sql = "SELECT * FROM whatever_table order by id desc"
Set RS = Conn.Execute(sql)
 
%>
			  <%
			  'iCounter = 1
			Do Until RS.EOF
                reviewID = RS("reviewID")
                title = RS("title")
                bluray = RS("bluray")
				
			%>
			
				
	  

 

													
 
 

<%=title%><br>
<%=reviewid%><br>
<%=bluray%><br>

 
 
 
		<%
RS.MoveNext
prevDate = curDate
Loop

RS.Close
Set RS = nothing%>

Open in new window

0
Comment
Question by:freezilla
5 Comments
 
LVL 54

Expert Comment

by:Scott Fell, EE MVE
ID: 35724754
Try this

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM Persons");

while($row = mysql_fetch_array($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'];
  echo "<br />";
  }

mysql_close($con);
?>

from http://www.w3schools.com/PHP/php_mysql_select.asp
0
 
LVL 14

Expert Comment

by:Ali Kayahan
ID: 35724774
Hi it should be like

<?php
mysql_connect("localhost","dbuser","dbpass");
mysql_select_db("mydbname");

$resource = mysql_query("select * from mytable where col = 'value' order by col2 asc");
while($result = mysql_fetch_assoc($resource)){
   echo $result['col1'] ;
}
?>
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35727575
Have a look at this teaching example of how to do some of the basics in PHP and MySQL.  Hopefully the code, comments and man page references will get you headed in the right direction.  MySQL is not a black box -- it can and will fail for reasons that are outside of your control, so you need to develop good habits for data visualization and error handling.  You might also want to invest a little money and a little time with this book.  The concepts of ASP and PHP are the same, but the syntax is somewhat different and the SitePoint book will help you spot the differences very easily.
http://www.sitepoint.com/books/phpmysql4/

I'll try to post some specifics in a minute or two... ~Ray
<?php // RAY_mysql_example.php
error_reporting(E_ALL);


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES




// ESCAPING A DATA FIELD FOR USE IN MYSQL QUERIES
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATING AND SENDING A SELECT QUERY AND TESTING THE RESULTS
// MAN PAGE:http://php.net/manual/en/function.mysql-query.php
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
$num = mysql_num_rows($res);
if (!$num)
{
    echo "<br/>QUERY FOUND NO DATA: ";
    echo "<br/>$sql <br/>";
}
else
{
    echo "<br/>QUERY FOUND $num ROWS OF DATA ";
    echo "<br/>$sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
echo "<pre>\n"; // MAKE IT EASY TO READ
while ($row = mysql_fetch_assoc($res))
{
    // MAN PAGE: http://php.net/manual/en/function.var-dump.php
    var_dump($row);
}




// A WAY OF DETERMINING HOW MANY ROWS WE HAVE IN A TABLE
// MAN PAGE: http://php.net/mysql_fetch_array
$sql = "SELECT COUNT(*) FROM my_table";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = mysql_fetch_array($res, MYSQL_NUM);
$num = $row[0];
$fmt = number_format($num);
echo "<br/>THERE ARE $fmt ROWS IN THE TABLE";




// MAKING AN INSERT QUERY AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (username) VALUES ('$safe_username')";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php
$id  = mysql_insert_id($db_connection);
echo "<br/>YOU JUST INSERTED A RECORD WITH AUTO_INCREMENT ID = $id";

Open in new window

0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 35727628
See if this helps and please post back with any specific questions.   Best regards, ~Ray
THIS PART CONNECTS TO A DATA BASE AND SELECTS AN APPLICATION
THEN RUNS A SELECT QUERY

<% 
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Application("features") (this uses a global.asa with a path to the MSSQL db)
sql = "SELECT * FROM whatever_table order by id desc"
Set RS = Conn.Execute(sql) 
%>


THIS PART LOOPS THROUGH THE RECORD SET AND ASSIGNS LOCAL VARIABLES
<%
Do Until RS.EOF
  reviewID = RS("reviewID")
  title    = RS("title")
  bluray   = RS("bluray")
%>
			

THIS PART WRITES THE LOCAL VARIABLES ON THREE DIFFERENT LINES
<%=title%><br>
<%=reviewid%><br>
<%=bluray%><br>


THIS PART GOES TO THE NEXT ROW OF THE RECORD SET
<%
RS.MoveNext


THIS APPEARS TO BE UNUSED?  NOT NEEDED IN PHP
prevDate = curDate
Loop

RS.Close
Set RS = nothing%>


THE PHP VERSION WOULD LOOK SOMETHING LIKE THIS:

<?php // RAY_mysql_example.php
error_reporting(E_ALL);


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES



// CREATING AND SENDING A SELECT QUERY AND TESTING THE RESULTS
// MAN PAGE:http://php.net/manual/en/function.mysql-query.php
$sql = "SELECT * FROM whatever_table order by id desc";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
while ($row = mysql_fetch_assoc($res))
{
    // ASSIGN LOCAL VARIABLES
    $reviewID = $row("reviewID");
    $title    = $row("title");
    $bluray   = $row("bluray");

    // WRITE THE LOCAL VARIABLES TO THE BROWSER
    echo "$title<br/>$reviewID<br/>$bluray<br/>" . PHP_EOL;
}

Open in new window

0
 

Author Closing Comment

by:freezilla
ID: 35770123
This worked for me.  Thank you!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month14 days, 2 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question