Connect to another database

newnoise · 2011-12-21 15:55:01

Hi,

I have a PHP-Script that collects data from another database.

——————————————————————————————————————————-

$database_host = “host”;
$database_user = “user”;
$database_pw = “pw”;
$database_db = “db”;

$_link = mysql_connect($database_host, $database_user, $database_pw);

if (!$_link) { die(“Keine Verbindung zur Datenbank möglich.”);
}

mysql_select_db($database_db, $_link);

$_sql = “SELECT * FROM table LIMIT 5”;

$_resultat = mysql_query($_sql, $_link) or die (“MySQL-Error: “ . mysql_error());

while($info = mysql_fetch_array( $_resultat)) {
Print “Title: “.$info[‘title’] . “ “;
Print “City: “.$info[‘location_city’] . “ ”;
}

mysql_close($_link);

——————————————————————————————————————————-

The problem is that the other mysql-queries of txp doenst work anymore afterwards, because it tries to connect to this database …

how can I connect to this other database, without interfering with textpatterns db connection?
right now I kind of hacked it inserting another connect to the txp-db after the mysql_close($_link);. But there should be a more clean possibility, shouldn’t it?

thanks
noise

Last edited by newnoise (2011-12-21 16:40:40)

maruchan · 2011-12-22 03:25:21

What about using an external script to write the db data to XML, like a little API? Then Textpattern could parse it with smd_xml.

ruud · 2011-12-22 16:00:19

Try setting the fourth parameter in the mysql_connect call to TRUE (currently you haven’t set it, so it defaults to FALSE).

MattD · 2011-12-22 18:30:45

ruud wrote:

Try setting the fourth parameter in the mysql_connect call to TRUE (currently you haven’t set it, so it defaults to FALSE).

Since I was currious about what this exactly meant I looked it up. In case someone else is also curious, the fourth parameter is new_link:

If a second call is made to mysql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned. The new_link parameter modifies this behavior and makes mysql_connect() always open a new link, even if mysql_connect() was called before with the same parameters. In SQL safe mode, this parameter is ignored.

newnoise · 2011-12-23 16:37:46

Hi,

setting the 4th param to true doesnt help, because this creates a new link, but the next connect of textpattern uses this link then.

So I did it as an external-script. Which works fine, BUT smd_xml cant parse the xml, always giving memory-exhausted error:

Allowed memory size of xxx bytes exhausted (tried to allocate yyy bytes) in /path/to/textpattern/textpattern/lib/txplib_misc.php(653) : eval()’d code on line 141

the xml is in this form:

<latest_events>
<event>
<location_name>Location</location_name>
<title>Title</title>
<url>www.url.com</url>
</event>
<event>
<location_name>Location</location_name>
<title>Title</title>
<url>www.url.com</url>
</event>
</latest_events>

and I use smd_xml like this:

<txp:smd_xml data=“http://url.com/external_latest” record=“event” fields=“title,location_name,url” wraptag=“ul”>
<li>
{location_name} 
<a href=”{url}” >{title}</a>
</li>
</txp:smd_xml>

anyone can help?
thanks

Last edited by newnoise (2011-12-23 16:38:08)

ruud · 2011-12-23 20:40:34

The username/password/hostname set in the mysql_connect call, are they identical to the one you use for TXP? I did a few tests, by inserting bits of PHP in <txp:php> into a page template, above other TXP tags that use the database.

$_link =  mysql_connect('localhost', $txpcfg['user'], $txpcfg['pass'], FALSE);
mysql_select_db($txpcfg['user'], $_link);
==> Works!

$_link =  mysql_connect('localhost', $txpcfg['user'], $txpcfg['pass'], FALSE);
mysql_select_db($txpcfg['user'], $_link);
mysql_close($_link);
==> ERRORS

$_link =  mysql_connect('localhost', $txpcfg['user'], $txpcfg['pass'], FALSE);
mysql_select_db('some_other_database', $_link);
==> ERRORS

$_link =  mysql_connect('localhost', $txpcfg['user'], $txpcfg['pass'], TRUE);
mysql_select_db('some_other_database', $_link);
==> Works!

$_link =  mysql_connect('localhost', $txpcfg['user'], $txpcfg['pass'], TRUE);
mysql_select_db('some_other_database', $_link);
mysql_close($_link);
==> ERRORS

I don’t understand why the last one fails… but as long as you don’t use mysql_close (when script execution ends, the DB connection is closed automatically), it does seem to work. Here’s something funny:

$_link =  mysql_connect('localhost', $txpcfg['user'], $txpcfg['pass'], TRUE);
mysql_select_db('some_other_database', $_link);
mysql_close($_link);
$_link2 =  mysql_connect('localhost', $txpcfg['user'], $txpcfg['pass'], FALSE);
==> Works!

$_link =  mysql_connect('localhost', $txpcfg['user'], $txpcfg['pass'], TRUE);
mysql_select_db('some_other_database', $_link);
mysql_close($_link);
$_link2 =  mysql_connect('localhost', $txpcfg['user'], $txpcfg['pass'], FALSE);
mysql_select_db('some_other_database', $_link2);
==> Works!

$_link2 is not the same as $link… but is also not the same as the $GLOBALS[‘DB’]->link that TXP creates, yet somehow this works. Is this a PHP bug or am I overlooking something?

newnoise · 2012-01-05 11:20:00

This is very crazy. Anyway removing the close statement helped me a lot!
Thanks.

Textpattern CMS

Textpattern CMS support forum

#1 2011-12-21 15:55:01

Connect to another database

#2 2011-12-22 03:25:21

Re: Connect to another database

#3 2011-12-22 16:00:19

Re: Connect to another database

#4 2011-12-22 18:30:45

Re: Connect to another database

#5 2011-12-23 16:37:46

Re: Connect to another database

#6 2011-12-23 20:40:34

Re: Connect to another database

#7 2012-01-05 11:20:00

Re: Connect to another database

Board footer