External database access, the right way? (and a couple of other things)

[pnikosis]

Hi there, first the compliments:

I found Processwire about a week ago (where have you been all this time PW?), and I found it mind blowing, basically because how easy is to create a site. I had my homepage made with Symphony CMS, and it was fun to develop with, but XSLT still gives me headaches (even I still have love for Symphony and its simple approach, and the power that XSLT gives). I've worked before with Drupal, Wordpress and some little experience with Joomla!, and always found myself programming hacks to make the CMS do exactly what I wanted.

The I found Processwire and it took me less than three days to learn and migrate my homepage. And I love it. I'll buy the cache module even if I don't really need it, just for the support, Ryan's work here is simply amazing. So now I'm preparing myself to start to work with PW for future clients.

So here is my question, maybe a dumb one, but I need advice on how to approach this:

A client needs a frontend for managing some data stored in his database (separated from PW). They manage their own users and even an authentication system by their own from their portal. So probably the pages I will have to create within PW are the needed admin sections, and then make internally calls to their database. So here's where I need some advice, to make it the correct PW way: Should I use mysqli_query directly in the file template to obtain the data, or is there a method in the API to query external databases? Also I could create pages directly from the queries (let's say, I make a query returning 5 integer values, and then create these values automatically in pages, so I can have them directly in PW, but I'm not quite comfortable with that because redundancy).

Also the authentification system, is there a way I could integrate their authentification? I was thinking on adding a request in the head.inc to see if the users is authenticated, and if not, redirect them accordinlgy.

So there are the questions, sorry if they seem dumb (or with obvious answers), but I'm just diving into PW, and I want to do it right.

Cheers

[kongondo]

Hi pnikosis,

Welcome to PW and to the forums! Yes, PW does give that Eureka! feeling

I'm heading out so will answer your questions briefly. Others will chip in am sure.

These topics might be helpful

http://processwire.com/talk/topic/1684-reading-and-displaying-data-from-a-custom-table/

http://processwire.com/talk/topic/2242-interact-with-external-database-mysql/

http://processwire.com/talk/topic/669-using-a-secondary-database/

ProcessWire has its own database class that you can use if you wanted to. You will see (and will hear) that if at all possible, move over the data in the external database to PW as pages. However, if that can't be done, then of course, you can use an external db with db class or direct SQL queries. BTW, internally, near future versions of PW will use PDO.

[pnikosis]

Thanks kongondo! It looks a lot simpler than I thought (like almost everything in PW, still flipping out about the awesomeness).

I think I will go for the Database class as Ryan suggested in one of the posts, for now

[ryan]

Welcome @pnikosis, glad that you are enjoying ProcessWire! If you've got an external database that is still being updated than I would just query the external database rather than trying to load it into PW. On the other hand, if the database is no longer being updated (meaning, you wouldn't have to maintain two versions) then moving it into PW might make sense. But for querying the external database, you can use mysqli, but you might also want to look at switching to the PW dev branch (future version 2.4) which switches to the PDO database driver. Mysqli is still available in 2.4, so you can actually use whichever you prefer. Though mysqli will probably be dropped once we reach ProcessWire 3.0, but if it's installed in your PHP then of course it remains available for you. So if you use mysqli, probably it's better to use PHP's "mysqli" class rather than ProcessWire's extension of it, just for future compatibility. 

[pnikosis]

Thanks Ryan! It looks that PDO is the safest bet for assuring future compatibility (the external database is being updated frequently). Is 2.4/dev branch stable? (no need to be production-like stable for now, not until a couple of months from now), if so, I think I'll move to 2.4 and use PDO (and help hunting some bugs in the dev branch, I guess!).

[ryan]

Thanks Ryan! It looks that PDO is the safest bet for assuring future compatibility (the external database is being updated frequently).

Either mysqli or PDO is equally stable so long as you use PHP's version rather than ProcessWire's version. ProcessWire has the WireDatabase class for mysqli and WireDatabasePDO class for PDO. They are primarily just wrapper classes used for tracking queries, so you shouldn't feel like you have to use them. 

Is 2.4/dev branch stable? (no need to be production-like stable for now, not until a couple of months from now), if so, I think I'll move to 2.4 and use PDO (and help hunting some bugs in the dev branch, I guess!).

Yes, it's generally pretty stable, and it's what I use for any site I'm working on. The only thing I'd advise is just keeping more of an eye out for issues and reporting them if you see any. You also want to test thoroughly every time you update to a new commit in the dev branch. Whereas on the master branch, you can assume that what's there has already made it through dev branch testing (i.e. had more people using it before you did).