MySQL access through PHP scripting (ODBC)

I'm trying to get ahold of my MySQL database via a small script in MUSHclient.

Here is my script file:
---------------------------------------------------------

---------------------------------------------------------

Apon loading this script I get this error message:
"Execution of line 6 column 1"
"Call to undefined function mysql_connect()"

---------------------------------------------------------

After some research (Yes, google is research) I found that this was a typical error for smart people that didn't have MySQL installed at all. However, I have it installed just fine. If I open up a browser and make a few quick changes to the script file (i.e. add <?php ?>'s and change $world->note.) the MySQL end works fine and all of my equipment shows up.

Anyways, if anyone has any input on this stump of mine, lemme' know.

-RashinLord

---------------------------------------------------------
!!Update!!

Looking deeper into this proble I found that MUSHclient isn't finding some DLL's. I'm trying to locate just WHERE the hell the paths to these files are located because they are quite obviously the wrong path.

-RashinLord

I want to shoot my computer in the head.

This one has been hard work. :)

I can't get it to work either, using mysql, however I think I know what is (should be) needed.

There is a DLL called "php_mysql.dll" - this is in the "ext" directory of the PHP distribution. I gather this needs to be loaded for it to recognise mysql extensions.

Inside the php.ini file are some lines that seem relevant:


;extension=php_mssql.dll
;extension=php_msql.dll
extension=php_mysql.dll
;extension=php_oci8.dll
;extension=php_openssl.dll


I uncommented the line for the DLL. Also:


; Directory in which the loadable extensions (modules) reside.
extension_dir = "C:\php.5.1.1\ext"


However, no matter where I put the php.ini file it doesn't seem to recognise it, and it never recognises the DLL.

For example, if you try this in MUSHclient:


/$world->Note (extension_loaded ("php_mysql"));


It echoes 0, which is false.

However, there is some hope. In the php.ini file I saw this:


; Windows Extensions
; Note that ODBC support is built in, so no dll is needed for it.


So, I tried to get to my database using ODBC, which worked, see next post.

This seemed to work for me, getting data from my database in MUSHclient. An intermediate step (which I had already done for other reasons) was to set up the ODBC DSN (datasource name) using the ODBC control panel.

This script here demonstrates:

• Opening the database (odbc_connect)
  
• Querying it (odbc_do)
  
• Find how many fields are in each row (odbc_num_fields)
  
• Get a row at a time (odbc_fetch_row)
  
• Get each column from the row (odbc_result)
  
• Find the column name (odbc_field_name)
  
• Close the database (odbc_close)
  

---

With odbc_result you can also specify a field name, so you can do something like this:


$amount = odbc_result ($queryresult, "bank_balance");


Enjoy!

Nice, I'm still just trying to get php working without errors apon loading MUSHclient.

As soon as I open the world with the php script it gives me about 20 popups about missing DLL's. Mostly the mysql DLL's, even though I didn't call any mysql.

Alas, I havn't given up yet.

It would be really nice if somehow MySQL could work, I don't know the first thing about ODBC.

-RashinLord

By the way, isn't php.ini irrelevant to activescript? I thought it's .ini file was php-script.ini or something.

Perhaps there is a commented mysql extension in that file aswell.

Give it a try, you seem to have everything working far better than I.

-RashinLord

You try it, this thing is driving me crazy. :)

Judging by the phpscript source it is looking for the php.ini file in the "application path". However I thought I had tried that.

Anyway, the solution I presented seems to work, so why not use that?

Because you didn't give me a solution for MySQL working. :P
I don't really think it's a problem with MUSHclient, I just wanted to point it out incase someone else has / or could find a way to get MySQL working in this certain application of php.

Worst comes to worst, I end up learning that other database.

Thanks Nick,
-RashinLord

What other database? ODBC is a way of accessing a mySQL database. My example uses a mySQL database.

ODBC is a "generic" database wrapper that accesses other databases via the ODBC control panel.

Oh, cool. See, I told you didn't have the first clue as to what it was. :P

Hehe. Ok, now I know this isn't the ODBC newbie forum but perhaps you can give me a quick hand.

I downloaded the ODBC .msi installer from the MySQL site. Ran it. Copied your bit of database connection script from the last post to my script.

"odbc_connect(): SQL error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified, SQL state IM002 in SQLConnect"

I'm stumped, I tried changing my connection info etc aswell as removing/reinstalling the .msi.

-RashinLord

OK, you have installed it, now you need to configure it. This is pretty simple, but under XP I found it a bit hard to find, so I have done some screenshots for you.

Locate the ODBC control panel. First select Performance and Maintenance:

Select Administrative Tools:

Choose ODBC control panel:

Click on the "System DSN" tab and then Add:

Scroll down until you see MySQL Driver, select it, and click Finish:

Fill in the boxes (I have pixellated some here). The "Data Source Name" is the thing you refer to in PHP. This is what connects the data source name (eg. mydsnname) to the server/user/password/database combination.

Once you have done all that you should be able to click "Test" and get a successful message back saying it connected. If not, click Diagnostics to see what the error message was (eg. no access).

Then click OK to close this dialog.

---

You should now see your DSN in the list of available system DSNs. Once that is there, close the control panel, and the script should work.

That's awesome Nick. Unfortunatly apon clicking 'test' I got this message.
"Request returned with SQL error."

-RashinLord

Ok, so it's offical. I'm a newb, and back to that age old computer tech-support question, "Is it plugged in?". - MySQL wasn't flicked on.

-RL

Alrighty Nick. Everything so far has worked wonderfuly, I can access all my item information via ODBC from my MySQL DB/Server.

Heres my latest problem. I have a small set of globals, some work, some don't.

Here is my code.
---------------------------------------
[Code]
$items = Array();
$ti = 0;
$reporting = 1;
$channel = "gdt";

function init_db(){
global $ti;
global $items;
$iparse = array();

$dsn = "mydsnname";
$user = "root";
$upasswd="********";

$query = "select * from items";
$conn = odbc_pconnect($dsn, $user,$upasswd );

if ($result = odbc_Exec($conn, $query)) { //execute query
$world->Note("Query returned : " . odbc_num_rows($result) . " rows");
while (odbc_fetch_row($result)) { // fetch each row
$items[$ti]['name'] = odbc_result($result, "name");

$ti++;
}
}
}

function set_channel ($name, $line, $wildcards){
global $channel;

$channel = $wildcards[1];
$world->Send($channel . " I am now reporting on this channel.");
test_channel();
}

function test_channel(){
global $channel;

$world->Note("this is a test: " . $channel);
}

function display_items ($name, $line, $wildcards){
global $reporting;
global $channel;
global $ti;
global $items;

test_channel();

$world->Note($channel . $reporting);

for($i=1;$i<$ti;$i++){
if(!$reporting || !$channel) {
$world->Note($items[$i]['name']);
}else{
$world->Note($channel . " " . $items[$i]['name']);
$world->Send($channel . " " . $items[$i]['name']);
}
}
}

function foobar (){
$world.Note("You foo, I bar");
}

$world->Note("!-[Loading Script]-!");
$world->Note("Init DB...");
init_db();
$world->Note("Complete");
$world->Note("!-[Script Loaded]-!");

function nick($name, $line, $wildcards){
    $world->Note($wildcards[1]);
}

My first reaction is to declare your global variables at global scope. That is, at the start of the script have:

global $reporting;
global $channel;
global $ti;
global $items;


As for the alias, it worked for me. Did it fire? Did you have "regular expression" checked?

Yeah, I got all that working. :)

When does mush send this error?

"Maximum execution time of 60 seconds exceeded"

Before I got it when there was an error and mush hadn't finished processing the script in 60 seconds. Now I'm not getting any errors, only the message 60 seconds after I load the script. My in-script notifications on what it's doing shows up all the way to then end. (i.e. "Script Loaded.").

Any Ideas?

Lol. It seems that the interpreter must be counting cumulative time. Google on the error message, there are quite a few hits.

Either increase the limit in php.ini (which only defers the problem) or find a way to make it reset the counter each time you enter a script.

Time to switch to Lua, I think. :)

ok. i installed php as CGI etc. Edited php_activescript.ini (in mush directory) with this:
---------
; Directory in which the loadable extensions (modules) reside.
extension_dir = "C:\php.5.1.1\ext"

;Windows Extensions
;Note that MySQL and ODBC support is now built in, so no dll is needed for it.
;
extension=php_mysql.dll
---------
And it seems to do "something". There occurs an error at session startup: "PHP Startup: Unable to load dynamic library 'C:\php.5.1.1\ext\php_mysql.dll' - procedure not found"

Maybe this is some clue for Nick ;) I have no other ideas...

"Procedure not found" can mean either the entry point you are looking for is not there, or there is a missing dependency (on another DLL). If you have a dependency-checker, you can see what other DLLs that one needs.

I checked php_mysql.dll with Dependency Walker. It requires apphelp.dll, so I'd downloaded it and put into windows/system directory. But when checking dll again, there are some errors like "Error: At least one module has an unresolved import due to a missing export function in an implicitly dependent module". I don't understan it... Mushclient is still going with previous errors ("procudure not found"). I don't know how to repair it. Any ideas?

The standard Windows DLL loader gives that error message ("The specified procedure could not be found."), I think, if there is any problem loading the DLL, except finding the DLL itself, in which case the message is "The specified module could not be found.".

Thus, until you get rid of any errors that the dependency checker reports, I don't think it will work.

You may want to go to the PHP site, this is really a PHP problem, perhaps there is a forum posting there that clears the issue up.