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: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.