Ok so I ran into a problem, i have the following database schema

PHP Code:

CREATE TABLE `affiliations` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `mapquest` text NOT NULL,
  `type` text NOT NULL,
  `visid` text NOT NULL,
  `address` text NOT NULL,
  `phone` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=908 ;


CREATE TABLE `affiliations_usr` (
  `id` int(11) NOT NULL auto_increment,
  `visid` text NOT NULL,
  `visname` text NOT NULL,
  `cat` varchar(255) NOT NULL default '',
  `name` text NOT NULL,
  `date` varchar(255) NOT NULL default '0000-00-00',
  `start` varchar(255) NOT NULL default '0000-00-00',
  `end` varchar(255) NOT NULL default '0000-00-00',
  `aff_id` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1363 ; 


affiliation is listed with all affilliations and affiliations_usr lists who is affilliated with those users. I have a script that finds all users affiliated with a certain affiliation.

I recently discovered a programming error on my part, for some reason I cannot search text with a ' in it for intance 'St. John\'s Prayer Club' because it wont find it at all... I read something about it and it said that the ' is treated like a seperate word. So after trying 100 different queries to find the name in the database I decided to use the id instead (btw if know of a way i can search with a ' then please let me know)

So i put in aff_id, but now i have 1000 entries with no aff_id, what im wondering is...

is there a way to go through all entries in affiliations_usr take the name field and compare it to affiliations (which are all in there) then take the id of that exact affilliation then go back and populate the affiliation_usr field with that id number?

Any help would be appreciated, if you dont understand something please let me know.

You managed to confuse me.

But, while it sounds like you have some cleaning up to do in your tables, the good news is you can definitely perform searches that include the ' character, or any other. In fact, there are a few different ways - you have choices.

Usually the best way is to create a stored procedure in the database, that takes an argument for the search text. You're probably running a PHP script (?) that creates a dynamic SQL query on the fly, embedding the text you want to find into the query. This doesn't work, as you've learned, because the search text is sent as text, and the ' character has a special meaning, besides the one it has in regular text. The database is choosing the wrong meaning to apply to it. Arguments to stored procedures are sent in binary.

You could also replace the quote mark with a wild card. Pattern searches are slower, though.

Chances are you could escape the quote mark; in SQL Server you would enclose it in two more of them.

You need to "escape" the single quote (') by placing a second one in front of it

use a simple replace function in whatever server side code you use. Look in the forum for the coding you use, there will be several examples.

Well currently in my database affiliations_usr I have this under the name cell with the slash already protecting the '

How would I go about searching for that?

St. John\'s Prayer Group

I was under the impression you could just search for this

PHP Code:

SELECT * FROM `affiliations_usr` where name = 'St. John\'s Prayer Group' 


then i tried escaping it like you said and nothing, with the follwing.

PHP Code:

SELECT * FROM `affiliations_usr` where name = 'St. John''s Prayer Group' 


I also tried a wildcard with the following and nothing

PHP Code:

SELECT * FROM `affiliations_usr` where name = 'St. John%s Prayer Group' 


nvm i messed that up i replaced = with LIKE and it worked fine, thanks for the help everyone