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

INSTALL: Installing CURL for PHP (win32)

Assumptions:

PHP folder is C:\PHP
extension folder is C:\PHP\ext
php.ini found in C:\WINDOWS
system32 is C:\WINDOWS\SYSTEM32
web folder is C:\Apache2\htdocs

1. Create a phpinfo.php file in C:\Apache2\htdocs
2. Write <?php echo phpinfo(); ?> as the text of the file phpinfo.php
3. Verify it works: http://localhost/phpinfo.php (should display readonly php.ini in a table). *This confirms that http server works and php is installed
4. At the DOS/Command Prompt type echo %PATH% to view the current path
5. Put C:\PHP and C:\PHP\ext in your path: Go to My Computer->Advanced->Environment Variables->System Variables, and add C:\PHP;C:\PHP\ext; to the end of your path
6. Download the php .zip archive (with all the extensions in it) that corresponds to your version of php and extract libeay32.dll and ssleay32.dll
7. Put libeay32.dll and ssleay32.dll in your C:\WINDOWS\SYSTEM32 folder
8. Verify php_curl.dll is found in your C:\PHP\ext folder
9. Modify C:\WINDOWS\php.ini
- remove semi-colon from line ;php_curl.dll
- in php.ini, verify that extensions_dir = "C:\PHP\ext"
10. Restart HTTP service if you can't tell it worked.
- Start->Control Panel->Administrative Tools->Services
- right click Apache or HTTP or IIS and select restart
11. Verify by checking http://localhost/phpinfo.php
- Curl should have its own spot

Troubleshooting:
If you get the error...
PHP Warning: Unable to load dynamic library 'C:\PHP\ext\php_curl.dll' - The specified module could not be found. in Unknown on line 0
... at the bottom of your http://localhost/phpinfo.php or at the bottom of any php script, then verify that you followed all the steps.

Also, verify that the php.ini you made changes to is the same one that http://localhost/phpinfo.php displays in the fifth (or so) row down under
"Configuration File (php.ini) Path"

Also, verify that you clicked [Apply] or [OK] after step 5

Keep trying stuff until it works.