Solved

PHP vs Python - Which is best for a simple MS SQL Server query web application

Posted on 2014-02-16
15
7,909 Views
Last Modified: 2016-10-19
PHP or Python? -  The goal is to make a little web application for a non-commercial purpose.  The app will query a MS SQL Server and display the query results in a datagrid.

Both languages are new to me. My server is an Ubuntu Server. The MS SQL Server is a requirement. (can't choose MySql)

I assume that the result is achievable in either language. PHP looks like is has a lower entry threshold for something like "Hello world.", but maybe one of the languages has an easier time with datagrids.

Which would be easier/faster for a newbie like me?  Thanks for any help!
0
Comment
Question by:oakie22
15 Comments
 
LVL 16

Assisted Solution

by:hankknight
hankknight earned 90 total points
ID: 39863669
Never underestimate Python.  It is widely used by many heavyweights including Google.   Python is generally more powerful than PHP and can be nicely scaled.

Here is a Python database interface to Microsoft SQL Server:
http://pymssql.org/

If you are a "newbie" you may find PHP to be a better choice because it is so widely used and so and widely supported.  It is typically easiest to get both free and paid support for PHP.

http://www.php.net/manual/en/book.mssql.php

Finally, you may wish to consider using Mono:
http://www.mono-project.com/ASP.NET
http://www.mono-project.com/SQL_Client

Mono would allow you to run ASP.NET applications on you Ubuntu server.  This could be the best option because ASP.NET is so widely used with MS SQL.

My personal choice would be Python but in your case PHP might be the best solution.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39863699
The MS SQL Server is a requirement. (can't choose MySql)
That is crippling to start with.  FreeTDS, the driver that is used to connect from Linux to an MS SQL server on a Windows machine, has been left at TDS version 7 for SQL Server 7 which was the one before SQL Server 2000.

@hankknight's suggestion of MONO is a possiblity but it uses FreeTDS at version 7 also.

If this was ever to go on commercial web hosting, I'd suggest PHP since it is almost universally supported.
0
 
LVL 52

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 150 total points
ID: 39863735
> The goal is to make a little web application for a non-commercial purpose.

What is the web application supposed to do.  I would start there.  Don't even get hung up on which language because if you are just starting out there is a lot to understand especially when you get a database involved.  

There are plenty of options that can build simple apps for you.  Or you may want to decide on using wordpress which has a lot of available plug ins that may do what you need.

Before you start with a serverside language, make sure you have a very good understanding of the basics of html, css, javascript/jquery first.  Otherwise you will run into trouble with output.    From there, you can test out PHP or python.   For just starting out to program any language, php will probably be easier to grasp.  The thing to watch out for is it is an easy language but that also makes it easy to get in trouble.  

If he has not started typing already... Ray Paseur will point you to http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html and http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11909-PHP-Sessions-Simpler-Than-You-May-Think.html

From there, learning about relational data, sql language, managing sql server and security.

Starting out from the beginning, this is not something you are going to be able to throw together in a few weeks.   I would start out with something that is already available.

If your website needs to be linux and db needs to be ms sql, your best bet is to use Microsoft https://www.windowsazure.com for your db. Let them handle the serverside db.
0
 

Author Comment

by:oakie22
ID: 39863808
Dave Baldwin: regarding the Linux--MSSQL connection, is this not an example of a workable  implementation?

http://www.tryolabs.com/Blog/2012/06/25/connecting-sql-server-database-python-under-ubuntu/
0
 

Author Comment

by:oakie22
ID: 39863815
Hmmmmm. I did this type of thing with C# and MSSQL in a 200-level comp sci class about ten years ago. It was so easy.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39863836
That page from tryolabs appears to be good info.  Doesn't change the fact that FreeTDS is many years out of date.  There several data-types available in recent versions of SQL Server that it does not support.  64-bit 'bigint' is one example.

"C# and MSSQL" on the Windows machine would be much easier and up to date.  FreeTDS from Linux is more limited.  If you were on Redhat or Suse Linux, there is a recent Microsoft driver available.  I don't believe it works on Ubuntu.  http://msdn.microsoft.com/en-us/library/hh568451.aspx
0
 

Author Comment

by:oakie22
ID: 39863841
Scott Fell: thanks for the wider perspective.  I thought I could knock this out in about 8 hours.  It's been a while, but I'm familiar with Perl and Apache and simple SQL queries. There are probably 100 ways to take
SELECT SKU, ITEM_DESCRIPTION, PRICE, QTY_OH FROM INVENTORY
(for example) and display it in an HTML table.... but which environment would be easiest? C# made it really easy. Alas, I must use LAMP this time. The MSSQL server can't be moved to the cloud (I think) because there's a  old production app on the LAN and it demands lots of bandwidth. (or is that a wrong assumption?)

It's surprising to be told that the MSSQL-to-LINUX connection isn't trivial.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:oakie22
ID: 39863844
This MS SQL Server is '2000'
0
 

Author Comment

by:oakie22
ID: 39863850
Is there any hope in possibly copying tables from MS SQL to mySql, updating on an hourly basis? It would be read-only, not really a synchronization.
0
 

Author Comment

by:oakie22
ID: 39863879
Oh, and I'm not doing Windows authentication to the MS SQL Server. I'm the 'sa' user, and had planned to add a new user as a read-only role.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39863940
If it's all "old-school" that makes it more doable actually because you are less likely to run into problems from the newer SQL Servers.  Soon as I can find it, I can give you a simple demo for PHP from my Ubuntu machine.  The instructions in that link from Tryolabs is probably good enough to set up FreeTDS.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39863953
First simple program that just checks the connection and reports the SQL Server version.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
 "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head><title>PHP MSSQL Test</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>

<body>

<h1>PHP MSSQL Test</h1>
<?php
 
$server = 'MSSQL05';
$username = 'youruser';
$password = 'yourpwd';
$database = 'yourdatabase';
$connection = mssql_connect($server, $username, $password);
 
if($connection != FALSE)
{
echo "Connected to the database server OK<br />";
}
else
{
die("Couldn't connect");
}
 
if(mssql_select_db($database, $connection))
{
echo "Selected $database ok<br />";
}
else
{
die('Failed to select DB');
}
 
$query_result = mssql_query('SELECT @@VERSION');
$row = mssql_fetch_array($query_result);
 
if($row != FALSE)
{
echo "Version is {$row[0]}<br />";
}
mssql_free_result($query_result);
mssql_close($connection);
?>
</body>
</html>

Open in new window

0
 
LVL 82

Accepted Solution

by:
Dave Baldwin earned 180 total points
ID: 39863979
Here's another simple PHP program that selects 10 rows and displays them in a table.  The $DSNName comes from the configuration file in FreeTDS.
<?php 
// MS SQL server 2005 using PHP 5.2 or lower and php_mssql.dll driver
$DSNName = "MSSQL08";    // Your database server
$dbuser = "youruser";      // Your db username
$dbpass = "yourpwd";      // Your db password
$dbname = "yourtDB";      // Your database name
$dbtable = "websitelist";  // the table we're using
//mssql_connect('localhost', 'mssql_user', 'mssql_password');
$conn = mssql_connect($DSNName, $dbuser, $dbpass);
mssql_select_db($dbname,$conn);

?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
 "http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
<title>Old Website List - PHP/MS-SQL</title>
</head>
<body>
<table border="0" cellpadding="0" cellspacing="0" summary="">
<tr valign="top">
<td>
<h2 align="center" style="margin: 1px;">PHP/MS-SQL</h2>
<div align="center">MS SQL server using and php_mssql.dll driver on Linux</div>
<?php
if ($conn != "") { 
// Formulate Query
$pgsize = 10;
// --------- Get data from table ---------------
$dquery = "SELECT TOP ($pgsize) ent_num, DisplayName, Sortname, WebSite, Descript, Cat, Approved FROM $dbtable";
// TEST PRINT!!
echo "<small>".$dquery."</small><br>";
/* Execute the query. */
$result = mssql_query($dquery);
if (!$result) {
   echo 'Could not run query: ' . mssql_error();
   exit;
	}
$nrows = mssql_num_rows($result);

 ?>
<table border="0" cellpadding="0" cellspacing="1" width="980px" bgcolor="#336699" style="font-family: Arial; font-size: 11pt;">
<tbody>

<?php  
while ($row = mssql_fetch_array($result, MSSQL_NUM)) {
echo '<tr bgcolor="#ffffff">';
echo '<td width="54px">'.$row[0].'</td>';
echo '<td>&nbsp;'.$row[1].'&nbsp;</td>';
echo '<td>&nbsp;'.$row[3].'</td>';
echo '<td>&nbsp;'.$row[4].'</td>';
echo '<td>&nbsp;'.$row[5].'</td>';
echo '<td>&nbsp;'.$row[6].'</td>';
echo '</tr>';
echo "\r\n";
	}
}
?>
</tbody> 
</table>

</body>
</html>

Open in new window

0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 80 total points
ID: 39864412
In my opinion the best language is the one you already know.   If you don't already know one of the languages, the best language is the one your friends know.  You might look around for PHP or Python user groups in your area.  Often Meetup.com is a good way to find like-minded individuals.

Python and PHP are both general-purpose programming languages.  PHP does very little to enforce standards, so a little more personal discipline is necessary to avoid making a mess of PHP scripts.  But that said, PHP is by far the most popular language for web app development, from the smallest hacked-together stuff to the largest (WordPress, Facebook, etc.).

Regarding this:
Is there any hope in possibly copying tables from MS SQL to mySql, updating on an hourly basis? It would be read-only, not really a synchronization.
Sure, there is hope.  You could use some kind of a CRON job or scheduled task.  The automated copy would need to be able to complete, and raise the "all clear" signal in less than an hour, minus the time it would take to do the report work you are planning to write.  Another way of looking at this is "Can I start by copying the MSSQL data base into a MySQL database, then running my reports against the MySQL data base?"  But that causes me to say, "Why not just use the MSSQL data base directly?"  Even if the interface is clunky, the additional complexity of using two databases when one is needed seems a bit counterintuitive.

SQL is SQL, but there will still be differences in the way the query syntax comes together. For example, MySQL uses the LIMIT clause; MSSQL implements this with the TOP() clause.

If you choose PHP, you will find no shortage of PHP examples all over the internet.  I urge you to avoid those.  PHP is a living language and many of the examples are old, full of security risks, obsolete or just plain bad code.  There is a joke in PHP circles about the PHP mascot, the Elephpant.  The mascot for most of those "free examples" is probably the Mastadon, since many of them seem to be from another age.  A good place to start learning PHP is with Welling/Thompson.  Buy the latest version, and when a new version comes out, buy that and give the old copy to one of your enemies.

In case you get involved with MySQL, you need to be aware of PHP's changing posture with respect to the data base extensions.  This article can help you avoid getting tangled up in an obsolete extension.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Best of luck with the project, ~Ray
0
 

Author Closing Comment

by:oakie22
ID: 39864742
These are great responses. Thanks a bunch!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article will show, step by step, how to integrate R code into a R Sweave document
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to count occurrences of each item in an array.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now