Loop through data without using mysql_fetch_assoc()

[matbeard]

I'm developing an appliocation that pulls data from two databases: one is MySQL, the other is Sage Line 100 (via ODBC).

I'm familiar with extracting data and displaying the resulting recordset, using either:

while ($row_rsList = mysql_fetch_array($rsData)) //for MySQL data

or

while ($row_rsList = odbc_fetch_array($rsData)) //for ODBCL data

Because the Sage ODBC driver is very flakey, I've created a couple of duplicate tables in MySQL, into which I import the ODBC data from time to time.

The idea is that I can check to see if the ODBC link is available, and if not, use the backup data from MySQL. To do this, I simply check whether odbc_connect returns an error, and if so, use MySQL instead.

Basically, I end up with either:

$row_rsData = odbc_fetch_array($rsData);

or

$row_rsData = mysql_fetch_array($rsData);

I'd like to be able to loop through the data in $row_rsData, without using the regular method, because I don't want to be specific about where the data has originated.

Hope this is clear enough. Any help is much appreciated.

[thorpe]

maybe wrap the pair of them in your own function? eg:

function multi_fetch($data,$dbtype)
  if ($dbtype == 'mysql') {
    return mysql_fetch_array($data);
  } else {
    return odbc_fetch_array($data);
  }
}

of course doing it this way you'd need to determine which db is being used, then pass that into the second argument, but its just a thought...

[matbeard]

I probably didn't make myself clear... I've got all this working apart from the actual display of the recordset.

I'd normally use something like

do {
echo $row_rsList['column']
} while ($row_rsList = mysql_fetch_array($rsData)) 

but I can't do that, because I might need to use

do {
echo $row_rsList['column']
} while ($row_rsList = odbc_fetch_array($rsData)) 

I know I can resolve this, but it requires duplicating a lot of the code, which would be unnecessary if there was a way to loop through the recordset without using while ($row_rsList = mysql_fetch_array($rsData)) or while ($row_rsList = odbc_fetch_array($rsData))

[thorpe]

well, somewhere allong the line your going to need to loop through the result. id ont see any real alternative.

[Roger_Ramjet]

Code 2 include files - 1 for odbc and 1 for mysql. Then include the required file according to the db. 1-time test instead of for every row. 😃

[superwormy]

Something like this maybe...?

function fetch_array($res)
{
if (get_resouce_type($res) == "odbc result")
return(odbc_fetch_array($res);
else
return(mysql_fetch_array($res);
}

[matbeard]

Sorry... showing my ignorance here (that's what happens when you use Dreamweaver)... but using the following code:

$query_rsList = "SELECT blah, blah, blah";
$rsList = mysql_query($query_rsList, $database) or die(mysql_error());
$row_rsList = mysql_fetch_assoc($rsList);

What does $row_rsList contain at this stage? I thought it was an array containing the recordset, and that there would be a way to loop through it without using while ($row_rsList = mysql_fetch_array($rsData))

[Roger_Ramjet]

mysql_fetch_assoc returns 1 record from the recordset and move the cursor on to the next record. That is why we need the while - to loop through all the records 1 at a time.

I personally prefer mysql_fetch_array as it returns an array that can be accessed by both key and index number whereas fetch_assoc can only be accessed by key(column name).

[superwormy]

$rsList contains a MySQL result resource, this is a variable PHP uses to point to the result set from MySQL, and can only be manipulated with the mysql_* functions.

$row_rsList will contain a single array corresponding to one record from the record set. You can loop through this array using any standard array notation. But you'll only be looping thorugh one single record, not the entire record set.

There is no way to loop through the entire record set without using something similar to a while() loop and the mysql ( or odbc depending ) functions.

[matbeard]

Okay. Thanks. That's clarified it.

I'll just use two separate loops and an if.

Thanks to everyone for the help.

[n_wattam]

matbeard wrote:

I'm developing an appliocation that pulls data from two databases: one is MySQL, the other is Sage Line 100 (via ODBC)

Hi mat,

I too am deleloping an application which needs to pull data from a MYSQL and Sage Database via and ODBC. in PHP

Its early days yet, i've not used ODBC before, but just figured how to read and write to a access database though ODBC withing PHP today, so feel i am getting that bit closer.

if possible at all, could you help at all with information on connecting to a sage part.

Cheers

[matbeard]

Hi,

Sage Line 100 (and, AFAIK Line 50) automatically installs an ODBC driver when you install the software, and configures a DSN to the Sage data.

You can install the ODBC driver on any system without installing Sage -- it's on the install disk in the ODBC folder.

For starters, install and configure the ODBC driver on your Web server. Obviously you'll need to use a valid Sage username and password when connecting via PHP.

If you've managed to use the ODBC commands to connect to Access, then you shouldn't have any problems with Sage.

There are some oddities with the Sage ODBC though: firstly, the ODBC driver is read-only (though I believe there is a third-party driver which allows writing to the Sage database, but I wouldn't recommend it).

Secondly, I've never been able to get PHP/ODBC (or ASP/ODBC) to work with Sage unless I've disabled anonymous access. It seems that despite having a valid username/password configured for the ODBC connection, the user accessing the PHP pages also needs permission to access the Sage data directly. If he/she doesn't have access, the OBDC link returns a username/password error and the ODBC link dies (and won't work again at all until IIS is restarted).

I've created several applications (in both ASP and PHP) which pull data from Sage, and getting the permissions right has always been a nightmare.

Hope this is of some help.

[n_wattam]

matbeard wrote:

Hi,

Sage Line 100 (and, AFAIK Line 50) automatically installs an ODBC driver when you install the software, and configures a DSN to the Sage data.

You can install the ODBC driver on any system without installing Sage -- it's on the install disk in the ODBC folder.

For starters, install and configure the ODBC driver on your Web server. Obviously you'll need to use a valid Sage username and password when connecting via PHP.

If you've managed to use the ODBC commands to connect to Access, then you shouldn't have any problems with Sage.

There are some oddities with the Sage ODBC though: firstly, the ODBC driver is read-only (though I believe there is a third-party driver which allows writing to the Sage database, but I wouldn't recommend it).

Secondly, I've never been able to get PHP/ODBC (or ASP/ODBC) to work with Sage unless I've disabled anonymous access. It seems that despite having a valid username/password configured for the ODBC connection, the user accessing the PHP pages also needs permission to access the Sage data directly. If he/she doesn't have access, the OBDC link returns a username/password error and the ODBC link dies (and won't work again at all until IIS is restarted).

I've created several applications (in both ASP and PHP) which pull data from Sage, and getting the permissions right has always been a nightmare.

Hope this is of some help.

Hi thanks for the reply...

Yeah i have a connection already on the network, just waiting for the accounts manager to come in so i can look at what data there is (structure) etc, so i know what tables to read from the ODBC.

In re regards to the driver only being read only that makes sence. We have purchased there full development suit, when i thought we might not need after working out i could connect to an ODBC from php, but now think whats included is the driver to allow me to write back to sage.

do you have any examples on a reading data from the ODBC when linked to sage..

i am using the following when reading from an ODBC when connected to access, am i right in thinking i still enter the same values, as in the TABLE names and then FIELDS names etc???

<?php
$conn=odbc_connect('neil','',''); 

if (!$conn) {
	exit("Connection Failed: " . $conn); 
}

$sql="SELECT * FROM Table1";
$rs=odbc_exec($conn,$sql);
if (!$rs) {
	exit("Error in SQL");
}

echo "<table><tr>";
echo "<th>Companyname</th>";
echo "<th>Contactname</th></tr>";

while (odbc_fetch_row($rs)) {
	$compname=odbc_result($rs,"name");
	$conname=odbc_result($rs,"address");

echo "<tr><td>$compname</td>";
echo "<td>$conname</td></tr>";
}

odbc_close($conn);
echo "</table>";

?>

[matbeard]

n_wattam wrote:

am i right in thinking i still enter the same values, as in the TABLE names and then FIELDS names etc???

Yes... exactly right. The only thing that will be any different is your SQL query (and the DSN, of course). Sage's SQL implementation isn't great, but the basics work.

Your biggest problem is (well, my biggest problem was) deciphering the Sage table structure. However, if you've purchased the development suite, there may be some documentation in there.

I found one of the easiet ways to figure out what's in the numerous tables is to use Microsoft Query from Excel -- it makes it easier to browse through the tables and fields.

Good luck.