current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -2 down vote favorite
  1. I have such MySQL query:
'... WHERE    
    LOWER(users.name) REGEXP LOWER(" '.$q.'") OR
    LOWER(users.name) REGEXP LOWER("^'.$q.'") OR
    LOWER(users.login) REGEXP LOWER("'.$q.'") OR 
    links.lv REGEXP LOWER("'.$q.'+")'

and I need to prepare this same condition in PostgreSQL and PDO. I was trying with regexp_matches(), but it didn't work with PDO.

share|improve this question
2  
Have you looked at the PostgreSQL pattern matching documentation? –  Chris Dec 23 '13 at 21:27
3  
What were you trying with PDO and what error messages did you get? PS: standard SQL (include PostgreSQL) uses double quotes for identifiers (such as table and column names), single quotes are for string literals. And please don't use string wrangling to build SQL, it is rarely needed. –  mu is too short Dec 23 '13 at 21:45
5  
Learn to use prepared statements, otherwise say welcome to sql injections –  HamZa Dec 23 '13 at 21:47
    
What's $q? Show us the PHP code that invokes this SQL and the exact text of any error message. –  Craig Ringer Dec 24 '13 at 1:06
    
In future, please always give us the PostgreSQL version, exact error messages, and explain what you've tried, with code. –  Craig Ringer Dec 24 '13 at 1:11

2 Answers 2

up vote 2 down vote

If you insist on fixing your regexp, the error message would be needed, but I'm guessing Postgres is spitting out a column not found error or something to that order if your original query is anything to go by. The reason is that you're using double quotes; these are used to quote identifiers in Postgres, in much the same way as backticks are in MySQL.

Also note that Postgres offers something better than what you're trying to achieve with the query you posted, in the form of full text search. To illustrate, you could use something like:

...
where (setweight(to_tsvector(coalesce(users.name,  '')), 'A') ||
       setweight(to_tsvector(coalesce(users.login, '')), 'B') ||
       setweight(to_tsvector(coalesce(users.bio,   '')), 'D') ||
       setweight(to_tsvector(coalesce(links.lv,    '')), 'B')
      ) @@ plainto_tsquery(:query)

http://www.postgresql.org/docs/current/static/textsearch.html

share|improve this answer
up vote 1 down vote

You haven't shown us what $q is, so it's rather hard to guess what you're trying to do. If it's a regular expression you'd do something like:

WHERE    
    LOWER(users.name) ~ :q OR
    LOWER(users.name) ~ :q OR
    LOWER(users.login) ~ :q OR 
    links.lv ~ :q

where :q is a placeholder variable that you pass as a named query parameter. See PDO::Prepare.

I strongly suspect that your $q is actually just something like .*somestring.*, i.e. you're just doing an infix pattern search. If that's the case you'd really be better off using LIKE instead of MySQL REGEXP or PostgreSQL ~ and using the pattern %somestring%.

Do not interpolate text straight into your SQL like you do above.

Bobby Tables

What happens if $q is "); DROP TABLE users; -- ? Oops! Please read:

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.