Page 1 of 1

PHP Array Queries

Posted: Thu Jan 28, 2010 3:17 pm
by JakeJ
Maybe I'm way off base and if I am, please disabuse me of my idea with some sound logic.

Wouldn't it make sense to be able to query an array using standard SQL structure? Why isn't that functionality built in to PHP?

For example, it would make a lot of sense to have phparray_query(), phparray_fetch_array() etc. Obviously there would be no db connection string since all arrays would be treated as the same db.

Just my thoughts. Feedback welcome.

Re: PHP Array Queries

Posted: Thu Jan 28, 2010 7:59 pm
by PHPHorizons
C# has LINQ which is a query syntax.

What LINQ really does is gives you a sql syntax, but then the compiler generates C# code that actually performs what that sql syntax would do. In other words, LINQ is emulated sql. There's nothing wrong with that concept though. It's a fantastic concept, and since using LINQ doesn't result in slower code (at least if it does, it's nothing to worry about) because it's converted into regular C# syntax at compile time, it's definitely recommended to use.

PHP isn't compiled... So that would be one downside to a sql style syntax implemented like C#'s. But it would be interesting and would make doing a lot of things easier to do.

Cheers

Re: PHP Array Queries

Posted: Thu Jan 28, 2010 8:27 pm
by JakeJ
After you mentioned LINq I googled linq php and found this!

I haven't tried it yet but I will. Just not today.

http://phplinq.codeplex.com/

Re: PHP Array Queries

Posted: Fri Jan 29, 2010 7:41 am
by PHPHorizons
You know JakeJ, I'm not terribly surprised that someone has made a LINQ inspired PHP class. I'll definitely hand it to them that they have done some great work there.

Do keep in mind that the parsing required for this means there is some overhead, and it's a penalty C# doesn't pay since it's compiled.
Otherwise, and with that in mind, I hope it works for ya as well what you were looking for!

Re: PHP Array Queries

Posted: Fri Jan 29, 2010 12:03 pm
by JNettles
PHPLinq sounds interesting conceptually (and I'm sure its a decent feat of programming) but I wonder at its usefulness nowadays. While this would have been great back in the days when I stored everything flat file with delimiters, or XML - if I'm in a situation where I have a query that complicated I'm more than likely pulling it from a database where it would be much faster to simply perform WHERE statements in SQL.

Re: PHP Array Queries

Posted: Fri Jan 29, 2010 2:03 pm
by Christopher
JakeJ wrote:Wouldn't it make sense to be able to query an array using standard SQL structure? Why isn't that functionality built in to PHP?
Back to the original question ... the reason is that PHP already has fairly powerful array functions. Adding a SQL frontend would just be overkill for something that is already pretty simple. Take a look at the array_walk() function (or simply loop through the array ;)). It is trivial to write a function that would "select" array elements based on an if() statement, or to "delete", "insert" or "update" array elements as well. There are probably SQL parsers around if you wanted to put a SQL frontend on those functions.

Re: PHP Array Queries

Posted: Fri Jan 29, 2010 2:32 pm
by PHPHorizons
It should be noted that the point behind LINQ is to abstract the process of searching and filtering data. There is LINQ to SQL, there is LINQ to Objects and a LINQ to XML, and there might be a LINQ to CSV.

Now LINQ provides one syntax for manipulating data from each source. Therefore, you can write code that performs a "where" type filter on some data, and that code can operate on XML, CSV, SQL, or anything that has a LINQ to it.

arborint wrote:Adding a SQL frontend would just be overkill for something that is already pretty simple.
It can be argued that this is overkill for php (or C# for that matter) if all it was is a SQL frontend, but the question of abstracting the process of data sorting and filtering is so much more than that and actually does have some powerful applications.
arborint wrote:PHP already has fairly powerful array functions
There is no question that that is true. There is also no question that those functions should be learned and mastered by any php programmers.

As far as why LINQ isn't built into PHP (or something like it), there are lots of neat language features that could be built into PHP. Since PHP is developed by people that donate their time and efforts, it's really just a matter of someone having the capability to add this functionality to PHP. Then, it must be accepted by the PHP community, or relagated to being an opt-in php extension.

One must remember that anonymous functions just recently became a part of PHP syntax. The C family of languages have had those for a long time. LINQ is still pretty new to C#.

Re: PHP Array Queries

Posted: Fri Jan 29, 2010 4:34 pm
by Christopher
PHPHorizons wrote:It can be argued that this is overkill for php (or C# for that matter) if all it was is a SQL frontend, but the question of abstracting the process of data sorting and filtering is so much more than that and actually does have some powerful applications.
I think this is less about abstracting and more about where the data goes. There are things like SQLite that also provide an SQL to a data file. If you have enough records then that makes more sense. If you are using arrays in memory then array sort and walk functions do the job.

We could code a simple class right here in this thread that queried arrays using SQL. I think the main question is how much SQL do you want to support? Do you want to support joining arrays? Do you want to support transactions? How many SQL functions (if any)? How about distinct and group by?

Code: Select all

class Array_Sql {
     public function __construct($data=array()) {
          $this->data = $data;
     }
 
     public function query($sql, $prepare_values=array()) {
     }
 
     public function _select() {
     }
 
     public function _insert() {
     }
 
     public function _replace() {
     }
 
     public function _update() {
     }
 
     public function _delete() {
     }
 
}

Re: PHP Array Queries

Posted: Fri Jan 29, 2010 4:50 pm
by JakeJ
I think the main question is how much SQL do you want to support?
Why not all of it? A class that conforms to the ANSI standard to start with, and then it can be extended to support MySQL, PostgreSQL, Oracle, M$ SQL, etc.

Re: PHP Array Queries

Posted: Fri Jan 29, 2010 5:42 pm
by Christopher
JakeJ wrote:Why not all of it?
Because it's huge and most people only use a small fraction of it?

Re: PHP Array Queries

Posted: Fri Jan 29, 2010 6:29 pm
by JakeJ
arborint wrote:
JakeJ wrote:Why not all of it?
Because it's huge and most people only use a small fraction of it?
Start with the obvious features and add the rest as time marches on. That's the beauty of Open Source.

Re: PHP Array Queries

Posted: Fri Jan 29, 2010 7:24 pm
by Christopher
JakeJ wrote:Start with the obvious features and add the rest as time marches on. That's the beauty of Open Source.
So is this list "obvious"?

- SELECT <field list> FROM table WHERE <conditions>
- does <field list> support AS?
- <conditions> : =, !=, >, <, >=, <=, LIKE, NOT LIKE, IS NULL, IS NOT NULL?

- INSERT <INTO> table (<field list>) VALUES (<value list>)
- INSERT <INTO> table SET (<assignment list>)

- REPLACE <INTO> table (<field list>) VALUES (<value list>)
- REPLACE <INTO> table SET (<assignment list>)

- UPDATE table SET (<assignment list>) WHERE <conditions>

What functions are supported? Here is a short list:

SQL Aggregate Functions
MAX() and MIN() functions allow you to display maximum and minimum values in a set.
COUNT() function used to count number of record of each database table.
AVG() calculates the arithmetic average of the series of numbers of its argument
SUM() function uses to sum the values

SQL Mathematical Functions
CEIL(or CEILING) and FLOOR functions are round the numbers. SQL CEIL roundup to the nearest integer value while FLOOR round down to the next least integer value.
RAND() function is used to generate some random numbers at run time.
ABS function returns the absolute value of an numeric input argument.
EXP() function returns e raised to the n-th power (e is the base of the natural logarithm and is equal to approximately 2.718281828459).
PI() function maybe the most simplest mathematical SQL function because it returns a constant value of pi
SQRT function extracts the square root from the positive numeric input argument. SQL SQRT function accepts only positive arguments, andreturns an error when a negative argument is passed in.
POWER() function returns the
ROUND function rounds a number to a specific length or precision.
LOG() function allows us to operate mathematical logarithmic calculation
Trigonometric functions?

SQL String Functions
ASCII() - If you want to find a numerical value of a character which is in range of 0 to 255 you can use SQL ASCII function
LENGTH() or LEN() - To get the length of a string.
CONCAT() - is used to concatenate two or more strings. MySQL allows you concatenate more than two strings while other force exactly two.
CHAR() or CHR() function is the opposite of ASCII. It converts an integer in range 0-255 into a ascii character.
REPLACE() - SQL replace function replaces every occurrence of the string specified as the searched string with the replacement string.
UPPER() and LOWER() - Upper function allow you to convert all characters in a string into uppercase. Otherwise SQL lower function allow you to convert all characters in a string into lowercase.
SUBSTRING() or SUBSTR() - In order to get a part of a string we can use sql substring or substr function.