Generalizing Database Access

bcrosby

I'm attempting to migrate to PHP from ASP and am having real problems duplicating the structures that I'm used to. (Yes, I realize that old structures may not work in this environment and I'm willing to change.)
My problem is that I'm trying to remove my database access code to a separate .inc file and run my DB access from a central location.
I'm getting a "Warning: mysql_fetch_array(): supplied resource is not a valid MySQL result resource in C:\Inetpub\sites\PHP\default.php on line 29" error and can't work out what the problem is.
The calling code is:
<?php
include("inc/db.inc");
include("inc/Nav.inc");

error_reporting(2); // 1 = no messages, 2 = show warnings

fnMain("PHP Test");

function fnMain($title) {
	PageHeader($title);
	DisplayMenu();
	switch ($_REQUEST["action"]) {
		case "Get DB Records":
			DisplayRecords();
			break;
		case "Do Nothing":
			echo "Page - <b>$title</b> - did nothing!";
			break;
		default:
			break;
	}
	PageFooter();
}

function DisplayRecords() {
	$rs = new DB;

// if (function_exists("DB_Read")) {
$rsTest = $rs->DB_Read("Select Item from Excess");
while($row = mysql_fetch_array($rsTest) or die("Error reading data file.")); {
echo $row("Item");
}
// } else {
// echo "Function not available.";
// }
}

function DisplayMenu() { ?>
	<p>Select an action from the list below:</p>
	<form method="post" action="<? echo $_SERVER['PHP_SELF'] ?>">
		<p><input type="submit" name="action" value="Get DB Records"></p>
		<p><input type="submit" name="action" value="Do Nothing"></p>
		<hr>
	</form>

<?php
} ?>

The db.inc file is:
<?php

Class DB {
var $rs;

function DB_Read($query) {
	$Conn = mssql_connect ("DELL7000", "php", "php") or die ("Unable to connect to database.");
	$db = "Axisold";
	mssql_select_db($db, $Conn) or die ("Unable to select database.");
	$rsX = mssql_query($query) or die("Error retrieving data from database.");
	$this -> rs = $rsX;
	return $this->rs;
}

}
?>

The nav.inc is:
<?php
function PageHeader($title) { ?>
<html>
<head>
<title><?php echo $title ?></title>
<style>
p {
font-family: sans-serif;
font-size: 11pt;
}
h1 {
font-family : sans-serif;
font-size : 14pt;
color : #03008B;
text-align: center;
}
</style>
</head>
<body bgcolor="black">
<table width="80%" align="center" cellpadding="20">
<tr bgcolor="#ffffff">
<td>
<h1><?php echo $title . ": " . $_SERVER['PHP_SELF'] ?></h1>
<?php }
function PageFooter() {?>
</td>
</tr>
</table>
</body>
</html>
<?php } ?>

This is all the code that is in the test environment.
Obviously the db connection string and query would need to change on other systems.
Any suggestions would be great.
Brett
PS: I've been programming in PHP for about 4 days now so please be gently on me.🙂

eoghain

Well the problem is that on line 29 you are calling a function that requires a valid database result resource and you aren't passing in that. This error usually means that you have some connection or query issue with your database.

I'm assuming that this is line 29:

while($row = mysql_fetch_array($rsTest) or die("Error reading data file.")); {

If so then the error means that $rsTest isn't being set as your database result resource. I'd check that your connection is working properly, and that your query works properly.

bcrosby

Thanks for your reply.
You picked line 29 correctly: that is where the code is failing. However, when I put echo $rsTest on the line preceding it prints
"Resource id #4" as the output. Possibly it is passing this reference as a string and not as a true resource id. If this is so, I guess my question is "can I pass resources as variables to and from functions"? If so, what is the technique?
The code that is listed above is the final variation of about 3,729 attempts (approximately) to work out how to do this.
Cheers
Brett

eoghain

PHP should pass the referance correctly. Maybe you are doing something to it in your class. I have built code to do things like this before, but I usually pass back the data and leave everything to do with the database inside the class.

I'm attaching a class I've used for MySQL before. It takes in a query and a referance to an array and fills the array with the queries data. It also returns the count of records.

I use it like so:

<?
   include classMysql.php;

   $db = new DBConnection("dbname", "username", "password", "host");

   $data = array();
   $sql = "SELECT * FROM test_table";

   $count = $db->doQuery($sql, $data);

// My class returns a 1 based array, not 0 as most do.
   for ($i = 1; $i <= $count; $i++) {
      $sep = "";
      for (reset($data[$i]); $key = key($data[$i]); next($data[$i])) {
         echo $sep . $data[$i][$key];
         $sep = ", ";
      }
      echo "\n";
   }

ednark

try looking into the PEAR DB class for an out of the box automation

pear.php.net

bcrosby

Thanks all,
Only took me 5 days to notice that I was using ASP array referencing syntax - arrX("item") instead of arrX["item"].
I fixed that and all is well in the universe!
I'll also look into the PEAR solution that was mentioned.
Thanks again.
Brett