0

I have simple table of hashes with 3 columns . Id is an email address.

Now, I want to retrieve the hash given id and type.

I do this:

$select = $this->getDbTable()->select();
$select->where('id=?', $id)->where('type=?', $type);

And I get

SELECT "hashes".* FROM "hashes" WHERE (id=\'randomemail@randomurl.com\') AND (type=\'email\')

instead of

SELECT "hashes".* FROM "hashes" WHERE (id='randomemail@randomurl.com') AND (type='email')

I have played around with quote and quoteInto, but it keeps escaping the quotes. Everywhere I look, it seems this should not be happening. Where could I be going wrong?

The same query works if type and id are integers though [in which case there are no quotes required]

Thanks!

crazyphoton
  • 623
  • 5
  • 20
  • 1
    are you sure that $id, $type is not quoted before using in the statement? – opHASnoNAME May 22 '12 at 06:48
  • Yes. I did echo $id.$type just before the where and I got randomemail@randomurl.comemail. No quotes. – crazyphoton May 22 '12 at 06:55
  • select() uses `quoteInto()` internally. All I can figure is that you are somehow picking up an extra set of single quotes somewhere. Try changing the query so that it doesn't use `select()`. Might shed some light. – RockyFord May 22 '12 at 07:09
  • 2
    uhm magic quotes enabled? :-) – opHASnoNAME May 22 '12 at 07:22
  • Nope, magic quotes disabled. I ll try doing an update and see if it changes anything.. – crazyphoton May 22 '12 at 07:38
  • 1
    try a clean query and see what happens `$result = $this->getDbTable()->fetchAll("id = $id AND type = $type");` This will at least tell if it's the data being passed or unexpected behavior in select(). – RockyFord May 22 '12 at 08:00
  • Hum sounds crazy but is it possible that " ' " is part of your string? like: ' 'TEST' ' ? – opHASnoNAME May 22 '12 at 09:54
  • @RockyFord - insert worked. So did fetchAll ($this->getDbTable()->fetchAll("id = '$id' AND type = '$type'");) and update. So it is a problem with select. I can use fetchAll to solve my current problem I guess. But it ll be nice to understand what is happening with select.. – crazyphoton May 24 '12 at 07:24
  • Actually, I need to use orWhere for another table, for which I need to use select. So, I am still stuck. – crazyphoton May 24 '12 at 07:37

2 Answers2

0

Try this instead

select = $this->getDbTable()->select();
$select->where('id=?',trim($id,"'"))->where('type=?', trim($type,"'"));
Wouter Dorgelo
  • 11,770
  • 11
  • 62
  • 80
Mr Coder
  • 8,169
  • 5
  • 45
  • 74
0

The problem with the query was with the code after the $select was created. Even though the quotes seem escaped, the select works fine when used with fetchAll or fetchRow.

The following snippet worked correctly,

    $select = $this->getDbTable()->select();
    $select->where('id=?', $id)->where('type=?', $type);;
    $hash = $this->getDbTable()->fetchRow($select)->toArray();

even though $select->__toString() showed

SELECT "hashes".* FROM "hashes" WHERE (id=\'someemail@gmail.com\') AND (type=\'default\') LIMIT 1
crazyphoton
  • 623
  • 5
  • 20
  • what was the original problem? You never did say what method you were trying to use. If you were trying to use find(), that only works with primary keys. A little late for this observation... – RockyFord May 24 '12 at 08:29
  • I couldnt retrieve the values from the database (I was using fetchRow). So I tried debugging by outputting the query, saw the backslashes and assumed that was the problem. Later the problem fixed itself, even though the backslashes remained. I am not exactly sure what fixed it as I was playing around with a lot of things. The code above works fine though. – crazyphoton May 24 '12 at 09:13
  • Don't you just hate that? Now what do you do next time you have the same problem?... :) – RockyFord May 24 '12 at 09:24
  • Haha, atleast I will look beyond the backslashes :) – crazyphoton May 24 '12 at 10:00