3
votes

I'm building a web application with PhalconPHP 1.3.4, I'm trying to select data from multiple tables at the same time, as certain values are stored in another table via relationships.

My query works fine in MySQL workbench, however when I try to execute it with PhalconPHP, i get the following error:

Scanning error before 'Bookings, Trips]...' when parsing: SELECT count(bkId) AS bookings FROM [Bookings, Trips] WHERE ((bkUserId = :userId:) AND (CURDATE() > tripFromDate)) AND (CURDATE() < DATE_ADD(tripFromDate, INTERVAL 2 WEEK)) (172)

My PHP code:

$query = new Builder();
$query->columns("count(bkId) AS bookings");
$query->from('Bookings, Trips');
$query->where("bkUserId = :userId:");
$query->andWhere("CURDATE() > tripFromDate");
$query->andWhere("CURDATE() < DATE_ADD(tripFromDate, INTERVAL 2 WEEK)");
$result = $query->getQuery()->execute(["userId" => $userId])->bookings;
return ($result > 0);

I've read that this could be a bug, however it should have been fixed in version 1.3.2, is there something I am currently doing wrong?

Thanks for the help in advance.

1

1 Answers

3
votes

I don't believe two columns in the from is the proper syntax. In the documentation there is this example:

$builder->from('Robots')
    ->addFrom('Parts', 'p');

So your example should be something like

 $query = new Builder();
    $query->columns("count(b.bkId) AS bookings");
    $query->from('Bookings', 'b');
    $query->addFrom('Trips');
    $query->where("b.bkUserId = :userId:");
    $query->andWhere("CURDATE() > b.tripFromDate");
    $query->andWhere("CURDATE() < DATE_ADD(b.tripFromDate, INTERVAL 2 WEEK)");

Either this or you're probably better off converting to use a join.