Elsewhere...
Another excellent piece by Andy Rutledge, an admonition/checklist for being a professional designer/developer.
Both the BBC and Fox news web sites have recently been redesigned, with the BBC losing its looks and Fox getting neat and serious.
jQuery diagramming with jsPlumb — cool.
Another wonderful blog post by Walter Russell Mead, this time hoping that the blogosphere find a not-so-distant mirror in 18th-century London.
Google argues that its mobile YouTube site is better than its YouTube iPhone app.
Jakob Nielsen tests reading usability on the iPad and Kindle and reports that they’re almost as good as reading on paper. People didn’t like reading on PCs — it reminded them of work.
Falling out of love with the iPad. None of this surprises me.
Wow, icons made entirely in CSS3 (currently Safari, Chrome only).
-
Version
2.3.4 -
Download
Register and/or log in to download -
Type
Plugins -
More info
-
Elsewhere
EE Forums thread
Devot-ee page
Programattically/automatically adding weblog entries -
Buy now via PayPal
The External Entries plugin enables you to update, insert into, select and delete from tables either within your ExpressionEngine database or any other MySQL database on the internet to which you have administrative access.
The plugin makes it much easier to view, add, edit or delete various types of data in your EE site — and also much easier to integrate your EE site with other systems.
Compatibility
The plugin works on both EE v1.x and v2.x.
Warning
If you are copying and pasting directly from these docs, make sure you replace the fancy quotes (“ and ”) with simple ones (").
Installation
EE v1.x
Place the pi.external_entries.php file in the /system/plugins directory.
EE v2.x
Create in the /system/expressionengine/third_party directory a directory /external_entries, then place the pi.external_entries.php file in there.
Functions
There are four functions, each of which lets you operate not only on ExpressionEngine’s database but on any database to which you have administrator access:
external_entries:select
The Select function displays tables from a database.
external_entries:update
The Update function lets you easily update rows in any database table. For example, another system, such as PayPal, can send a form to the page containing the Update function, which can update both EE (such as changing the status of a payment from pending to paid) and another system in turn.
external_entries:insert
The Insert function lets you easily insert a row into any database table.
external_entries:delete
The Delete function lets you easily delete rows from any database table.
Parameters for external_entries:select
Required parameters
table
Required. This is the table to be displayed.
Optional parameters
allow_php
Lets the plugin’s values contain PHP. Values are “y” or “n”; default is “n” (for security). Set to “y” for characters such as greater than/less than signs to not be converted to entities.
connection
Set to “persistent” if using an external database and EE’s database settings are for non-persistent connections.
database
Required if using an external database.
hostname
Required if using an external database.
limit
Works like in the weblog:entries tag; the default is 100.
orderby
Works like in the weblog:entries tag.
parse
Setting parse=“inward” allows another plugin to be placed within Select Entries. (This is a universal parameter that works in all plugins.)
password
Required if using an external database.
search:[column_name]
Searches can be based on any column name, ie, search:my_column=“my text”. Multiple column searches are treated as a series of AND conditions.
sort
Works like in the weblog:entries tag. Requires orderby.
username
Required if using an external database.
Variables for external_entries:select
{select:count}
Works like {count} in the weblog:entries tag.
{select:total_results}
Works like {total_results} in the weblog:entries tag.
{select:[column_name]}
Displays that column’s value for the current row.
Examples for external_entries:select
For the table exp_actions, which is part of our ExpressionEngine database, we want to show all entries, sorted by the action_id column in ascending order:
{exp:external_entries:select table="exp_actions" limit="999" orderby="action_id" sort="asc" }
{if "{select:count}"=="1"}
<table>
<tr>
<th>Action ID</th>
<th>Class</th>
<th>Method</th>
</tr>
{/if}
<tr>
<td>{select:action_id}</td>
<td>{select:class}</td>
<td>{select:method}</td>
</tr>
{if "{select:count}"=="{select:total_results}"}
</table>
{/if}
{/exp:external_entries:select}
For table exhaust_outlets, which is outside our EE database, we want to show 15 entries, sorted by the reactor column in ascending order, where the circumference column equals “medium” and the shape column equals “straight”:
{exp:external_entries:select
hostname="mysql.deathstar.com"
username="deathstar_vader"
password="luk35dad"
database="deathstar_plans"
table="exhaust_outlets"
search:circumference="medium"
search:shape="straight"
orderby="reactor"
sort="asc"
limit="15"
}
<p>{select:title} is connected to the {select:reactor} reactor and is exposed at {select:mouth_location}!</p>
{/exp:external_entries:select}
Parameters for external_entries:update
Required parameters
debug
The default value for the debug parameter is “yes”. This is a safety measure to avoid inadvertently changing data wrongly. When debug is on, the plugin does not do the update. Instead, the SQL query and any error messages are displayed within a div of class “update-entries-message”. To activate the plugin, set debug to “no”.
table
The required table parameter tells the plugin which database table to update.
Optional parameters
allow_php
Lets the plugin’s values contain PHP. Values are “y” or “n”; default is “n” (for security). Set to “y” for characters such as greater than/less than signs to not be converted to entities.
connection
Set to “persistent” if using an external database and EE’s database settings are for non-persistent connections.
database
The database parameter is required only if updating a table on an external database. In this scenario the hostname, password and username parameters are also required.
hostname
The hostname parameter is required only if updating a table on an external database. In this scenario the database, password and username parameters are also required.
limit
If the limit parameter is not set, the default value is “1”. This is a safety measure to minimize the damage done by any inadvertent data changes.
parse
The parse parameter is common for all plugins. Setting its value to “inward” allows the plugin to contain another plugin.
password
The password parameter is required only if updating a table on an external database. In this scenario the database, hostname and username parameters are also required.
search:[column_name]
The search:[column_name] parameter limits the plugin to update only those rows in the table where the value of the specified column is equal to the value entered. Any number of search:[column_name] parameters may be used. They are connected by an “and” boolean operator; there is currently no option for “or”. These parameters form the part of the SQL query following “WHERE”.
username
The username parameter is required only if updating a table on an external database. In this scenario the database, hostname and password parameters are also required.
Variables for external_entries:update
update:[column_name]
The only variables used by the plugin are the names of the columns to be updated, preceded by “update:”.
Examples for external_entries:update
To update a table on the current ExpressionEngine database:
{exp:external_entries:update table="exp_weblog_titles" search:entry_id="{entry_id}" debug="n"}
{update:status}Purchased{/update:status}
{/exp:external_entries:update}
(To update weblog entries fields requires knowing the actual column name of the field being updated. All weblog fields are in the format “field_id_[xx]”, such as field_id_40. One easy way to find this value is to look at the query string of the URL when editing a field. At the end of the URL’s query string is the field_id — the value here is the number we seek.)
To update a table on an external database:
{exp:external_entries:update
hostname="www.cia.gov"
database="agencies"
username="felix_leiter"
password="alimentary"
table="field_agents"
search:agents_id="7"
debug="n"
}
{update:weapon}Walther PPK 7.65mm{/update:weapon}
{/exp:external_entries:update}
Parameters for external_entries:insert
Required parameters
debug
The default value for the debug parameter is “yes”. This is a safety measure to avoid inadvertently adding data wrongly. When debug is on, the plugin does not do the insert. Instead, the SQL query and any error messages are displayed within a div of class “insert-entries-message”. To activate the plugin, set debug to “no”.
table
The required table parameter tells the plugin into which database table to insert.
Optional parameters
allow_php
Lets the plugin’s values contain PHP. Values are “y” or “n”; default is “n” (for security). Set to “y” for characters such as greater than/less than signs to not be converted to entities.
connection
Set to “persistent” if using an external database and EE’s database settings are for non-persistent connections.
database
The database parameter is required only if inserting into a table on an external database. In this scenario the hostname, password and username parameters are also required.
hostname
The hostname parameter is required only if inserting into a table on an external database. In this scenario the database, password and username parameters are also required.
parse
The parse parameter is common for all plugins. Setting its value to “inward” allows the plugin to contain another plugin.
password
The password parameter is required only if inserting into a table on an external database. In this scenario the hostname, database and username parameters are also required.
username
The username parameter is required only if inserting into a table on an external database. In this scenario the hostname, database and password parameters are also required.
Variables for external_entries:insert
insert:[column_name]
The only variables used by the plugin are the names of the columns with values to insert, preceded by “insert:”.
Examples for external_entries:insert
To insert a row into two tables in the current ExpressionEngine database:
{exp:external_entries:insert table="exp_weblog_titles" debug="n"}
{insert:title}My New Entry{/insert:title}
{insert:url_title}my-new-entry{/insert:url_title}
{/exp:external_entries:insert}
{exp:external_entries:insert table="exp_weblog_data" debug="n"}
{insert:field_id_1}My New Content{/insert:field_id_1}
{insert:field_ft_1}none{/insert:field_ft_1}
{/exp:external_entries:insert}
Note that adding weblog entries requires knowing the actual column name of the field being added. All weblog fields are in the format “field_id_[xx]”, such as field_id_40. One easy way to find this value is to look at the query string of the URL when editing a field. At the end of the URL’s query string is the field_id — the value here is the number we seek.
To insert a row into a table on an external database:
{exp:external_entries:insert
hostname="www.cia.gov"
database="agencies"
username="felix_leiter"
password="alimentary"
table="field_agents"
debug="n"
}
{insert:name}James Bond{/insert:name}
{insert:family_motto}The world is not enough.{/insert:family_motto}
{insert:weapon}Beretta 418{/insert:weapon}
{/exp:external_entries:insert}
Parameters for external_entries:delete
Required parameters
debug
The default value for the debug parameter is “yes”. This is a safety measure to avoid inadvertently deleting data mistakenly. When debug is on, the plugin does not do the delete. Instead, the SQL query and any error messages are displayed within a div of class “delete-entries-message”. To activate the plugin, set debug to “no”.
table
The required table parameter tells the plugin from which database table to delete.
Optional parameters
connection
Set to “persistent” if using an external database and EE’s database settings are for non-persistent connections.
database
The database parameter is required only if deleting from a table on an external database. In this scenario the hostname, password and username parameters are also required.
hostname
The hostname parameter is required only if deleting from a table on an external database. In this scenario the database, password and username parameters are also required.
limit
If the limit parameter is not set, the default value is “1”. This is a safety measure to minimize the damage done by any inadvertent data deletions.
parse
The parse parameter is common for all plugins. Setting its value to “inward” allows the plugin to contain another plugin. Using this requires adding a closing tag for the plugin, which otherwise isn’t necessary.
password
The password parameter is required only if deleting from a table on an external database. In this scenario the database, hostname and username parameters are also required.
search:[column_name]
The search:[column_name] parameter limits the deletion to only those rows in the table where the value of the specified column is equal to the value entered. Any number of search:[column_name] parameters may be used. They are connected by an “and” boolean operator; there is currently no option for “or”. These parameters form the part of the SQL query following “WHERE”.
username
The username parameter is required only if deleting a table on an external database. In this scenario the database, hostname and password parameters are also required.
Variables for external_entries:delete
There are no variables for this function.
Examples for external_entries:delete
To delete weblog content on the current ExpressionEngine database (there is no need for a closing tag):
{exp:external_entries:delete table="exp_weblog_titles" search:entry_id="{entry_id}" debug="n"}
{exp:external_entries:delete table="exp_weblog_data" search:entry_id="{entry_id}" debug="n"}
To delete weblog entries fields based on fields requires knowing the actual column name of the field being updated. All weblog fields are in the format “field_id_[xx]”, such as field_id_40. One easy way to find this value is to look at the query string of the URL when editing a field. At the end of the URL’s query string is the field_id — the value here is the number we seek.
To delete from a table on an external database:
{exp:external_entries:delete
hostname="www.cia.gov"
database="enemies"
username="felix_leiter"
password="alimentary"
table="enemies_leaders"
search:leaders_name="Ernst Stavro Blofeld"
debug="n"
}
Embedding Functions
If you are calling an External Entries function within another one, or if you are placing it within an embedded template that is called while still within an External Entries tag, and if the two calls to External Entries are to different databases, and if the outer External Entries function is to an external database and the inner one is to the EE database, then you need to add all the database parameters for the EE database (hostname, database, username, password) to the inner External Entries function, otherwise it continues to stay connected to the external database selected in the outer External Entries function.
Changelog
v2.3.3 (2010 July 20)
- added missing opening parenthesis
v2.3.3 (2010 July 13)
- added connection parameter to enable persistent database connections
v2.3.2 (2010 Jun 30)
- fixed bug where plugin could not be used more than once in a template tree in EE2
v2.3.1 (2010 Jun 23)
- fixed typographic bug that was breaking the plugin in EE2
v2.3 (2010 Jun 15)
- restructured plugin extensively to share code among the four functions and enable more robust debugging
v2.2.3 (2010 Apr 12)
- added allow_php parameter to override variable cleaning and allow PHP
v2.2.2 (2010 Apr 9)
- Added missed bit of SQL injection prevention (as spotted by Ruthie of unruthless.com)
v2.2.1 (2010 Apr 7)
- Added security measures: SQL Injection Prevention for all parameters and sanitization for variables in the Insert and Update functions
v2.1.1 (2010 Mar 3)
- Limited bugfix for repeat use of plugin in EEv2 to only kick in when using EEv2
v2.1 (2010 Mar 3)
- Added delete functionality
- Fixed bug where plugin could not be used more than once in a template on EEv2
v2.0 (2010 Feb 9)
Combined the functionality with that of the Update Entries and Insert Entries plugins and renamed the previous version as Select Entries
v1.3 (2010 Feb 7)
- Now works with either EE v1.x or v2.x
v1.2 (2009 Dec 16)
- Fixed where entries not being brought in from an external database
Roadmap
- add count function (requested by lukemcr)
Sun 17 Jan ’10
1:11pm
Adam Khan
Adrienne, the license is per EE installation, so if you’re using MSM or another multiple-domain method, you need only buy one license. I’ve updated the license agreement to include this. Thanks for forcing the clarification.
Mon 15 Feb ’10
8:02pm
dumbguy
Just curious - the trial version is not encoded. So how does the licensing work? Just on Trust that people who downloaded will come back and pay you?
Mon 15 Feb ’10
8:16pm
Adam Khan
Yes, Trust with a capital T. The setup may change at some point but for now payment is on the honor system. EE itself works like that once you’ve bought your first copy.
Mon 15 Feb ’10
8:19pm
dumbguy
great… :)
okay, i downloaded the plugin, but how do I install it? I am using EE2, but it doesnt show up in CP, I have uploaded it in thirdparty folder…
Mon 15 Feb ’10
9:33pm
Adam Khan
Plugins in EE2 need to be in a folder inside /third_party, so it needs to be /third_party/external_entries/pi.external_entries.php
Wed 17 Feb ’10
12:38am
Ben
Will this work with a PHP 4.3 and MySQL 4.0 environment?
Wed 17 Feb ’10
5:35am
Adam Khan
Ben, I don’t know, my guess would be yes. I’d appreciate your giving it a try in that environment and posting the result here.
Sun 21 Feb ’10
10:35am
Brendon Carr
Has anyone given this a try as a means to update just a part of a single entry (i.e., to make a leaner Stand-Alone Edit Form)?
Sun 21 Feb ’10
11:38am
Adam Khan
Brendon, you’ve anticipated the next release, External SAEF…
Wed 24 Feb ’10
5:05am
Lewis Jones
Hi Adam, Very much looking forward to the EE2.0 version of External SAEF. Related to Brendon’s question, it seems to me the Update Entries function is designed to update just one column of the table. Shouldn’t this work for an edit form already? Anyway, about to give it a try!
Wed 24 Feb ’10
9:04am
Adam Khan
Lewis,
> it seems to me the Update Entries function is designed to update just one column of the table.
Update Entries can update one, some or all the columns in a table.
> Shouldn’t this work for an edit form already?
Not sure what you mean here. By edit form do you mean the built-in EE saef tag?
Wed 24 Feb ’10
9:36am
2ways
Hmmm… yes, not sure what I meant either. I was thinking about the built-in SAEF tag, but realise now it won’t work the way I thought it would.
Could you use it in a two-stage process, where you used a regular form to post information to another page (almost like a preview page, but it would never need to be visible), then take the output of the form values from there to use in the update tags to update the database? Would require php, but would still be more flexible than the built-in SAEF for editing.
Anyway, just musing. In all likelihood, I’ll be waiting for your super-duper EE2.0 External SAEF module. Any time frame on that? Thanks.
Wed 24 Feb ’10
9:44am
Adam Khan
Yes, you can definitely do the 2-stage process with the form going to a template that then does the updating using Update Entries or External Entries’s Update function. It would just need PHP to grab the $_POST values, not a big deal. But then you’re using a $15 plugin to do the job of a $0 module!
External SAEF for EE2.0: shouldn’t be too madly difficult, I’ll try to get on that today since you ask so kindly.
Wed 24 Feb ’10
12:12pm
2ways
Hey, you’re the one setting the pricing! I’ll pay just about anything for a stand alone edit form that works.
The site I’m building is all about trying to keep users on the front end and away from the control panel.
The two-stage solution sounds pretty reasonable, actually. It also means radio buttons and checkboxes would behave more consistently than they do with the SAEF.
The next issue to tackle is a SAEF file upload plugin that works and formatting buttons for SAEF forms. I know they are supposed to work, but I can’t seem to get them to.
Sorry for the off-topic rant. Thanks for the comments.
Wed 24 Feb ’10
1:46pm
Adam Khan
Just wondering—what is the drawback of the first-party SAEF if what you’re wanting to manage is actual weblog content of the current site? Are there aspects of it that don’t work? I was aware of the file upload difficulties but didn’t know of any others. Isn’t the file upload issue solved with the SAEF Enctype (http://expressionengine.com/forums/viewthread/122306) extension?
Wed 24 Feb ’10
2:39pm
2ways
Thanks, Adam. To be honest, I gave up a while back and can’t remember the exact errors that were troubling me, though I might have another go now.
I know other people seem to think the issues were solved, but I just couldn’t get the file uploads to work or formatting buttons to appear at all.
On the edit side of things, the SAEF has trouble with just about everything other than text and textarea fields. I managed to get radio buttons working with an if-then check to see what to display and I did an ugly fix of the select boxes by appending the currently selected value at the top of the list. Checkboxes appear to be a lost cause for editing because they only appear in the data stream when they are ticked, which means if you untick it, it won’t tell anyone it’s been unticked.
The file upload doesn’t seem to have any chance of working with an edit form in the current configuration. By working, I mean being able to view what you’ve uploaded (other than a file path), deleting the current file, and uploading another.
Part of my problem is that I am on EE2.0 where the SAEF stuff is not as well tried in the community, so less forum traffic. Also, I am hardcoding everything instead of using {custom_fields} because I want to have control over the styling. Mind you, I went back to have a go using {custom_fields} and found I still couldn’t get it working.
Thanks for pointing me to that thread. Still don’t know how it all works with EE2.0. Sorry for the long reply.
Wed 24 Feb ’10
3:01pm
Adam Khan
Aha, v2.0, say no more (*4)! I can’t really speak to it, I only have it installed in one place just to test plugins against on a simple template.
(I’m not using 2.0 mainly because I can’t stand the cpanel design. But that’s another rant…)
At any rate, I’ve used SAEFs for this sort of thing before and not had these problems. Good—well, useful—to know that there are issues with this on v2.
Wed 24 Feb ’10
5:17pm
Adam Khan
Lewis/2Ways,
I left it a bit late today to start to EE2 port for External SAEF. Will go at it tomorrow morning.
Wed 3 Mar ’10
10:27am
Adam Khan
Lewis/2Ways,
Sorry, I haven’t gotten to the EE2 port for External SAEF. Since it’s a module not a plugin I need to learn some new stuff for that, and haven’t had the time yet.
Wed 3 Mar ’10
10:45am
2ways
No problem. Don’t stress on my account. Will enjoy it when it comes!
Oh yeah. Call me Lewis. I never really like being someone else online, and I didn’t realise my posts would all be from 2ways.
Thanks for your work.
Wed 7 Apr ’10
2:01pm
Craig Allen
Hi Adam, looks like this will be very handy in a website I’m planning to start work on soon. I noticed a typo in the docs. You have written ...
“For EE v1.x, create in the /third_party directory a directory /select_entries, then place the pi.select_entries.php in there.”
That should read “For EE v2.x”.
I guess though that someone would have to be pretty thick to not work that out for themselves!
Wed 7 Apr ’10
6:31pm
Adam Khan
Craig, it looks right to me here, but it was wrong on some other plugins. Thanks for the note.
I’m finding External Entries to be very useful, planning to write an article explaining just how it was used throughout the new user management system at http://www.takecontrolbooks.com.
Please post here if you come across any problems when testing it.
Wed 7 Apr ’10
7:46pm
unruthless
Hi Adam, I’m evaluating External Entries for a project, and functionality-wise it looks like it’ll be a good fit. Before integrating it, though, I have two concerns related to security, and I was hoping to get your input on both:
1. Having our database credentials in plaintext in a template seems like bad security practice. A possible workaround for people using this plugin to access tables in the same database as ExpressionEngine’s tables: the hostname + database name + username + password to this database are already stored in /system/config.php file, in the variables $conf[‘db_hostname’], conf[‘db_name’], $conf[‘db_username’], and $conf[‘db_password’]. Any way for this plugin to just use those credentials, rather than having to specify database connection info at the template level?
2. For INSERT, UPDATE, and DELETE queries, there doesn’t appear to be any escaping of query data happening. Am I missing something, or shouldn’t there be some mysql_real_escape_string action happening?
Happy to take this discussion offline, and thanks for releasing this plugin.
Wed 7 Apr ’10
8:49pm
Adam Khan
Ruthie,
Thank you for the detailed questions. None of us is omniscient and detailed feedback is a major way these plugins get developed.
If you’re using tables in the same db as EE, there’s no need to specify the db info—they’re only required if you’re using a different db.
Re the mysql_real_escape_string question, the answer is, probably, yes. I hadn’t done it because the source of the data is coming from the owners/editors own site/s, but yes, it should be in there. I’ll add it and update the plugins.
Wed 7 Apr ’10
9:27pm
unruthless
Hi Adam, thanks for the quick response!
Regarding (1), I just took another look at the source code, and you’re totally right—for my project, we won’t need to include the db info in the template, so we’re good.
Regarding (2), fantastic to hear you’ll be adding escaping to those queries. Can you give me a rough timeline, so I know whether it makes sense for me (given a looming project deadline) to wait for your patch or attempt to patch it on my own?
Thanks again,
Ruthie
Wed 7 Apr ’10
9:36pm
Adam Khan
Ruthie,
Working on the security update as we speak. Just checking it now before adding it here.
Wed 7 Apr ’10
9:51pm
Adam Khan
Ruthie, External Entries v2.2 is available to download with security that I hope you’ll accept passes muster. Now going to update its spin-off plugins (Select, Update, Insert, Delete Entries).
If there’s anything else you think should be in there security-wise, please post here.
Thu 8 Apr ’10
2:33pm
unruthless
Adam, looks like you missed one needed escape on line 480 - other than that, looks great. Way to use EE’s built-in escaping rather than writing your own, quite elegant.
I’ll be setting this up on my project today, and will let you know how it goes. Thanks!
Fri 9 Apr ’10
6:17am
Adam Khan
Well spotted, Ruthie, and fixed for v2.2.2. Hope you’ll post here how it goes with your project.
Fri 14 May ’10
2:22pm
carvingCode
Adam - Can’t get it (ee 2.2.3) installed in EE2.0.2PB1. It doesn’t display in the ‘addons->pluugins’ list. I have it inside ‘third_party/external_entries’. Any ideas?
Fri 14 May ’10
2:51pm
Adam Khan
I have v2.2.3 installed in EEv2.0.2pb01 as well, and it’s fine, so can’t replicate the problem. You’re using the built-in /third_party directory within the /system/expressionengine directory, right? I mean, you didn’t create a /third_party directory elsewhere?
Fri 14 May ’10
3:24pm
carvingCode
Yes, using the ‘third_party’ dir installed with EE. This is a fresh install and external_entries is the only 3rd party addon installed. Odd.
Fri 14 May ’10
3:33pm
Adam Khan
Yeah, odd. Want to try another plugin and see if that takes?
Fri 14 May ’10
5:24pm
carvingCode
OK. Must have had a bad install of EE. Couldn’t install other plugins either. Deleted/reinstalled EE and all is well. Am testing external_entries for db project that, instead of building out as an EE module, may do everything within EE itself. If external_entries is all its cracked up to be (which I’m sure it will be), I’ll be registering it.
Fri 14 May ’10
6:02pm
Adam Khan
Cool, post here if you have any questions using it. I’ve got a big article planned explaining how I’ve been using it, but too busy doing it to write about it…
Tue 15 Jun ’10
2:49am
Peter R Mansfield
I’m using v1.6.8. I want to select data from another database called ‘sympa’ on my server. I can do it with an ordinary php file but when I copy the php code into an EE template and when I use the equivalent External Entries code, regardless of what SQL statement I use I get this output:
MySQL ERROR:
Error Number: 1146
: Table ‘sympa.exp_global_variables’ doesn’t exist
Query: SELECT variable_name, variable_data FROM exp_global_variables WHERE site_id = ‘1’ AND user_blog_id = ‘0’
For some reason the SQL statement I write is being replaced by the statement above that is rubbish. Any suggestions?
Tue 15 Jun ’10
8:28am
Adam Khan
Peter, to use External Entries you don’t need to even switch PHP on in your template. I’m pretty sure External Entries doesn’t even use the exp_global_variables table. Are you getting error when you use the External Entries plugin without copying the PHP code into an EE template?
Tue 15 Jun ’10
10:32am
Peter R Mansfield
Adam, Yes I get the error with all combinations including a new template with PHP off and nothing but:
{exp:external_entries:select
hostname=“localhost”
username=“myusername”
password=“mypassword”
database=“sympa”
table=“subscriber_table”
}
{select:user_subscriber} - {select:reception_subscriber}
{/exp:external_entries:select}
If I delete <table=“subscriber_table”> then I get a blank page except if I turn EE debug info on - but that does not indicate any problem. I’ll let you have access to my site if you want to see it for yourself.
Tue 15 Jun ’10
3:21pm
Adam Khan
Peter, I’m working on an update to the plugin to make the debugging more robust so hopefully we’ll get a better picture of what’s going on on your template.
Tue 15 Jun ’10
8:47pm
Adam Khan
Peter, can you download v2.3 of the plugin (not the one I emailed you) and try again? I’ve restructured things so hopefully the debugging will be more helpful.
Tue 15 Jun ’10
9:07pm
Peter R Mansfield
Adam, I’ve had a brainwave this morning (South Australian time). I can get the php code equivalent for what I am trying do to work properly by adding mysql_close($connectiontoexternaldatabase) then mysql_connect() and mysql_select_db() to reconnect to my EE database.
Tue 15 Jun ’10
9:25pm
Peter R Mansfield
Adam, Sorry I submitted my last post before I saw your request to download v2.3. I’ve now done so: It has made no difference that I can see. I don’t get anything other than the MySQL error message I reported above.
Tue 15 Jun ’10
9:32pm
Adam Khan
Peter, want to send me login details to your site so I can take a look? Or are you able to do what you want to do without the plugin?
Tue 15 Jun ’10
10:09pm
Peter R Mansfield
Adam, I can do what I want to do faster with your plugin. Also the problem I am having could be a problem for other people. So please register at http://www.healthyskepticism.org and I will give you SuperAdmin status. If there is delay, it will be because I’m dong my day job. I’m a GP (family physician).
Wed 16 Jun ’10
2:59pm
Adam Khan
Registered, Dr.
Fri 18 Jun ’10
10:34am
Lee
Hi Adam, could I use this to delete entries from specific, or all channels that are 3 days older than now?
Best wishes
Lee
Fri 18 Jun ’10
10:38am
Adam Khan
Lee, yes, but remember the deletion has to be triggered by the template being loaded by the visitor.
Fri 18 Jun ’10
10:40am
Lee
Or by a cron?
Fri 18 Jun ’10
10:42am
Adam Khan
Sure
Fri 18 Jun ’10
12:55pm
Adam Khan
Peter, my test at your site is working.
Fri 18 Jun ’10
8:21pm
Peter R Mansfield
Adam, I’ve worked it out. External Entries works perfectly if System Preferences › Database Settings > Database Connection Type = Persistent. If Database Connection Type = Non Persistent then it works only if there is additional output PHP that re-establishes a connection with the EE database. I suggest you add a few lines to your plug in to do that or ask customers to ensure that their Database Connection Type is set to Persistent.
Mon 21 Jun ’10
6:14pm
Adam Khan
Peter, non-persistent works fine for me on both EEv1.x and EEv2.x. If that fixes it for you, then good, but I haven’t seen that setting making a difference anywhere else.
Mon 21 Jun ’10
7:35pm
Peter R Mansfield
Adam, So there must be something unusual about my system. We have Debian setup to be very secure so perhaps there is something in that that caused the problem. Since we have a treatment that works we don’t need to investigate the exact cause of the problem. If anyone else gets similar symptoms I recommend ensure that the database connection type is persistent. All’s well that ends well. Thank you for some very useful plugins!
Wed 23 Jun ’10
12:36pm
Vee Van Dyke
Hey Adam - I am getting an error when I try to use the select command: here is the error.
A PHP Error was encountered
Severity: Notice
=============================
Message: Undefined variable: DB
Filename: external_entries/pi.external_entries.php
Line Number: 49
=====================
I am using 2.3 on EE 2. Any ideas what might be causing this?
Here is the page that is throwing the error:
http://www.wildernessfamilycoop.com/admin/delivery_dates/
Thanks!
Vee
Wed 23 Jun ’10
1:05pm
Vee Van Dyke
I figured it out….
I changed lines 49 and 50 in the plugin to
$DB = $this->EE->db;
$TMPL = $this->EE->TMPL;
FROM
$DB -> $this->EE->db
$TMPL -> $this->EE->TMPL
Works great now, BTW.
Wed 23 Jun ’10
1:14pm
Adam Khan
Vee, you’re right. Fixed in v2.3.1.
Mon 28 Jun ’10
6:29pm
JonnyTurner
Hi Adam, I’m trying to extract data from an external database and insert it into custom fields in my EE2 database. This is so I can create an advanced search (for a car site). Will your plugin do this?
I can successfully display results from a table and also insert into the database from standard text but not on the same page.
Mon 28 Jun ’10
6:36pm
Adam Khan
Jonny, did you try putting the external_entries:update within the external_entries:select, with parse=“inward” as one of the parameters of the external_entries:select?
If that doesn’t work (and I don’t think it will, you can place an embed within the external_entries:select and pass the results to an embedded template that contains the external_entries:update. That should work.
Mon 28 Jun ’10
7:33pm
JonnyTurner
Hi Adam, thanks for the very quick reply and advice!
I’ve set up the embed as described and can display the data results in the embedded template. Then I copied the insert example above to insert the {embed:stock_model} into new rows to create new entries but I get a couple of errors - one being:
mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource
I tried the update example as you initially mentioned and had the same error. Not sure where I’m going wrong with it.
Thanks for the help.
Mon 28 Jun ’10
10:20pm
Adam Khan
Jonny, presumably you mean you can display the data results in the parent template?
Can you email me your parent and embedded templates?
Fri 2 Jul ’10
7:14pm
lukemcr
I’m looking for a quick way to just run a count from an external database using external_entries:select: something like SELECT COUNT(*) FROM table and having it spit out a number.
{select:count} and {select:total_results} aren’t really doing it for me. Is this really easy, or am I missing something?
Thanks a lot.
Fri 2 Jul ’10
7:35pm
Adam Khan
lukemcr, why doesn’t {select:total_results} give you what you need?
Fri 2 Jul ’10
7:54pm
lukemcr
Well, what I want is just a single number - the number of entries in a particular table in an external database. {select:total_results} loops through the table one time for each row, and displays the row count one time for each row.
Also, it’s capped at 100. I’m trying to get the post count from an external vBulletin database with 5 million + posts, so that doesn’t quite work.
FWIW, here’s my tags:
{exp:external_entries:select
hostname=“127.0.0.1”
username=“user”
password=“password”
database=“database”
table=“post”
}
{select:total_results}
{/exp:external_entries:select}
Fri 2 Jul ’10
7:58pm
Adam Khan
lukemcr, the limit can be set as a parameter to any number, but yes, I guess you really wouldn’t want to loop through 5million+ times.
I can add this functionality, but it won’t be immediately—got a deadline coming up.
Fri 2 Jul ’10
8:02pm
lukemcr
Great, thank you. I’d love to be able to run the equivalent of
SELECT COUNT(*) FROM posts
in an external database, and display the results in EE.
Fri 2 Jul ’10
8:17pm
Adam Khan
lukemcr, feature added to roadmap.
Fri 2 Jul ’10
9:55pm
Chad Crowell
FYI Adam I am getting the same exact issue that Peter had before, and the persistent/non-persistent setting does fix it. Seems like an update should be in order to allow non-persistent connections since EE recommends that.
Fri 2 Jul ’10
10:00pm
Adam Khan
Thanks Chad, added as first item on the plugin’s roadmap.
Mon 5 Jul ’10
1:33pm
Jaycee
This plugin would be extremely useful to me if it allowed me to execute select queries that joined 2 or more tables. I have instances where I would want to associate data outside the EE database with entries in the EE database in a many-to-many relationship, so I’m using a linking table to store the cross references. Is there any possibility of using this plugin in such a case?
Mon 5 Jul ’10
1:39pm
Adam Khan
Jaycee, right now there’s no joining. You could nest one instance of the plugin within another, passing the join column’s value from the outer to the inner.
Tue 13 Jul ’10
4:06am
Kurt Deutscher
Hi Adam,
I’m having the same issue as Dr. Mansfield above. I would love for things to work without having to have the database connection Persistent in EE.
Any chance you have a version of this that works without a persistent connection type?
Tue 13 Jul ’10
1:36pm
Adam Khan
Peter, Chad, Kurt: with v2.3.3 the plugin now has another parameter, connection, which, if set with a value of “persistent”, sets up a persistent connection. I don’t have the issue on my server, can one of you post here whether it works or not?
Fri 16 Jul ’10
8:59am
Peter R Mansfield
Adam, Sorry about the delay. I tested v2.3.3 with the code you wrote on my test page except I added connection =“persistent”. It does not work with my site having a persistent or a non persistent connection. Also it makes my utilities > plugins page be a blank screen.
Tue 20 Jul ’10
8:20pm
tree
Adam, Peter:
line 89 is missing the opening paren. that should solve peter’s problem (it did mine).
hope that helps!
teresa
Tue 20 Jul ’10
8:39pm
JonnyTurner
Hey guys,
I’m getting ‘Access denied for user’ when using select to connect to external database. I know the credentials are correct so is this a setting in EE 1.6.9 or external entries? It’s working fine in EE2.1 but had to downgrade the CMS to super search.
Tue 20 Jul ’10
8:58pm
tree
Hi again, Adam,
Here’s an odd one:
My html input elements aren’t being parsed inside and exp:external_entries_select. The greater than/less than signs are converted to entities. paragraph and label tags are fine, except for the closing paragraph tags that follow offending input tags.
If you know off-hand what the problem is, that’s great. I’ll dig through the plugin code some more to see if I see anything, as well…
thanks!
Tue 20 Jul ’10
9:42pm
Adam Khan
My apologies, guys, for disappearing for a few days, and thanks Teresa for the fix. Yes, there was a missing opening parenthesis in v2.3.3 that was messing things up. Fixed in v2.3.4. Jonny, Peter, does this fix help you?
Tue 20 Jul ’10
10:01pm
JonnyTurner
Hi Adam, definitely fixed the blank page but not the access error unfortunately. I’ve used the same plugin from my EE2 version (where it works) and also the same code. Completely stumped on this one.
Tue 20 Jul ’10
10:19pm
tree
Another bit of info re my input tag non-parsing problem, above.
The template contains includes external_entries has “allow php” set to yes, on output. That template is embedded in another template that allows php on input… and that template is included in a template that does not allow php. Changing the top-level template does not fix the problem. When i echo the value of $params[‘allow_php’], it is always “n”....
Surely, I’m just missing something….
Tue 20 Jul ’10
10:52pm
tree
In response to my input vs allow php problem…..
Right! Got it.
I needed to set “allow_php” in the external_entries tag. I WAS missing something….
sorry for all the noise, but if it helps someone like me in the future, woot!
Tue 20 Jul ’10
10:55pm
tree
oh yeah, i could more _carefully_ read documentation ...
thanks again, and i’ll shut up now :)
Wed 21 Jul ’10
4:52am
Adam Khan
Thanks for the feedback, Teresa. I’ve added a note in the docs next to the “allow_php” parameter mentioning the entities-converting issue. Jonny, I’m looking into your issue of the plugin not working on EE1.x…
Wed 21 Jul ’10
3:18pm
Adam Khan
Jonny, I’m not able to replicate your problem. Using EEv1.6.9 build 20100430, and connecting to an external database, with connection=“persistent”, it works for me. Want me to take a look at your templates and setup?
Thu 22 Jul ’10
1:27pm
JonnyTurner
Hi Adam, yeah that would be extremely appreciated. I’ve emailed across the login details. I’m using the same build as you’ve mentioned above, installed the 2.3.4 plugin and also created a new db but with same probs.
Fri 8 Jan ’10
10:27am
Adrienne L. Travis
I keep harping on this point with extension developers, and you may have seen it, so I’m sorry in advance if so: is the license fee *per EE installation* (backend) or *per front-end site* (if using MSM or other multiple-domain method)?
Thanks!