Saturday, December 5th, 2009 (updated 23 Jul ’13)

External Entries v2.6 ($25 per installation)

 

As ExpressionEngine becomes more deeply embedded in organizations’ business processes, there’s more of a need to integrate it with other systems. The External Entries plugin helps make this easier. It also helps you manage your EE database.

The plugin enables you to select from, update, insert into and delete from a table either within your EE database or any other MySQL database on the internet to which you have administrative access. This means your site can easily import and export data to and from EE and among the backends of any number of other MySQL-based systems.

Put the plugin in its own EE template, call the template with jQuery’s .load() and .on() functions for AJAXy goodness, and your web site is now a web app!

One possible use is when deciding how to approach a catalog for ecommerce. Do you publish your product catalog as EE entries? Or as entries in your ecommerce package? Or both? This plugin could tip the balance towards keeping them in the ecommerce package only, as it enables EE to easily view, edit, create and delete them over there.

External Entries costs $25 per EE installation and is free to try before you buy. It works in both EE v1.x and v2.x.

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!

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.

Mon 9 Aug ’10
3:43pm

Frank Harrison

Hi. I’m wondering if this might be the solution to my problem. I need a way to:

1) Delete all entries that have a particular value in a custom field
2) Copy the value from a particular custom field onto the Status for all entries in a weblog, if they don’t already match

I’d be looking to do one of these actions (preferably the second) each night with a cron job, and the weblog might eventually have 10,000 or more entries to search through. Each night though only 10-50 entries would actually have changed/need updating.

Is this do-able with this plugin?

Thanks, Frank

Mon 9 Aug ’10
4:12pm

Adam Khan

Frank, both the delete and the copy can be done with the plugin (the latter with the update function). I presume the cron job would just visit the web page that they’re on, which would trigger the action.

Mon 9 Aug ’10
5:14pm

Frank Harrison

Thanks Adam. I think I MAY have found a way to do it with EE’s built in queries, but will definitely give this a go otherwise. Cheers!

Mon 9 Aug ’10
6:42pm

Adam Khan

Remember the Query tag will only let you select, not delete nor update.

Tue 17 Aug ’10
12:49pm

luvd

When external_entries:insert is used in a {if} ... {if:else} ... {/if} structure, the inserts are both executed.

For example:

{if logged_in}
  {exp:external_entries:insert table=“mytable” debug=“n” allow_php=“yes”}
  {insert:myfield}ABCD{/insert:myfield}
  {/exp:external_entries:insert}
  logged_in = TRUE
{if:else}
  {exp:external_entries:insert table=“mytable” debug=“n” allow_php=“yes”}
  {insert:myfield}EFGH{/insert:myfield}
  {/exp:external_entries:insert}
  logged_in = FALSE            
{/if}         

Although EE only shows either “logged_in = TRUE” or “logged_in = FALSE”, there are 2 records inserted in mytable: one containing the value “ABCD” and one containing the value “EFGH”.

Tue 17 Aug ’10
12:55pm

Adam Khan

Luvd, this is probably due to the EE template parsing order, where the plugins are being executed before the conditionals are. Can you redo this as two separate conditionals, ie, if logged_in and if logged_out? Simple conditionals are evaluated earlier in EE’s parsing order than more complex ones.

Tue 17 Aug ’10
2:49pm

luvd

The same result when using two separate conditionals.

Note hower that, when the parameter debug=“y” is set on the 2 external_entries:insert tags, that only one SQL-statement is displayed. But when both tags have debug=“n”, they are both executed.

Tue 17 Aug ’10
3:07pm

Adam Khan

Hmm, looks like things are not getting reset at the end of calling the plugin. I’ll take a look. Are you using EE 1 or 2?

Tue 17 Aug ’10
3:09pm

luvd

Using EE 2.1.0

Tue 17 Aug ’10
4:58pm

Adam Khan

Luvd, I don’t think this is something that can be fixed from within the plugin—seems more like a problem with EE’s conditionals. I suggest you embed the plugin in another template:

OUTER TEMPLATE:

{if logged_in}
{embed=“site/.update” value=“Test”}
{/if}

{if logged_out}
{embed=“site/.update” value=“Test2”}
{/if}

INNER TEMPLATE:

{exp:external_entries:insert table=“table” debug=“n”}
{insert:field}{embed:value}{/insert:field}
{/exp:external_entries:insert}

Tue 17 Aug ’10
6:49pm

luvd

That seems to work. Thx.

Fri 20 Aug ’10
8:13am

Victor

Could you provide examples of using .load and .live ?

Fri 20 Aug ’10
6:47pm

unruthless

Hi Adam, how would you go about using data output by this plugin within an EE conditional? I’m trying to do something like the following:

{exp:external_entries:select table=“Events” search:event_id=”{segment_3}” limit=“1”}

This event is of type {select:event_type}.
This means that this event is a ...
  {if select:event_type==”“}(no idea){/if}
  {if select:event_type==“community”}community event!{/if}
  {if select:event_type==“special”}fundraising event!{/if}


{/exp:external_entries:select}

The {segment_3} works fine, since I guess EE parses URL segment variables before parsing plugin code.

But for whatever reason, the second paragraph always outputs “(no idea)”, even when the first paragraph correctly outputs “community” or “special”.

I’ve tried doing this using PHP conditionals rather than EE conditionals, but keep getting errors like “Notice: Use of undefined constant special - assumed ‘special’ in [redacted]/system/core/core.functions.php(634) : eval()‘d code on line 31”.

Wed 25 Aug ’10
5:46am

Adam Khan

Victor,

> Could you provide examples of using .load and .live ?

I’ve been meaning to write an article explaining this, but haven’t gotten around to it yet. If I don’t get on it within the next couple of weeks I’ll post something quick and dirty here. At any rate, the principle is to add a button with an id, and a jquery event listener for a mouseclick of that button containing a .load() of another EE template that contains the Select Entries plugin. This easily enables application-style operations on a web page.

Wed 25 Aug ’10
5:49am

Adam Khan

Ruthie,

Apologies for the delay. Within the plugin the variables have to be surrounded by quotes and curly brackets, so:

{if “{select:event_type}” == ”“}(no idea){/if}
{if “{select:event_type}” == “community”}community event!{/if}
{if “{select:event_type}” == “special”}fundraising event!{/if}

Does that fix it?

Wed 1 Sep ’10
9:00pm

jonhorton

Hi Adam,
I really like the capabilities of this plugin, but i’m having a few issues.
I’m trying to use the select function to pull data related to the currently logged in member_id:

{exp:external_entries:select table=“exp_member_data” search:member_id=”{member_id}” debug=‘n’}
{select:m_field_id_11}
{/exp:external_entries:select}

However, I don’t think {member_id} variable is parsing properly.  I’ve tried the parse=“inward” parameter, but that doesn’t change anything.

I’ve found that if I replace {member_id} with <?php global $SESS; echo $SESS->userdata(‘member_id’);?> and enable php on input, it works properly. 

Is this the best way to access member variables?

Thanks!

Wed 1 Sep ’10
9:16pm

Adam Khan

Jon,

Try surrounding the entire plugin with the {exp:member:custom_profile_data} tag (see http://expressionengine.com/user_guide/modules/member/custom_profile_data.html).

Wed 1 Sep ’10
9:37pm

jonhorton

Unfortunately that doesn’t work, as my query now shows like this: member_id = ‘1{/exp:member:custom_profile_data}’

According to the documentation, {member_id} is a global variable, and should be available without the custom_profile_data tags. Am I wrong in this?

It’s interesting to note that when I turn debugging on, and just using {member_id} variable, that the SQL shows the correct member_id.  Seems like that should work, but it’s hanging somewhere.

Wed 1 Sep ’10
10:07pm

Adam Khan

Jon,

Testing on EE2.1 the following works fine for me:

{exp:member:custom_profile_data}
{exp:external_entries:select table=“exp_member_data” search:member_id=”{member_id}” debug=“n”}
{select:m_field_id_11}
{/exp:external_entries:select}
{/exp:member:custom_profile_data}

> According to the documentation, {member_id} is a global variable, and should be available without the custom_profile_data tags. Am I wrong in this?

I’ve found often not, probably due to the parsing order—when it’s by itself it’s likely parsed after things like plugins, whereas when it’s within the {exp:member:custom_profile_data} tag then it’s processed earlier and available as a parameter value for plugins.

Wed 1 Sep ’10
10:22pm

jonhorton

Adam,

THANK Y OU! :)

I was putting the custom_profile_data tag in the wrong spot - inside of the external entries plugin, instead of wrapping external entries with the custom_profile tag .  Your example fixed all of my problems, and I no longer have to use php.

This plugin is awesome, Thanks again for your help!

Wed 1 Sep ’10
11:55pm

Adam Khan

My pleasure. (Though for this particular example you don’t need the plugin, the {exp:member:custom_profile_data} tag should be enough by itself—presumably you need the plugin for other things as well..)

Thu 2 Sep ’10
2:59pm

jonhorton

Allrighty, back with another weird issue!

I’m using a module called Redeemer which is a coupon redemption system.  It checks the entered coupon code and if valid returns a response.  If invalid, it returns an invalid response. I’m trying to use external entries in conjunction with redeemer to automatically update a custom member data field if the coupon is valid.

{if valid}
{exp:member:custom_profile_data}
  {exp:external_entries:update table=“exp_member_data” search:member_id=”{member_id}” debug=“n”}
      {exp:math_plus calculate=”{reward-points} + {coupon-points}” }
          {update:m_field_id_11}{math_plus_result}{/update:m_field_id_11}
      {/exp:math_plus}
  {/exp:external_entries:update}
{/exp:member:custom_profile_data}
{/if}
{if invalid}
  Sorry, your code is not valid
{/if}

If the coupon returns a valid response everything works fine.  When the coupon is invalid, the external entries plugin still runs and tries to update the database.  I receive a php error: Notice: Undefined variable: sql in /home/fintronx/fintronx.com/admin/plugins/pi.external_entries.php on line 593

Is there an obvious reason as to why the external entries plugin runs even though it’s wrapped with an if statement that’s not true?

Thu 2 Sep ’10
3:07pm

jonhorton

I also forgot to mention that I’m running EE 1.6.9

Thu 2 Sep ’10
3:17pm

jonhorton

Back again.  I may have found the culprit. (i’m a php novice, so hopefully this is right!)

$sql=”“; wasn’t defined for the Update and Insert functions in pi.externalentries.php around line 512 and 632.

Once i defined sql, i no longer receive the error, and everything is functioning as it should.

Thu 2 Sep ’10
7:15pm

Adam Khan

Thanks for the tip! I’ve added those declarations in v2.3.6.

Fri 3 Sep ’10
7:16am

Paul

Yes, it’d be great to see an example of “using .load and .live” - how to use a form to submit data to an external database “seamlessly” using Ajax.

Wed 8 Sep ’10
9:34pm

Bob Romanko

Hi there Adam, and greetings from Alaska!

I’m evaluating External Entries 2.3.5, and have it installed with EE 1.6.8.  I can’t seem to shake this 1146 error:

MySQL ERROR:

Error Number: 1146

Description: Table ‘resp_db.exp_template_groups’ doesn’t exist

Query: SELECT exp_templates.template_name, exp_templates.template_id, exp_templates.template_data, exp_templates.template_type, exp_templates.edit_date, exp_templates.save_template_file, exp_templates.cache, exp_templates.refresh, exp_templates.no_auth_bounce, exp_templates.enable_http_auth, exp_templates.allow_php, exp_templates.php_parse_location, exp_templates.hits, exp_template_groups.group_name FROM exp_template_groups, exp_templates WHERE exp_template_groups.group_id = exp_templates.group_id AND exp_template_groups.site_id = ‘1’ AND exp_templates.template_name = ‘header’ AND exp_template_groups.group_name = ‘_includes

(by the way…this error only shows up in Chrome.  Firefox and IE8 only give blank pages)

The code in the registration template is pretty straightforward…pretty much off the example:

{exp:external_entries:select
  hostname=“my_hostname”
  username=“my_username”
  password=“my_password”
  database=“resp_db”
  table=“resp_users”
  orderby=“dsc_name”
  sort=“asc”
  limit=“15”
  debug=“yes”
}

<table>
<tr><th>Full Name</th><th><UVa ID</th><th>Email Address</th><th>Employee ID</th></tr>
<tr><td>{select:dsc_name}</td><td>{select:a_code}</td><td>{select:email}</td><td>{select:emp_id}</td></tr>
</table>

{/exp:external_entries:select}

I saw Peter Mansfield’s posts regarding this same error, and tried also connection=persistent, since my EE install is set to non-persistent, and it made no difference.

My EE install is using the same MySQL server as my external database, so the server name is the same as my EE install, but the database name is different, of course.

Please let me know if you need any clarification, and thanks so much!

- Bob

Fri 10 Sep ’10
11:23am

Adam Khan

Bob, can you try repairing then optimizing your database? There are tools to do that from within EE.

Fri 10 Sep ’10
3:57pm

Bob Romanko

Hey Adam….I’m squared away.  I tried the repair/optimize, but no joy, so I just moved my table into my EE database and removed the parms no longer needed.  It’s working great now.  My next step is to include a checkbox next to the rows to allow users to check off which rows to delete.  Hmmm…..will have to kick around in the examples ‘n such to see the best way to pull that off!  Thanks for your help.

Mon 20 Sep ’10
6:58am

aaronlax

I am working on implementing this as a front end form for inserting new weblog entries…I have successfully created a form that uses the .post function to send field data to my external_entries template used to process the changes.
I have exp:external_entries:insert used for tables exp_weblog_entries, and exp_weblog_data
I also have exp:external_entries:update in there to update exp_weblogs (total_entries and last_entry_date)

My problem is that my actual display of the entries added to the database this way dont show up unless I go into the CP and manually update ONE of the entries

So: i can add any number of entries using external_entries and then update ANY one of those entries through the CP and only then will they show up inside of a weblog tag on the front end
I have checked and double checked all the database entry areas, time fields, etc and I cannot find anywhere there is anything inconsistent.

Do you know if there is anywhere else that needs to be updated/refreshed so this update through CP process wont be needed?

Mon 20 Sep ’10
9:10am

Adam Khan

Aaronlax,

Are you using caching on any templates? The plugin does not flush the caches and that can be necessary to show new or updated weblog entries.

I’ve added the addition of a flush_cache parameter to the plugin’s roadmap.

Mon 20 Sep ’10
9:46am

Adam Khan

Aaronlax,

I’m just trying this myself and there are problems. The entry_id field in exp_weblog_data is not an auto count field, so without hardwiring the entry_id in the exp_weblog_titles table, you can’t coordinate the entry_id field in the two tables, so can’t see how this works at all.

Also, are you adding all the required fields in exp_weblog_titles? They are site_id, weblog_id, author_id, title, url_title and status. And any fields in exp_weblog_data need both the field_id_XX and field_ft_XX fields set.

Mon 20 Sep ’10
10:11am

Adam Khan

Aaronlax,

Aaronlax, as I play with this, it seems that the lack of an auto increment in the exp_weblog_data field is a showstopper. The plugin wasn’t designed to replace EE’s SAEF but to complement it. I’d suggest using the SAEF for at least initially adding new entries. The plugin would have to support table joins in order for the insert to work on actual weblog entries.

Mon 20 Sep ’10
12:44pm

aaronlax

Adam, thanks for the quick response!  I actually had gone ahead and simply updated the weblog_data -> entry_id field to auto increment without having any issue…
other entry post processes seem unaffected by the change and it also cured the sync issue of this process.
I have all the caching for the templates turned off, and flushing the cache manually through the utilities does not make a change either.  Only through re adding or re saving an entered entry does the system recognize the external_entries added posts.

My initial thought was that there is a count or some other field somewhere that gets updated letting the system know to recognize the added entries, or a cache somewhere else…

if you want to contact me through email directly i can show you the code im working with to execute this, and although I wish i could just rely on the SAEF, if this works your module will allow me to string together a number of different functions this specific client has requested to help ease workflow for their users
so close!!

Mon 20 Sep ’10
2:15pm

Adam Khan

Aaronlax, I wouldn’t let the exp_weblog_data autoincrement because the two can get out of whack. Instead I’d pass the value to an embedded template that contains the exp_weblog_data insert.

There is the total_entries field in the exp_stats to increment (you’d have to grab the current value and use PHP to return an incremented value) but that doesn’t seem to be the problem.

Are you sure you can’t put the plugin in an embedded template called from within the SAEF, or vice versa?

Mon 20 Sep ’10
5:31pm

aaronlax

I adjusted that auto increment back like you suggested, i was concerned about that anyway so your way makes sense.
I could put the plugin in to an embedded templste…but at this point I keep thinking that i shouldnt have, and ive spent so much time getting this solution staged it would feel like a step backwards…if only in admittance of defeat..hah!

Mon 20 Sep ’10
5:43pm

Adam Khan

No shame in embedding templates, they are your friend. I think of them like functions—functions within functions are fine. They can even call themselves recursively. So ‘tis not defeat, rather the door through the wall.

Mon 20 Sep ’10
6:06pm

aaronlax

I hear ya, I am going to give this one more try through to see if i can’t do it all using your module!
I’ll keep you updated!

Thu 23 Sep ’10
5:26pm

aaronlax

I got it!  Apparently in an unrelated issue query caching was turned on even though the setting in the CP indicated otherwise…
I have gotten the update, and insert functions working, with a combination of ajax and _POST functions
I am now working on the delete entry functions, my initial attempt using the same php code to bring in post data has failed, is there a php_allow parameter available here or do you believe there is another issue?

Thu 23 Sep ’10
5:34pm

aaronlax

Scratch that last post, sorry for cluttering up your board with questions. I got everything working flawlessly, if you want me to post examples for your other users of clean usage of this module with jQuery and Ajax for posting, editing, and deleting entries, let me know!

this module rocks!!

Thu 23 Sep ’10
5:39pm

Paul Shand

@aaronlax - I’d love to see some examples of what you did here.

Thu 23 Sep ’10
5:42pm

Adam Khan

Excellent. No need for allow_php paramter for the delete function because there’s no closing tag so nothing between the opening and closing tag where PHP could go.

Thu 23 Sep ’10
5:44pm

aaronlax

Yeah, makes sense.  I’ll put up an example end of day (EST), have to finish this stuff!

Thu 23 Sep ’10
5:44pm

Adam Khan

Sorry, cross-poting. Glad it does the trick and congrats for persevering. Absolutely, please post examples. If I can understand them myself I’ll add them to the examples above in the docs with a wee note of thanks.

Sun 3 Oct ’10
8:05am

timmo26

@aaronlax - any joy on the examples, would be great to see some!

Sun 3 Oct ’10
11:35pm

aaronlax

Hey guys, sorry for the delay, had to finish the client project then get something generic on our side so I could share!  I posted a blog entry at:
http://www.wearepapertiger.com/blog/entry/expression_engine_front_end_publishing_using_external_entries

Feel free to leave comments there with any questions, and thanks again Adam for working with me on this!

Mon 4 Oct ’10
12:18am

Adam Khan

Excited to see the article, Aaron—thanks!  You definitely captured the idea of the plugin—not to mention taking it somewhere I hadn’t managed to myself…

Thu 7 Oct ’10
11:41am

crossfade

I’m wondering about the following…

The easiest way to pass variables in Expression Engine (or at least an easy way) is through the URL segments.

I’m testing out External Entries, but it seems that the segment_n etc values are not available at the time external entries is “doing its thing”.

Is there a work around to allow this to be the case?

for example, if I have template_group/data1/data2/data3 and want to insert these data values into a table, it doesn’t work, because external entries runs before those variables are parsed.

Any ideas?

Thu 7 Oct ’10
11:46am

Adam Khan

Crossfade,

Have you tried with the parse=“inward” parameter (though I’m doubtful that will help). How about passing the segment value to a PHP variable outside the plugin, then using the allow_php=“yes” parameter and using the PHP variable inside the plugin. Try on both PHP input and output.

if not, you can embed the plugin, passing the segment variables as embed variables, which I know the plugin can read.

Thu 7 Oct ’10
12:14pm

crossfade

Thanks for the quick response and the suggestion.

I seem to be hitting a really weird problem.

I tried the php method and it “sort of” works.  Actually, it is “sort of” working just using the segment without php now that I’ve set the field to be text rather than numerical, in that the {segment_3} is being parsed, but as a completely different thing.

It’s actually being parsed as “images”, which is nowhere in the URL segments at all.

Even more intriguing is that with debug set to “y” it appears to be working correctly.  But when I set debug to “n” it runs and inserts a different value into the table than the debug info shows.

I tried the embed method first, because that was the first thing I thought to do, and it gave the same result.

I’ll keep trying and see if I can get to the bottom of it

Thu 7 Oct ’10
12:18pm

Adam Khan

Crossfade, that sounds pretty messed up—and I didn’t think the plugin was that crazed! It shouldn’t do anything with debug=“y”, only output what the SQL would be if it were to execute it, which it shouldn’t.

To help you properly I’ll have to replicate what you’re doing, and don’t have time to do that right now (client deadlines), but can do so later if you’re still stuck. Before doing that, you can try posting the template here and I can see if any syntax is wrong.

Thu 7 Oct ’10
1:00pm

crossfade

Sorry, just to clarify what I meant…

With debug set to “y” it does as it is meant to do.  It outputs the SQL on the template.

And the SQL it outputs on the template in debug mode is correct.

ie. UPDATE exp_channel_data SET field_id_x = “correct data”

However, when I then set debug to “n”, the actual SQL executed is:

UPDATE exp_channel_data SET field_id_x = “images”

(Well, that’s what I assume the SQL is, because what gets entered into the table is the word “images”)

However, I have found a fix, although it completely baffles me as to why this works.  If I use segment_3 to pass the data through, then it works.  If I use segment_4 it doesn’t.

I was passing entry_id as segment_3 and the data as segment_4

I swap them around and have data in segment_3 and entry_id in segment_4 and it works fine.

I’ll play around a bit and keep you updated.

Thu 7 Oct ’10
1:46pm

Adam Khan

A bit baffling re the segment differences. Nor can I think where the “images” value is coming from. It’s a bit obscure to try to debug, especially if you can get it working by swapping them around. Yes, please post if you have more to report.

Fri 8 Oct ’10
3:14am

al

hi there,
I have just bought your plugin, and i think its great. However its missing one key thing - ability to grab the autoincrement value straight after an insert. The only way to get around it is to run a separate select straight after grabbing the last entry id. However because it is a separate query, you will run into concurrency issues for high volumn sites. The last id entry that you are selecting might not be for the record that you have just added.

Sat 9 Oct ’10
10:11am

Adam Khan

Al, I’ll look into implementing that, passing any autoincrement values.

Mon 11 Oct ’10
11:04am

denza

Hi,
Are supported nested methods?

For example:

{exp:external_entries:select EXTERNAL DB}
  {exp:external_entries:insert LOCAL DB}
      ...
  {/exp:external_entries:insert}
{/exp:external_entries:select}

Mon 11 Oct ’10
11:39am

Adam Khan

Denza,

Should work with the parse=“inward” parameter in the outer call of the plugin.

Mon 11 Oct ’10
4:26pm

denza

With parse=“inward” there are PHP Errors was encountered:

Message: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource
Filename: external_entries/pi.external_entries.php
Line Number: 165

Message: Undefined variable: fields
Filename: external_entries/pi.external_entries.php
Line Number: 172

Message: Invalid argument supplied for foreach()
Filename: external_entries/pi.external_entries.php
Line Number: 172

The same issue when both methods are separate (and without parse=“inward” )

Mon 8 Nov ’10
6:47pm

Adam Khan

Wow, Denza, my apologies it’s taken nearly a month to get back to you. Things have been very hectic workwise but are now calmed down.

In v2.3.7 I’ve declared some variables so that at least the 2nd and 3rd of these three errors don’t come up.

But looking at the first error, it seems to me it may be caused by the table name. The SQL statement that’s messing things up I think is merely “SHOW COLUMNS FROM $table”. What is the table name?

Thu 25 Nov ’10
1:05am

crossfade

Hi,

Just a couple of things.  First is a feature request.  The “orderby” and “sort” functions of external entries do not allow for ordering via multiple conditions via the use of the pipe character as the normal expression engine channel entries tag does.

So, for example, I have a list of people and I want to order them by gender, surname and first name.  But currently have to do this in a very complex way.  I’m not sure how difficult this would be to implement, but it’s a feature request to consider anyway :-)

The second is a question regarding the use of form fields within the external entries select tag.  For some reason, when I use form fields (ie. <input type=“text”> etc.) within the tags, all the html from that point onwards starts being parsed as plain text.  I’m not sure why this would be the case, and was wondering whether there was a way around it.

Thanks!

Thu 25 Nov ’10
4:29am

crossfade

Just another question :-)

Does external entries ignore “if” statements?

Or more correctly, is it parsed prior to the if statements in a template?

What I’m experiencing is that if the external_entries tag is on a template, it runs regardless of whether the if statements that surround it are true.

By the way, in the “debug” mode it seems to be outputting correctly, but with debug set to ‘n’ it doesn’t

Tue 30 Nov ’10
5:24am

ContextDesign

I’ve been playing around with External Entries, trying to accomplish something like this, but it’s not working:

<? $yesterday = {current_time} - 86400; ?>

{exp:external_entries:update table=“exp_weblog_titles” search:entry_id=“888” debug=“y” allow_php=“y”}
{update:entry_date}<? echo $yesterday; ?>{/update:entry_date}
{/exp:external_entries:update}

With debugging on, it shows the correct MySQL query, but with it off, it resets the entry_date to 0. Using {current_time} works, but I’m trying to offset the time.

Using 1.6.9, if it makes a difference.

thx!

Tue 7 Dec ’10
1:33pm

Ciaran Mc Donnell

Hi Adam

When I try to add an entry it doesn’t work. I enable debug and try and enter the query manually to the database but i expects values like channel_id, entry_date, year etc. Do i need to include these values when using exp:external_entries:insert?
I am using EE version 2.1

Thanks

Thu 9 Dec ’10
1:31am

PixelGrinch

I am swapping from custom fieldgroups to the gipsy plugin. I am now in the process of copying the data over to the new gipsy fields but have a problem with my uploaded images. How can I save the tag eg. {filedir_3}, raw into the new field. At this stage it always rewrites it and replaces it with the actual value of the web address to the image .... Is there any way to do this essentially suppressing expressionengine to expand the tag.
I need this:
{filedir_3}17344760.jpg
instead of this:
http://www.somedomain.com/images/uploads/17344760.jpg
Thanks!

Thu 9 Dec ’10
10:44am

Adam Khan

PixelGrinch,

I’ve never used gypsy. Before we go further, have you taken a look at the fabulous Field General?

Thu 9 Dec ’10
6:44pm

PixelGrinch

Gipsy is a Custom Field Add-On also. I guess it just caused confusion! Let’s forget about Gipsy.

current content of custome field {report_scan} is
{filedir_3}17344760.jpg.
I am trying to copy this exact value into field_id_224 using the following entries operation:

{update:field_id_224}{report_scan}{/update:field_id_224}

Now I am sitting here and the value in the updated field is not
{filedir_3}17344760.jpg, it actually is now:
http://www.somedomain.com/images/uploads/17344760.jpg

I understand that expressionengine converts every tag into its value and {filedir_3} happens to be the directory where I uploaded the image to, through the EE CP,
http://www.somedomain.com/images/uploads/ !

if I ever decide to rename {filedir_3} I am completely SOL. Another issue using https breaks the SSL by mentioning unsecure items on the page.

Maybe it makes more sense now and the question remains how can I retain the original value of

{filedir_3}17344760.jpg and copy it into field_id_224?

Thanks.

Fri 10 Dec ’10
3:23am

PixelGrinch

I found a solution to my problem I described above.
It required the use of an embedded template. In my case:

{embed=“includes/tester” fname=”{report_scan}” entryid=”{entry_id}” }

the call of the embed was made within the weblog:entries tag. through the embed I passed on the entry_id and the unwanted expanded content of the custom field.

the content of the embedded template is this:

{exp:weblog:entries weblog=“reports” entry_id=”{embed:entryid}” status=“not whatever” dynamic=“off”}

{exp:external_entries:update table=“exp_weblog_data” search:entry_id=”{entry_id}” debug=“n”}
{update:field_id_224}{filedir_3}<?php echo end(explode(’/’,’{embed:fname}’)); ?>{/update:field_id_224}
{/exp:external_entries:update}
{/exp:weblog:entries}

I basically passed on the expressionengine expanded {filedir_3}. I then stripped out everything but the filename and extension, then added {filedir_3} in front of the filename and extension within the entries:update tags.

Thanks ...

Tue 14 Dec ’10
9:27pm

Adam Khan

Thanks, PixelGrinch, for posting the solution. Yes, as is often the case, embedded templates to the rescue! A nice ingenious solution you came up with, seems to me, hardcoding the {filedir_3} bit.

Mon 3 Jan ’11
8:30pm

peishu

Hi Adam,

I am trying to update a table by using a form. Here is the processing page once the form is submitted:

{exp:external_entries:update table=“exp_matrix_data” search:row_id=”{segment_3}” limit=“1”  debug=“no” allow_php=“y” parse=“inward”}
{update:col_id_1}<?php echo $_POST[‘student_first_name’]; ?>{/update:col_id_1}
{update:col_id_2}<?php echo $_POST[‘student_last_name’]; ?>{/update:col_id_2}
{update:col_id_3}<?php echo $_POST[‘student_nickname’]; ?>{/update:col_id_3}
{/exp:external_entries:update}

Everything works fine unless the form contains a blank field. If the field is left blank and submitted, the php code gets inserted into the database.

For example, if I leave student_nickname blank on the form,  then

<?php echo $_POST[‘student_nickname’]; ?>

gets inserted into the database. Other fields still get inserted/updated properly.

There are no errors when debugging, nor does the code show up as being inserted when debugging.

PHP parsing is set to Input on the update template.

I hope this makes sense.

Thanks!

Peishu

Wed 5 Jan ’11
3:39am

peishu

Adam,

I figured out the problem.

Peishu

Mon 10 Jan ’11
12:30pm

Adam Khan

Peishu, sorry I wasn’t able to help. I’ve been busy with personal stuff, but I’m back now. Glad you figured it out. (I think PHP has to be on Output, no?)

Mon 10 Jan ’11
6:28pm

peishu

Adam,

I found the problem, but do not have the solution. It appears that when I UPDATE a field by removing the content and making it empty, the database will not update with an empty field. 

Updates with characters and spaces work.

Peishu

Mon 17 Jan ’11
5:18pm

heaversm

Hi - I wanted to do something like this:

I wanted to do something like this:


{exp:external_entries:update table=“exp_custom_stats” search:stat_name=“site_views” debug=“n” allow_php=“y”}
{update:stat_count}stat_count++{/update:stat_count}
{/exp:external_entries:update}

How would I increment the stat_count variable?

Tue 18 Jan ’11
2:01am

Adam Khan

Heversm, you’d have to get the stat_count variable first. You could do that with an external entries select, saving the value to a php variable. Then increment that variable. Then do what you’ve done above, passing your new incremented variable.

Tue 18 Jan ’11
2:22am

Adam Khan

Peishu, I would generally have PHP set to output to work within the plugin.

Regarding not updating if the value is empty, you’re right. Fixed in v2.3.8.

Tue 18 Jan ’11
3:07am

heaversm

Hi Adam,

Thanks for the tip. I’ve done what seems should work, but with no luck. I can get the value to increment, but I can’t seem to get it to commit to the DB. If I hard code a statistic value, it works, but when I replace it with the echoed php variable, it doesn’t work. Here’s the code:

{exp:external_entries:select table=“exp_custom_stats” search:stat_name=“site_views” limit=“100” allow_php=“y”}
      {select:stat_name} : {select:stat_count}
  <?php
      $site_views = ‘{select:stat_count}’;
      $site_views++;
      echo “$site_views”;
    ?>
{/exp:external_entries:select}


{exp:external_entries:update table=“exp_custom_stats” search:stat_name=“site_views” debug=“n” allow_php=“y”}
{update:stat_count}<?php echo “$site_views”; ?>{/update:stat_count}
{/exp:external_entries:update}

Tue 18 Jan ’11
3:23am

Adam Khan

Mike—presume you have PHP set to output?

Try passing the value to an embedded template, so that the select is in the parent template, the update in the child one.

Sun 30 Jan ’11
5:17pm

heaversm

Hi Adam - regarding my comment above - is there no way to do this without an embed template? The problem with using an embed is it executes immediately before the rest of the template is parsed, and I want this to execute on a javascript onclick command.

Mon 31 Jan ’11
12:58am

Adam Khan

Mike, I don’t think so. But you can make it happen onclick by having ajax load in the part of the page that does the update. I’ve done this many times (watch out to secure the ajax-loaded page somehow otherwise anybody visiting it performs the update).

Aaron Lax wrote about this in some detail at Expression Engine Front End Publishing Using External Entries. Take a look at the .load jquery usage.

Mon 31 Jan ’11
10:53pm

heaversm

Thanks - I’ve attempted this many times, but nothing seems to submit to the database. I’m checking my syntax and my passed variables - everything’s right but it’s not increasing the value. This is my setup:

Initial template increments the variable

{exp:external_entries:select table=“exp_custom_stats” search:stat_name=“tv_views” limit=“1” allow_php=“y”}
  <?php
      $stat_val = ‘{select:stat_count}’;
      $stat_val++;
      echo “$stat_val”;
  ?>
{/exp:external_entries:select}

the javascript function:

$(’.stat_increment’).click(function(){
  $(’.stat_loader’).load(’/index.php/ajax/download_increment/’+<?php echo “$stat_val” ?>,function(){
      alert ($stat_val);
  });
});

And the ajax/download_incremement template:

<?php $new_val = {segment_3}; ?>

{exp:external_entries:update table=“exp_custom_stats” search:stat_name=“tv_views” debug=“n” allow_php=“y”}
  {update:stat_count}
  <?php echo “$new_val” ?>
  {/update:stat_count}
{/exp:external_entries:update}

Mon 21 Feb ’11
9:54pm

Marc Tanis

I know it would require some hacking, and I’m not asking you to support this, but would it be a major overhaul to manipulate this plugin to work with a MS SQL Server? I can do all the code manipulation. I know Codeigniter can handle this and Just wanted to see if this sounded like a plausible feat with this plugin

Wed 9 Mar ’11
5:20pm

Pv Ledoux

Hi there!

I’m trying to do this:


  {exp:external_entries:Update table=“exp_channel_data” search:entry_id=”{segment_3}” debug=“n”}
      {update:field_id_38}y{/update:field_id_38}
      {update:field_id_39}{screen_name}{/update:field_id_39}
  {/exp:external_entries:Update}

But screen_name is not parsed. Do you know how I can do that?

Thanks!


Regards,

Wed 9 Mar ’11
8:26pm

Adam Khan

Pv Ledoux, try wrapping the plugin within the {exp:member:custom_profile_data}{/exp:member:custom_profile_data} tag. Seems to make many member-related variables available that otherwise aren’t due to parsing order.

Thu 10 Mar ’11
8:04am

Pv Ledoux

Thanks Adam, that works!

Wed 6 Apr ’11
12:51pm

JonnyTurner

Hi Adam, is there a way to pull unique records only e.g. SELECT DISTINCT etc?

Wed 6 Apr ’11
5:10pm

Adam Khan

JonnyT, good idea, added, go get v2.3.9.

Wed 6 Apr ’11
9:51pm

JonnyTurner

Absolute star!  I’m getting ‘undefined variable: distinct’ line 385. I’ve had a look but I’m not really good at php.

Sat 16 Apr ’11
1:29am

Anthony

Could you please give me a quick tutorial on how to use the select function with checkboxes to sort through columns of a database? I want to dynamically provide search results from an external database by checking and unchecking checkboxes using your plugin. Much appreciated.

Wed 20 Apr ’11
9:40pm

ThreeLoop

How would you go about adding a switch class to every second row?

Thu 21 Apr ’11
2:20am

ThreeLoop

I came across a weird bug. when I add the conditions of my search to the {exp:external entries:select} tag. It seems to base filtered search results on the order in which the search paramater is listed.

Here’s a test page:

[Removed]

If I filter for “A” it lists all fields that match “A”. If I then check “B” (which is the second search parameter) it then correctly filters to fields that match both “A” and “B”,

However, if I select a checkbox, and then select a checkbox previous to that checkbox in the search parameter order, then it doesn’t filter the results correctly and only filters for the first parameter. Any help would be much appreciated as I am on a time contraint.

Thu 21 Apr ’11
11:48am

Adam Khan

ThreeLoop, can you email me the template as well?

Re switch class, I’d just use a php counter $x and if ($x % 2 == 1) then display something.

Thu 21 Apr ’11
11:59am

Adam Khan

JonnyTurner, I implemented that incorrectly. v2.4.0 should work.

Fri 6 May ’11
12:31pm

Casey

I want to purchase this but I’m not sure if it’s possible to do what I’m trying. I need to change the member group_id when the template is called. Is it possible to update member data? I wasn’t able to get it working, but I’ll keep trying if you tell me it’s possible.

{exp:external_entries:update table=“exp_members” search:member_id=”{member_id}” debug=“no” limit=“1”}
{update:group_id}3{/update:group_id}
{/exp:external_entries:update}

Fri 6 May ’11
12:49pm

Adam Khan

Casey, see the comment above to Pv Ledoux: try wrapping the plugin within the {exp:member:custom_profile_data}{/exp:member:custom_profile_data} tag. Seems to make many member-related variables available that otherwise aren’t due to parsing order.

Sat 7 May ’11
8:47am

Valon Sopi

Hello crew,

I’ve tried updating EE1.6 to 2.x // however there’s many glitches during the updating part.

My reasoning was to use an add-on that would allow me to import all previous data (close to 2000) into a brand new installation of EE (given that I create the similar custom fields, etc.)

My specific question — Would this add-on allow me to do that? Meaning: Allow me to import 2000+ entries from an older DB into a brand new installation of EE2.x? /// And are there any specific steps I need to take to achieve this — Or is this all covered in the documentation : )

(Apologies for not reading through a lot, as I’m quite restless trying to find the ideal solution)

Cheers.

— Valon.

Sat 7 May ’11
11:19am

Adam Khan

Valon,

External Entries is not the plugin you’re looking for. It enables SQL stuff from templates, and since you’re doing a one-off, you don’t need that.

What would be better for you is DataGrab.

But apart from that, if an update from v1 to v2 isn’t going smoothly, the EE Forums guys should be able to help you with that until it’s done.

Sat 7 May ’11
11:43am

Valon Sopi

Hey Adam.

Thanks tons for the reply and the suggestion for DataGrab.

Yes. EE Tech Support is great, however I’ve decided to go with a fresh install as many of the modules I’ve used on 1.x will no longer be valid on 2.x anyways. Also, a fresh install just feels much better : )

Cheers man!

— Valon.

Sat 21 May ’11
3:43am

Dave

1) Does External Entries work with nested queries “WHERE field IN (Select field FROM table)”? 
2) Would the nested query be escaped?
3) Does External Entries have a WHERE clause?  Especially for tables not in EE.
4) How can I do complex queries with External Entries?
Thanks

Fri 17 Jun ’11
2:28pm

Baba Ganoosh

How do I do MySQL LIKE search with this component?

Thanks

Fri 17 Jun ’11
6:43pm

Baba Ganoosh

Sorry I could not wait, I made minor modification to the code to support like

function _insert_filters($filters)
  {
      $i=”“;
      $sql=”“;
      foreach($filters as $filter_field => $filter_value)
      {
        $i++;
       
        $val = strtolower($filter_value);
       
       
        $is_like = false;
       
        if ( ($index = strpos($val, ‘like:’)) !== false)
        {
          $filter_value = substr($filter_value, 5);
          $is_like = true;
        }
       
        if ($i > 1)
        {
          $sql .= ” AND “;
        }
       
        if ($is_like)
        {
          $sql .= ” $filter_field LIKE ‘$filter_value’ “;
        }
        else
        {
          $sql .= ” $filter_field = ‘$filter_value’ “;
        }
      }
      return $sql;
  }

I am sorry if this is redundant.

Cheers,

Mon 27 Jun ’11
3:07pm

vickster

Adam:

I’ve read through this whole thread and it seems my problem is identical to Peter Mansfield’s, but I’ve set the db connection to persistent - both in the cp and in your code - and it makes no difference. I’ve also repaired and optimized the EE database tables. Any other idea what might be going on?

The specific error is:
MySQL ERROR:

Error Number: 1146

Description: Table ‘EXTdbNAME.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’

I’m using the following code in it’s own template:
{exp:external_entries:select
hostname=“blah”
username=“blah”
password=“blah”
database=“DBNAME”
connection=“persistent”
table=“Listing”
sort=“asc”
limit=“4”
}

{select:title}
{select:display_url}
Thumb-Id: {select:thumb_id}

{/exp:external_entries:select}

Tue 28 Jun ’11
9:57pm

ContextDesign

Still having the same troubles as months ago:

I’ve been playing around with External Entries, trying to accomplish something like this, but it’s not working:

<? $yesterday = {current_time} - 86400; ?>

{exp:external_entries:update table=“exp_weblog_titles” search:entry_id=“888” debug=“y” allow_php=“y”}
{update:entry_date}<? echo $yesterday; ?>{/update:entry_date}
{/exp:external_entries:update}

With debugging on, it shows the correct MySQL query, but with it off, it resets the entry_date to 0. Using {current_time} works, but I’m trying to offset the time.

Using 1.6.9, if it makes a difference.

thx!

Thu 30 Jun ’11
9:32pm

Adam Khan

ContextDesign,

Try setting PHP to input rather than output.

Thu 30 Jun ’11
9:35pm

Adam Khan

vickster,

Apologies for taking days to get back to you. Just to make it clear: when you enter database=“DBNAME” the error message is “Table ‘EXTdbNAME.exp_global_variables’ doesn’t exist”, as if it’s sticking “EXT” in front of the db name?

Thu 30 Jun ’11
9:41pm

Adam Khan

Dave,

If you’re still listening, more than a month later:

1) Does External Entries work with nested queries “WHERE field IN (Select field FROM table)”?

Right now it doesn’t. You should be able to place the tag within itself though using the universal parse=“inward” plugin parameter.

2) Would the nested query be escaped?

No nested queries I’m afraid.

3) Does External Entries have a WHERE clause?  Especially for tables not in EE.

Yes, it’s the search:custom_field parameter(s), which gets translated into WHERE custom_field=“something”.

4) How can I do complex queries with External Entries?

Right now the only way to get a little bit more complex is to nest calls to the plugin.

Thu 30 Jun ’11
9:43pm

Adam Khan

Baba Ganoosh,

Good on you for taking some initiative! I’ll add “like” functionality to the plugin’s development roadmap.

Thu 30 Jun ’11
9:55pm

ContextDesign

Thanks Adam. It’s always the simple things that trip me up ; )

cheers!

Fri 1 Jul ’11
7:02pm

vickster

Adam: sorry there is no EXT in front of the database name. That must have been my typo when I was removing the db ids. The weird thing is when I got your message I looked at the page in the browser and it worked! I have no idea why, but I then tested some php in the same template, then went back to the original external entries code and it gave me the same original error again.

I can’t figure out why. I’ve tried every php setting on Preferences (right now it’s set to off), I’ve cleared the cache, restarted the computer. I thought this might be why it worked before, even though I’m testing on a remote server.

Still perplexed

Fri 8 Jul ’11
2:16am

ThreeLoop

I’m having an issue with one of the forms on my site that deals with external entries.  I’m not sure if it’s an external entries problem or not as I don’t think it is, but maybe someone does know how to fix it. When I submit a form and pass get variables to a page containing external entries and embed the $_GET variables as parameters in my search I get the errors below. I do this in other areas of my site without any issues. Thoughts?


Message: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given
Filename: external_entries/pi.external_entries.php


Message: Cannot modify header information - headers already sent by

Tue 12 Jul ’11
1:53pm

vickster

orderby=“random” does not appear to work (no results are returned when I use it). I can orderby a field, but in the instructions you said orderby works as it does for ee tags. Is there another way to return random entries/rows?

Tue 12 Jul ’11
2:32pm

ThreeLoop

is there a way to display certain entries based on ID,? Say I want entry_id 2, 3, and 4 to display.

Tue 12 Jul ’11
2:44pm

Adam Khan

ThreeLoop, no, right now there isn’t. Right now each search:entry_id parameter would be treated as a clause in a WHERE of ANDs not ORs. I’ll add this feature to the roadmap—piping as OR within a search: parameter.

Wed 13 Jul ’11
12:19pm

opalescence

I figured out the problem.

Sat 30 Jul ’11
9:00pm

Casey

Hi, I really like this plugin. Thanks.
Is there any reason you can think of why this works:

{if group_id == ‘3’}
<?php
$member_id = ‘{embed:memberid}’;
$groupid = ‘5’;
$data = array(‘group_id’ => $groupid);
$sql = $this->EE->db->update_string(‘exp_members’, $data, “member_id = ‘$member_id’”);
$this->EE->db->query($sql);
?>
{/if}

But this does not:
{if group_id == ‘3’}
{exp:external_entries:update table=‘exp_members’ search:member_id=’{embed:memberid}’ debug=‘n’ limit=‘1’ parse=‘inward’}
  {exp:channel:entries channel=“orders” status=“Open” author_id=“CURRENT_USER” limit=“1”}
      {order_items}
     
      {if entry_id == ‘404’ || entry_id == ‘405’ || entry_id == ‘406’}
        {update:group_id}6{/update:group_id}
      {if:else}
        {update:group_id}5{/update:group_id}
      {/if}
     
      {/order_items}
  {/exp:channel:entries}
 
{/exp:external_entries:update}
{/if}

Sun 31 Jul ’11
12:25am

Kurt Deutscher

We just trying to post weblog entries from a second install of EE (different database) within the same hosting account. This is for a client with 40+ sites, and we have some content in the older EE 1.7.x that we need duplicated in a site within an EE 2.2.x install that share the same hosting account.

We can get either the title or the content of the entry to display, but not both at the same time. Also we need to use entries with a status of “open” and that works fine with the titles.

How on earth do we do do the equilivent of a weblog/channel tag?

This was our last attempt, but it’s not displaying a thing:

External Entries (Select) Debug is on. Errors:

  No errors!

External Entries (Select) SQL: SELECT * FROM exp_weblog_titles INNER JOIN exp_weblog_data WHERE ( exp_weblog_titles.status = ‘open’ ) AND ( weblog_id = ‘376’ ) LIMIT 6;

and our tag:

{exp:external_entries:select
  hostname=“localhost”
  username=“xxxxxx”
  password=“xxxxxx”
  database=“xxxxxx” 
  table=“exp_weblog_titles INNER JOIN exp_weblog_data”
  search:exp_weblog_titles.status=“open”
  search:weblog_id=“376”
  limit=“6”
  debug=“yes”
}

<h1>{select:title}</h1>
{select:field_id_21}


{/exp:external_entries:select}

Wed 10 Aug ’11
1:54am

icebreaker

The link to the article on the use of JQuery load() by Aaron Lax is broken—he may have moved on. It could be useful with an example where e.g. a click on a row could trigger a status change without having to leave the template showing the list of entries.
(Perhaps using external entries for this is an overkill).

Fri 9 Sep ’11
4:23pm

Jaxbulls

I’m having an issue with the latest version on EE 1.7.1 where {if no_results}text{/if} does not work if there are no results.

It also breaks any other other {if no_results} within a {exp:external_entries:select} to not work.

I have an exp:query inside of an external_entries:select and the exp:query cannot display no_results but if i move it outside of the exp:external_entries:select it works again.

Is there any way this could be fixed?

Mon 12 Sep ’11
7:38pm

Adam Khan

icebreaker, thanks for the heads-up, link to Aaron Lax’s article removed. Casey, Kurt, Jaxbulls, I’m coming…

Sun 18 Sep ’11
8:44pm

Dmitry

hi Adam! Thanks for the awesome work!
My problem is that i am getting questions marks when trying to store russian characters in DB. I would usually set mysql_set_charset(‘utf8’);  in php, that would solve it.. But i’m lost what should i do within exp:external_entries:select tag. Can you help?

Sun 18 Sep ’11
9:34pm

Adam Khan

Hi Dmitry, thanks for the kind words.

Can you set the plugin’s allow_php parameter to “yes” then do what you usually do in PHP? Does that work?

Mon 19 Sep ’11
8:31am

Dmitry

Thanks Adam, i tried this with different syntax, but no luck so far..

Fri 28 Oct ’11
7:53pm

Gavin

Adam, I was wondering how best to exclude table values. One of the columns who’s values I want to display is an “Initials” column, but during testing a whole lot of “TEST"s were entered.  My various attempts at this using various combinations of != have proved to be failures. Advice?

Sat 10 Dec ’11
6:35pm

David Slattne

Hi!

EE has defenitely got some very nice and needed updates on the filemaneger and Assets is also a very sweet eh… well ‘asset’ for handling files and images.
The only working way of having people upload images is the let them batchupload but after that I defenitely want to be able to treat each and every image as if they were separetely posted as entries.
This is what I hope for External_entries to help me with. Every time the filemanager gets synced thera are new rows in the exp_files table. Listing the contents of the file_names column was not very hard following your examples.
But now I would like to have External_entries automatically create a new entry in for each file_name just listed. And this is where my smartlessness hits my coding in all its glory.
Is there a way to accomplish this you think?

And if so… How do I avoid reduntant imports next time this template loads. Should I have it take note of the highest file_id from the exp_files table and keep it until next time?

/regards David S, Sweden

Sat 7 Jan ’12
1:21pm

Ruslan Ka

Hi Adam, just want to clarify few points before buying the plugin.
Will I be able to go through the entries of the particular channel and change particular value in all of them ?
And will you be able to assist me with syntax for it?

Sat 7 Jan ’12
5:09pm

Adam Khan

Hi Ruslan, yes and yes.

Gavin and David, apologies, I don’t think I saw these posts at the time.

Gavin, I’d suggest maybe using the plugin to gather all the entry_ids of entries with that particular value, then pass that string of entry_ids to another template (or the same template if you’re using Stash), where you can use the plugin again to delete them.

David, in my smartlessness I’m not sure if I fully understand what you’re trying to pull off here.  Maybe keep the file_id in a field in the entries that External Entries creates, so you can compare it next time and only create the entry if the value is bigger?

Remember you’ll need to do two inserts to create an entry, first into the exp_channel_titles table then into the exp_channel_data table. There’s a risk I think creating an EE entry via External Entries that their entry_id values may get out of synch.

Sun 8 Jan ’12
7:49am

redkeycode

Can you provide just one simple example where (using EE 2x) I just allow the user to submit a variable and I use that variable in my SQL.  So on 1st page they have a form box that says, enter a color.  hit submit - with that color the DB sql (custom table) gets back (select hat where color = “user input”)

Mon 9 Jan ’12
2:17am

Adam Khan

Redkeycode, example added in the Documentation. See “As an Ajax Form”. Your scenario is even simpler than the example, as there’s no insert, just the select.

Mon 9 Jan ’12
3:39am

redkeycode

Sweet - thanks for quick reply!

Mon 9 Jan ’12
3:58am

Adam Khan

Redkeycode, let me know if you have trouble getting it to do what you want it to. I’ve found this to be a major use for the plugin, so thanks for the nudge to provide an example.

Thu 2 Feb ’12
11:55pm

casey

I was curious if it’s possible to submit queries via json as parameters?

Fri 3 Feb ’12
12:30am

Adam Khan

Hi Casey,

Not. Can you give an example how you’d want that to work?

Mon 13 Feb ’12
10:21pm

Marco Vazquez

Hello, I’m having issues with the nesting options. Could you provide an example of how to use a field from the first query as a variable for the next query?
Hope I can explain myself:
{exp:external_entries:select parse=“inward”
  // Data for the server, tableA
  search:fieldA=“6”
  limit=“5”
}

{exp:external_entries:select parse=“inward”
  // Data for the server, tableB
  search:fieldB=”{idFieldTableA}”
  limit=“1”
}

{/exp:external_entries:select} 

{/exp:external_entries:select} 

The idea is to get the items from a Table and each one of them, has an Image that is assigned on another Table using the Item ID .  Something like a JOIN.

What is happening is that the system is able to read the variables for each item but when displaying the info is using the value from only the first Item found.
Please, any help will be great!

Mon 13 Feb ’12
10:35pm

Adam Khan

Marco, try parse=“inward” as a parameter in the outer call of the plugin.

Mon 13 Feb ’12
11:45pm

Marco Vazquez

Hi Adam,
I’m using inward in both calls.  Should it be only on the outer call? I have tried both combinations.

Thanks

Tue 14 Feb ’12
12:26am

Adam Khan

Marco, I guess it shouldn’t matter if both have it or only the outer one. Are you willing to put the second one in an embedded template?

Tue 14 Feb ’12
12:34am

Marco Vazquez

I’m a newbie and haven’t used embedded template but I will try in a few.  I guess that the process will be more complicated as I need also to create a pagination system for the results of the query…  And here, I think it will require the declaration of some global variables….

The funny thing is that I used php to hold the variable and when I display just the variable it shows the # that should be.  As soon as I use the variable as part of the query, it shows only the value of the first result of the outer call.

Also, tried a if conditional for other values and it only uses the value from the first item.

Tue 14 Feb ’12
12:45am

Adam Khan

Yes, parsing order is complicated. I’m realizing it can’t work because if we’re parsing the internal one first, it doesn’t have the value yet created by the external one!

Embedding isn’t complicated, but there is a performance price if done all over the place or repeatedly.

I would set the allow_php parameter to yes, stick some PHP inside the plugin to assign the PHP variable, then echo this PHP variable to create the value of the embed variable that you will use in the embedded template.

Tue 14 Feb ’12
1:00am

Marco Vazquez

This is what I have with PHP (still without using embedded templates)
—————————————————-
{exp:external_entries:select
  // Server info // Table A
  search:strItem_type=1
  limit=“15”
  parse=“inward”
}
<?php $propertyID = “{select:strItem_id}”; ?>

  {exp:external_entries:select allow_php=“yes”
  // Server info // Table B
  search:strUploads_itemid=”<?php   echo $propertyID; ?>”
  search:strUploads_type = “primary”
  parse=“inward”
}
<?php   echo $propertyID; ?>

{/exp:external_entries:select}
{/exp:external_entries:select}
—————————————————-
And $propertyID is showing the right value… weird isn’t? Or is there something that I don’t see…

Tue 14 Feb ’12
1:05am

Adam Khan

I don’t think that will work. Need to embed the inner one in a template. I’ll add to the roadmap finding some method to make this sort of thing easier. Embedding isn’t difficult, but it’s someting to minimize.

Tue 14 Feb ’12
6:20pm

Marco Vazquez

Thanks a Lot Adam,
I did what you suggested and It works without using php
—————————-
{exp:external_entries:select debug=“n”
  // Server / TableA
  search:strItem_type=“1”
  limit=“15”
  parse=“inward”
  allow_php=“yes”
}

{embed=“query/displayItems”
  queryid=”{select:strItem_id}”
  strItem_status=”{select:strItem_status}”
  strItem_title=”{select:strItem_title}”
  strItem_address=”{select:strItem_address}”
  strItem_city=”{select:strItem_city}”
  strItem_state=”{select:strItem_state}”

——————————————-
In query/displayItems:

{exp:external_entries:select allow_php=“yes” debug=“n”
    // Server / TableB
  search:strUploads_itemid=”{embed:queryid}”
  search:strUploads_type = “primary”
  limit=“1”
  parse=“inward”
}

{embed:strItem_title}
{embed:strItem_address}
{embed:strItem_city}
{embed:strItem_state}

{/exp:external_entries:select} 


Now, I need to figure it out how to control pagination but this thing was starting to make me think that the Force is not longer with me…

Thanks a lot!

Tue 14 Feb ’12
6:45pm

Adam Khan

Cool, I didn’t think embeds could be placed directly within the plugin. Re pagination, yes the plugin is not strong with that. Possibly another feature for the roadmap.

Tue 28 Feb ’12
1:09pm

Tahir

Hi Adam,
Just downloaded External Entries and gave it a try.  I just tried to close the status of an entry using above example code.  The Plugin seems to have done its magic but if I go to Edit Entries in the back-end and open the entry , the Status field is blank whereas EE exp_channel_entries table reflects the change as expected.  Need some light on this.  I badly need this plugin and have already started to experiment.  Any response would be highly appreciated.

Thanks for such a great addon.

Thu 8 Mar ’12
10:57pm

Marco Vazquez

Hello again,

I’m trying to sort the query by Random but i’m getting a warning for a variable that doesn’t exist: $sort_array.
I’m using:
orderby=“RAND ()”

and the line that shows the warning:
$sql .= $orderby_array[$key] . ” ” . $sort_array[$key] . “, “;

For now, it’s ok because I modified the code by commenting out the variable part:
$sql .= $orderby_array[$key] ; //. ” ” . $sort_array[$key] . “, “;

Any ideas?

Thanks

Fri 9 Mar ’12
12:03am

Adam Khan

Marco,

Thanks for pointing that out. Fixed now in v2.5.2—use the EE-style “random” value.

Fri 9 Mar ’12
12:08am

Adam Khan

Tahir, you double-poster you. I answered over in the plugin’s Devot-ee forum.

Mon 26 Mar ’12
12:02am

graphicmachine

I have been working with the following code to update all the records that a member has made to reflect their latest info.

{exp:user:edit return=“site/updatemain”}
     
{exp:external_entries:update table=“exp_channel_titles” search:field_id_4=”{member_id}” debug=“y”}
{update:field_id_4}{grantee_number_a}{/update:field_id_4}

{/exp:external_entries:update}

There is a permission error that is being thrown though which prevents the update from happening.
This is for a person within the member group.
Any suggestions?

Thu 29 Mar ’12
4:07pm

pesto9

Looking at using this plugin for a project, was wondering if it could be used to synchronize channels? We need to pull a .csv into a staging channel with DataGrab, then either add new items or update product quantity, etc info based on SKU if item already exists.

Sat 31 Mar ’12
1:34pm

Adam Khan

graphicmachine, not sure, what is throwing up the permission error, User? EE itself? What’s the precise message?

Sat 31 Mar ’12
1:36pm

Adam Khan

pesto9, keep in mind it’s a plugin, so is called only by a visit to a URL—there’s nothing in the cpanel. If that is how these additions or updates will be called, then yes, External Entries sounds appropriate.

Sat 31 Mar ’12
2:51pm

graphicmachine

Here is the error I received when trying to run the script.

The following errors were encountered
You are not authorized to perform this action

Thu 5 Apr ’12
4:26pm

Marco Vazquez

Hello everyone,
I’m probably missing it from the previous posts but:
- How can you search in a range using the plugin?

SELECT * from table where ( table.field1 >= 1 && table.field1 <=5)

Thanks!

Mon 23 Apr ’12
10:48pm

Marco Vazquez

Any suggestion?

Tue 24 Apr ’12
8:36pm

Marco Vazquez

Just in case if someone could find it useful…
Searching with a defined range :

Added a new parameter:
range=“SQL Range criteria”

In the external_entries.php file:
in the $params block of code:
$range = $DB->escape_str( $TMPL->fetch_param(‘range’) )

And in the Select () function, In the get and insert the search parameters block:
if (isset($range)){
  $sql .= $range;
}

I know is not elegant but is working.

Fri 18 May ’12
12:41pm

Adam Khan

Marco, thanks for posting, good idea, I’ll add it to the code for the next version.

Thu 24 May ’12
1:43pm

ChiefAlchemist

Wow Adam, this looks great so far. Makes ya wonder why it’s not in the EE core.

What can you tell me about this plugin and Ajax? Can I use it write backend queries and such and then return the results to the front end? Or at least return a success message? Maybe this would be outside the scope of this plugin?

Fri 19 Oct ’12
8:08am

Paul Steven

Can you show me an example of the syntax for deleting a record from a table based on 2 column values.

I tried the following with no success

{exp:external_entries:delete table=“collections” search:id=“1” and search:libraryitemid=“18”  debug=“n”}

And I tried

{exp:external_entries:delete table=“collections” search:id=“1” and libraryitemid=“18”  debug=“n”}

Fri 5 Jul ’13
2:35pm

Jenny Harle

I’m trying out your plugin on my current project and it’s really handy. Getting the autoincrement on insert it a must-have for me, though. Any idea when that might be implemented?

Thu 22 Aug ’13
12:29pm

Stephan Bourdon

Hi,

I bought a copy of external entries on 8/8. It has been working flawlessly for until today, where suddenly the following error appeared:

A PHP Error was encountered
Severity: Warning
Message: Missing argument 2 for External_entries::_insert_filters(), called in /opt/www/independent/web/www.indikeit.com/system/expressionengine/third_party/external_entries/pi.external_entries.php on line 721 and defined
Filename: external_entries/pi.external_entries.php
Line Number: 136

Can you give any suggestions on how to fix this?

Thanks in advance and Best Regards,


stef

Thu 3 Oct ’13
11:08am

johnny

HI Adan

We are using your plugin to connect to Sugar and Integrate to EE and it’s working much faster than SugarEE plugin made just for that.

Do you have any code sample or where we can start to look to paginate results from the result of a query ?


Thanks

Thu 3 Oct ’13
11:31am

johnny

Hi Adan

Also, any way we can cache the results of a query?


Thanks

Fri 4 Oct ’13
11:39am

Johnny

Hi Adam,

Your add-on runs very well. But we have a issue when trying a {exp:external_entries:insert}.
We have a EE template contains php code and then embed this template in {exp:external_entries:select} so it runs well.

But if we embed this template in {exp:external_entries:insert}, it didn’t run. Just so only EE embed tag.

Could you help us resolve this issue!

Thanks

Mon 14 Jul ’14
1:52pm

TiaSms4

I am interested in this topic and would like to find out some more information as my friend need information on this topic. Do you have any other articles about this?
buy likes on instagram

Sun 27 Jul ’14
7:24pm

DNkRockzzzzZ

Interesting Plugin. What do you think, will it run on my page?
Casino Online Geld verdienen

Wed 30 Jul ’14
8:03pm

lily prichard

Hi I would like to install this on my blog oldwivestail , do you offer discounts for new site?

Sat 2 Aug ’14
3:27pm

JhonLewontin

I found this post very exciting. I think you will have any other post on this topic? I am also sending it to my friend to enjoy your working style. Cheers!
http://www.google.com/images/

Thu 14 Aug ’14
3:58pm

ChrisChivas

Will this work on my websites:
cheapoutdoorovens
whiskeyreviews

Do you offer a rate for installation?

Post a comment

Name:

Email:

Location:

URL:

Your comment:

Please answer the following question to assure your intentions are honorable: Enter the last name of a famous silent-movie era comedian named Charlie. (7 characters required)

Remember my personal information
Notify me of follow-up comments?