Database - CPanel

[Spunky]

A friend referred me to this forum knowing I've been struggling for quite a while unable to find proper help. She says the people here are excellent and quick to respond. I do hope I am posting this is the correct forum area.:-/

My problem is is that I have a website hosted and my host allows me to use CPanel to create databases. However, I am unsure how the heck to implement these databases in my website in any sort of way. I hope that someone here knows how to use CPanel and could help me with this issue because my website would be much more fantastic if only I could use databases. Please help me out.:(

[elamigosam]

what I had to do, after a long discussion with a tech suport that hardly speak engish that I have to add the username before the databse name..
example:

mysql_select_db("username_database"

hope this helps.

PS:
look some were else before calling costumer service.

[Spunky]

I know how to create the database as well as I created a user with a password for it. I've even added data to the database. I just dont know how to incorporate it into my created website.

[Spunky]

Ok. Let me explain my issue a little better.

I've used CPanel to create databases, I give them a name, I apply a user to them, and in PHPMyAdmin I can create tables and add data to the tables. Now, what I need to know, is how to get my website to search these databases? How do I show this data on my website according to what link is selected, so its pulled from the database what is shown?

[pritaeas]

You'll need a language to connect to them, for example PHP, Python, Ruby.

In PHP the code would look like this:

$db_link = @mysql_connect('localhost', 'youruser', 'yourpass') or die(mysql_error());
@mysql_select_db('yourdatabase') or die(mysql_error());
$query = 'SELECT * FROM yourtable';
$result = @mysql_query($query) or die(mysql_error());
if ($result)
{
  while ($row = mysql_fetch_assoc($result)) 
  {
    // use your data
  }
  mysql_free_result($result);
}
if ($db_link)
  mysql_close($db_link);

[Spunky]

Yea, I knew to use PHP, guess I should have said that. But thank you. That's a start. I don't know PHP but the best way to learn is hands on. So, I'm guessing all that connects me to the server, but then, what do I put in the "// use your data" area? Like, could you point me in the right direction of where to learn what goes there? I use w3schools to learn the web languages including PHP. However, all I seem to find on there is code that creates the database as well as insert data in it. But CPanel already does that..?

[elamigosam]

first of all you need a goal,
like me I have a site where you can add songs and lirics to it, so a user enter the site, selects its church and then sees a list of songs that his church plays, so the "//use your data" becomes title songs and authors name, and lirics.

if you have a goal already, tellus what is it that you want to do and that should help us determine what to show you.....

[Spunky]

Alright then. My website is a World of Warcraft Fansite. It isn't official yet and probably wont be for a very long time. But I am ok with that, this is a learning experience.

To start out with getting a database going I'm starting with the Achievements page. In the game you can earn different achievements by accomplishing certain tasks. There are hundreds of achievements and they are listed into different categories. I.E Quests, General, Exploration, Professions. Things like that. I want it to be like where you click on a link, say, Quests, and then it lists all of the Quest achievements out that are stored in the database, and all the info that is stored with it, like how many achievement points it awards and the description of how to accomplish it. I want to be able to make it cool looking though ofcourse, not just list it in a table. But I have to start out with the basics. I'd be exstatic just to get my info to show on my page from my database.

[pritaeas]

This will print just one column name:

while ($row = mysql_fetch_assoc($result)) 
{
    // use your data
    echo $row['column_name'] . '<br/>';
}

Play around with it first, and when you have new/more questions, post them.

In addition, it may be worthwhile to check out Smarty, to be able to separate your data from your layout. (smarty.net)

[Spunky]

Hey yea, that's actually what I tried doing with something that I found on w3schools. Except it was to show 2 column names at same time. But whatever. I couldn't get it to work. But I think I just figured it out. I put what you just told me to put in and it WORKED! OMG I love you! I have been trying to get data to show up for MONTHS! I just couldn't find the php that it took to connect to my database! Ahhhh this is a happy day.

Ok ok ok, so, how do I be hands on with my data? Like Right now it shows that one column when you go to that specific page. But, how do I stay on that same page and have a link clicked to show a different column? I'll try to figure it out myself now that I know the basics it will make it soo much easier. I want to get all the links set up first though before I try to make the data fancy.

Also another question. Is it possible to store images in a database? Can't figure it out in CPanel but it must be possible cuz I know that places that sell merchandise the images of them are stored in databases.

[Spunky]

I just want to add I guess I don't exactly have to have the data on the same page change..or maybe I want to I'm not sure. See, with most other fansites when you search for things you can specify your search and it will show what you want..well that might be a little more advanced but, the point is, it shows up on the same page no matter what you choose you want to search for. The only thing that changes is the content that shows up.

[pritaeas]

Glad to see you got it to work.

Images can be stored in a varbinary field, or you store the image on the server as file, and put only the filename as string in the db.

I'm not quite sure what you want to achieve with your links. Could you give a small example?

[Spunky]

Ok I see where to select the type to be Varbinary, but, how do I actually get an image in there?? Hehe.

Also ok umm. Well. At least to my knowledge the page doesn't really change but tell me if I am wrong and if it is possible to do what I want to do. I want when the main page loads it says something, like, explaining what the page is for. Then when you click on something, that text changes into the information that you clicked on. Small visual go to http://www.wowtah.com/achievements.php#main. Right now the data in there is what I chose to test with using your help. But, when the person first visits that page say in place of that data it's explaining what achievements are. Then when you click on the links off to the left (General, Quests, Exploration, all those) that data changes and shows you the achievements in that category. Is this possible? I mean, if I had to put each category on a new page then I don't understand the point in using a database for this. I thought to use one so that I didn't have to make a page for each one.

An example of something I am trying to achieve is go here: http://www.wowhead.com/?npcs=1 you will see it says npcs=1 in the url at the top yes? Well if you hover over the NPCs link that is above the shown data, (Database>NPCS>Beasts) You can select something other than Beasts by going to NPCs in the drop menu and clicking Critters. Now in the url shows instead of npcs=1 it shows npcs=8. The rest of the page did not change. Just the data inside it. Perhaps this is a different concept than what I am trying to achieve, though, eventually I will need to do this because I am going to store that sort of searchable data as well.

I hope I've made it clear what I am trying to do. I don't know a whole lot about how databases work so, thats why it's a little hard to explain haha.

Also I just tried creating a new table to test the Varbinary type field and, wasn't able to create it. Got error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL) ENGINE = MyISAM' at line 1. But I think it could be just cuz I am not filling in the other fields correctly. Like heres the SQL it showed for the fields I filled:

CREATE TABLE `wowtahc1_achievements`.`test` (
`Images` VARBINARY NOT NULL
) ENGINE = MYISAM

Is there something I need to put in the others like the Length/Values, Collation or Attributes?

[Spunky]

Ok I actually did a bit of research. I added 255 to the length like I would for a VARCHAR field and got it to create. But how do I get an image in it when I try to insert data?

[pritaeas]

http://www.daniweb.com/forums/thread111092.html

[Spunky]

You know. I've really appreciated your help up until here but that link was just a disappointmet.

1.) Have I not made it clear I don't know the coding yet for PHP? That link brought me to people discussing code and how to fix or add to the code. I noticed the dude used XAMPP which, I tried using to make a server on my computer but failed so instead bought a host that did it for me.

2.) I need to know how to do this through CPanel if possible and with as little self coding as possible. Obviously if I need to code I need to code, I am willing to learn. But I can't look at that post you linked and learn.

The guy on that post and I may have a similar issue but we are both on different levels of the issue and knowledge. I understand I may need more knowledge, but I don't know it yet till I know how to do what.

Do you know how to use CPanel? Cuz really that's what I came on this forum for, to find someone that knows CPanel that can help me out.

[pritaeas]

Probably misunderstood, possibly because english is not my native language, but I'm trying. Uploading an image through phpMyAdmin (in cpanel) can be done by using the choose file button, when you insert a new record. this works on varbinary and blob fields.

[Spunky]

Oh and sorry for coming off harsh. Hadn't realized you english isn't the best. It's alright you misunderstood.

I hadn't seen a choose file button when I looked before and I just looked again now that I know to look for one and still don't see anything. Where is it located? I am inserting a new field into the table that has a Varbinary field. I can change the function and type in the value field, but that is all I see.

[pritaeas]

Doesnt matter. I can understand your frustration. Create a table containing a blob field (not varbinary, my mistake). Then you can insert data in the table. I made a screenshot so you can see:

http://pritaeas.info/DaniWeb/Screenshot/DatabaseCPanel.png

[Spunky]

Omg thank you!! You are so helpful! Thank you so much!

Ok so now I still need to know how to change my data when a link is clicked on. I'm sure you haven't answered that because I'm probably still confusing.

Go to http://www.wowtah.com/achievements This is my website; particularily the page I am specifically working on. See those yellow words off to the left? Those are the different categories of the different achievements you can aquire. The data in the middle currently is just the test data I put there with your help. Eventually I will make it so it introduces you to what achievements are and blah blah blah but then when you click on one of the categories it will list those achievements.

What I am trying to accomplish is like on http://wowhead.com they have a huge database of stuff you can sift through. And no matter what you click on the rest of the page stays the same. As if only the data is changing. Or am I wrong? Is everything else perhaps in a template that is used over and over again? My site is created with a template ofcourse. But, with a database, how do I have people search through it then like on wowhead without having a new page for each thing they search for? I notice that the url changes only slightly at the top, like the number at the end when you search. Just like on this website, everything stays the same except the post number.

Do you see what I mean? I am doing my best to explain lol. I guess I am just not all that familiar with how websites go about having their databases searched within. I'm still new to this.

I guess wowhead also has their data organized in a different way than I am trying to do. More in lists. But I am sure that's all customizable. I just need to know how its done first..I dont know how else to explain what I am trying to do. If you dont understand exactly, ask any questions that may help.

[pritaeas]

As far as i can tell wowhead has one large item list on a single page, where at the top they jump down to a specific part (http://ptr.wowhead.com/?new-items=3.2). Everything here is already loaded from the start. Is this what you mean. The links jumping down is controlled by in-page links (e.g. href='mypage.php#bookmark')

Just to show you what one php page can do, have a look at one of my sites, just to get an idea. I know you can't read the contents, but everything is controlled by one php page. www.onderwaterwereld.org

[Spunky]

Yea I guess that is what I need to do too then. Have all the content loaded and controlled by one php page and only have what is clicked on to be shown. That's probably some difficult php coding though lol. But that's alright, I'll just need to learn. This is exactly what I am talking about with learning hands on.

But actually. The link you pasted for wowhead isn't what I am talking about. That page in particular yes is using anchor points, which you will find here: http://www.wowtah.com/transportation.html that I am incorporation this as well, having links jump down to another part of the page.

An example I am talking about on wowhead is this: say you click on one of those items on the page you linked to. How is the page it brings you to created? Surly the developer didn't create an individual page for every item; yet, on that page when you click the item is its info as well as its own unique quick facts about it off to the right hand side. All data from a database put into a page that I am not sure is created..thats what I want to know. Where did this page come from? And even when someone does a search with filters; how is the data changed on one page?

This is all stuff I need to learn and understand. First I want to understand the concept, then I want to learn the php it takes to do it. Will you help me?

[pritaeas]

I can help you with php, but as far as i can tell, the hover information and some other stuff is javascript. I don't know much about that, as I always try to avoid using it.

The detail page in the middle will probably a template page, which can be easily filled with database info. I use smarty for that, it could be useful to look into. (smarty.net)

[Spunky]

Well. Ill need to know what I am trying to accomplish before I know what to learn in php. As for the hover info, yea thats javascript, I can figure out how to do that easy. Learning javascript as well and have more knowledge in it.

As for the detail in the middle. I know how to make a template page. My entire website is created with a template. The top header and navigation as well as the bottom info is all the same on each page due to a template I created. But, how do I get the info in the middle to change depending on what is selected/what the user is searching for? That I don't understand.

[pritaeas]

Most common method would be to use mypage?id=X on an item click. The page will use id to retrieve the data from the database and display it in your template. If you have spread your data over multiple tables/categories than you can just extend this, e.g. mypage.php?category=goblin&id=324 You can retrieve the id by using $_GET

[Spunky]

Ok because I don't expect you to give me a full lesson on how to do this I went to w3schools.com to look for help and I found:This but it seems to be directed more towards frames. And yes, my data is spread over multiple tables.

Im finding it difficult to find anything of use to me to learn this. Then, both with PHP and Javascript I find it difficult to learn because like, you learn the functions but what makes you smart in using it is HOW you use what you learn. Gives me a headache. That's why what I know in Javascript is what I've used on my site derived from tutorials for doing what it is I want to accomplish..I suppose in time I will know a lot...

Ok, do you think you could point me in the right direction to learning what I need to know? Not just a tutorial website but the actual useful tutorial on it or something, or you could help me yourself that'd be useful too lol.

[pritaeas]

I have just written code for someone, that implements an account administration panel. It has a search tab, a detail view, a notes tab and a mass e-mail tab. The detail tab is a template which gets filled with data from the database. I could modify this to give you an idea. I'll strip out the notes and email, and change the database a bit.

[pritaeas]

As promised.

http://www.pritaeas.info/DaniWeb/AdminPanel.rar
http://www.pritaeas.info/DaniWeb/test.sql

[Spunky]

Omg thank you so much, you are ever so helpful. When I get a chance to take a look at this I will get back to you for any additional questions. =)

[Spunky]

Alright I know it's been like two weeks since my last response but things have been pretty busy for me. I've got work and then school is starting for me soon, bf coming home from Afghanistan soon, friend just moved up from Texas.......anyways..

Ok, well, I've tried to take a look at what you made. Um, but I could get the php to work using my host, I just wanted to be able to view it and how it works cuz that is the best way to learn how the code that I can see works, you know? Unfortunately I couldn't get it working and I am not sure why.

What are the .tpl files and what is that code written in? I am guessing the index.php calls those .tpl files to function certain things, I just wish I could see how. Hands on you know? Otherwise all this confusing code doesn't really help.

Ah well, Im sure in time I will learn all I need to know to do this kind of stuff.