DB Error: mismatch

[Toni]

The following code:

$sql = 'SELECT user_id FROM ' . TC_DATA_NEW_TABLE_NAME . 'subscribe
WHERE ' .
TC_UTIL::parentClause($this->getType(), TC_DATA_NEW_TABLE_PREFIX .
'_type');
$res = $this->_db->query($sql, DB_FETCHMODE_ASSOC); if
(DB::isError($res)) { die($res->getMessage() . ' [' .
$res->getUserInfo() . '] - ' .$res->getDebugInfo() . ' in: ' .
__FILE__ . ' at line nr. ' . __LINE__); }

Generates the following error message:

DB Error: mismatch [SELECT user_id FROM data_newsubscribe WHERE
(tc_datanew_type='' OR tc_datanew_type LIKE 'ligfiets' OR
tc_datanew_type LIKE 'ligfiets.links' OR tc_datanew_type LIKE
'ligfiets.links.thuispagina' )] - SELECT user_id FROM
data_newsubscribe WHERE (tc_datanew_type='' OR tc_datanew_type LIKE
'ligfiets' OR tc_datanew_type LIKE 'ligfiets.links' OR tc_datanew_type
LIKE 'ligfiets.links.thuispagina' ) in:
c:\data\www\be\technetium\objects\www\include\TC_D ATA_NEW_MAIL.php at
line nr. 31

Seaching in google gives me lots of the same errormessages, especially
on pages with wiki. The other pages point in the direction of place
holders. In a german newsgroup this error is also discussed (also with
reference to place holders).

I don't use placeholders in the query, zo I don't have any idea how to
solve this problem. Can anybody of you give me please some
suggestions.

Thanks,

Toni

[Erwin Moller]

Toni wrote:

The following code:

$sql = 'SELECT user_id FROM ' . TC_DATA_NEW_TABLE_NAME . 'subscribe
WHERE ' .
TC_UTIL::parentClause($this->getType(), TC_DATA_NEW_TABLE_PREFIX .
'_type');
$res = $this->_db->query($sql, DB_FETCHMODE_ASSOC); if
(DB::isError($res)) { die($res->getMessage() . ' [' .
$res->getUserInfo() . '] - ' .$res->getDebugInfo() . ' in: ' .
__FILE__ . ' at line nr. ' . __LINE__); }

Generates the following error message:

DB Error: mismatch [SELECT user_id FROM data_newsubscribe WHERE
(tc_datanew_type='' OR tc_datanew_type LIKE 'ligfiets' OR
tc_datanew_type LIKE 'ligfiets.links' OR tc_datanew_type LIKE
'ligfiets.links.thuispagina' )] - SELECT user_id FROM
data_newsubscribe WHERE (tc_datanew_type='' OR tc_datanew_type LIKE
'ligfiets' OR tc_datanew_type LIKE 'ligfiets.links' OR tc_datanew_type
LIKE 'ligfiets.links.thuispagina' ) in:
c:\data\www\be\technetium\objects\www\include\TC_D ATA_NEW_MAIL.php at
line nr. 31

Seaching in google gives me lots of the same errormessages, especially
on pages with wiki. The other pages point in the direction of place
holders. In a german newsgroup this error is also discussed (also with
reference to place holders).

I don't use placeholders in the query, zo I don't have any idea how to
solve this problem. Can anybody of you give me please some
suggestions.

Thanks,

Toni

Hi Toni,

Does the database (which one??) accept that SQL-statement anyway?
Did you try to feed it directly by using commandprompt??
To name something:

tc_datanew_type LIKE 'ligfiets.links'

Maybe tc_datanew_type isn't a text/string but a number????
(Just guessing)

You should give more information to us.
Like: What is the definition of the table you are querying?
What database do you use?

My guess is that you use the WRONG DATATYPE in your query.

Regards,
Erwin Moller

[Toni]

Erwin Moller wrote, in respnse to my question:

Generates the following error message:
DB Error: mismatch [SELECT user_id FROM data_newsubscribe WHERE

(...)
Does the database (which one??)
MySQL (version 3.23.49 on the windows machine and 3.23.56 on the linux
box)

accept that SQL-statement anyway?

Yes:

mysql> SELECT user_id FROM data_newsubscribe WHERE (tc_datanew_type=''
OR tc_dat
anew_type LIKE 'ligfiets' OR tc_datanew_type LIKE 'ligfiets.links' OR
tc_datanew
_type LIKE 'ligfiets.links.thuispagina');
+---------+
| user_id |
+---------+
| 1733 |
| 3 |
| 3 |
| 1835 |
| 2219 |
| 3522 |
| 546 |
| 546 |
| 3815 |
| 4023 |
| 13 |
| 2457 |
+---------+
12 rows in set (0.01 sec)

No problem.
Did you try to feed it directly by using commandprompt??
See above.
Maybe tc_datanew_type isn't a text/string but a number????
(Just guessing)
Sorry, I gave to litte information, but you guessed wrong 8-)

CREATE TABLE `data_newsubscribe` (
`tc_datanewsubscribe_id` int(11) NOT NULL auto_increment,
`user_id` int(11) NOT NULL default '0',
`tc_datanew_type` varchar(255) NOT NULL default '',
`tc_datanewsubscribe_update` int(11) NOT NULL default '0',
PRIMARY KEY (`tc_datanewsubscribe_id`)
)
You should give more information to us.
Like: What is the definition of the table you are querying?
What database do you use?

Thanks for your efforts.

Sorry, I gave to little information. I hope you (as in the members of
this group) can give me more help with the information I've given
now.

Toni

[Erwin Moller]

Toni wrote:

mysql> SELECT user_id FROM data_newsubscribe WHERE (tc_datanew_type=''
OR tc_dat
anew_type LIKE 'ligfiets' OR tc_datanew_type LIKE 'ligfiets.links' OR
tc_datanew
_type LIKE 'ligfiets.links.thuispagina');
+---------+
| user_id |
+---------+
| 1733 |
| 3 |
| 3 |
| 1835 |
| 2219 |
| 3522 |
| 546 |
| 546 |
| 3815 |
| 4023 |
| 13 |
| 2457 |
+---------+
12 rows in set (0.01 sec)

CREATE TABLE `data_newsubscribe` (
`tc_datanewsubscribe_id` int(11) NOT NULL auto_increment,
`user_id` int(11) NOT NULL default '0',
`tc_datanew_type` varchar(255) NOT NULL default '',
`tc_datanewsubscribe_update` int(11) NOT NULL default '0',
PRIMARY KEY (`tc_datanewsubscribe_id`)
)

Hi Tony,

I see only 1 more thing that is strange.
You use LIKE 'blabla' instead of LIKE '%blabla%'

The % means: match anything (before or after the literal string).

Using LIKE and no % is a little meaningless because it means the same as the
= sign.
But I am unsure if it gives an error.
I have never touched MYSQL (I am a postgresql fanatic), but have a look if
that is the source of the error.

Sorry I cannot help you any futher.

Good luck.
If you find the solution, be sure to post it here for others to enjoy. :-)

Regards,
Erwin Moller

[Toni]

Erwin Moller wrote:

I see only 1 more thing that is strange.
You use LIKE 'blabla' instead of LIKE '%blabla%'
I know, this code is in a function that allows the use of wildcards.
But I am unsure if it gives an error.

I never hurts to test, but I don't think it matters since phpmyadmin
uses them in the same way.

DB Error: mismatch [SELECT user_id FROM data_newsubscribe WHERE
((tc_datanew_type = '') OR (tc_datanew_type = 'ligfiets') OR
(tc_datanew_type = 'ligfiets.links') OR (tc_datanew_type =
'ligfiets.links.thuispagina') )] - SELECT user_id FROM
data_newsubscribe WHERE ((tc_datanew_type = '') OR (tc_datanew_type =
'ligfiets') OR (tc_datanew_type = 'ligfiets.links') OR
(tc_datanew_type = 'ligfiets.links.thuispagina') ) in:
c:\data\www\be\technetium\objects\www\include\TC_D ATA_NEW_MAIL.php at
line nr. 31

Nope, same result. After the change from like to = I also added extra
parentheses, bebause I was unsure about the order of operators. The
message I've included in my post is from that test.

Could this be a bug in either PEAR, PHP or MySQL and do you know
how/where to check this?

Thanks for your efforts,

Toni

[Erwin Moller]

Toni wrote:

<snip>

Could this be a bug in either PEAR, PHP or MySQL and do you know
how/where to check this?
Hi Tony,

I think you might be right this is maybe a bug.

I would suggest that you just repost in this group with 'PEAR-DB BUG' in the
subject.
Maybe that will attrack the attention of some PEARDB-guru.
I am out of ideas and am far from a PEARDB-guru.


Thanks for your efforts,

You are welcome.
Toni

Good luck!

Regards,
Erwin Moller

[Toni]

I wrote:
(...)

$res = $this->_db->query($sql, DB_FETCHMODE_ASSOC); if

(...)
Without knowing the second argument is a list of parameters, and not a
way to set the fetchmode. Removed it, and set the fetchmode at the
appropriate place => problem solved.

Sorry for bothering you,

Toni

[Erwin Moller]

Toni wrote:

I wrote:
(...)

$res = $this->_db->query($sql, DB_FETCHMODE_ASSOC); if

(...)
Without knowing the second argument is a list of parameters, and not a
way to set the fetchmode. Removed it, and set the fetchmode at the
appropriate place => problem solved.

Sorry for bothering you,

Toni

Glad you found it. :-)

Regards,
Erwin Moller