Page 1 of 2

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

Posted: Tue May 29, 2012 9:44 am
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?

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

Posted: Tue May 29, 2012 1:27 pm
by tr0gd0rr
do you mean `LIKE '%|%'`

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

Posted: Tue May 29, 2012 1:32 pm
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.

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

Posted: Tue May 29, 2012 1:37 pm
by Celauran

Code: Select all

SELECT columnA, columnB
FROM table_name
WHERE field_name REGEXP '^\\|+$'

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

Posted: Tue May 29, 2012 1:40 pm
by simonmlewis
Thanks - can you explain it please?

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

Posted: Tue May 29, 2012 1:52 pm
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".

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

Posted: Tue May 29, 2012 1:57 pm
by simonmlewis
With nothing else inbetween?
So starts with one or more |.... and immediately ends?

CLEVER.

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

Posted: Tue May 29, 2012 2:10 pm
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.

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

Posted: Tue May 29, 2012 2:13 pm
by Celauran
simonmlewis wrote:It's throwing an error
Can you elaborate?

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

Posted: Tue May 29, 2012 2:17 pm
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]

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

Posted: Tue May 29, 2012 2:20 pm
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?

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

Posted: Tue May 29, 2012 2:26 pm
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?');\">";
    }
}

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

Posted: Tue May 29, 2012 2:35 pm
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'>"; 
          }
        }
      }

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

Posted: Tue May 29, 2012 2:36 pm
by Celauran
Is $result2 false?

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

Posted: Tue May 29, 2012 2:39 pm
by simonmlewis
I don't know. The echo $query isn't echoing anything on screen.