XOOPS 
Forum
Forum Index General Forums Community Support Modules Support Themes Support Development International Support Module Hack Reviews Theme Designer Talk
  1. Board index / 
  2. XOOPS Community Support forums / 
  3. Q and A 
  4.  / select a single quote from MySQL ?
Register
1
highlander
select a single quote from MySQL ?

  • 2006/11/13 9:36

  • highlander

  • Not too shy to talk

  • Posts: 151

  • Since: 2004/12/5


Hi All,

This can't be hard but it is driving me nuts.
The following query does not produce any results when I can clearly see the correct row in the database :

SELECT * FROM `tablename` WHERE NAME = 'A name with ' a single quote';


I have tried every possible adaptation but can't select a row based on a string with a single quote in it.

Phpmyadmin suggests the following :

SELECT * 
FROM `tablename` 
WHERE `NAME` LIKE CONVERT( _utf8 'A name with \'' a single quote'
USING latin1 ) 
COLLATE latin1_swedish_ci
LIMIT 0 , 30

But still does not produce my row.

The string stored in the database is simply the following :
name with \' a single quote

Can someone please tell me how to select it !?

Kind regards,

Highlander

edit: seems to have been submitted twice, could an admin press delete ?
So, I'm in the park wondering why frisbees get larger as they get closer when suddenly, it hits me...
www.AnimalPedigree.com
2
davidl2
Re: select a single quote from MySQL ?

  • 2006/11/13 10:11

  • davidl2

  • XOOPS is my life!

  • Posts: 4843

  • Since: 2003/5/26


Duplicate deleted
3
Dave_L
Re: select a single quote from MySQL ?

  • 2006/11/13 15:21

  • Dave_L

  • XOOPS is my life!

  • Posts: 2277

  • Since: 2003/11/7


Quote:
The string stored in the database is simply the following :
name with \' a single quote


1) Does the column value start with "name" or "A name"?

2) Does the column value contain ' or \' ?

Personally I waste a lot of time solving quotes-related problems, and wish they would go away. To everyone who reads this: Stop using quotes. Never use them again.
4
highlander
Re: select a single quote from MySQL ?

  • 2006/11/13 15:51

  • highlander

  • Not too shy to talk

  • Posts: 151

  • Since: 2004/12/5


1) the data is completely fictious.

2) the column value contains \'

I agree with you on the not using quotes, they can be nasty.
However I have always been able to solve stuff, but can't seem to get this one done...grr

Highlander
So, I'm in the park wondering why frisbees get larger as they get closer when suddenly, it hits me...
www.AnimalPedigree.com
5
Dave_L
Re: select a single quote from MySQL ?

  • 2006/11/13 16:12

  • Dave_L

  • XOOPS is my life!

  • Posts: 2277

  • Since: 2003/11/7


1) I asked this question because in the queries you have "A name..." but then you say that the actual data is "name...". Obviously those don't match.

2) If the actual column value is 'name with \' a single quote', then this query should work:

SELECT * FROM `tablename` WHERE `NAME` = 'name with \\\' a single quote';

Note that the \ is escaped as \\, and the ' is escaped as \'.

I tested it with phpMyAdmin, and it works for me.
6
highlander
Re: select a single quote from MySQL ?

  • 2006/11/13 16:33

  • highlander

  • Not too shy to talk

  • Posts: 151

  • Since: 2004/12/5


Hello Dave_L,

1) Thats just me trying to make an example which makes more sences than my actual data. Figures I'd get that bit wrong !!

2) Works well for me to !! Thanx a bunch.

Strange how you can just stare at stuff and not see the anwser. I'd tried every variation of escaping characters exept the right one. Makes perfect sense now.

Kind regards,

Highlander (in an escaping mood)

p.s. strange though that phpmyadmin creates an incorrect query when using the Search mode and a LIKE query instead of a = query.
SELECT * 
FROM `tablename` 
WHERE `NAME` LIKE CONVERT( _utf8 'name with \'' a single quote'
USING latin1 ) 
COLLATE latin1_swedish_ci
LIMIT 0 , 30
So, I'm in the park wondering why frisbees get larger as they get closer when suddenly, it hits me...
www.AnimalPedigree.com
7
Dave_L
Re: select a single quote from MySQL ?

  • 2006/11/13 16:43

  • Dave_L

  • XOOPS is my life!

  • Posts: 2277

  • Since: 2003/11/7


Quote:
Strange how you can just stare at stuff at not see the anwser.


True. I've spent hours staring at an obvious error without seeing it.

Login

Register now!  |  Lost Password?

Search

Advanced Search

Recent Posts

Who's Online

123 user(s) are online (79 user(s) are browsing Support Forums)

Members: 0

Guests: 123

more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Apr 30
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits