SQL Database

[herme3]

The company I work for has all of their customer accounts in a SQL database on a server. They need me to create a program that can display certain information from the database, and then organize it so the employees can find customers who haven't paid their bills and other things like that. I'm not really sure what the best way is to create the program. Can I use PHP or does SQL require a separate programming language?

[Cap'n Refsmmat]

You can probably use PHP, but first - what sort of SQL database? MySQL? Microsoft SQL Server? PostgreSQL? Oracle? Firebird? SQLite? Access? Check the PHP manual to see if it supports the particular server you use.

 

Note that if you want to create a graphical program (not a web application), you'll want to use C or something similar and the API that goes with each database system.

[Sayonara]

SQL is largely platform independent (in terms of the queries used by clients connecting to it), with the exception of minor differences in proprietary products such as Oracle.

 

If you want to use PHP but still produce a GUI interface, use the PHP-GDK on php.net:

http://gtk.php.net/

 

I'd suggest learning it properly before steaming ahead and making something dreadful though.

[Cap'n Refsmmat]

I'd suggest learning it properly before steaming ahead and making something dreadful though.

Quite. Also, read up a few good articles on PHP security and SQL injection - there are far too many PHP scripts that are vulnerable to attacks, and letting someone steal customer data could be disastrous.

[Pangloss]

SQL has a built-in programming language called Transact SQL, which is similar to Oracle's Procedure Language (I teach both; as Sayo points out, they're very similar). It lets you create what they call "Stored Procedures" which contain all your progamming logic, and then you can call them from Query Analyzer, inputting the relevent parameters. You can also write a front-end interface in any programming language when calls those stored procedures remotely, passing in the relevent parameters.

 

Alternatively you can construct your SQL queries in any external programming language and treat SQL Server as a mere data repository, constructing all of your data elements in arrays and so forth, entirely within your program.

 

The first approach is seen as having an additional "tier" (e.g. "two-tiered programming" or "three-tiered programming"). That's an advantage because it means that your external program doesn't have to be modified every time your database changes (so long as your database people keep their stored procedures updated).

 

You might want to check out this site, which shows the relevent connection strings used to connect to various database products:

http://www.connectionstrings.com

 

In addition to the PhP suggestions above, you may also want to check out Visual Web Developer 2005, which is a free download from Microsoft. It's very simple to use and might get you to your goal more quickly, espeically if you're not actually trying to learn programming, you just need to complete the task your boss assigned. Even so it still has a learning curve.

 

Microsoft posted something new the other day called "Blinq". It's some sort of automatic Web app creator. All you do is point it at your database (giving the appropriate credentials and DB location) and it constructs an ASP.NET application that interacts with it. Unfortunately I have not yet looked at it or know anything at all beyond what I just wrote, so caveat emptor.

 

http://www.asp.net/sandbox/app_blinq.aspx?tabid=62

[Chuck Edge]

Can I use PHP or does SQL require a separate programming language?

 

SQL is a separate language, specifically the language to manipulate information stored in a relational database. You can use a PHP library like PEAR DB, PDO, or ADOdb to abstract out a good amount of SQL coding you'd otherwise have to do with other hooks, but you should still understand some basic SQL (and the syntax associated with the specific database management system

) in order to perform queries that can't be covered by your typical create, retrieve, update and delete operations.

 

Also, some clarifications on previous points raised in the thread. Transact is an SQL syntax specific to Microsoft's SQL Server. A number of commercial and a few open source DB's offer a procedural extension to their SQL implementations. However, you'll first want to tell us out what you're using. Also, it sounds like you're going to build an application over an existing schema (the layout of data and its relations). You'll want to figure out exactly how that schema looks (or at least the part relevant to your task) before moving forward.

 

That said, if you want to develop quickly with the shallowest learning, follow Pangloss' suggestion and use Visual Web Developer. For one, you won't have worry too much about injection. You shouldn't have to anyway, and you definitely don't need to read a couple of articles to get the idea that you should escape any strings derived from user input before doing any mutating operations.

[mooeypoo]

I'm actually building a small humble "how to" guide for people with only the basic knowledge of general programming on the subject of PHP and mySQL, after a friend asked me to teach him how to build a mysql/php site.

 

If it will help you out, I will post you the link when I'm done.. but it deals with mySQL, so make sure this is the database you are using, if you want this.

 

~moo

[Aeternus]

SQL is a separate language' date=' specifically the language to manipulate information stored in a relational database. You can use a PHP library like PEAR DB, PDO, or ADOdb to abstract out a good amount of SQL coding you'd otherwise have to do with other hooks, but you should still understand some basic SQL (and the syntax associated with the specific database management system

) in order to perform queries that can't be covered by your typical create, retrieve, update and delete operations.

 

Also, some clarifications on previous points raised in the thread. Transact is an SQL syntax specific to Microsoft's SQL Server. A number of commercial and a few open source DB's offer a procedural extension to their SQL implementations. However, you'll first want to tell us out what you're using. Also, it sounds like you're going to build an application over an existing schema (the layout of data and its relations). You'll want to figure out exactly how that schema looks (or at least the part relevant to your task) before moving forward.

 

That said, if you want to develop quickly with the shallowest learning, follow Pangloss' suggestion and use Visual Web Developer. For one, you won't have worry too much about injection. You shouldn't have to anyway, and you definitely don't need to read a couple of articles to get the idea that you should escape any strings derived from user input before doing any mutating operations.

 

This is why things like LINQ look so cool, adding not just the usual levels of abstraction to db access but allowing a common set of querying constructs (effectively built into the language) that you can use to access data stored in a database, or even a datastructure in memory (you can write classes to allow a particular db or datastructure to be accessed and therefore extend the system).

 

http://en.wikipedia.org/wiki/Language_Integrated_Query

[Chuck Edge]

This is why things like LINQ look so cool' date=' adding not just the usual levels of abstraction to db access but allowing a common set of querying constructs (effectively built into the language) that you can use to access data stored in a database, or even a datastructure in memory (you can write classes to allow a particular db or datastructure to be accessed and therefore extend the system).

 

http://en.wikipedia.org/wiki/Language_Integrated_Query[/quote']

 

Hey look, it's ActiveRecord! I'll play with this, though.

[doG]

The company I work for has all of their customer accounts in a SQL database on a server. They need me to create a program that can display certain information from the database, and then organize it so the employees can find customers who haven't paid their bills and other things like that. I'm not really sure what the best way is to create the program. Can I use PHP or does SQL require a separate programming language?

SQL is a language itself, i.e. Structured Query Language. Just how long are they willing to wait for you to learn SQL? You will need SQL to access the data in the database and some other programming language to generate the report with the dataset returned by SQL. PHP will work as the second language just fine.

[5614]

I'm actually building a small humble "how to" guide for people with only the basic knowledge of general programming on the subject of PHP and mySQL' date=' after a friend asked me to teach him how to build a mysql/php site.

 

If it will help you out, I will post you the link when I'm done.. but it deals with mySQL, so make sure this is the database you are using, if you want this.

 

~moo[/quote']Could you please post a link, I would be interested in it.

[mooeypoo]

Yeah it's not done yet, so when it will be done, I most certainly will.

 

It - at least for now - deals with the basics of PHP and web programming, for people with small background in programming language. The next step is mySQL integration and databases.

 

It's going to take me a little while, coz of work being a major occupant of my time lately (i hate summer) but when it's done --- or close enough to be helpful -- i will post it here.

 

~moo

[5614]

Yeah sure, no rush!

[herme3]

Quite. Also, read up a few good articles on PHP security and SQL injection - there are far too many PHP scripts that are vulnerable to attacks, and letting someone steal customer data could be disastrous.

 

The program will only be used on some of the computers in the office. Nobody will have access to the program who doesn't already have access to the customer data.

 

In addition to the PhP suggestions above, you may also want to check out Visual Web Developer 2005, which is a free download from Microsoft. It's very simple to use and might get you to your goal more quickly, espeically if you're not actually trying to learn programming, you just need to complete the task your boss assigned. Even so it still has a learning curve.

 

I think that will be my best option. All I need the program to do is read certain data from the database, and then organize it. One thing the program will need to do is read the dates of when each customer last made a payment. Then, the program will need to display a list of customers who are past the due date for their payment.

 

Just how long are they willing to wait for you to learn SQL?

 

They are in a hurry, and would like the program to be finished as soon as possible.

[Gaz]

The program will only be used on some of the computers in the office. Nobody will have access to the program who doesn't already have access to the customer data.

 

Thats still no excuse to code sloppy sql-injectable code. What happens if Mr employee gets fired and decides to ' DROP DATABASE really_important_stuff ?

 

If they only need to display data, I suggest creating a read only user for the database in question and using that for queries.

[Chuck Edge]

Thats still no excuse to code sloppy sql-injectable code. What happens if Mr employee gets fired and decides to ' DROP DATABASE really_important_stuff ?

 

Then there's no need for herme3 to handle this issue. The company clearly has someone else who can.

 

If they only need to display data, I suggest creating a read only user for the database in question and using that for queries.

 

This is really getting into the weeds. Same thing for the whole injection point.

[herme3]

I just checked, and the database is a Microsoft SQL server.

[doG]

I just checked, and the database is a Microsoft SQL server.

For no more than you're trying to do I would use Excel to connect via ODBC and create a refreshable query in a spreadsheet. You might have to install MS Query from the Office CD, it gives Excel the ability to query ODBC database connections. You will end up with a read-only query that any user can refresh as needed.

[bascule]

Hey look, it's ActiveRecord! I'll play with this, though.

 

ActiveRecord is the best ORM I've ever used

[bascule]

I just checked, and the database is a Microsoft SQL server.

 

Nasty

[herme3]

For no more than you're trying to do I would use Excel to connect via ODBC and create a refreshable query in a spreadsheet. You might have to install MS Query from the Office CD, it gives Excel the ability to query ODBC database connections. You will end up with a read-only query that any user can refresh as needed.

 

I've talked to someone else at the company, and we are thinking that it might be possible to just do everything in Access. Could we could move some of the information in the database into an Access database, and then accomplish our objective by creating queries in Access?

[Cap'n Refsmmat]

You don't need to do that. That duplicates data and really isn't necessary. Excel can use ODBC to access a Microsoft SQL database.

[doG]

I've talked to someone else at the company, and we are thinking that it might be possible to just do everything in Access. Could we could move some of the information in the database into an Access database, and then accomplish our objective by creating queries in Access?

You can create linked tables in Access and build queries and reports on them just like the data was really there. It's like an invisble ODBC connection. It will be more work than just creating a query in Excel though and Excel would be easier for the users to refresh the dataset as needed. You original post sounds like this is a simple, one query report.

[john5746]

I've talked to someone else at the company, and we are thinking that it might be possible to just do everything in Access. Could we could move some of the information in the database into an Access database, and then accomplish our objective by creating queries in Access?

 

Linked tables might be the way to go - simple and quick. You will need to setup an ODBC connection then link the tables you need. Be aware that ODBC is quick and dirty, but will can tie up bandwidth and be slow for excessive data.

 

If you link the tables and query them on the client side, you will be bringing the whole table of data to the client and then sorting through it. It is easy enough to do it and see what the performance is like, but if you are going to have more than 5 users, performance will degrade quickly.

 

So, you can steam ahead, but you may eventually need to either make procs in SQL server or pass-thru queries in Access. It is amazing how much stuff like this is written in Access DB's and spreadsheets throughout corporate America.

[herme3]

You don't need to do that. That duplicates data and really isn't necessary. Excel can use ODBC to access a Microsoft SQL database.

 

From what I've heard today, the company wants to separate the data into two separate databases. A new database will be managed through the program I need to create. This database will contain all the customer information for people who automatically renew their account. The other database will contain the rest of the customers. The old database will be managed using the program they already have.

 

The reason for them splitting up the data is because the old program doesn't let them sort certain information related to renewing accounts. I'm still not really sure of everything the program will need to do. The company just gave me a little information about what they need, and they want to know if I can create the program, or if they will need to hire a programmer to complete the task.

 

You original post sounds like this is a simple, one query report.

 

I'm still not sure of everything the program will need to do. However, I think it will have to do much more than one task.