SQL Query to search for "non letter"

Discussion forum for MOD Writers regarding MOD Development.
OBeer-WAN-Kenobi
Registered User
Posts: 38
Joined: Fri Jul 25, 2008 2:22 am

SQL Query to search for "non letter"

Post by OBeer-WAN-Kenobi » Thu Aug 14, 2008 2:58 am

Hi guys,
I'm working on getting a query that returns all topic titles that start with any character besides A-Z.

Code: Select all

$sql = 'SELECT topic_title, forum_id, topic_id
	FROM ' . TOPICS_TABLE . '
	WHERE topic_approved = 1 
		AND forum_id = 1
		AND topic_moved_id = 0
		AND topic_title NOT LIKE "[A-Z]%"
		' . $sql_where . '
	ORDER BY topic_title ASC';
This isn't working. I've tried a bunch of different configurations and can't get it to work. I must be doing something wrong. It doesn't give me an error, it just returns everything, including a topic with (") as the first character.

P.S.
This query works fine if I
Replace:

Code: Select all

		AND topic_title NOT LIKE "[A-Z]%"
With:

Code: Select all

		AND topic_title LIKE "A%"
as I am using seperate .php files for A-Z

So I'm pretty sure the rest of the query and my html file are just fine.
Image

User avatar
EXreaction
Former Team Member
Posts: 5666
Joined: Sun Aug 21, 2005 9:31 pm
Location: Wisconsin, U.S.
Name: Nathan

Re: SQL Query to search for "non letter"

Post by EXreaction » Thu Aug 14, 2008 3:44 am

I don't think something like that will work.

You would need something like:

Code: Select all

AND topic_title NOT LIKE "A%" AND topic_title NOT LIKE "B%" AND topic_title NOT LIKE "C%" AND topic_title NOT LIKE "D%"
etc...

You can use a regular expression in a like statement with RLIKE, but that only works with MySQL.
http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: SQL Query to search for "non letter"

Post by drathbun » Thu Aug 14, 2008 4:23 am

It's not the most efficient, but this works:

Code: Select all

select topic_id, topic_title 
from phpbb_topics 
where ucase(substring(topic_title,1,1)) NOT BETWEEN 'A' AND 'Z'
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

OBeer-WAN-Kenobi
Registered User
Posts: 38
Joined: Fri Jul 25, 2008 2:22 am

Re: SQL Query to search for "non letter"

Post by OBeer-WAN-Kenobi » Thu Aug 14, 2008 5:00 am

drathbun wrote:It's not the most efficient, but this works:

Code: Select all

select topic_id, topic_title 
from phpbb_topics 
where ucase(substring(topic_title,1,1)) NOT BETWEEN 'A' AND 'Z'
Thanks for that!
I was able to take it and efficien-tize it... :lol:

This Works:

Code: Select all

$sql = 'SELECT topic_title, forum_id, topic_id
	FROM ' . TOPICS_TABLE . '
	WHERE topic_approved = 1 
		AND forum_id = 1
		AND topic_moved_id = 0
		AND topic_title NOT BETWEEN "A%" AND "Z%"
		' . $sql_where . '
	ORDER BY topic_title ASC';
Here it is in it's semi-live form http://thenutgallery.com/phpBB3/mreviews_1.php

I'm not using the proper forum ID yet as I don't have a movie reviews forum built at this time.
Image

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: SQL Query to search for "non letter"

Post by drathbun » Thu Aug 14, 2008 10:15 pm

This

Code: Select all

AND topic_title NOT BETWEEN "A%" AND "Z%"
... is not doing what you think it is, and in fact, it's broken. :) The wildcard % only works with the operator LIKE and NOT LIKE. When you use it with other operators like BETWEEN it is treated as a literal.

That's why I did a substring an then a between on the single character.
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

OBeer-WAN-Kenobi
Registered User
Posts: 38
Joined: Fri Jul 25, 2008 2:22 am

Re: SQL Query to search for "non letter"

Post by OBeer-WAN-Kenobi » Fri Aug 15, 2008 5:08 pm

drathbun wrote:This

Code: Select all

AND topic_title NOT BETWEEN "A%" AND "Z%"
... is not doing what you think it is, and in fact, it's broken. :) The wildcard % only works with the operator LIKE and NOT LIKE. When you use it with other operators like BETWEEN it is treated as a literal.

That's why I did a substring an then a between on the single character.
It doesn't seem to be broken.... It seems to be working properly to me. I just added a few test topics with numbers and one with a percent sign. Check this out. http://thenutgallery.com/phpBB3/mreviews_1.php
click back and forth on any of the letters.

How is it broken?
Image

User avatar
Brf
Support Team Member
Support Team Member
Posts: 51873
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: SQL Query to search for "non letter"

Post by Brf » Fri Aug 15, 2008 5:15 pm

Try one with a title like:
"A something"

OBeer-WAN-Kenobi
Registered User
Posts: 38
Joined: Fri Jul 25, 2008 2:22 am

Re: SQL Query to search for "non letter"

Post by OBeer-WAN-Kenobi » Fri Aug 15, 2008 5:38 pm

Brf wrote:Try one with a title like:
"A something"
OK, IC. With the title (A something) it also goes into the "# list" instead of just showing up under the "A" list.

So I should try something like this from what drathbun posted?

Code: Select all

$sql = 'SELECT topic_title, forum_id, topic_id
   FROM ' . TOPICS_TABLE . '
   WHERE topic_approved = 1 
      AND forum_id = 1
      AND topic_moved_id = 0
      AND topic_title ucase(substring(topic_title,1,1)) NOT BETWEEN 'A' AND 'Z'
      ' . $sql_where . '
   ORDER BY topic_title ASC';
What I really don't understand is why my original code didn't work.

Code: Select all

      AND topic_title NOT LIKE "[A-Z]%"
Because that's what a bunch of online sources said to use when I googled this in the first place... :?
Image

User avatar
Brf
Support Team Member
Support Team Member
Posts: 51873
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: SQL Query to search for "non letter"

Post by Brf » Fri Aug 15, 2008 5:43 pm

Did you try

Code: Select all

NOT LIKE '[A-Z]%'
or

LIKE '[!A-Z]%'

OBeer-WAN-Kenobi
Registered User
Posts: 38
Joined: Fri Jul 25, 2008 2:22 am

Re: SQL Query to search for "non letter"

Post by OBeer-WAN-Kenobi » Fri Aug 15, 2008 5:49 pm

Brf wrote:Did you try

Code: Select all

NOT LIKE '[A-Z]%'
I think so, but I don't remember. in any case, it wouldnt work with ' I always had to use ". I will give it a try tonight as I can't ftp from work.
Brf wrote: or

Code: Select all

LIKE '[!A-Z]%'
I don't think I tried this one. I'll give it a try tonight also.

Thanks very much for your help!
Image

User avatar
Brf
Support Team Member
Support Team Member
Posts: 51873
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: SQL Query to search for "non letter"

Post by Brf » Fri Aug 15, 2008 6:02 pm

SQL uses the single-quote apostophes around strings, not double-quotes.

The NOT LIKE '{a-c]%' seems to work fine on MSSQL. The "!" for NOT does not.

OBeer-WAN-Kenobi
Registered User
Posts: 38
Joined: Fri Jul 25, 2008 2:22 am

Re: SQL Query to search for "non letter"

Post by OBeer-WAN-Kenobi » Fri Aug 15, 2008 6:37 pm

Brf wrote:SQL uses the single-quote apostophes around strings, not double-quotes.

The NOT LIKE '{a-c]%' seems to work fine on MSSQL. The "!" for NOT does not.
Wierd........ I tried it with single quotes and it gives me a syntax error. It was frustrating the heck out of me until I finally tried the double quotes. Then it would work with no syntax error.

Here is the actual file (maybe a slightly older version) that I am running for the "A" list.

Code: Select all

<?php
define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);

// Start session management
$user->session_begin();
$auth->acl($user->data);
$user->setup();

page_header('Movie Reviews A');

//
// Movie Reviews in Alphabetical Order
//

$sql = 'SELECT topic_title, forum_id, topic_id
	FROM ' . TOPICS_TABLE . '
	WHERE topic_approved = 1 
		AND forum_id = 1
		AND topic_moved_id = 0
		AND topic_title LIKE "A%"
		' . $sql_where . '
	ORDER BY topic_title ASC';

$result = $db->sql_query($sql);

while( ($row = $db->sql_fetchrow($result)) && ($row['topic_title']) )
{
	// auto auth
	if ( ($auth->acl_get('f_read', $row['forum_id'])) || ($row['forum_id'] == '0') )
	{
		$template->assign_block_vars('m_reviews', array(
			'TITLE'	 		=> ($row['topic_title']),
			'FULL_TITLE'	=> censor_text($row['topic_title']),
			'U_VIEW_TOPIC'	=> append_sid("{$phpbb_root_path}viewtopic.$phpEx", 'f=' . $row['forum_id'] . '&t=' . $row['topic_id'])
		));
	}
}
$db->sql_freeresult($result);

$template->set_filenames(array(
'body' => 'mreviews.html',
));

make_jumpbox(append_sid("{$phpbb_root_path}viewforum.$phpEx"));
page_footer();

?>
Image

User avatar
Brf
Support Team Member
Support Team Member
Posts: 51873
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: SQL Query to search for "non letter"

Post by Brf » Fri Aug 15, 2008 6:55 pm

You are using single-quotes to delimit the SQL string you are building.... so you had single-quotes within single-quotes.

Code: Select all

$sql = 'blah blah blah LIKE 'blah' ';
You need to use double quotes for those:

Code: Select all

$sql = "blah blah blah LIKE 'blah' ";

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: SQL Query to search for "non letter"

Post by drathbun » Fri Aug 15, 2008 7:38 pm

The database "like" operator does not use regular expression logic. Thus, like [A-Z]% is not going to work.

If you want to use non-standard code, you can use RLIKE rather than LIKE. I don't think that's portable across databases, but it does work for MySQL, as detailed here in the 5.x man page:

http://dev.mysql.com/doc/refman/5.1/en/ ... tor_regexp
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

User avatar
Brf
Support Team Member
Support Team Member
Posts: 51873
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: SQL Query to search for "non letter"

Post by Brf » Fri Aug 15, 2008 7:43 pm

drathbun wrote:The database "like" operator does not use regular expression logic. Thus, like [A-Z]% is not going to work.
It works fine for MSSQL. Does it not work for MySql?

Ah... too bad... I just tested it. Doesnt work with MySql.

Locked

Return to “[3.0.x] MOD Writers Discussion”