LINUX: SUSE + APACHE2 + PHP + MYSQL + ODBC

SUSE + APACHE2 + MYSQL + PHP + ODBC = SAMPO

SAMPO is a type of LAMP install. The acronmym LAMP comes from Linux + Apache + Mysql + PHP.

I wrote this a while ago while consulting for a company with SAMPO needs. The goal was to host a php website on a linux webserver while the data was hosted on a windows mysql server. Only step 7 is specific to that where you end up pointing to the windows server across the network. Optional points in step 4 are specific to the client I was doing this for.
----------------------------
Meant to be included are a number of files:
testfile.html [inline at end]
phptest.php [inline at end]
mysqltest.php [inline at end]
myodbctest.php [inline at end]
odbc.ini [inline at end]
odbcinst.ini [inline at end]
MyODBC-3.51.11-2.i586.rpm [click to download]

Note:
A. Your linux machine's assumed IP Address is 192.168.0.2
B. commands to be typed at the command prompt start with 'command:' (some may need to be done as root)

1. Install Suse Linux 9.3
-In the screen labelled 'Installation Settings', click on 'Software'
-Make sure you select Standard System w/ KDE
-In the top-left of the screen, under Filter, select 'Search'
-Search and make sure all of the following packages have been selected
-apache2
-apache2-mod_php4
-apache2-prefork
-php4
-php4-bz2
-php4-curl
-php4-ftp
-php4-gd
-php4-mcrypt
-php4-mysql
-php4-session
-php4-unixODBC
-php4-zlib
-mysql
-mysql-client
-mysql-shared
-unixODBC
-openssl
-bison
-libxml2
-readline
-flex

2. Enable apache2 and mysql services
-After booting your system, go to
-Linux 'Start' Menu > System > Yast > Network Services > HTTP Server
-configure the http server
-Linux 'Start' Menu > System > Yast > System > System Services
-enable mysql and apache2 (now they will start when the machine reboots)

3. Test apache2
-Copy testfile.html to /srv/www/htdocs
-test it by viewing http://192.168.0.2/testfile.html

4. Test php
-edit php.ini
-set register_globals to On [optional]
-set display_errors to Off [optional]
-restart web server to apply php changes
-command: rcapache2 restart
-Copy phptest.php to /srv/www/htdocs
-test it by viewing http://192.168.0.2/phptest.php
-ensure phptest.php display information about mcrypt, odbc, and mysql

5. Setup mysql locally (not required but good to test mysql connectivity locally before testing it over the network)
-set mysql root password (below, change 'mysqlrootpassword' to your root password preserving quotes)
-command: /usr/bin/mysqladmin -u root password 'mysqlrootpassword'
-go into mysql and execute the following commands
-command: mysql -u root -p

create database testdb;
use testdb;
create table testtable (item_id char(10), item_desc char(100));
insert into testtable values ('001','This is test data 1');
insert into testtable values ('002','This is test data 2');
select * from testtable;
exit;

-Copy mysqltest.php to /srv/www/htdocs
-modify mysqltest.php to contain a reference to the mysql user and password that you setup (can be root)
-test it by viewing http://192.168.0.2/mysqltest.php

6. Setup unixODBC (best to test with database created in step 5)
-install MyODBC-3.51.11-2.i586.rpm
-as root, command: rpm -iv MyODBC-3.51.11-2.i586.rpm
-Copy odbc.ini and odbcinst.ini to /etc/unixODBC
-modify odbc.ini to contain a reference to the mysql user and password that you setup (can be root)
-test with isql (obviously modify mysqluser and mysqluserpassword to point to the user you setup)
-command: isql testdb mysqluser mysqluserpassword
-Copy myodbctest.php to /srv/www/htdocs
-modify myodbctest.php to contain a reference to the mysql user and password that you setup (can be root)
-test it by viewing http://192.168.0.2/myodbctest.php
-if it doesn't work go to the fix on 6a.

6a. unixODBC fix
-as root (su) execute the following commands at the shell:
-command: cd /usr/lib/php/extensions
-command: sed -i -e 's/libc.so.6/xxodbc.so/' unixODBC.so
-command: cd /usr/lib
-command: ln -s libodbc.so xxodbc.so
-command: rcapache2 restart
6a source: http://susewiki.org/index.php?title=PHP4_ODBC

7. Point to final distination
Now that you have a server with Suse + Apache + PHP + Mysql + unixODBC + MyODBC setup,
-setup odbc.ini and odbcinst.ini to point to a windows mysql server over the LAN
-you may have to open a hole in the windows mysql server firewall at port 3306 to allow mysql connections through

======================================
ATTACHED FILES
======================================
testfile.html

If you can see this in <b>bold</b> your
http server is working.

phptest.php

<? echo phpinfo(); ?>

mysqltest.php

<?php
echo "just checking";
$link = mysql_connect('localhost', 'root', 'r00tpwd') or
die('not connect');

echo "\ngood connect";

mysql_select_db('testdb') or ('not select db');

$query = 'SELECT * from testtable';
$result = mysql_query($query) or die('Query failed');

while($line = mysql_fetch_array($result, MYSQL_ASSOC))
{
print_r($line);
}

mysql_free_result($result);

mysql_close($link);
?>

myodbctest.php

<?php echo "just checking";
$conn = odbc_connect('mysqltest', 'root', 'r00tpwd')
or die('error connecting');

echo 'connected successfully';

$sql = "select * from testtable";
$rs = odbc_exec($conn, $sql);
while(odbc_fetch_row($rs))
{
$f1 = odbc_result($rs, 1);
$f2 = odbc_result($rs, 2);

echo "
QUERY RESULTS:" .$f1." ".$f2;
}

?>

odbc.ini

[mysqltest]
Description = MySQL ODBC Database
Driver = MyODBC
Server = localhost
Database = testdb
#Port =
#Socket =
#Opinion =
#Stmt =

odbcinst.ini

[MyODBC]
Description = MySQL ODBC 3.51 Driver DSN
Driver = /usr/lib/libmyodbc3.so
Trace = Off
TraceFile = stderr

[MySQL ODBC 3.51 Driver]
DRIVER = /usr/lib/libmyodbc3.so
SETUP = /usr/lib/libmyodbc3S.so
UsageCount = 1