Support/Comments for Rowboat 1.0.0-rc1

5 Posts

lvbakker Reply #11, 15 years, 6 months ago

Quote from: splittingred at Mar 25, 2011, 07:33 AM

It defaults to AND; you shouldn’t need that. Although, that statement should still work. Are you sure you have the field names typed right?

thanks for your reply.
I still feel like i am doing something wrong.
Can it be the problem that I am escaping my double quotes?

&where=`{\"field1:=\":\"Foo\",\"field2:=\":\"Bar\"}`
It creates this query now:
SELECT * FROM `test` WHERE `field1` = :field1 AND `field2` = :field2
and gives me no results.

If i do this:
&where=`{\"field1:=\":\"Foo\",\"AND:field2:=\":\"Bar\"}`
it seems to completely ignore the first where clause.
but it does give me results.

Thanks again for your help!

Linda

4,149 Posts

splittingred Reply #12, 15 years, 6 months ago

Why are you escaping the quotes?

shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com

5 Posts

lvbakker Reply #13, 15 years, 6 months ago

I am calling the rowboat snippet from another snippet, and if I don’t escape the double qoutes, the server error log shows me a php parse syntax error:
PHP Parse error: syntax error, unexpected T_STRING, expecting ’,’ or ’;’

And if i escape the double quotes, and exec this:
&where=`{\"field1:=\":\"Foo\",\"AND:field2:=\":\"Bar\"}`
It ignores the first where clause.

thanks for your time.

Linda

110 Posts

salscode Reply #14, 15 years, 6 months ago

[[!Rowboat?
   &table=`products`
   &tpl=`product_base`
   &limit=`10`
   &where=`{"a:=":"blah"}`
   &sortBy=`pid`
&debug=`1`
]]

is producing:

SELECT * FROM `products` WHERE OR `a` = :a ORDER BY `pid` ASC LIMIT 10

Any ideas why it is not creating the correct WHERE statement? (splittingred, I suspect line 152 of rbquery.class.php is incorrect but don’t know enough to be certain).
Thanks

Sal Sodano
President & Co-Founder of SkyToaster
My Website Salscode.

4,149 Posts

splittingred Reply #15, 15 years, 6 months ago

That’s definitely a bug. Try this:

  &where=`{"a":"blah"}`

You don’t need the = unless you’re using a AND/OR conditional.

shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com

110 Posts

salscode Reply #16, 15 years, 6 months ago

Quote from: splittingred at Mar 28, 2011, 03:18 PM

That’s definitely a bug. Try this:
  &where=`{"a":"blah"}`
You don’t need the = unless you’re using a AND/OR conditional.

Just tried that also:

[[!Rowboat?
   &table=`products`
   &tpl=`product_base`
   &limit=`10`
   &where=`{"a":"blah"}`
   &sortBy=`pid`
&debug=`1`
]]

and got this SQL, seems odd that it wouldn’t change.

SELECT * FROM `products` WHERE `a` = :a ORDER BY `pid` ASC LIMIT 10

Sal Sodano
President & Co-Founder of SkyToaster
My Website Salscode.

110 Posts

salscode Reply #17, 15 years, 6 months ago

Line 163 rbquery.class.php

    public function addParam($k,$v) {
        if (!empty($v)) {
            $kz = explode('.',$k);
            $k = !empty($kz[1]) ? $kz[1] : $kz[0];
            if (array_key_exists($k,$this->_params)) {
                $k = $k.uniqid($k);
            }

            $this->_params[$k] = $v;
            $k = ':'.$k;
        } else { $k = '""'; }
        return $k;
    }

It looks like your addParam function is returning the key with a : in front of it instead of the value. addParam is used in building the where statement:
Line 152 rbquery.class.php

$tw[] = $operand.' '.$this->escape($field).' '.$operator.' '.$this->addParam($field,$v);

Which is producing "WHERE key = :key"

Sal Sodano
President & Co-Founder of SkyToaster
My Website Salscode.

13 Posts

feeela Reply #18, 15 years, 6 months ago

Why do you use JSON for column-selection and the where-clause? I think its much harder to read as well to write. And as one can see in the above comments, its a source of lots of errors. I can’t even guess how a combination of different table joins would like in JSON.

Compare the following two params (from your examples):

&where=`{"description:!=":"","OR:name":"Test"}`

&where=`description != '' OR name = 'Test'`

5 Posts

lvbakker Reply #19, 15 years, 6 months ago

Quote from: thekiller237 at Mar 28, 2011, 04:32 PM

Line 163 rbquery.class.php
    public function addParam($k,$v) {
        if (!empty($v)) {
            $kz = explode('.',$k);
            $k = !empty($kz[1]) ? $kz[1] : $kz[0];
            if (array_key_exists($k,$this->_params)) {
                $k = $k.uniqid($k);
            }

            $this->_params[$k] = $v;
            $k = ':'.$k;
        } else { $k = '""'; }
        return $k;
    }
It looks like your addParam function is returning the key with a : in front of it instead of the value. addParam is used in building the where statement:
Line 152 rbquery.class.php
$tw[] = $operand.' '.$this->escape($field).' '.$operator.' '.$this->addParam($field,$v);
Which is producing "WHERE key = :key"

I think the new (:key) "value" is replaced later on by $this->stmt->bindParam(’:’.$k,$v); in the execute function.
It is supposed to replace the :key with the proper value but this does not seem to happen.

I am having some problems with an AND query but i think it comes down to the same trouble as your having.

5 Posts

lvbakker Reply #20, 15 years, 6 months ago

Quote from: lvbakker at Mar 29, 2011, 06:19 AM

Quote from: thekiller237 at Mar 28, 2011, 04:32 PM
Line 163 rbquery.class.php
    public function addParam($k,$v) {
        if (!empty($v)) {
            $kz = explode('.',$k);
            $k = !empty($kz[1]) ? $kz[1] : $kz[0];
            if (array_key_exists($k,$this->_params)) {
                $k = $k.uniqid($k);
            }

            $this->_params[$k] = $v;
            $k = ':'.$k;
        } else { $k = '""'; }
        return $k;
    }
It looks like your addParam function is returning the key with a : in front of it instead of the value. addParam is used in building the where statement:
Line 152 rbquery.class.php
$tw[] = $operand.' '.$this->escape($field).' '.$operator.' '.$this->addParam($field,$v);
Which is producing "WHERE key = :key"
I think the new (:key) "value" is replaced later on by $this->stmt->bindParam(’:’.$k,$v); in the execute function.
It is supposed to replace the :key with the proper value but this does not seem to happen.

I am having some problems with an AND query but i think it comes down to the same trouble as your having.

I changed

$this->stmt->bindParam(':'.$k,$v);

to this:

$this->stmt->bindValue(':'.$k,$v);

on line 184 in rbquery.class.php

The queries all work fine for me now. Is there a reason why bindParam was chosen?

Linda

http://php.net/manual/en/pdostatement.bindparam.php