Back in the good old Drupal 6 days, if you ever had to integrate a generic SQL table with Views you would use Table Wizard. You could write custom code, but Table Wizard made it very easy to integrate tables without writing any code.
Once you have SQL tables exposed to Views a whole world of possibilities opens up. You can have “non-technical” site builders create complex queries using data from tables by simply creating a view. Currently there is no Drupal 7 version of Table Wizard because it was deprecated in favour of the Data module. The Data module offers more than just Views integration, but in this article we’ll focus on the Views integration part only.
We’ll also have a quick look at two modules called Schema Views and Views Schema, both allow you to integrate Schema API defined tables with Views, but in two different ways. It’s important to note that Schema Views and Views Schema module only allow you to integrate tables that have been defined using Drupal’s Schema API.
If you create a bunch of tables manually using phpMyAdmin or via the command line, then you have to use the Data module or write the integration yourself using hook_views_data.
How to Use the Data Module
As stated earlier, the Data module does a lot more than just Views integration. It offers a set of low level APIs that help you manage SQL tables and data within those tables. If you want to find out more about the Data module check out the project page.
Integrate Table using Data Module
1. Download and enable the Schema module.
2. Download the Data module and enable the Data and “Data Admin UI” module.
3. Go to Structure -> “Data tables” (admin/structure/data) and click on the “Adopt tables” tab.
Any tables that have been created manually by using phpMyAdmin or any other way should appear on this page.
4. Select the table and click on Adopt.
5. Once the table has been adopted if should appear on the Overview page.
6. If you now create a view, the table should appear within the Show drop-down list on the “Add new view” page.
How to Use the Schema based Modules
As discussed earlier, only use the Schema Views or Views Schema module if you want to integrate Schema API defined tables with Views.
Using Schema Views
The biggest difference between the two modules is that Schema Views generates scaffolding code that you must paste into a custom module.
I believe this method gives you the most flexibility, because you have full control over the code. Use the module to generate the boiler plate code and then modify it as required. Once you have copied the code you can uninstall the module as it’s no longer need.
The Schema Views configuration can be accessed by going to Structure -> Schema and click on the Views tab (admin/structure/schema/views).
Using Views Schema
The Views Schema module works slightly differently. It integrates Schema API defined tables at runtime using the hook_views_data hook. This has the benefit of not having to deal with custom modules and code so it’s a better solution for non developers.
The Views Schema configuration page can be accessed by going to Structure -> Views and click on “Views Schema modules” tab.
Select the tables you want and click on “Save configuration”.
If you go to the “Add new view” page, you should see the selected table within the Show drop-down list.
If you have any questions, please leave a comment.
Thanks for the great tutorial. This was very simple. I hope you can create a tutorial on using hook_views_data to make this a complete tut. I have a site and I am using Search API and Search DB modules. The index is really huge and it has an impact on the performance. Do you have any idea if I can use your approach to make things faster? Can I create a table with the data and use your above approach to create a faster search page? The number of records in the content type is about 500K, but after indexing, it gets up to millions. Your advice will be much appreciated.
I also noticed under “admin/structure/data” a link called ‘Create new table’. Can I use this to create a table, then import the date into that table and then starting following your instructions above.
Thanks again for your sharing your expertise with the community.
If you want to implement the hook_views_data, have a look at how it’s implemented for core tables. Look in the modules directory within Views.
Example:
http://api.drupal.org/api/views/modules%21aggregator.views.inc/function/aggregator_views_data/7
Or use the Schema Views module to build the scaffolding code. http://drupal.org/project/schema_views
If you need to import data into a table, then you can use the Data module or create a table directly into the database.
Now about the search index. If you’re dealing with 500k rows then you should seriously look at using an Apache Solr backend. Use Search API Solr module to integrate Solr with Search API (http://drupal.org/project/search_api_solr)
Hope this helps.
Thanks for sharing this great info.
Im currently doing a project for my study at the IT University of Copenhagen, where im trying to fetch all posts, comments and likes from a public facebook page (fan page), da do Social Media Analytics. – It’s heavy to create nodes for each data-row, so the Data module would be a nice alternative.
And if a can create the samt views from the Data table fields, as I can with Node fields, it would be great, as im creating visual graphs (types of posts, who post the most the most, most liked comment etc) with the google graph api.
Still struggling to adjust the Feeds-parser to do another fetch, if the Post-object contains a new dataset in the form of comments, but thats something I will eventually found out. – With this Data.moduel I should be able to do some more flexible solutions. Once again thank you for sharing!
/Steffen
Not trying to be negative, but I would be cautious about basing my site around a module that 2 years after D7 still haven’t got a stable release, and has 3 commits the last 6 months.
That is a valid point. As site builders/developers we need to take those things into consideration.
Thank you for your generosity in posting these tutorials. They have been quite helpful.
Thank you!
Thanks a lot!!
Hi Ivan,
This solution is great for single table. I am working on the website that requires to join custom tables or create a reference field to reference each other. Data from these tables needs to be mashed up into multiple displays. Is there a way to do this?
Thank you in advance.
I don’t know if the Data module supports joins. What I would do is use “Schema Views” to generate the scaffolding code for Views and write the join myself.
Look at the link below for an example.
http://drupalcode.org/project/views.git/blob/refs/heads/7.x-3.x:/modules/node.views.inc#l602
I want to koow how to display mysql data in phpmyadmin in drupal 7?After that we can search mysql data in drupal 7.I know that in drupal 6 ,table wizare and views modules can solve the question that mysql data display in drupal 6 content.
I’ve explained how to do that exact thing using the Data module in this article.
Hi Ivan,
Thank you for this tutorial. I followed it to adopt a table that I had added to the default site database and have successfully used that table in Views. So that’s been very helpful!
But I have another table that I want to use in more than one site in my multi-site installation. This means I need to have the table in a database that is separate from all the site databases so each site can access it.
In the “settings.php” file, the section on “Database settings” has detailed documentation on how to define additional databases that the site can work with. I have followed that to define an additional database that exists in MySQL. But the Data module does not recognize the table in that database in its list of “orphan tables” on the “Adopt Tables” page.
Is there a way to get Data to recognize tables in a database defined in “settings.php”? Or, aside from the Data module, is there any way to get Views to work with a table that is not in the site database — without writing my own module (which I’m not experienced enough yet to know how to do)?
Thanks for your help.
Sorry can’t help, I’ve never used it with an external database. It’ll be best to ask in the module’s issue queue.
In your screenshot, it shows “View Records” and “Edit View” links on the Overview page. For some reason, after following your directions, I don’t get those links. Also, the adopted table does not appear as an option when creating new views. Not sure what I am missing.
It’s been a long time since I’ve used the Data modue. I would recommend you ask in the module’s issue queue.