Calling MySQL database tables in WordPress

Hi,

In the most basic terms, to call data from the database there is a list of functions you can use, these functions do something and will in most cases retrieve specific data from the database and display it, check out this page that lists WordPress functions, each function says what it does. Then you might want to watch a video on how to use these functions that explains exactly where to place the functions into your theme or plugin code.

Check out this YouTube video, it is a tutorial on how to output the author name onto the post, it also shows you how to output the category of the post your viewing on to the page or post. Hopefully it gives you the general idea and you can go from there.

This image of the WordPress database might also be useful to get a idea of what info WordPress is storing by default and that you might want to get to. Remember you can also create and store you own custom data using Custom Fields.

Thanks Julian for the quick response.

I created a Microsoft Access database of songs, lyrics, references and links to images. I uploaded these tables to WordPress. I am trying to create pages with WordPress that link directly to the tables to display the contents and grant specific users ability to edit the tables from the linked webpages.

You are welcome,

It sounds like you want to create a Custom Post Type. First one would need to ‘program’ WordPress to create the tables in the structure that WordPress works in. Its more common to use the WordPress interface to input data, whether you mass import it or enter each entry manually.

WordPress can hold and display many different types of content. A single item of such a content is generally called a post, although post is also a specific post type. Internally, all the post types are stored in the same place, in the wp_posts database table, but are differentiated by a column called post_type.

– WP Codex – Post Types

So basically you would want to create a ‘Song’ custom post type and attached to each post type would be some custom fields for things like: lyrics, references and bio. All WordPress post types can by default have images attached to them via a link or by uploading directly.

I recommend you take a look at this treehouse overview video on creating custom post types, and then do some googling, don’t hesitate to come back here and ask some more questions.

I’ll tag this article and hopefully a more experienced WordPress developer can explain clearly and precisely what you need to do based on where you’re coming from. I’m still learning WP too, but you also sound like you have some experience with developing.

??

I’ll tag this article

Yes, please don’t. ??

*Drinks more coffee*

The modlook tag is for reporting abuse, spam, Amazingly Bad Trolls? and other abuse. It is not for escalating topics and abusing that tag can get you into trouble.

Which you are not in (trouble that is), just please don’t use that tag that way. ??

Also while I’ve not tried it myself mysql calls in WordPress outside of WordPress functions is just like another PHP/mysql integration.

What are you trying to call a database for? The reason I am asking is that there may be a WordPress function that can do the database lifting for you which may be simpler on your part.

Ok, sorry about that, I won’t abuse that tag ?? hope that coffee is good.

It’s a song database that I’m compiling for further development.

I have a couple of tables already created with Microsoft Access
The different tables are linked together. In access, Forms are created to link to those tables, run queries, etc. I’m trying to make a similar web based form to view the tables, update, query etc.

I hope I explained this clearly enough

If you want to use WordPress, theres really no reason to use Microsoft Access as well.

What i was trying to explain to you before was, WordPress has its own set of commands for reading and writing to the database. There is no point in using Access and WordPress together, just choose one and stick with it.

Since u want to make a website out of this data it makes sense to use WordPress, so that means you’re going to have to use WordPress to create the tables, insert the data, and retrieve the data for users.

MS Access and WordPress don’t really need to or as far as i know can work together. MS Access is a database, WordPress uses MySQL which is a database. you don’t need two databases.

Sorry i miss read what you wrote, of course your not trying to use them together. ??

So yeh, from what you just said, are indicating that you want to create a custom post type ‘Song’ and then use custom meta data to store lyrics and artists etc in the ‘songs’. These post types and meta data are stored in the WordPress database tables and using WP functions as described above you can query them and display them and allow users to edit that data.

Also,

WordPress stores your data in a pre-defined set of tables, unlike MS Access where you have to define your own tables, so you will have to use the tables already defined in WP. check out the WP database structure in this image, when you create the custom post type ‘Songs’ all the titles of those songs will be stored in wp_posts, notice in the wp_posts table how it says ‘author’, that will be you, the person who created the ‘song’ post in WP, not the author of the song itself.

Now when you use WordPress to add lyrics to your songs, that data will be stored in wp_postmeta. Notice how wp_postmeta has a post_id column? that is the ID of the song post type and this how the lyrics are connected to the song title, Notice how wp_postmeta has a key and a value? when you create a song, WP will store meta with the key ‘lyrics’ and the value will be the lyrics of the song you are creating.

This is how WordPress will store data, and how that data will be organised into tables.

First one would have to set-up WordPress so that one could ‘create’ a song in input lyrics and artists using the WordPress interface.

After this step is complete, in your case, one would need to import your current tables into the appropriate tables defined in WP

Sorry for the late response.

I’ve looked around and couldn’t find how to create tables directly in wordpress. I saw plugins like ‘Edit Any Table’ ‘Ultimate Tables’ but don’t know how to import the data into those tables. I don’t mind creating another set of tables to replicate what I created in access, I just need to be able to get the data in the access tables into the new tables.

I am not really sure how to go about moving data from Access to MySQL exactly. You may need to go on over to stackexchange or stackoverflow or a different forum that can answer that specific question, this forum is specifically for WordPress (you are sort of asking a question outside of WordPress). To get you started here is a link i found:

Exporting Access Data to MySQL
https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-tools-with-access-export.html

And here is a video:

How to migrate ms access database to mysql server
https://www.youtube.com/watch?v=4u6ObJ8y6Vw

a stackoverflow forum question:
How to import an Access MDB format database to MySQL?
https://stackoverflow.com/questions/4809654/how-to-import-an-access-mdb-format-database-to-mysql

sorry i couldn’t help anymore than that.

I apologize for not getting to the point.

I’ve looked around and couldn’t find how to create tables directly in wordpress.

It is not necessary to create your own tables in WordPress, because we store all data in the existing tables, we generally do not create new ones. Your situation does not require its own set of tables.

I saw plugins like ‘Edit Any Table’ ‘Ultimate Tables’ but don’t know how to import the data into those tables.

The best way to import raw data into WordPress tables is to access the database directly using a different software named phpMyAdmin.

I don’t mind creating another set of tables to replicate what I created in access, I just need to be able to get the data in the access tables into the new tables.

As i mentioned, we do not create new tables, we only use the existing WordPress tables. Instead of creating new tables, to accomplish your task, you must create a custom post type called ‘Songs’. After you have created a custom post type called ‘Songs’ you would have to carefully import your raw data from Access into the specific tables being used by WordPress to manage your custom post type.

Plugins you can create custom post types with:

• https://www.ads-software.com/plugins/custom-post-type-ui/

A tutorial to get you started creating your Songs custom post type:

• https://www.youtube.com/watch?v=-r1b0wA98Ew

I can’t really explain it anymore than that, you will have to go ahead and start creating custom post types, and then look into the database to see where that data is being stored so you can understand it for yourself.