connect to separate database

makason · 2006-12-21 00:13:28

how do I connect and retrieve results from a separate database within textpattern?

The faq is says that any database connection is shared. Is there a way around this?
I can’t rename the database.

In a test connection to a database named “ellis” textpattern added the txp_ to the database name:

Error 1146 : Table ‘txp_ellis.customer’ doesn’t exist

Sencer · 2006-12-21 09:39:16

The way to retrieve data from another db, is to use mysql’s own db functions.

You can also open a new connection, make the query, then close the connection again.

makason · 2006-12-21 18:46:23

<code>
Isn’t this what I’m doing? This is the test I put in a page template. Since everything is going through txp the database name gets renamed and no results are returned.

<txp:php>
$hostName = “localhost”; $databaseName = “test”; $username = “name”; $password = “”;

$connection = mysql_pconnect($hostName, $username, $password); mysql_select_db($databaseName, $connection);

$query=“SELECT * from customer ORDER By lastName ASC”;
global $result;
$result= @mysql_query($query);

if (!($result = @ mysql_query ($query, $connection))) die(“Error “ . mysql_errno() . “ : “ . mysql_error());

</txp:php>

</code>
Error 1146 : Table ‘txp_test.customer’ doesn’t exist

Mary · 2006-12-21 20:31:22

I would try putting it within it’s own function:

<txp:php>
function customers()
{
	$host = 'localhost';
	$database = 'test';
	$username = 'name';
	$password = '';

	$connection = mysql_pconnect($host, $username, $password);

	if (!$connection)
	{
		return 'Error '.mysql_errno($connection).' : '.mysql_error($connection);
	}

	$selection = mysql_select_db($database, $connection);

	if (!$selection)
	{
		$msg = 'Error '.mysql_errno($connection).' : '.mysql_error($connection);

		mysql_close($connection);

		return $msg;
	}

	$rs = mysql_query("SELECT * FROM customer ORDER BY lastName ASC");

	if (!$rs)
	{ 
		$msg = 'Error '.mysql_errno($connection).' : '.mysql_error($connection);

		mysql_close($connection);

		return $msg;
	}

	$out = array();

	// display results
	while ($row = mysql_fetch_array($rs))
	{
		$out[] = print_r($row, true);
	}

	mysql_close($connection);

	return join('', $out);
}

echo customers();
</txp:php>

mercury · 2006-12-28 06:15:16

@Mary, thanks. yes, that works. but at the same time if we have after that function some textpattern_db_related tags, e.g.:

<txp:article_custom id="22" />

that leads to many errors like that:

tag_error <txp:article_custom id="22" /> ->  Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'user'@'localhost' (using password: NO)  on line 11

tag_error <txp:article_custom id="22" /> ->  Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established  on line 11

tag_error <txp:article_custom id="22" /> ->  Textpattern Warning: Access denied for user 'user'@'localhost' (using password: NO)
select *, unix_timestamp(Posted) as uPosted from tp_textpattern as textpattern where 1 and Status >= 4 and Posted <= now() and ID = '22' order by  limit 0, 10  on line 84

mercury · 2006-12-28 06:36:18

there was a mod for that http://forum.textpattern.com/viewtopic.php?id=2718
unfortunately source code is 404

Mary · 2006-12-28 18:00:36

The code, as I posted it, works perfectly for me; you should check what you are using.

mercury · 2006-12-28 20:38:16

yeah. that’s true if we’re not using txp connection after that.
else we should switch back to txp table:

mysql_pconnect($txp_host, $txp_username, $txp_password);
mysql_select_db($txp_database);

and all will work perfectly and in my example too )

Mary · 2006-12-29 03:22:04

? No, I mean I can use Txp tags both before and after the code, no problems. i.e. this works, no errors:

<txp:article limit="1" />

<txp:php>
function customers()
{
	// all the previously shown php code
	// not repeated here, for brevity
}

echo customers();
</txp:php>

<txp:article offset="1" limit="1" />

cmcgregor · 2007-01-23 16:34:16

I am having this same issue as Mercury. I have tried putting the code into a function as Mary suggested but I am still not able to call a textpattern form after this code. Functions that were called before this code still function after it, but uncalled txp functions flag errors.

This is the error I get:

tag_error <txp:output_form form=“footer” /> -> Warning: mysql_query(): 3 is not a valid MySQL-Link resource on line 78
tag_error <txp:output_form form=“footer” /> -> Textpattern Warning:
select Form from ncpa_txp_form as txp_form where name=‘footer’ on line 84
tag_error <txp:output_form form=“footer” /> -> Textpattern Notice: Form not found: footer on line 1332

cgradio · 2008-08-14 23:26:36

This thread was pretty stale, but I’m trying to use it to do the same thing being asked. I’m getting the exact error message described.
I am new to txp, but not to php. My previous site design was manually coded php. I had a database of songs for which I have chord sheets available. I’m trying to get the database to display within txp. I’m not worried about the formatting at this point (font sizes, etc.). After several attempts to rewrite the previous code, and then a few attempts at writing from scratch, I’m at my wit’s end.

Thank you for any help you could provide. I am working on a bit of a deadline, so I’m beginning to wig out.

I’m calling a form “musiclist” from inside a txp page (http://www.gadlage.com/music/) using the code:
<notextile>
<txp:output_form form=“musiclist” />
</notextile>

Two posts coming… • form musiclist • errors returning

Cheers,
Chris

cgradio · 2008-08-14 23:28:29

The beginning of the table and the header is defined on the page prior to calling musiclist.
form musiclist:

<txp:php>
function musiclist()
{ $host = ‘localhost’; $database = ‘removed for security’; $username = ‘removed for security’; $password = ‘removed for security’; $tablename = ‘removed for security’; $count = 1;

$connection = mysql_pconnect($host, $username, $password);

if (!$connection) { return ‘Error ‘.mysql_errno($connection).’ : ‘.mysql_error($connection); }

$db_selection = mysql_select_db($database, $connection);

if (!$db_selection) { $msg = ‘Error ‘.mysql_errno($connection).’ : ‘.mysql_error($connection);

mysql_close($connection);

return $msg; }

$rs = mysql_query(“SELECT * FROM $tablename ORDER BY Title ASC”);

if (!$rs) { $msg = ‘Error ‘.mysql_errno($connection).’ : ‘.mysql_error($connection);

mysql_close($connection);

return $msg; }

$out = array();

// display results while ($row = mysql_fetch_array($rs)) { //New Stuff ********************************** // Define Variables $Title = $row[Title]; $Key = $row[K]; $Genre = $row[Genre]; $Audience = $row[Audience]; $Theme = $row[Theme]; $Notes = $row[Notes]; $URL = $row[URL] . ‘.pdf’; // Display rows and add 1 to counter echo (“<td valign=middle align=left><a href=\”“ . $URL . “\” target=\”_blank\”>”); echo ($Title . “</a></td>\n”); echo (“<td valign=middle align=center>” . $Key . “</td>\n”); echo (“<td valign=middle align=center>” . $Genre . “</td>\n”); echo (“<td valign=middle align=center>” . $Audience . “</td>\n”); echo (“</tr>\n <tr>\n”); echo (“<td colspan=4 valign=middle align=left>” . $Theme . “</td>\n”); echo (“</tr>\n <tr>\n”); echo (“<td colspan=4 valign=middle align=left>” . $Notes . “</td>\n”); echo (“</tr>\n”); $count = $count + 1;

// End of New Stuff *************************** }

mysql_close($connection);

return join(‘’, $out);
}

echo musiclist();
</txp:php>

Textpattern CMS

Textpattern CMS support forum

#1 2006-12-21 00:13:28

connect to separate database

#2 2006-12-21 09:39:16

Re: connect to separate database

#3 2006-12-21 18:46:23

Re: connect to separate database

#4 2006-12-21 20:31:22

Re: connect to separate database

#5 2006-12-28 06:15:16

Re: connect to separate database

#6 2006-12-28 06:36:18

Re: connect to separate database

#7 2006-12-28 18:00:36

Re: connect to separate database

#8 2006-12-28 20:38:16

Re: connect to separate database

#9 2006-12-29 03:22:04

Re: connect to separate database

#10 2007-01-23 16:34:16

Re: connect to separate database

#11 2008-08-14 23:26:36

Re: connect to separate database

#12 2008-08-14 23:28:29

Re: connect to separate database

Board footer