Can you see if a DB field contains only a certain character?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Can you see if a DB field contains only a certain character?

Post by simonmlewis »

Need to see if a field contains ONLY one or more of these: |

ie....
|
Or...
||||

etc. If it contains anything else, I don't wana know.

Is that possible?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Re: Can you see if a DB field contains only a certain charac

Post by tr0gd0rr »

do you mean `LIKE '%|%'`
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you see if a DB field contains only a certain charac

Post by simonmlewis »

No. Because if the field contains: "218.jpg|367_pool.jpg".... it will not produce the fact that it contains "ONLY" one or more | characters.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Can you see if a DB field contains only a certain charac

Post by Celauran »

Code: Select all

SELECT columnA, columnB
FROM table_name
WHERE field_name REGEXP '^\\|+$'
Last edited by Celauran on Tue May 29, 2012 1:50 pm, edited 1 time in total.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you see if a DB field contains only a certain charac

Post by simonmlewis »

Thanks - can you explain it please?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Can you see if a DB field contains only a certain charac

Post by Celauran »

Just fixed a typo in it. It's just a simple regex match. ^ denotes start of new line, \\ escapes the | in order to match the literal character, + means match one or more times, and $ denotes the end of line. '^\\|+$' essentially says "match a line that starts with one or more | characters, then ends".
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you see if a DB field contains only a certain charac

Post by simonmlewis »

With nothing else inbetween?
So starts with one or more |.... and immediately ends?

CLEVER.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you see if a DB field contains only a certain charac

Post by simonmlewis »

Code: Select all

$result2 = mysql_query ("SELECT photos FROM property WHERE (photos REGEXP '^\\|+$' OR photos = '' OR photos IS NULL) AND id = '$row->id'");
        $num_rows2 = mysql_num_rows($result2); 
        if ($num_rows2 != 0)
        {
        echo "<input type='submit' value='Delete' onclick=\"javascript:return confirm('Are you sure you wish to delete $row->title?');\">";
        }
Is there something wrong with this? It's throwing an error on

Code: Select all

$num_rows2 = mysql_num_rows($result2); 
When I run this query direct from the database, it's fine.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Can you see if a DB field contains only a certain charac

Post by Celauran »

simonmlewis wrote:It's throwing an error
Can you elaborate?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you see if a DB field contains only a certain charac

Post by simonmlewis »

(idiot Simon....)
Sorry.

[text]Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\phpMyAdmin\orange\includes\a_products.inc on line 111[/text]
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you see if a DB field contains only a certain charac

Post by simonmlewis »

When I run this query direct, with |||| in the field, it produces one row.
When I run it with "B.M 2 front.jpg|B5.jpg|BM2Bedroom.jpg|BM2 Bathroom.jpg" in the field, it produces zero results.

Which is perfect. So why is it not working?
How do I show the query it's producing on screen?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Can you see if a DB field contains only a certain charac

Post by Celauran »

Code: Select all

$query = "SELECT photos FROM property WHERE (photos REGEXP '^\\|+$' OR photos = '' OR photos IS NULL) AND id = '$row->id'";
echo $query;
$result2 = mysql_query($query);
if ($result2 !== FALSE)
{
    $num_rows2 = mysql_num_rows($result2); 
    if ($num_rows2 != 0)
    {
        echo "<input type='submit' value='Delete' onclick=\"javascript:return confirm('Are you sure you wish to delete $row->title?');\">";
    }
}
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you see if a DB field contains only a certain charac

Post by simonmlewis »

This is producing no button at all.

Code: Select all

			if ($row->photoprimary == NULL || $row->photoprimary == '')
			{
        $query = mysql_query ("SELECT photos FROM property WHERE (photos REGEXP '^\\|+$' OR photos = '' OR photos IS NULL) AND id = '$row->id'");
        echo $query;
        $result2 = mysql_query($query);
        if ($result2 !== FALSE)
        {
          $num_rows2 = mysql_num_rows($result2);
          if ($num_rows2 != 0)
          {
           echo "<input type='submit' value='Delete' onclick=\"javascript&#058;return confirm('Are you sure you wish to delete $row->title?');\">";
          }
          else 
          { 
          echo "<input type='button' onclick=
\"alert('You have PHOTOS stored.  Click Edit, delete all photos, then delete item.')\" 
value='Delete'>"; 
          }
        }
      }
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Can you see if a DB field contains only a certain charac

Post by Celauran »

Is $result2 false?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you see if a DB field contains only a certain charac

Post by simonmlewis »

I don't know. The echo $query isn't echoing anything on screen.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply