Title: Database Create View
Last modified: August 21, 2016

---

# Database Create View

 *  Resolved [SpikeTheLobster](https://wordpress.org/support/users/spikethelobster/)
 * (@spikethelobster)
 * [12 years, 10 months ago](https://wordpress.org/support/topic/database-create-view-1/)
 * I’d like to create a new view in my database but I can’t seem to do it actively,
   through a php function. I can’t find any info on doing this, though there’s plenty
   on making tables. The only support thread I saw on it was a year old and had 
   no responses.
 * I’ve tried using the table creation functionality ($sql = “blahblah”, require_once
   the upgrade.php file and dbDelta($sql)), but that doesn’t achieve anything.
 * I even tried running the view creation SQL as a query using get_results($sql)
   but that didn’t work.
 * Is it possible to create a view via a function and, if so, how does one go about
   it? I can do it through phpMyAdmin if I have to, but I’d really rather do it 
   dynamically so that it handles itself as part of my custom theme.
 * Thanks in advance.

Viewing 9 replies - 1 through 9 (of 9 total)

 *  Moderator [bcworkz](https://wordpress.org/support/users/bcworkz/)
 * (@bcworkz)
 * [12 years, 10 months ago](https://wordpress.org/support/topic/database-create-view-1/#post-3991705)
 * I’m not sure what you’re trying to view. I know all the various tables in the
   backend are built by extending the WP_List_Table class. See the various source
   files in wp-admin/includes/class-wp-*-list-table.php for how this is done. I 
   hope this helps.
 * If not, perhaps try describing more specifically what you are trying to do.
 *  Thread Starter [SpikeTheLobster](https://wordpress.org/support/users/spikethelobster/)
 * (@spikethelobster)
 * [12 years, 10 months ago](https://wordpress.org/support/topic/database-create-view-1/#post-3991728)
 * I’m not trying to view anything – I’m trying to create a database view in much
   the same way as creating a new database table.
 * This is specifically to summarise (dynamically) the data stored by a plugin so
   that I don’t have to search through hundreds of thousands of rows and do a SUM()
   as part of a complex, custom SQL query.
 * I had hoped that I could use the functionality that plugins use to create a new
   table in the database (with dbDelta) but it doesn’t seem to work for views.
 *  Moderator [bcworkz](https://wordpress.org/support/users/bcworkz/)
 * (@bcworkz)
 * [12 years, 10 months ago](https://wordpress.org/support/topic/database-create-view-1/#post-3991767)
 * I think I understand what you’re trying to do now, I don’t think it’s possible
   to leverage that functionality in the way you are expecting, but I’m not totally
   sure.
 * I am pretty sure you can generate a view similar to what you envision by using
   WP_List_Table. There is a learning curve in using it, but once you get how it
   works, it’s a powerful tool. You still end up with a complex query, but it’s 
   built dynamically in the `prepare_items()` method based on the state of various
   form elements.
 * The `display()` method is then used to display the results of the query in table
   form. This all typically appears in the WP backend, but I see no reason it would
   not work on the frontend as well if need be.
 * If this is not close to what you are seeking, I’m afraid I just don’t get it 
   and I’m sorry to take up your time.
 *  Thread Starter [SpikeTheLobster](https://wordpress.org/support/users/spikethelobster/)
 * (@spikethelobster)
 * [12 years, 10 months ago](https://wordpress.org/support/topic/database-create-view-1/#post-3991781)
 * I’m not familiar with WP_List_Table, but going on the text at the top of class-
   wp-list-table.php, it is a “Base class for displaying a list of items in an ajaxified
   HTML table.”
 * If my assumption is correct – that it is a class to do things like building alternate
   post list pages on the Dashboard (etc.) – then that’s not what I’m trying to 
   do.
 * I may be completely wrong, of course, but if it’s a complex query it’ll still
   suck even if it would do what I want – without a summary database view, the query
   is so heavy it effectively shuts down the site while it runs… for 2+ minutes!
 * I’m trying to build a _database_ view, behind the scenes in the MySQL database.
   In other words, I’m trying to run SQL like this:
 * CREATE ALGORITHM = UNDEFINED VIEW wp_wti_totals ( post_id, value )
    AS SELECT
   post_id, SUM( value ) FROM wp_wti_like_post GROUP BY post_id
 * But dynamically, with the correct DB prefixes and stuff. I was trying to do it
   with dbDelta, as shown below, but it doesn’t work (presumably ‘cos it’s a view,
   not a table):
 *     ```
       $table_name = "{$wpdb->prefix}wti_totals";
       if($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {
       	$sql = "CREATE ALGORITHM = UNDEFINED VIEW {$wpdb->prefix}wti_totals ( post_id, value )
       		AS SELECT post_id, SUM( value )
       		FROM {$wpdb->prefix}wti_like_post
       		GROUP BY post_id" ;
       	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
       	dbDelta( $sql );
       }
       ```
   
 * Hope that makes it clearer.
 *  Moderator [bcworkz](https://wordpress.org/support/users/bcworkz/)
 * (@bcworkz)
 * [12 years, 10 months ago](https://wordpress.org/support/topic/database-create-view-1/#post-3991809)
 * Ah! I get it now! Sorry for being so dense. I’m a dunce when it comes to SQL,
   so I’ll be no help at all. This really has nothing to do with WordPress. Perhaps
   you can find a mySQL forum where people actually know something about this? Or
   simply try stackexchange, lots of knowledge there too. Sorry I couldn’t help,
   good luck on your quest.
 *  Thread Starter [SpikeTheLobster](https://wordpress.org/support/users/spikethelobster/)
 * (@spikethelobster)
 * [12 years, 10 months ago](https://wordpress.org/support/topic/database-create-view-1/#post-3991812)
 * Not dense at all – it was good of you to try to help with what you thought I 
   wanted!
 * Unfortunately, this is very much a WP issue: the SQL itself is perfectly acceptable…
   WP just won’t run it or needs some special prodding to do so, which is the problem
   I have. It will run custom queries, no problem; it will create tables (apparently,
   though I haven’t tried it); it just won’t build a view. Pffft!
 * Thanks again for trying to help. 😉
 *  Moderator [bcworkz](https://wordpress.org/support/users/bcworkz/)
 * (@bcworkz)
 * [12 years, 10 months ago](https://wordpress.org/support/topic/database-create-view-1/#post-3991828)
 * I’m at least a little slow, I didn’t realize dbDelta was a WP function, thus 
   my claim of not being a WP issue. Out of curiosity, I reviewed the function’s
   source code. It’s clear that it only accepts certain types of queries and ignores
   the rest. Your’s is one of those ignored, so dbDelta is a dead end for you.
 * Have you tried using $wpdb->query()? Other than some housekeeping, it essentially
   just passes your query straight through to mysql_query() without the need for
   you to setup a DB connection. Maybe I’m missing something but I would think any
   acceptable SQL should be able to be passed this way successfully. This is the
   method ultimately called by dbDelta() when it’s done doing whatever it is it 
   does.
 * If I may ask, what’s so special about dbDelta? I see how it’s useful to core 
   WP developers to script DB upgrades, beyond that, I don’t really understand what
   it’s doing that’s so important.
 *  Thread Starter [SpikeTheLobster](https://wordpress.org/support/users/spikethelobster/)
 * (@spikethelobster)
 * [12 years, 9 months ago](https://wordpress.org/support/topic/database-create-view-1/#post-3991873)
 * Sorry for the long wait (only a week, feels like a month!).
 * First things first: dbDelta was just because I found an article stating it was
   the standard way to create a table, so I figured it would be the same for a view.
   I didn’t check much beyond there when it didn’t work!
 * The $wpdb->query() suggestion works, as long as all the {$wpdb->prefix} tags 
   are in place (I had them as just “$wpdb” originally, which was causing even more
   problems), so a big THANK YOU for that suggestion.
 * The simplest solution turned out to be the one that works. 🙂
 *  Thread Starter [SpikeTheLobster](https://wordpress.org/support/users/spikethelobster/)
 * (@spikethelobster)
 * [12 years, 9 months ago](https://wordpress.org/support/topic/database-create-view-1/#post-3991874)
 * Oh, and here’s the final code snippet:
 *     ```
       $table_name = "{$wpdb->prefix}wti_totals";
       if($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {
       	$sql = "CREATE VIEW {$wpdb->prefix}wti_totals ( post_id, value )
       		    AS SELECT post_id, SUM( value )
       		FROM {$wpdb->prefix}wti_like_post
       		GROUP BY post_id" ;
       	$result = $wpdb->query($sql);
       }
       ```
   

Viewing 9 replies - 1 through 9 (of 9 total)

The topic ‘Database Create View’ is closed to new replies.

## Tags

 * [function](https://wordpress.org/support/topic-tag/function/)

 * In: [Hacks](https://wordpress.org/support/forum/plugins-and-hacks/hacks/)
 * 9 replies
 * 2 participants
 * Last reply from: [SpikeTheLobster](https://wordpress.org/support/users/spikethelobster/)
 * Last activity: [12 years, 9 months ago](https://wordpress.org/support/topic/database-create-view-1/#post-3991874)
 * Status: resolved

## Topics

### Topics with no replies

### Non-support topics

### Resolved topics

### Unresolved topics

### All topics