Pointers for using PDO

[muzzer]

One thing I love about PW is the built in variables ($pages etc) and selectors/methods which allow easy accessing of page data in the database. Another is the beautiful API which allows easy manipulation of page data. It's damn awesome I have to say. Unparalleled in any other system I've used.

But there are times when I want to use a custom table rather than pages, for example, collecting stats or logging changes. One tourism site I have collects info on every booking/enquiry made from a tourism operators page, another logs every alteration a client makes to their particular page, and these tables may have several hundred thousand rows. I'm thinking a PW page representing every row is not ideal for this, and a custom table is more suitable. Please, shoot me down in flames if I'm wrong in this assumption - I would like to be wrong about this!

Coming from a modx background: One thing about modx is that because it didn't have this type of system for selectors and sweet API you were forced to use more basic techniques for accessing the database. Eg. to select stats from my custom stats table relating to the site homepage and covering the last two days I might do this:

$handle = $modx->db->select( '*', 'pagestats', "page='homepage' AND hours='48'" );

Can one you you pw gurus offer some pointers on basic database access in PW 2.4 with PDO? How to connect, insert a row, update it, even select rows like the above query. Or is this something that's "just not done", and "what the hell man, just use pages!"

[diogo]

You can create your own databases and execute queries easily with PW. Here is an example from Martijn that I found with a quick search (there are many more in the forums) http://processwire.com/talk/topic/4691-can-i-use-my-own-database-table-in-processwire/?p=45770

That said, I thinks it's proven already that PW can handle that amount of pages, and using them instead of custom tables would give you the benefit of being able to use the API of course. I guess I'm not exactly saying "what the hell man, just use pages!", but you need to find a balance between performance and how easy it will be to work with that data, knowing that performance will be also dependent on the system your site will live in and that there are cool tools like pro-cache that could help you with that.

[muzzer]

Hey diogo, thanks for that,

but you need to find a balance between performance and how easy it will be to work with that data

This is it in a nutshell. I guess I'm kind of struggling to accept that pages could be used for such a thing as I've outlined above, and I'm sure I'm not the only ex-modx-er here feeling this, it just feels wrong. Under modx, using pages (or resources in modx-speak) for such a thing would be simply farcical. So accepting the idea of using pages in this fashion completely goes against the methods I've been using for years, and it's kinda doing my head in. Having said that, it is exciting to think PW can handle this, as like you say, it allows for the use of the API which is fantastic.

You're right, there are posts in the forum regarding custom tables, which I have now found using Google rather than the forum search. Lesson learned. Cheers

[Pete]

A Pw field is its own table in the database so don't worry about it

A lot of us usually set up a config page or something under the admin for things where you'll collect data with not so many fields. Of your example above requires dozens of fields of data then maybe a custom db table is the way to go, but honestly it's not a problem storing stuff as pages in Processwire - you get API access if you ever need to pull statistics and it's not adding overhead just having the pages simply exist I'm your site.

I've set up config pages before for anything from a list of countries and associated country codes and currencies (simple 3 field template to use as a select field on a checkout as well as currency lookups etc - you can use markup cache in the template so it only re-queries to build that list once a week (or far longer in this case as countries don't change that often). I've also created a config page for simply storing Yes and No as pages to use as radio buttons on a form - this felt the strangest but I knew I'd want to pull some stats later on and there were a few yes and no questions so I had a few fields like this.

The hardest part is un-learning stuff you've learned from other systems, plus the fact that every field is a table and that that's not as crazy as it sounds and certainly not as resource hungry as you might automatically assume (I know I did) as ryan has been very careful in how the system works and it works fast for many hundreds of thousand of pages and beyond - and that's when you're doing searches. For statistical data that just sits there and doesn't do much a lot of the time for example, well that's just like having untouched data sitting in your database - it's there albeit a bit more visible which is maybe why it bothers you

So yeah, for simple stuff you want to store like what you've posted, if it's not stacks of fields then use pages, but if not then your own db table is fine and it's easy enough to query it with some $database commands.

[muzzer]

OK, so I'm starting to gel with the idea of fields = tables, pages = collection of several fields (tables) etc,. As you say Pete, it's a matter of unlearning what I'm used to and looking at things from a slightly different perspective.

Using SQL I can select rows using "DISTINCT", for example if I have 100000 rows with different IP addresses, I can count all the distinct IP addresses in a table (or field) with a single SQL query in a split second. To my understanding to do this using the api and selectors I would need to foreach through every single page, which is mighty memory intensive and slow. Correct?

If so, I still have a need for direct sql queries, even if using pages rather than custom tables. I've searched for examples of how to do these and gather the $database var is used, but there don't seem to be any clear examples of connect, insert, select etc using PDO.Can someone point the way if such exist? Do I need to "connect" or is a connection already available, is this what $database represents?

I realise PDO is a recent addition so this maybe why I can't find an example of what I'm after. Any help appreciated

[diogo]

You are already connected. The example I gave above and an understanding of the db structure are all you need.

[SiNNuT]

Using SQL I can select rows using "DISTINCT", for example if I have
100000 rows with different IP addresses, I can count all the distinct IP
addresses in a table (or field) with a single SQL query in a split
second. To my understanding to do this using the api and selectors I
would need to foreach through every single page, which is mighty memory
intensive and slow. Correct?

Yes. 'distinct' and 'group by' like functionality can't really be done very efficiently using the API. So if, like it seems in this case, we're talking about lots of rows/pages/fields you probably want to use an SQL query instead of the API. A google search like "site:processwire.com/talk sql distinct group by" will give you some interesting threads on the subject like, http://processwire.com/talk/topic/2047-group-by/?hl=unique#entry19148 and http://processwire.com/talk/topic/2038-distinct-selector-in-processwire/

If so, I still have a need for direct sql queries, even if using
pages rather than custom tables. I've searched for examples of how to do
these and gather the $database var is used, but there don't seem to be
any clear examples of connect, insert, select etc using PDO.Can someone
point the way if such exist? Do I need to "connect" or is a connection
already available, is this what $database represents?

Yes, $database gives/creates a PDO instance from ProcessWire $config API variable, like this (from wire/core/WireDatabasePDO.php):

$database = new WireDatabasePDO($dsn, $username, $password, $driver_options);

So the connection part is easy, $database is always available. From there on you can just use a 'select distinct' query and PDO (such as the example Diogo mentioned). Just as well for inserts, deletes etc. I don't think this i specific to PW, it's just SQL/PDO

Of course when you use PW pages you could easily use the PW API to handle all the stuff it is really good at, and just use some direct SQL queries where you need it.

When using pages make sure the fields you connect to say a 'logentry' template are unique to that template. This is because a field table only contains a pages_id reference and it's data, and it would need a more complex query if the table contained data belonging to other templates.

Then there also is the option to create you own fieldtype/inputfield, let say FieldtypeLogentry. This way you can have one field hold as many columns of data you need and it would be only one database table. I haven't yet created a fieldtype/inputfield myself but i've heard say that it's not that hard.

Anyway, lots of options, just choose the best according to your needs and data(base)structure. If a custom table works best for you, go for it.

[Pete]

You've found one of the times where a custom table would be best

The connection is already done actually if you're using the same database as your ProcessWire install (may as well) - this way you can use the existing $database connection.

SiNNuT's example above should probably be $mydatabase to differentiateas that bit of code is for connecting to another database entirely.

PDO is basically a database abstraction layer (think my terminology is right there). The underlying database is still mySQL, but PDO means that the same queries could be used for many different types of database backend (Microsoft SQL server etc).

There's an example from StackOverflow here that I'll borrow from and amend to do a basic query using ProcessWire's $database: http://stackoverflow.com/questions/767026/how-can-i-properly-use-a-pdo-object-for-a-select-query

$query = $database->prepare("SELECT * from your_table where field_name = :name");
$query->execute(array(':name' => $name));
while ($row = $query->fetchAll()) {
    // Do something with your rows
}

The reason for preparing the select query with the :name placeholder (in this example) is that your variable (or string) for :name gets properly escaped to prevent SQL injection. It's a little more verbose, but it does the job nicely. I'm pretty sire you could just have done "WHERE field_name = $name" but the above is safer if you're not sure what someone might pass to that query.

For your counting requirements you might be able to do adapt the following query from this forum topic: http://processwire.com/talk/topic/3515-processwire-24-compatibility-for-modules/ ):

$query = $db->prepare("SELECT COUNT(*) FROM my_fake_table WHERE name=:name");
  $query->execute(array(":name" => $name));
  $count = $query->fetchColumn();

The queries are a bit different to regular mySQL, but there are a lot of PDO query examples out there - a quick Google search for PDO SELECT DISTINCT should get you started.

[muzzer]

Thanks you Sinnut and Pete for absolutely awesome responses - covered absolutely everything I was unsure of with beautiful clarity.

The prepared statements/PDO are new to me and there seems to be a lot of info on StackOverflow but oftentimes confuses rather than clarifies, but it's starting to make sense now. And of course now I can get my hands dirty thanks to the examples provided and hash around with PDO statements it should all become clear pretty quick I imagine.

Can't emphasize enough the appreciation for the time you PW gurus put into providing such detailed information for newer users. Oftentimes all thats needed is a straightforward explanation on something seemingly simple and then everything just clicks and we're away - exactly what you've provided. 

[SiNNuT]

I'm far from a guru (but pete probably is ) but glad to be of help sometimes. I'm sure you are more than capable of googling PDO, but i wanted to mention there are some good intro/tutorials around like this one http://code.tutsplus.com/tutorials/php-database-access-are-you-doing-it-correctly--net-25338

It's really not hard once you get the concept.

[muzzer]

Thx Sinnut, thats a great link. I think I've pretty much nailed PDO basics today using various tuts on the net but the nettuts one is one of the better ones. You're right, it's actually pretty straight-forward, just a slightly different way of thinking to what I'm used to. Will bookmark the link for reference. CHeers

[Pete]

Wouldn't go as far as guru, SiNNuT

The tutsplus link is great, better than anything I found. I thought it was a bit poor that PHP.net didn't have more examples in their docs, but this will do nicely as it explains the differences clearly with good examples.

I'm actually marking the answer with the link in as "best answer" purely because I think people would get the most benefit out of that page.