As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

Database question

ChrispyChrispy Registered User regular
edited June 2008 in Help / Advice Forum
Last semister, in my database class, we were given a project to create a bookstore website. We had to make databases and webpages (in PHP and HTML, you know the easy stuff). It didn't actually need to be online, we could just run it from our machine.

What I was wondering though is: How would I go about putting something like that online. I know I have to put the actually pages online (I have access to a server). But what about the actual database? I know the database was on my machine because the offline pages could access it. But if I upload the pages and not the database, will the website still be able to pull the info out. If I need to upload the database as well, where would go about locating that, because it never seemed like it was saved on my computer.

Why: The reason I am asking about this is, I was put in charge of creating my family business' website. I'm putting all the stuff we sell (which is a lot) on it. I originally wanted to do it with databases, but I couldn't figure it out. But since I've started, I've come up with some ideas that I would like to do that would be really hard to hard code (but I've basically have been doing), and I know that a database system would be a heck of a lot easier.

I'm using apache with myPHPadmin (localhost).

I hope this makes sense, I Know what I want to say but I'm doing my best to convey it into words. I guess: when I'm working with a website and databases, do i just upload the website and not worry about the database, the website knows what to do. Or do i have to do more?

~ Make life how you want it ~
Chrispy on

Posts

  • VThornheartVThornheart Registered User regular
    edited June 2008
    Well, it depends on what you're using for a database.

    If you're using a "server" style database like SQL Server or Oracle, you'd need a web host that is actually running said server, and an account that you would then connect to. Where the database itself is hosted isn't the big issue at that point as much as the question "does your website have sufficient access rights to reach it?" This generally means that the connection string you use will have a URL to the database server, and your assigned username and password.

    You'll want to make sure to be careful not to store the username and password in such a way that people can find it by navigating to your website (i.e. don't store it in a text file on the server). This issue of connection string security scratches the surface of a whole ocean of security issues, which may or may not be relevant for your situation and fill several tomes. Luckily, if you need to, there's plenty of resources about how to make your website more secure... but I digress.

    If you're running a file-based database like MySQL or Microsoft Access (by the way... if at all possible, don't do it with Microsoft Access), you would need a folder that people navigating to your site couldn't reach (a private folder), but that your website has the rights to access. Then you would point your connection string to that file and give any other needed permissions.

    Is that what you were looking for, or were you looking for actual connection string syntax?

    If you were looking for how to do it syntactically, go to:

    http://www.connectionstrings.com

    That site shows you how to make connection strings for just about any database in existence. All you need to know at that point is where the database is located that you need access to, and to make sure that your website can actually reach it in the first place. Note that you will likely have to get this information from your ISP if it is a server-based database setup.

    VThornheart on
    3DS Friend Code: 1950-8938-9095
  • ChrispyChrispy Registered User regular
    edited June 2008
    Well I know that the databases I can create have nothing to do with my server. I'll have to talk to my friend to see what type of options we have with our server. Maybe they allow us to make databases really easy, at that point it's a piece of cake. Some of the stuff you said went over my head, but I'll look into it. Thanks.

    Chrispy on
    ~ Make life how you want it ~
  • ÆthelredÆthelred Registered User regular
    edited June 2008
    If the database is on your local machine, a web-page won't be able to access if it your computer isn't connected to the internet - as simple as that.

    Since you've already learnt php, go ahead and combine that with mySQL. There's plenty of documentation out there on how to use the two in tandem. Buy some web hosting and you get database options along with it. Run the database and web page on your hosting together.

    Æthelred on
    pokes: 1505 8032 8399
  • ChrispyChrispy Registered User regular
    edited June 2008
    I just don't know how to connect the databases I already have with my website that I already have.

    I already have the hosting so it's just a matter of talking to my friend (we have it jointly). When I talk to him I can see if we have the ability to make databases (which I'm sure we can).

    Chrispy on
    ~ Make life how you want it ~
  • DaenrisDaenris Registered User regular
    edited June 2008
    How are they connected now to the offline webpages? You would connect them the same way. Generally a website will have code (PHP or whatnot) that connects to a database. To do that you need to give the PHP script a location of the database. Running them both off your local machine it's likely that the server address you use is something like localhost or 127.0.0.1 along with a username and password for the database. To connect to a remote database you would just use the IP address of your database server.

    However, you really should not be running the database off your personal computer if this is actually supposed to be a permanent business website.

    Since you're using MySQL you should find a webhost that offers a MySQL database with your website -- many do. Once you get a database set up, you can go to your myPHPadmin on your local computer and do a backup of your database, and then set up myPHPadmin on your webspace and restore the backed up database into it. Then it's just a matter of updating your webpages to point at the new database server address (like www.mydatabase.com or whatever).

    Daenris on
  • ChrispyChrispy Registered User regular
    edited June 2008
    Ok, I'm getting frustrated. Here is where I am at:

    I've talked to my friend and we can make databases with our server. I've made table, with fields, and put one item into it. This is to test to make sure it works before I go head first into it.

    I just want to display the database at that moment. So what I did was this:
    In the website page, that people will be viewing I have this

    [PHP]<?include_once ("../../functions.php");

    NormalDisplay("generic_table_name");[/PHP]

    Where functions.php (two folder above this file) is a bunch of functions that I have.


    in functions.php, I have:
    [PHP]
    <?function Make_Connection()
    { $link = mysql_connect('mysql.generic_name.com ', '', '');
    if(!$link)
    { die("Error: Could not connect -> ".mysql_error());
    }
    $result = mysql_select_db("generic_database" ,$link);
    if(!$result)
    { die("Error: Could not find Database -> ".mysql_error());
    }
    return $result;
    }


    function NormalDisplay($TableName)
    { ?> Testing<br>
    <?
    $connect = Make_Connection();
    $result = mysql_query("SELECT * FROM ".$TableName);


    $Type = mysql_query("SELECT Type FROM ".$TableName);
    $Category = mysql_query("SELECT Category FROM ".$TableName);
    $Code = mysql_query("SELECT Code FROM ".$TableName);
    $Name = mysql_query("SELECT Name FROM ".$TableName);
    $Price = mysql_query("SELECT Price FROM ".$TableName);
    $Item = mysql_query("SELECT Item FROM ".$TableName);
    $NC = mysql_query("SELECT NC FROM ".$TableName);

    if(!$result)
    { die("Error: Could not find Table -> ".mysql_error());
    }
    ?>
    <table border = "1" valign = "top" align = "center" width = "90%">
    <tr>
    <th colspan = "5"> <? print $TableName ?> </th>
    </tr>
    <?
    for($i = 0;$i < mysql_num_fields($result);$i++)
    { $header = mysql_field_name($result,$i);
    if($header != "ID" && $header != "NC" && $header != "Era" && $header != "Color")
    ?> <td> <? print $header ?> </td> <?
    }
    ?>
    </tr>
    <?

    ?></table><?
    $connect.mysql_close();
    }

    ?>[/PHP]

    Side note - where it says "generic_name", I have the actual name of the item


    I have both of these files uploaded, but it's not getting the database. I know when I was looking around for some PHP information, I found that a function file needs to be in the same folder as the file that's calling the function. BUT, when I was doing the project for school, the function file was not in the same folder. SO I don't see why it's not allowing me to do it here. There will be more in that NormalDisplay function, again, I wanted to test things before going head deep into it. (Sidenote: Turned out that I did need to be online to view that site, even though it wasn't uploaded)

    Chrispy on
    ~ Make life how you want it ~
  • DrFrylockDrFrylock Registered User regular
    edited June 2008
    Okay, this at least tells us what kind of database you're using. In case you're curious, it's a MySQL database. There are lots of other relational database engines, as others have pointed out: Microsoft Access, Microsoft SQL Server, Oracle, DB2, PostgreSQL, etc. etc.

    I understand that you have web hosting. But web hosting is not everything hosting. You need to have a webhost that permits you to run MySQL (or some other database). Just because you can host HTML pages with Apache and run PHP doesn't guarantee you can run MySQL on your webhost's computers. In the OP, you stated:
    I'm using apache with myPHPadmin (localhost).

    phpMyAdmin (not myPHPadmin) is a tool that allows you to administer MySQL databases through a Web interface. Normally, you administer MySQL databases through a command-line interface. phpMyAdmin lets you do administrative things through a browser.

    Because you said "myPHPadmin (localhost)" this leads me to conclude that your database is running on your local host - that is, your personal computer. Not your web host's computer. Ideally, what you'd like is for your web host to install a copy of phpMyAdmin on their computers and then you would administer MySQL databases hosted there through their installation of phpMyAdmin.

    The code tells a little bit of a different story: it attempts to connect to "'mysql.generic_name.com". Does this machine actually exist? Can you ping it?

    Finally, a word of caution: hosting database-driven websites running your own custom code can be a lot of fun. However, if you don't take adequate steps to protect it, especially from SQL injection attacks, you're in for a world of hurt. If you don't know what I'm talking about, you need to stop now and get smart on this stuff first. A SQL injection attack lets a not-very-sophisticated person from the outside manipulate and query your entire database remotely. They could surreptitiously add new data, read all your data, or just delete the whole database in one swell foop. Bad news, and all it takes is a bit of sloppy coding on your part.

    I surely hope that you're only using the database for delivering product information, and not for e-commerce. If you want to do e-commerce (i.e., actually taking orders, possibly including credit card numbers, over the Web), there are about a billion turnkey packages that will help you and which have been vetted against these kinds of things.

    DrFrylock on
  • ChrispyChrispy Registered User regular
    edited June 2008
    Sorry, the apache and phpMyAdmin that I stated from the beginning was when I wasn't sure about anything. As of now, it doesn't matter one bit a difference if i have it now, since the database I'm using, and trying to connect to in that code is part of a server and done through the server. I do NOT need Apache running to even access the database I have.

    Yes, that machine exists. I typed it into the browser and typed in my user name and password. which sent me to the phpMyAdmin of the server.

    As of right now, it's only a display purpose type of thing, showing all of product. I was also going to use it a search type of set up. We did buy a shopping cart to add to the site. It's some program that I just add and set up myself. I'm guessing I'll have to change some things when that time comes.

    Chrispy on
    ~ Make life how you want it ~
  • DaenrisDaenris Registered User regular
    edited June 2008
    You don't say what's actually happening when you try to view that page. Do you get any kind of error message or something, or is it just completely blank as if it didn't load a page?

    Daenris on
  • ChrispyChrispy Registered User regular
    edited June 2008
    It's loading the page, like the background stuff, menu and all. It's the content though, the table, that's not being displayed. It looks like it's not getting anything in the function file.

    Chrispy on
    ~ Make life how you want it ~
  • DaenrisDaenris Registered User regular
    edited June 2008
    Well, you can try putting the functions.php script in the same folder and including it that way. Once everything is working it's certainly possible to move it elsewhere, but for now just to eliminate any potential problems it may be best to put it in the same location so that there's no question of it actually finding the script.

    So it's not even displaying the "Testing" that you have at the beginning of the table display function?

    Daenris on
  • ChrispyChrispy Registered User regular
    edited June 2008
    Nope, not at all...

    In the connect function, do I need my user name and password? How safe is that?

    Chrispy on
    ~ Make life how you want it ~
  • DaenrisDaenris Registered User regular
    edited June 2008
    Yes, you need to give it a username and password to connect... otherwise how exactly is it supposed to connect to the database?

    http://www.php-mysql-tutorial.com/connect-to-mysql-using-php.php
    http://phpsec.org/projects/guide/3.html

    Usually the username/password is kept in a separate config file that is kept outside of the webpage path, so that it can't be accessed by anyone just typing in the location of the file, but that the PHP script can see.

    For example if your website is located in something like /home/username/www and that's where your index.html and such are, then you might put this config file in /home/username and include it in that way from your PHP script. That way someone can't go and type www.mywebsite.com/config.php and actually bring up your config file, but the PHP script can still include it with include_once('/home/username/config.php'); or whatnot.

    Daenris on
  • ChrispyChrispy Registered User regular
    edited June 2008
    and that call to the config file is in the function itself? I'll look into those links, thank you.

    Chrispy on
    ~ Make life how you want it ~
  • VThornheartVThornheart Registered User regular
    edited June 2008
    DrFrylock wrote: »
    Finally, a word of caution: hosting database-driven websites running your own custom code can be a lot of fun. However, if you don't take adequate steps to protect it, especially from SQL injection attacks, you're in for a world of hurt. If you don't know what I'm talking about, you need to stop now and get smart on this stuff first. A SQL injection attack lets a not-very-sophisticated person from the outside manipulate and query your entire database remotely. They could surreptitiously add new data, read all your data, or just delete the whole database in one swell foop. Bad news, and all it takes is a bit of sloppy coding on your part.

    Chrispy, Frylock states well the security issues that could result, which I eluded to earlier but didn't spell out. You would be well advised to read up on it and take whatever steps you can (there's a lot of good websites out there that teach you how to thwart potential SQL Injection attacks) to make sure this cannot happen on your site.

    VThornheart on
    3DS Friend Code: 1950-8938-9095
Sign In or Register to comment.