On March 26, 2019 we launched new MODX Forums. Please join us at the new MODX Community Forums.
Subscribe: RSS
  • Quote from: splittingred at Mar 25, 2011, 12:33 PM

    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
    • Why are you escaping the quotes?
        shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com
      • 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


        • [[!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.
          • 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
            • Quote from: splittingred at Mar 28, 2011, 08: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.
              • 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.
                • 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'`
                  


                  • Quote from: thekiller237 at Mar 28, 2011, 09: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.

                    • Quote from: lvbakker at Mar 29, 2011, 11:19 AM

                      Quote from: thekiller237 at Mar 28, 2011, 09: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