PHP or SQL

[The Little Guy]

Of the two, which one do you prefer to do most of your data processing, why?

[Maq]

Of the two, which one do you prefer to do most of your data processing, why?

This question depends on what you're doing.  They are used for 2 totally separate things, you can't really compare them.

[The Little Guy]

Yes you can when your comparing data processing.

[Maq]

Yes you can when your comparing data processing.

Then I'm either missing something or simply don't understand.  Could you provide an example of when it's difficult to decide which one to use?

[per1os]

TLG, SQL all the way.

 

PHP's flat file system is not really meant for storing Relational Data, which SQL and other stuff are built to handle that extremely well. So Maq's statement holds true in that sense. If you are doing stuff that stores fine in a flat file, then yea, a flat file would be better. But 9 times out of 10 you want a relational database.

 

So the answer, SQL is way better for handling data that you are constantly accessing and can potentially have multiple users accessing it and changing it constantly, not to mention it is much easier to make changes to and add audit trails etc.

[The Little Guy]

very basic example (off the top of my head):

<?php
$sql = mysql_query("select * from cash_spent");
$array = array();
$rows = mysql_num_rows($sql);
$total = 0;
while($row = mysql_fetch_assoc($sql)){
    $array[$row['member_id']]['amount'] += $row['amount'];
    $array[$row['member_id']]['age'] = $row['age'];
}
foreach($array as $key => $member){
    if($member['age'] > 30 && $member['age']  < 60){
        if($member['amount'] > 100,000){
            echo "Member: ".$key." spent more than $100,000 and is older than 30 and less than 60\n";
        }
    }
}
?>

 

MySQL Equivalent:

$sql = mysql_query("select *, sum(amount) as total from cash_spent where age > 30 and age < 60 group by member_id having total > 100000");
while($row = mysql_fetch_assoc($sql)){
    echo "Member: ".$row['member_id']." spent more than $100,000 and is older than 30 and less than 60\n";
}

 

Note that both of theses are untested, but I hope you get my drift.

 

I know some would say duh, of course the mysql way because the code is shorter, but the php way is more visual to others, and makes most sense to them.

 

Now there are some queries that when written (and formatted nice and pretty) may take 30+ lines and that is another reason someone may choose the php way over the mysql way, mainly because they can read/understand it better, and yet they both end up with the exact same output.

 

Does that make more sense?

[Philip]

Most of the time MySQL is more efficient to use data processing - but ONLY if the data was already in the database.

 

E.g. As mentioned, a flat file -> database & needing to filter out various rows isn't really a MySQL job and moreso a PHP job. I still consider that data processing.

[gizmola]

If you can do something in SQL rather than having to drop to procedural it is certainly easier than writing a procedural program, and in most cases it is also faster.  The only time that is not true in my experience is when you need to transform mass amounts of data, and the cost of SQL transactions around batch updates slows things down to an unacceptable level.  Those circumstances tend to be few and far between outside data warehousing and the need to bulk load lots of data.

[proggR]

Correct me if I'm wrong TLG but you're not referring to the act of storing the data but the act of processing it once you have it?

In other words you're asking whether someone would rather write a well written SQL script that grabs exactly what you require for a given task or just grab a bunch of unneeded data and process it with PHP?

If that's the case I'd still choose SQL since it's just an extra step to have PHP worrying about extra data.

[Maq]

If you can do something in SQL rather than having to drop to procedural it is certainly easier than writing a procedural program, and in most cases it is also faster.  The only time that is not true in my experience is when you need to transform mass amounts of data, and the cost of SQL transactions around batch updates slows things down to an unacceptable level.  Those circumstances tend to be few and far between outside data warehousing and the need to bulk load lots of data.

Exactly.  Rule of thumb, usually if you can do it SQL, than do it in SQL.  Of course there are some exceptions.  This is what SQL & databases were designed for.  If you have a table of 3 million rows you're not going to extract them all and see if ID = 1 with PHP, that would take forever, you're obviously going to do this in SQL.  Again, don't really see how you can compare the two.

[per1os]

TLG, hopelessX hit the nail on the head. You are not comparing the storing of data, just the processing.

 

It all depends on your needs, but in general, it would be better to pull the data out in it's raw format and let the processor (in this case PHP) handle the display. This is because you should have your View Logic and Business Logic separated, going with those standards (MVC route) PHP should handle all of the display parts. You can, however, pull certain stuff out of SQL how you want, such as dates, but it would be much easier to change the view logic to handle the date vs the SQL (and less detrimental to the site if you messed up the SQL upon editing) and I would rather not tax my SQL server when PHP can handle it.

 

So, my vote will go with PHP and a separation in the form of a Model View Controller setup. 

[The Little Guy]

TLG, hopelessX hit the nail on the head. You are not comparing the storing of data, just the processing.   

 

Correct.

 

 

I wen't SQL, mostly because it is:

- It's usually faster

- It can get all the data you need with less code

- And a few other points I can't think of at the moment

[Pikachu2000]

TLG, hopelessX hit the nail on the head. You are not comparing the storing of data, just the processing.

 

It all depends on your needs, but in general, it would be better to pull the data out in it's raw format and let the processor (in this case PHP) handle the display. This is because you should have your View Logic and Business Logic separated, going with those standards (MVC route) PHP should handle all of the display parts. You can, however, pull certain stuff out of SQL how you want, such as dates, but it would be much easier to change the view logic to handle the date vs the SQL (and less detrimental to the site if you messed up the SQL upon editing) and I would rather not tax my SQL server when PHP can handle it.

 

So, my vote will go with PHP and a separation in the form of a Model View Controller setup. 

 

He actually said the exact opposite of that; he'd write the query to return only what's needed.