[Home] [Downloads] [Search] [Help/forum]


Register forum user name Search FAQ

Gammon Forum

[Folder]  Entire forum
-> [Folder]  MUSHclient
. -> [Folder]  General
. . -> [Subject]  MySQL access through PHP scripting (ODBC)

MySQL access through PHP scripting (ODBC)

It is now over 60 days since the last post. This thread is closed.     [Refresh] Refresh page


Pages: 1 2  

Posted by RashinLord   Canada  (29 posts)  [Biography] bio
Date Fri 09 Dec 2005 06:37 AM (UTC)

Amended on Fri 09 Dec 2005 03:15 PM (UTC) by RashinLord

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

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

    $total_items=0;
    $items = array();
    $i = 0;

    if(!mysql_connect("33333","3333333","3333333")) {
        $World->Note("Error with MySQL");
    }

    mysql_select_db("equipment");
 
    $result=mysql_query("SELECT * FROM `items`;");
    while( $row=mysql_fetch_array($result) )
    {
          $items[$i]->vnum = 	      44;
	    $items[$i]->flag = 		$row['flag'];
	    $items[$i]->name = 		$row['name'];
	    $items[$i]->loc =  		$row['loc'];
	    $items[$i]->pac = 		$row['pac'];
	    $items[$i]->mac = 		$row['mac'];
	    $items[$i]->saves = 	$row['saves'];
	    $items[$i]->str = 		$row['str'];
	    $items[$i]->int = 		$row['int'];
	    $items[$i]->wis = 		$row['wis'];
	    $items[$i]->dex = 		$row['dex'];
	    $items[$i]->con = 		$row['con'];
	    $items[$i]->hp = 		$row['hp'];
	    $items[$i]->mana = 		$row['mana'];
	    $items[$i]->move = 		$row['move'];
	    $items[$i]->damroll = 	$row['damroll'];
	    $items[$i]->hitroll = 	$row['hitroll'];
	    $items[$i]->weapdam = 	$row['weapdam'];
	    $items[$i]->level   =       $row['level'];

  	    $World->Note("Got a piece");

	    $i++;
    }

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

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
[Go to top] top

Posted by RashinLord   Canada  (29 posts)  [Biography] bio
Date Reply #1 on Fri 09 Dec 2005 04:33 PM (UTC)
Message
I want to shoot my computer in the head.
[Go to top] top

Posted by Nick Gammon   Australia  (23,016 posts)  [Biography] bio   Forum Administrator
Date Reply #2 on Fri 09 Dec 2005 11:36 PM (UTC)
Message
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.

- Nick Gammon

www.gammon.com.au, www.mushclient.com
[Go to top] top

Posted by Nick Gammon   Australia  (23,016 posts)  [Biography] bio   Forum Administrator
Date Reply #3 on Fri 09 Dec 2005 11:42 PM (UTC)
Message
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)




$conn = odbc_connect ("DSN=mydsn;", "username", "password");

$queryresult = odbc_do ($conn, "SELECT * FROM faq");

$fields = odbc_num_fields ($queryresult);

while (odbc_fetch_row ($queryresult)) 
  {
  $world->Note ("-- New record --");
  for ($i = 1; $i < $fields; $i++)
    {
    $data = odbc_result ($queryresult, $i);
    $name = odbc_field_name ($queryresult, $i);
    $world->Note ($name . " = " . $data);
    }
  }  // end of while

odbc_close ($conn);



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


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


Enjoy!

- Nick Gammon

www.gammon.com.au, www.mushclient.com
[Go to top] top

Posted by RashinLord   Canada  (29 posts)  [Biography] bio
Date Reply #4 on Sat 10 Dec 2005 12:23 AM (UTC)
Message
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
[Go to top] top

Posted by RashinLord   Canada  (29 posts)  [Biography] bio
Date Reply #5 on Sat 10 Dec 2005 12:25 AM (UTC)
Message
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
[Go to top] top

Posted by Nick Gammon   Australia  (23,016 posts)  [Biography] bio   Forum Administrator
Date Reply #6 on Sat 10 Dec 2005 12:53 AM (UTC)
Message
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?

- Nick Gammon

www.gammon.com.au, www.mushclient.com
[Go to top] top

Posted by RashinLord   Canada  (29 posts)  [Biography] bio
Date Reply #7 on Sat 10 Dec 2005 01:01 AM (UTC)
Message
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
[Go to top] top

Posted by Nick Gammon   Australia  (23,016 posts)  [Biography] bio   Forum Administrator
Date Reply #8 on Sat 10 Dec 2005 01:20 AM (UTC)
Message
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.

- Nick Gammon

www.gammon.com.au, www.mushclient.com
[Go to top] top

Posted by RashinLord   Canada  (29 posts)  [Biography] bio
Date Reply #9 on Sat 10 Dec 2005 01:30 AM (UTC)
Message
Oh, cool. See, I told you didn't have the first clue as to what it was. :P
[Go to top] top

Posted by RashinLord   Canada  (29 posts)  [Biography] bio
Date Reply #10 on Sat 10 Dec 2005 12:24 PM (UTC)
Message
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
[Go to top] top

Posted by Nick Gammon   Australia  (23,016 posts)  [Biography] bio   Forum Administrator
Date Reply #11 on Sat 10 Dec 2005 07:41 PM (UTC)
Message

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:

Main control panel

Select Administrative Tools:

Administrative Tools

Choose ODBC control panel:

ODBC control panel

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

Add ODBC item

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

MySQL data source

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.

Datasource name fields

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.

DSN in list


- Nick Gammon

www.gammon.com.au, www.mushclient.com
[Go to top] top

Posted by RashinLord   Canada  (29 posts)  [Biography] bio
Date Reply #12 on Sun 11 Dec 2005 01:48 AM (UTC)
Message
That's awesome Nick. Unfortunatly apon clicking 'test' I got this message.
"Request returned with SQL error."

-RashinLord
[Go to top] top

Posted by RashinLord   Canada  (29 posts)  [Biography] bio
Date Reply #13 on Sun 11 Dec 2005 06:42 AM (UTC)
Message
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
[Go to top] top

Posted by RashinLord   Canada  (29 posts)  [Biography] bio
Date Reply #14 on Sun 11 Dec 2005 10:55 AM (UTC)

Amended on Mon 12 Dec 2005 07:05 PM (UTC) by RashinLord

Message
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]-!");


The global variables in the function "display_items" seem to be registering as local variables and not pointing to the global ones previously defined.

Also, I can't get any wildcards to work.

i.e.


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

The output of that is null, as if I typed nothing.
yet my aliases is set up for "^nick (.*?)$".

Thanks!

RashinLord
[Go to top] top

The dates and times for posts above are shown in Universal Co-ordinated Time (UTC).

To show them in your local time you can join the forum, and then set the 'time correction' field in your profile to the number of hours difference between your location and UTC time.


62,807 views.

This is page 1, subject is 2 pages long: 1 2  [Next page]

It is now over 60 days since the last post. This thread is closed.     [Refresh] Refresh page

Go to topic:           Search the forum


[Go to top] top

Quick links: MUSHclient. MUSHclient help. Forum shortcuts. Posting templates. Lua modules. Lua documentation.

Information and images on this site are licensed under the Creative Commons Attribution 3.0 Australia License unless stated otherwise.

[Home]


Written by Nick Gammon - 5K   profile for Nick Gammon on Stack Exchange, a network of free, community-driven Q&A sites   Marriage equality

Comments to: Gammon Software support
[RH click to get RSS URL] Forum RSS feed ( https://gammon.com.au/rss/forum.xml )

[Best viewed with any browser - 2K]    [Hosted at HostDash]