Help with an SQL Database

Discussion in 'Silicon (v)Alley' started by JayF, Oct 1, 2011.

  1. #1 JayF, Oct 1, 2011
    Last edited by a moderator: Oct 1, 2011
    More specifically with connecting to an SQL Database through MS Excel. I have full access to the domain's cPanel and phpMyAdmin.

    The Server given in phpMyAdmin info only says "Localhost via UNIX socket", obviously not what I'm looking for.

    I am something of an expert when it comes to Excel and have a good understanding of relational databases, not so much SQL or webdevelopment. Can anybody point me in the right direction of where I might find the Server Name that Excel is asking for in it's External Data Connection Wizard. I am sure this is possible, I'm probably just being a moron.

    The reason I want this connection is to merge two databases of separate installations of an open source shopping cart (I'll need to manually tie in product_ID's, etc.) I wouldn't know where to start with doing it with SQL queries, but as I understand it Excel essentially generates the queries for you when connected to an SQL database.

    I have a bottle of Johnny Walker riding on this, any help would be much appreciated.
     
  2. well server name in many cases is simply 'localhost'. if not you will have to go find your webserver's info on the subject or send in a ticket. good luck, need any more help post here
     
  3. #3 Zylark, Oct 1, 2011
    Last edited by a moderator: Oct 1, 2011
    Why excel?

    If an online SQL driven web-shop, your best bet is to parse SQL commands back and forth using a Javascript (or even Flash/ActionScript) driven HTML form serverside. Pretty easy, but ofcourse, it do require you to know basic SQL. Plenty of good books exist on the topic. It's not that difficult either, pretty simple from a programming perspective (read: no math needed :D ).

    However, due to the sensitive nature of web-shops (creditcard info and such) I'd just ditch any notion of making my own due to the liability issue alone, and rather outsource that part of the operation to a reputable third-party, like say FastSpring or similar.
     
  4. #4 JayF, Oct 1, 2011
    Last edited by a moderator: Oct 1, 2011
    Thanks Arnack. This is something I don't understand about SQL. Normally localhost refers to something on the machine you are currently working on, right? There would be an equivalent IP address or domain name to access the same location from an external connection (if it were configured as such). For example typing Grasscity.com Forums into a browser would be the same as typing http://1.1.1.1/ if 1.1.1.1 were my IP address. Or maybe I'm misunderstanding something?

    Edit: typing http:// local host / above resulted in a link referring to GC Forums. Interesting, and I think nicely illustrates my point.

    So surely there must be an external address for what my hosting provider is referring to as localhost. I have tried both the domain name and IP address, with and without the cpanel connection port, as the server name, with no results. I am presuming what I'm looking for will look something like 111.111.111.111:2082/sql_server_location/sql_server/

    I'm working on this as a favour for my brother in law. I don't have access to his hosting provider account, and thus can't submit a ticket. I can't do it soon I'm going to tell him to contact someone with experience in SQL, but I'd like to figure it out just to satisfy my own curiosity.

    Thoughts?
     
  5. Thanks Zylark.

    I'm trying to do it in Excel because I have no experience in SQL, I'm very good at manipulating data in Excel and I'm pretty sure it can be done.

    There's no liability to worry about just yet. The data consists of three webshops complete with products, descriptions, etc. a couple of customers and their details and orders, but no credit card details or financial information. Manually copying the data product by product would be time consuming (and boring) and trying to export the tables through phpMyAdmin and import them on the other site only worked for one set, as the product_ID's, etc. are duplicated. I'm Sure there is probably a way of doing all this using SQL commands, I just wouldn't have a clue where to start.

    Thanks again.
     
  6. you'd have a lot easier job if you had access to the website, you could do like zylark suggested. but to continue, i'm guessing you'd need to grab the server name, which you could get if you were able to run a simple statement on the server (which you seemingly don't), i'd try getting the info from the hosting company. but to eliminate any other factors, you sure the sql username and pass are correct?
     
  7. #7 JayF, Oct 1, 2011
    Last edited by a moderator: Oct 1, 2011
    I can run SQL commands on the server, I can bring up the command line, but just don't know any SQL commands :confused_2:. I have full admin access to cPanel and phpMyAdmin, just not to his account on the host's website.

    The username and password would be the same as the cPanel UN and PW right? I'll take a look into this now. I saw somewhere I could add and edit SQL usernames.

    In the meantime, an SQL command to return the external server name and address would be great.
     
  8. #8 Zylark, Oct 1, 2011
    Last edited by a moderator: Oct 1, 2011
    Then you just need to have the correct login for the already made database. In mySQL it is (using telnet to connect to your site server) "mysql -u youruser -h yourdomain.com -p yourdomain_com". That will produce a prompt asking for the password. From there on in you have full access, and can do whatever you like.

    For excel to connect though, and assuming you use mySQL, you'll need to add the server-side database info as a windows resource on your client. There are several windows drivers for this, look around here: mySQL connectors

    Then after installing the driver, through your windows control panel (admin-tools -> data sources(odbc)), add the correct type of SQL database you're trying to connect to, enter domain, user and password info and test the connection.

    Finally, in Excel, through the Data menu, you can import tables from your database after creating a new datasource (New Source -> ODBC DSN). As for exporting, you can only export data into already existing database tables, you can not create new ones in excel. Only update or append data, no monkeying around with set tables.
     
  9. "I can run SQL commands on the server"

    How? How are you connecting to the server to run SQL commands? If you can connect to the server it sounds like you already know the hostname or IP address...

    Are you talking about MySQL? If so you should specify, it makes a world of difference compared to MSSQL, or other Server Query Languages.
     
  10. #10 JayF, Oct 1, 2011
    Last edited by a moderator: Oct 1, 2011
    Zylark, I know almost nothing about telnet, other than a vague knowledge of it being some kind of low level IP protocol, that doesn't come enabled by default in Windows 7 (just enabled it), as for how to connect to a site with it: I tried a few commands but couldn't get any sort of connection, presumably it isn't the same port number as the cPanel (I tried) and I couldn't find any mention of it in the cPanel.

    I installed the Windows drivers, but the next step (through control panel) gives me an almost identical dialog box to the Data Connection Wizard in Excel. I am right in presuming that the SQL server name isn't just the domain name, right? I've double checked the SQL username and password, and they are correct.


    Feanor, thanks for joining. I can run SQL commands through the form within phpMyAdmin.

    I'm a little unsure as to the differences between SQL and MySQL, but I'm presuming that it's all the same to Excel once it gets a connection? I see various mentions of MySQL in phpMyAdmin, so I'm guessing I'm working with MySQL.


    Again, am I right in thinking that the SQL Server name for my site isn't just my domain name, but my domain name and folder or the IP address and port number?
     
  11. SQL is a standard language for databases. MySQL is a Database Management System (DBMS). A database app if you wish. It's comparable to say BASIC. The language is pretty well defined, but there are a plethora of interpreters and compilers all with their own little quirks and nuances. Language is the same, concept is the same, logic is the same. Implementation and the nitty-gritty of practical use will vary though.

    As for the connecting to your online database, you really need to get the correct information from your host. For obvious reasons, you will not be able to connect through the ODBC driver without it.
     

  12. I've emailed him asking for his login details for the host's website . I'll submit a ticket with them.

    Am I right in thinking that the Server Name could be the domain name alone, or it could be the domain name + folder, depending on the configuration of the server?


    So the database is the same regardless, it's the language and method of querying the database that's different.

    Thanks for the info. I enjoy challenges like this. I find them the best way to learn. Challenges and results provide a big motivator and incentive to learn something. Sitting down and studying something isn't nearly as fun.
     
  13. Gotcha, I overlooked that this is a hosted setup. Zylark is spot on that you'll have to go through the hosting provider for direct database access. There may be somewhere in phpMyAdmin to look it up but I'm just not familiar enough with phpMyAdmin and I'd have to poke around for a bit.

    There is a good chance that the hosting provider will not allow you to connect the way you are trying to; they may allow you to download a copy of your database to work with on your own local MySQL installation, but they may not want you connecting directly to their database server except through the website. Huge security concerns come into play when opening up a database server to the Internet like that.

    The Server Name is the DNS hostname or IP address of the server on which MySQL is installed.

    For example: Your computer might be named JayF-PC and have an IP address of 192.168.1.101. Excel is installed on your computer JayF-PC (192.168.1.101).

    With the same concept, your hosting provider has a server named something like MySQL1234 with an IP address of 172.1.1.101. MySQL is installed on that server. The IP address or hostname of that server is what you're looking for. It very well could be on the same server as the website.
     

Share This Page