envms / fluentpdo Goto Github PK

Add support for subselect but I don't know how to do it...

HI,

I have some complex SQL queries that I couldn't replicate with FluentPDO. Is it possibile to run a query "manually" by enterint the raw SQL?

EDIT: in the meanwhile, i've found a way to select only a few columns thanks to an issue here on git and i've managed to create my query.

An additional question though: how can I debug queries? On SELECTs, I'm using

var_dump($query->execute());

..but is there a better way (that for example doesn't execute the query in INSERT\UPDATE\DELETE statements)?

How would I generate a WHERE clause containing an OR condition? For example:

SELECT tid FROM foo_table WHERE foo = 1 OR bar < 5;

Hello,
\PDOStatement::fetchAll() method allows to fetch data in various ways, including object creation and single-column fetch. These features are disabled by FluentPDO.

FluentPDO's fetchAll should be renamed to something else.

Thank you.

Hello,

When i try this code :

$fpdo   = new FluentPDO($dbh);
$query  = $fpdo -> from('tabledoesnotexist');
$rows   = $query -> fetchAll();
var_dump( $rows );

the result is false

but there is no SQL errors.

Is it a problem from my code ?

Thank's for your answers :)

Kaimite

Hello,
when building more complex SQL queries, automatic joins are very painful feature. Only little typo in table alias will add new joins and completely hide where I made an error, producing wierd and totally unrelated error messages. It is very hard to find where is actual error.

Solution can be very simple: Add option to disable "smart" joins. When "smart" joins are disabled, all unknown aliases are passed to the result query with no change/action, and database will produce accurate error about unknown table/alias.

It is also possible to make "smart" joins a bit less smart by requiring explicit mention of the join.

For example:

• $fpdo->from('comment')->select('user.name') will fail,
• but $fpdo->from('comment')->smartJoin('user')->select('user.name') will work as expected without need to write all boring stuff.
• This will also make $fpdo->from('comment')->smartJoin('user')->select('usr.name') fail with "unknown table/alias 'usr'" error, instead of adding wrong join to usr table.

Thank you!

I think user will be confusing why two function for delete...

90% cases don't need define DELETE clause

$fpdo->delete('table')->where('id', 1);
// this is more natural but it is sql error

for this reason I think to remove deleteFrom and if you need from you can do this:

$fpdo->delete('t1, t2')->from('t1')->where('id', 1);

without from it will be

$fpdo->delete('t1')->where('id', 1);
// DELETE FROM t1 WHERE id = 1

Old pages are outdated may be create new gh-pages?

With a clean install of FluentPDO and a clean fblog database, running the tests produces errors like these below:

838:tests$ php run-tests.php
PHP Warning:  PDOStatement::execute(): SQLSTATE[HY000]: General error: 1364 Field 'published_at' doesn't have a default value in FluentPDO/BaseQuery.php on line 104 failed tests/32-insert.phpt (insert into)
PHP Warning:  PDOStatement::execute(): SQLSTATE[HY000]: General error: 1364 Field 'user_id' doesn't have a default value in FluentPDO/BaseQuery.php on line 104
PHP Warning:  PDOStatement::execute(): SQLSTATE[HY000]: General error: 1364 Field 'user_id' doesn't have a default value in FluentPDO/BaseQuery.php on line 104
failed tests/33-insert-update.phpt (INSERT with ON DUPLICATE KEY UPDATE)
-- Adminer 3.2.2 MySQL dump
0.058 s, 2582 KiB

These errors seem to be all caused by NOT NULL declarations on columns which don't have DEFAULT values.

How do i add a raw where statement without parameters, like this:
AND EXISTS ( select 1 from cms_content_to_tag tf1, cms_tag tf2 where c1.cms_id=tf1.cms_id and tf1.tag_id=tf2.tag_id and tf2.tag_code like "A%" ) AND

perhaps add a ->where_raw() method tht just call ->AddStatement('WHERE',$raw_query) ?

What I need to do is something like

->where('
    ( ownerType = :parentType AND ownerId IN (:parentIds) )
        OR
    ( ownerType = :childType AND ownerId IN (:childIds) )
',array(
    ':parentType'=>'parent',
    ':parentIds'=>array(1,3,6,7),
    ':childType'=>'child',
    ':childIds'=>array(2,3,4,5,6,7),
)

How is this possible with fluentPDO,

Would I have to pass all my own parameters and insert the strings directly into where the placeholders are.

Works:

$query = $fpdo->from('users')->where('user_id = ?', 2);
$row = $query->fetch();
var_dump($row);

Does not work:

$query = $fpdo->from('users', 2);
$row = $query->fetch();
var_dump($row);

Producing the error:
Fatal error: Call to a member function fetch() on a non-object in SelectQuery.php on line 73

It seems the execute() method in BaseQuery.php does not return the right result (it returns false).

Because SelectQuery::fetch() and its brethren call PDO::fetch() this way

$this->execute()->fetch();

any time execute() fails and returns false instead of an object, the PHP script terminates with a fatal PHP error, with a message like this:

Thu Jun 20 13:38:38 2013] [error] [client fe80::1] PHP Fatal error:  Call to a member function fetchAll() on a non-object in FluentPDO/SelectQuery.php on line 115

Could better error handling be implemented?

Hi!

I want to execute a query with a LIKE %% Statement and it works well when the terms are letters and words but when I try with just a number like a year, nothing is returned. Could someone help me to achieve this query?

Here are my query:

$query = $fpdo->from('table')->where('keywords LIKE ?', "%$terms%")->fetchAll();

Thanks!

Current way to do things

Long version to check if member exists:

$rows = $fpdo->from('member')->where('id',$id)->select(null)->select('1')->fetch();
if(is_array($rows) && count($rows) > 0){
    $exist = true;
}else{
    $exist = false;
}

Long version to get a member:

$row = $fpdo->from('member')->where('id',$id)->fetch('email');

New features

Short version to check if member exists:

$exist = $fpdo->exist('member')->where('id',4)->execute(); //return boolean
$exist = $fpdo->exist('member')->id(4)->execute(); //equivalent

Short version to get a member:

$row = $fpdo->from('member')->id(4)->fetch('email');

Delete and update short version:

$fpdo->update('member')->id(4)->set(['email'=>'@@@']);
$fpdo->deleteFrom('member')->id(4);

My ideas is good?

$fpdo->from("mytable") results in "SELECT mytable.* FROM mytable..." but in most cases with real databases, one does not want to retrieve all of the columns from the primary table.

In my case, the primary table is large and has many columns. I only want to select a very few of them. Retrieving extra data slows down the retrieval of the data and consumes more network bandwidth, as database and web servers are not on the same host machine.

since the documentation doesn't cover how you should handle the result that fpdo gives i'm kind of stuck

i have this query

$bxslider = $fpdo->from('bxslider')->fetchAll('box_img, box_link, box_title, box_desc');

and now a foreach loop

foreach ($bxslider as $bx) {

    $BXSLIDER = "<li>";
                            $BXSLIDER .= "<em>";
                                $BXSLIDER .= "<img src='{$bx['box_img']}' alt='{$bx['box_title']}' />";
                                $BXSLIDER .= "<a href='{$bx['box_link']}'><i class='fa fa-link icon-hover icon-hover-1'></i></a>";
                                $BXSLIDER .= "<a href='{$bx['box_img']}' class='fancybox-button' title='{$bx['box_title']}' data-rel='fancybox-button'><i class='fa fa-search icon-hover icon-hover-2'></i></a>";
                            $BXSLIDER .= "</em>";
                            $BXSLIDER .= "<a class='bxslider-block' href='#'>";
                                $BXSLIDER .= "<strong>{$bx['box_title']}</strong>";
                                $BXSLIDER .= "<b>{$bx['box_desc']}</b>";
                            $BXSLIDER .= "</a>";
                        $BXSLIDER .= "</li>";

}

i get only the last inserted row ...

is this ongoing or you abandoned the project
you haven't committed for 8 months now ...

I have a table called "set", and since this is a reserved word and FluentPDO doesn't escape the table names, any FluentPDO transaction on this table fails.

(No need to comment on the insanity of my table naming convention ;-))

I produced a SQL error and setting up the debug I get que following message:

Warning: PDOStatement::execute(): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'tbinterveniente.id_dados' in 'where clause' in /www/usuario/evandro/system/lichtner-fluentpdo/FluentPDO/BaseQuery.php on line 117

Now, how can I get the error code (SQLSTATE[42S22]) and the rest of the message separately?

Thanks.

Currently I'm using "$fpdo->from('table')->select(null)->select('count(*)')"

I find out FluentPDO always do "SELECT table.*", what if I only want two columns?

please delete

May be this shortcuts will be useful:

$fpdo->update($table, $id, $values);
$fpdo->delete($table, $id);
$fpdo->deleteFrom($table, $id);

Hi!

I'm trying to achieve this with fluentpdo:

UPDATE product SET stock = stock - 1 WHERE id = 5;

I've tried some ideas but none worked yet.. Could you help me? Thanks a lot!

Queries tried so far:

$query = $fpdo->update('products')->set(array('stock' => -1))->where('id', 5)->execute();
$query = $fpdo->update('products')->set(array('stock' => '- 1'))->where('id', 5)->execute();
$query = $fpdo->update('products')->set(array('stock' => 'stock - 1'))->where('id', 5)->execute();

Is it possible to use just the query builder in FluentPDO? I use a different library (not PDO) to execute queries, but would like to programmatically produce my SQL statements.

I have a case where I need to UNION 2 queries.

SELECT * FROM tableOne WHERE name = 'bob'
UNION
SELECT * FROM tableTwo WHERE name = 'bob'

I'm trying to figure out what the syntax should be if I were to build this into FluentPDO. My first thought is to build 2 fluent PDO objects, then on one of them, be able to call ->union()

$tableTwoFluent = $fluent->from('tableTwo')->where('name', 'bob');
$tableOneFluent = $fluent->from('tableOne')->where('name', 'bob')->union($tableTwoFluent);

What are your thoughts?

Test:

$fpdo->from('manufacturer AS man')->getQuery()

Expected:

SELECT man.*
FROM manufacturer AS man

Result:

SELECT manufacturer AS man.*
FROM manufacturer AS man
LEFT JOIN man ON man.id = manufacturer AS man.man_id

raw code here

public function execute() {
    $result = parent::execute();
    if ($result) {
        $result->rowCount();  // here
    }
    return false;
}

should $result->rowCount() return ??

I'm executing following query to select specific fields from table, but it is returning all fields.

$query = $this->fpdo->from('table2')->where('someid', $someid)->select('field1');

I'm looking if there is some built in solution to perform subquery with fluent pdo.

What I want to do :

SELECT * FROM ... WHERE something IN ( SELECT id FROM ... WHERE something AND something AND ... LIMIT 30)

But in my case the subquery is much more complex and using FPDO to build it would be a real +

Is there a solution, or would it need to implement it ?

I'd like to do something like this:

$fpdo->insertInto("table")
    ->values(array(
        "created" => new FluentLiteral("NOW()")
    ))
    ->execute();

.. but unfortunately it looks like InsertQuery.php doesn't handle FluentLiteral anywhere.

I saw the test files are written using some odd phpt syntax.
Is there a plan for adding phpunit support? how much coverage this project have?

Hi, how about:

WHERE column LIKE '%value%' ?

SELECT COUNT(*) FROM article;

is this hard to implement ?

Once again thanks for the DELETE and UPDATE functions 👍

Hi,

I'm new to PHP and recently started using FluentPdo in a project and I like it very much. I some queries regarding update query, as per my understanding, update query does not get executed if values passed are same as in database. Right?
Second follow-up question is, if updation fails, how I can identify if is failed due to save values or other reason.

Thanks

Hello,

/* ... */
    ->select('buyer.name')
    ->leftJoin('`user` AS buyer ON item.buyer_id = buyer.id')

Fails with wierd error becouse FluentPDO adds join to buyer table.

After removing back quotes around user, query works fine:

/* ... */
    ->select('buyer.name')
    ->leftJoin('user AS buyer ON item.buyer_id = buyer.id')

I guess there is an error in regexp used for parsing these joins.

In this case is possible to ommit quoting around table name, but what if table name is some reserved word, like date or character?

It would be nice to be able to create a FluentPDO object from direct queries. I suggest a using a tokenizer for this (e.g. http://code.google.com/p/php-sql-parser/ ).

This would allow a query like to return an FluentPDO object:

$sql = 'SELECT article.*, user.name FROM article';
$fpdo = FluentPDO::parseFromSQL($sql);

This would generate the same query as on the README:

SELECT article.*, user.name 
FROM article 
LEFT JOIN user ON user.id = article.user_id

using example below:
$this->fluentPdo->from('tablename')->select('col1');
Sql became below:
"Select tablename.*,tablename.col1 from tablename";

As you know due to performance and memory usage "*" is not a good choice

How can i make query without " * "?

Maybe I am mistaken but I am not able to select only one column from the table
I tries the following
'SELECT col_name FROM table_name'
by
$fpdo->from('tbl_name')->select('column_name')

and in fact getting the code equivalent to
'SELECT table_name.*, col_name FROM table_name'

clear string concatenating in SelectQuery, line 42 -
$this->statements['SELECT'][] = $this->fromAlias . '.*';

home page said it was on the todo list, the test docs had a good example, but it does not work.

log entry: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 1064 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 '' at line 1 in libs/FluentPDO/BaseQuery.php on line 83

kept it simple - id/text field.
the code:

$pdo = new PDO("mysql:dbname=sorted", $dbuser, $dbpass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$pdo->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
$fpdo = new FluentPDO($pdo);
$ex = explode(',',$_POST['order']);
$query = $fpdo->insertInto(array('text'=>$ex));

the reads work perfect thought.

tia,

TODO

Implement INSERT, UPDATE, DELETE, ...

It would be nice if results could be optionally returned in PDO::FETCH_NUM format.

Documentation on web page says:

select($columns[, ...])

which looks like

select('column1', 'column2')

should work, but only

select('column1, column2')

(single string parameter) works.

I suggest using func_num_args() and func_get_arg() to collect a variable number of column names and build the comma separated list of column names internally. I also suggest to allow for an array parameter to select() that contains the select fields. like

select(array('column1', 'column2'))

Perhaps even $key => $value is possible to cover "column1 AS fancy_column_name" as

select(array('column1' => 'fancy_colums_name))

I discovered fpdo only this evening, but i am hooked immediately and apologize upfront if i overlooked something. Documentation is a little bit scattered between web page and readme.md.

How do I go about selecting only one item? e.g.

$query = $fpdo->from('ts_users', 1);
How do I get all the columns from that?

some project use more than one project.
$fpdo->from('datbase1.table1')->getQuery();

this print some left join query string.

consider test:

--TEST--
WHERE reset
--FILE--
<?php
include_once dirname(__FILE__) . "/connect.inc.php";
/* @var $fpdo FluentPDO */

$query = $fpdo->from('user')->where('id > ?', 0)->orderBy('name');
$query = $query->where(null)->where('name = ?', 'Marek');
echo $query->getQuery() . "\n";
print_r($query->getParameters());
print_r($query->fetch());
?>
--EXPECTF--
SELECT user.*
FROM user
WHERE name = ?
ORDER BY name
Array
(
    [0] => Marek
)
Array
(
    [id] => 1
    [country_id] => 1
    [type] => admin
    [name] => Marek
)

actual result:

SELECT user.*
FROM user
WHERE id > ?
    AND name = ?
ORDER BY name
Array
(
    [0] => 0
    [1] => Marek
)
Array
(
    [id] => 1
    [country_id] => 1
    [type] => admin
    [name] => Marek
)

When I made shortcuts I did it inconsistent

$fpdo->from('table', $id);
$fpdo->update('table', $set, 'id', $id);
$fpdo->deleteFrom('table', 'id', $id);

from() expect as second parameter primary key. In update() and deleteFrom() you can set whole WHERE conditions with more than one parameter.

But now I realized that shortcut for update() and deleteFrom() look confusing and complicated. I recommend If you need full WHERE statement use where().

I suggest use shortcuts for all functions in same way like in from(). Only table name and if you want to manipulate with row be primary key add primary key.

$fpdo->from('table', $id);
$fpdo->update('table', $set, $id);
$fpdo->deleteFrom('table', $id);

Question:

1. Do you use shortcuts?
2. Do you agree with this BC break, no problem for you?

May be than I release first version of FluentPDO 1.0.0 in standard semver way to avoid problems with BC breaks in future.

I have this table

CREATE TABLE IF NOT EXISTS `login` (
  `idLogin` bigint(20) NOT NULL AUTO_INCREMENT,
  `idUser` bigint(20) NOT NULL,
  `date` datetime NOT NULL,
  `expiracy` datetime NOT NULL,
  `key` varchar(255) NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `ip` varchar(30) NULL,
  `os` varchar(30) NULL,
  `screen_resolution` varchar(30) NULL,
  PRIMARY KEY (`idLogin`),
  KEY `idUser` (`idUser`),
  UNIQUE KEY `key` (`key`),
  CONSTRAINT `login_idUser` FOREIGN KEY (`idUser`) REFERENCES `users` (`idUser`) ON DELETE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The problem is how the query is created, and I don't know if this bug is because fluentpdo or PHP pdo.

When I do this:

$query = $this->_fdb->insertInto('login')->values($data);
die($query->getQuery());

I get this response:

INSERT INTO login (idUser, date, expiracy, key, active)
VALUES ('1', '2013-11-13 21:18:51', '2013-12-11 21:18:51', 'QMgwJOAy]s0pVt2W/LlFnYUfmx8T&cDBr3[}CG@b{;jXI6zRi57.E4$vq,!S', 1)

The table columns should be like ( \idUser, date, expiracy, key, active) because key is a restricted MySQL Word

can anyone help ?

When using FluentPDO with a typical modphp Apache PHP installation, PHP CLI constants such as STDERR are not defined. Hence, if one has debug enabled and hits an error, messages like the below are issued to where PHP errors are configured to be sent.

[Tue Jun 04 15:27:35 2013] [error] [client ::1] PHP Notice: Use of undefined constant STDERR - assumed 'STDERR' in /disk2//lib/php/FluentPDO/BaseQuery.php on line 136

[Tue Jun 04 15:27:35 2013] [error] [client ::1] PHP Warning: fwrite() expects parameter 1 to be resource, string given in /disk2//lib/php/FluentPDO/BaseQuery.php on line 136

Cannot implement the following 'where' constraint -
''assigned_at BETWEEN STR_TO_DATE(?, '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE(?, '%Y-%m-%d %H:%i:%s')", sorry if it was somewhere here, but how to bind all parameters? Or any other smart solution?