Opening a database [Archive]

Rapscallion

07-11-2007, 05:10 AM

volatile

07-11-2007, 05:31 AM

I run more than one forum. I moved one to this server last night, but the sql dump wasn't having anything to do with inserting into this server's database.

Question is, can I access the database and get posts out of there? If so, how? I need posts that were posted by one specific user.

Rapscallion

I do not know the specifics of how databases for a forum works, but I'm assuming it's the same as any other SQL database. You need to first find out how the database is structured. Find out what the field is that stores the poster's user name then run a SQL search query on that name and field. This is assuming you are using an SQL database.

It should be something along the lines of:

SELECT * FROM <Database_Name> WHERE <Username Column> = '<username>'

I do some development where I have to interact with databases so my knowledge is not at all complete. I hope that I read and understood your question correctly and that I may have answered...or at least given you an idea of where to go. If this wasn't what you were going after or already know this stuff, sorry I probably just misread the question.

Rapscallion

07-11-2007, 07:46 AM

Er, which program do you use to do the searching? I know it's SQL (Structured Query Language), but I don't know how to go into them.

Rapscallion

volatile

07-11-2007, 01:45 PM

Er, which program do you use to do the searching? I know it's SQL (Structured Query Language), but I don't know how to go into them.

Rapscallion

Unfortunately, that's where my knowledge ends. We have our own database architect that deals with that stuff and all I do is retrieve and store information into that database. My first guess would be to take a look at something like MySQL or MS SQL Server 2000. Btw, you the database is probably set up as a relational database so you may have to look across multiple tables via foreign keys to get the post information you want. Hopefully, someone on here who knows a little bit more about database programs can help you.

ringo

07-11-2007, 02:20 PM

vBulletin uses MySQL. There are a couple different ways you can get into the MySQL Database.

MySQL Query Browser will allow you to connect to a MySQL Database and run query strings on it. Information on it here: http://mysql.com/products/tools/query-browser/
The GUI Tool Downloads: http://dev.mysql.com/downloads/gui-tools/5.0.html

I don't know about the database structure to help. If I have time, I will try to see if I can find the database table structure.

LostMyMind

07-11-2007, 03:18 PM

Depending on how you connect to the database. MySQL has a software that allows you to browse the database, users, logs, etc... called "Query Browser" (ringo was nice enough to provide a link).

But since you're an admin of the database, I'm guessing you're using MySQL. You might want to look at the admin software that MySQL also provides.

computer_cowboy

07-11-2007, 03:23 PM

Most servers that will run mySQL will also have a program called phpMyAdmin running. You can usually get to it through the admin panel of the server. It lets you look at the tables in your web browser. I have found it helpful when I have run forum sites in the past. Here is the link for the site: phpMyAdmin (http://www.phpmyadmin.net/home_page/index.php)

Rapscallion

07-11-2007, 03:30 PM

I've downloaded the sql dump from the previous server. I need to search through that sql file. Any advice about applications for doing that on my own PC rather than the server?

I'll check out those applications above later when I get home. It was a phpBB2 board for reference.

Rapscallion

LostMyMind

07-11-2007, 03:41 PM

phpBB2, I believe will connect to either MySQL or SQL Server. But since it cost money for SQL Server and nothing for MySQL. I'm going to guess they used MySQL. :lol:

What you want to do is export from the database to a file on your computer. I'm not sure if Query Browser will do that. It's been a while since I've messed with MySQL. You'll need a database software on your computer that can accept the data (as well as save it into a file database).

Rapscallion

07-11-2007, 05:22 PM

I already have the database sat in a file on my computer. What I need is the name of a database software (free for preference) to open it up and have a good browse. Suggestions, please.

Rapscallion

draggar

07-11-2007, 07:21 PM

You should be able to open up the database dump though Excel.

As for accessing it, if you have a LAMP or WAMP server, try getting phpMyAdmin running on it and hop onto it from another computer. phpMyAdmin is a very easy MySQL management program.

Was the other forum also vBulliten?

Most databases should accept other's unless your hosting company does not allow importing a database (unfortunately, like mine).

*Edit*

Duh, if you have a profiessional version of MS Office, try Access. It's crude, but may get the job done. If not, Excel should be able to do it. Too bad php can't be run locally. ;)

*Edit part deux*

Rap, I'm booting up my other PC right now, if you have any of these, send me a yell:

AOLIM: Draggardevir
ICQ - 2096329 (Draggar Devir)
MSN: draggardevir at hot mail dot com(I'm trying to remember my password for this one)

It's only 9pm your time, you should still be up. ;)

draggar

07-12-2007, 02:29 AM

I'm going to bed soon but I'll be online most of the day tomorrow, send me an IM from the list above, even if I'm away it will go though and I can respond when I am up and available.

Pedersen

07-12-2007, 02:43 AM

Heya Raps, here's my advice: Download MySQL from here : http://www.mysql.com/ and make sure to download the latest version. It's free, and can be run on Windows.

After doing that, there's a set of gui tools available in the bin directory of the installation (though I am unable to remember the names, and don't have a Windows machine to look at them). Just double-click until you see something come up on screen.

Once there, you can restore the database dump. After that, you can get at the data in any number of ways, though if you prefer local access, I would recommend installing the MySQL ODBC COnnector, and using MS Access to get at it.

You can also try to use SQLite (http://www.sqlite.org/ ), and that will likely work, but may be harder to use, and I have less advice on how to do so.

Hope that helps somewhat.

Rapscallion

07-12-2007, 04:34 AM

Actually, I found that opening it in wordpad gets some results - it's not pretty or user-friendly, but I can search for most of the stuff I'm looking for.

I think.

I'll get back to you.

Rapscallion

draggar

07-12-2007, 12:38 PM

Not bad, if messy. :)

Excel you can filter the rows so you can filter them by the user# (you'll have to go into the user part of the DB to get that)

LostMyMind

07-12-2007, 02:19 PM

ringo

07-12-2007, 03:14 PM

Can you post a couple of lines of the file you have?

draggar

07-12-2007, 03:41 PM

Well, for free. Get openoffice. It has a database program that like Access. It will allow you to connect to the server or open a database file. However, since you didn't say what format the database file was saved as.

Since he's running phpBB (like all of my communities) on that forum, my guess would be MySQL v. 3.0 or 4.0

Latest is 5.0 but I don't think phpBB supports that.

I may do a dump of one of mine to play with the code to give Rap some tips, Access isn't as easy to use as Excel. :)

*edit* I'm looking at the databse exported as an Excel file but I do not see where it links a poster to the topic. Hmmm... I see the system generated alphanumeric, which I think that is it but they're not constant.

LostMyMind

07-13-2007, 06:29 PM

If it's a SQL command dump, then it's not formated, chopped, etc.... You really do have to "export". But if you have access to the server, just grab the files in the data folder of MySQL. Even through the extensions are not .dbf. It's dbase format and Access (sometimes excel) can open it directly (however, memo or unlimited text fields sometimes don't get access since only a few programs can read how MySQL does it properly).