mikecao/sparrow

A simple database toolkit for PHP

sparrow's Issues

Join fix name conflict

I have a join with 2 table that have the same field name.
But how to distinct the field names?

Usually I do like below:

"SELECT posts.* , categories.* FROM ...."

Now query is just "SELECT * FROM ...."

Thanks!

Group by isn't working

In groupBy method, line 365
you're asking if order property is empty. But it should be groups property, right?

When I execute method groupBy

$sparrow = new Sparrow;
$sparrow->groupBy('id');
print_r($sparrow);

Should be

    [protected::groups] => GROUP BY id

But I get

    [protected::groups] => , id

Model class relationship

Hi,
when it's used model class is possible to setup relationship between models class?
Something like "Belongs To"
Thanks

question

Hello!

I did some test with 'sparrow' is performed.
Here is my database access values ​​and the columns of the table 'test'.

include_once('./library/sparrow/sparrow.php');

$connect = array(
'type' => 'mysql',
'hostname' => 'localhost',
'database' => 'test',
'username' => 'test',
'password' => 'test'
);

$cols = array(
'name',
'vorname'
);

---

$database = new sparrow_sparrow();
$database->setDb($connect);
$database->from('test');
$database->select($cols);
$database->where("name = 'eee'");
$rows = $database->many();

$query = $database->last_query;

echo $query;

SELECT name,vorname FROM test

Where are the select columns ???

---

$database = new sparrow_sparrow();
$database->setDb($connect);
$database->from('test');
// $database->select($cols);
$database->where("name = 'eee'");
$rows = $database->many();

$query = $database->last_query;

echo $query;

SELECT * FROM test WHERE name = 'eee'

O.K.

---

$database = new sparrow_sparrow();
$database->setDb($connect);
$database->from('test');
$database->select($cols);
// $database->where("name = 'eee'");
$rows = $database->many();

$query = $database->last_query;

echo $query;

SELECT name,vorname FROM test

O.K.

---

$database = new sparrow_sparrow();
$database->setDb($connect);
$database->from('test');
// $database->select($cols);
$database->where("name = 'eee' ");
$database->where("vorname = 'eee' ");
$rows = $database->many();

$query = $database->last_query;

echo $query;

Fatal error: Uncaught exception 'Exception' with message 'Database error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'vorname = 'eee''
at line 1' in C:\xampp\htdocs\flight\library\sparrow\sparrow.php on line 892

---

$database = new sparrow_sparrow();
$database->setDb($connect);
$database->from('test');
$database->select($cols);
$database->where("name", 'eee');
$database->where("vorname", 'eee');
$rows = $database->many();

$query = $database->last_query;

echo $query;

SELECT name,vorname FROM test

Where is the where clauses ???

---

$database = new sparrow_sparrow();
$database->setDb($connect);
$database->from('test');
// $database->select($cols);
$database->where("name = 'eee'");
$database->where("vorname = 'eee'");
$rows = $database->many();

$query = $database->last_query;

echo $query;

Uncaught exception 'Exception' with message 'Database error:
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'vorname = 'eee'' at line 1'
in C:\xampp\htdocs\flight\library\sparrow\sparrow.php on line 892

---

I think 'sparrow' is a wonderful class.
It must be improved some things.
Sorry for my bad english.

Sincerely yours

Stephan

mysql_* functions are depreciated and will be removed in future.

Deprecated: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in Sparrow.php on line 622.

PDO Double Escapes on String Submit

I create an object with PDO as my dsn. However when searching on strings it escapes extra.

The below query will output as

$db->from('testcase')->where('name', 'test')->select()->many();
SELECT * FROM testcase WHERE name =''test''

When searching for ->where('id', 1) it works just fine

Tests

Hi Mike.
Do you have tests or something like that for sparrow? I made a small patch to your project but want to be sure that everything is fine.

Making Sparrow and Package on Packagist

Hi @mikecao , just wondering if you had the intention to package up Sparrow for inclusion into Packagist. There is already someone with a fork of your class so not sure if you plan on making this available.
👍 for a great Database Layer. Simple yet elegant.

On duplicate key support?

Has anyone been able to integrate ON DUPLICATE UPDATE into sparrow?

UTF8

Hi,

I just noticed, that sparrow (i'm using pdo) doesn't display special characters correctly (noticed with äöü).
I fixed this by changing line 667 to:

$this->db = new PDO($dsn, $db['username'], $db['password'], array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

By the way: I really appreciate your work and started using Flight as well on a project!

Update: I just made a pull request :)

Expire not used in execute

In execute() method the param $expire seem not used, here:

https://github.com/mikecao/sparrow/blob/master/sparrow.php#L756-L762

seem that fetch() check inside cached file expiration, here for example:

https://github.com/mikecao/sparrow/blob/master/sparrow.php#L1297

is this an issue or missing check of expiration? seem only file cache check it.

thanks

PDO Connection from within Flight

Hey, Just trying to figure out how your DB class is working here - it seems pretty awesome so far, however I have a few questions.

I have managed to get this working within Flight with no problems by doing the following :
Flight::register("db", "flight\services\Sparrow");
Flight::db()->setDb(array(" /* Connection information in here */ "));

However my question is, with Sparrow how do we set our own parameters for the PDO connection? I don't know if it is just me bust I like to use PDO_FETCH_OBJ where I can as I find it easier to work with.

Anyway, thanks for the awesome class! Keep up the great work

Add better error handling support.

For now, all I could find about handling errors when a query is executed is this piece from documentation:

When Sparrow encounters an error while executing a query, it will raise an exception with the database error message.

However, I think that maybe a more manageable approach would be interesting. Example:

$query = $db->from('user')->select()->many();

if($query->success) { // return true or false
    return $query;
} else {
    return $query->listErrors(); // array with errors
}

Some solution like the above would help make easier verifications over the query status.

Fix charset

Hi,
can you please explain how to fix charset for different case?
For example I am using mysql and sqlite, and also pdo in some project.
I try to check what other suggest, but without success.
Maybe you can make a method for handle this?
THanks!!

Null in where()

I have table with field default is null.

id	user	email
1	name1	[email protected]
2	name2	[email protected]
3	name3	NULL

Query:

SELECT users WHERE email IS NULL

Code:

$db->from('users')->where('email', null)->many();

This code is not work.

PDO prepare statment

Hello,

can i use sparrow with PDO prepare statement ?

Your sincerly

Stephan

Question about join

Hi,
when we have join with extra condition like this below:

echo $db->from('user')
->join('role', array('role.id' => 'user.id', 'role.id' => 10))
->select()
->sql();

Then we are passing an array with the same index "role.id".

How to handle this ?

Thanks!

Get and set charset from connection string

The mysql connection string mysqli://user:password@host/database?charset=utf8 allows to add the charset in the query string.

Use $url["query"] in public parseConnection to fetch the query string and look for the charset-parameter using parse_str($url["query"], $array) ... isset($array["charset"]) ...

And apply it to the connection:
$this->getDb()->set_charset(...);
mysql_set_charset(...);

Cache issue

when set expire and $time() < $data['expire'] return null
code line 1298:

if ($data['expire'] == 0) {
    return $data['value'];
}
else if (time() > $data['expire']) {
    $this->is_cached = false;
}

my patch:

if (($data['expire']  == 0) or (time() < $data['expire'])){
    return $data['value'];
}else{
    $this->is_cached = false;
}

Add parenthesis

I have a query with different filter in where clausole.
I would like to do something like below:

SELECT * FROM table
WHERE status=1 AND (title LIKE '%test%' OR content='%test%') AND category_id=2

I have do like this:

$db->from('table')
->where('status', 1)
->where('title %', '%test%')
->where('|content', '%test%')
->where('category_id', 2)
;

But this return without parentesis, like below:

SELECT * FROM table
WHERE status=1 AND title LIKE '%test%' OR content='%test%' AND category_id=2

Support for REDIS

Hi, do you plan to add support for redis in the future ?
I can only see memcache, memcached, apc, xcache, file and memory in README.md

Thanks.

Missing operator !=

I can't see how to make operator not equal to, example

where id != 2

thanks

Selecting data from multiple tables

I am currently trying to construct a query, which uses the following schema.

The query I am attempting to run in SQL is :

SELECT * FROM people, people_const, people_multi, multi_field 
WHERE people.id = people_const.person_id AND people_multi.person_id = people.id
AND people_multi.field_id = multi_field.id AND people.id = "u55154b923d79e";

I have tried the following :

$this->app->db->from("table name", "table name", "table name")

Also this :

$this->app->db->from("table name, table name, table name")

And this :

$this->app->db->from(array("table name", "table name", "table name"))

This is just the start of it of course, but I am going to be tidying up the real query itself as time goes on. How can I select from multiple tables?

Sparrow is not quoting the queries automatically.

Sparrow is not quoting the queries automatically.

When i execute this code:
$db->from($table)->where($where)->select()->many();

with these parameters:

$table = 'produtos';

$where = [
  'codigo' => 99,
];

it gives me this SQL:
SELECT * FROM produtos WHERE codigo=99

and it returns me this:

Array
(
    [0] => Array
        (
            [id] => 478
            [codigo] => 99KIT03
            [saldo] => 3
            [descricao] => Kit 03 - Carregador
        )

    [1] => Array
        (
            [id] => 477
            [codigo] => 99
            [saldo] => 1
            [descricao] => Carregador
        )

)

Now, when i execute this: (with the same parameters)
$db->from($table)->where($where)->select()->one();

it returns me the wrong result, it returns the item with "codigo" "99KIT03", not the "99", because the values of the query are not quoted.

PDO Connection

The function setDb isn't parsing the $db string correctly, when using the pdo_mysql example from the documentation.

Using this:

$db->setDb('pdo_mysql://admin:hunter2@localhost/mydb');

Returns this:

Array (
    [type] => //admin:hunter2@localhost/mydb
    [hostname] => pdo_mysql
    [database] => /admin:hunter2@localhost/mydb
    [username] => 
    [password] => 
    [port] => 
) 

MySQL Function NOW()

Hi,
I would like to execute a query like this :
INSERT INTO logs (name, action, creation) VALUES ('test', 'test', NOW())

Here is what I've tried so far :
$data = array('username' => 'test', 'action' => 'test', 'creation' => now());
$db->from('logs')->insert($data)->execute();

But unfortunately this doesn't work as PHP interprete 'now()' as a PHP function.
I also tried to write "now" instead of "now()" but it returns "0000-00-00 00:00:00" instead of the current datetime.

So I would like to know, is it possible to execute MySQL functions using your database toolkit please ?

Execute no have close function

I see that in the execute function, the connection is opened but it is never closed, which causes it to collapse if there are a number of connections

mysql insert execute

There is an error when I try to insert data to the database.
Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in sparrow.php on line 809

<?php
require 'sparrow.php';

$db = new Sparrow();
$db->setDb('mysql://user:pass@localhost/database');

$data = array('name'=>'Abe');
$db->from('users')->insert($data)->execute();

• Mac OS X 10.6.8
• PHP 5.3.8
• Apache 2.2.20
• MySQL 5.5.15