Page 1 of 1

How do I check if MySQL field is numeric?

Posted: Thu Apr 11, 2013 5:13 am
by simonmlewis

Code: Select all

$result = mysql_query ("SELECT id, photo FROM products WHERE id = '$id'") or die(mysql_error());
I want this to check that anything in the "photo" field is numeric.

Reason being, it's looking in there, and if anything is found, it's trying to delete the filenames form the server. But often there is just a ||| in the field. Which is causing errors.

So it would be better if it only runs the following bit (not shown here), if it also checks that the field is numeric.

The filenames will always have numeric values: 234234fred.jpg, for instance.

Re: How do I check if MySQL field is numeric?

Posted: Thu Apr 11, 2013 11:32 am
by mikosiko
You are approaching the issue in the wrong way IMHO... you are not solving the real issues
a) why the field photo could possible have a NON valid filename? .... field should contain either NULL or a valid filename.
b) If the delete option is causing an error when the field photo contain an nonexistent filename, then your delete option is wrong... it should check if the file is valid and existent before try to delete it.

Re: How do I check if MySQL field is numeric?

Posted: Thu Apr 11, 2013 12:33 pm
by Christopher
I agree with mikosiko. It would make more sense to validate the data going into the photo field. That said, do you want to do the check in SQL or PHP?

Re: How do I check if MySQL field is numeric?

Posted: Thu Apr 11, 2013 3:37 pm
by simonmlewis
When a photo is deleted, sometmes it leaves it as NULL, but there are occasions that I have yet to discover when it leave a | (separator) in the field.

So the simplest option, is, if the field does NOT contain numeric values, then delete the row.
I don't really care about the issue of "why does it not delete the | as well", as that is not an issue here. This particular button is designed to delete the entries and the files mentioned in the fields.

So if there is only a | in the field I want it to ignore that script and delete the row.

So - how do you check if a MySQL field has a numeric value in it?

Re: How do I check if MySQL field is numeric?

Posted: Thu Apr 11, 2013 7:43 pm
by Christopher
I would recommend finding where the |'s are added and fixing that problem.

To check for numbers (or |'s) probably regexp:

http://dev.mysql.com/doc/refman/5.1/en/ ... tor_regexp

If it is only the '|' character then you could do DELETE FROM tablename WHERE fieldname LIKE '%|%'

Re: How do I check if MySQL field is numeric?

Posted: Fri Apr 12, 2013 1:45 am
by simonmlewis
The field could contain: 234234fred.jpg|24235hello.png|
It *should* Delete the | and filename, but sometimes it leaves one or two || behind.
So you can see that if there are no numerics in there, then there are no files in there.

So I need only to know, to SELECT * FROM products WHERE id = '$id' AND photo IS NUMERIC.
This kind of thing.

Re: How do I check if MySQL field is numeric?

Posted: Fri Apr 12, 2013 10:20 am
by mikosiko
The only reasonable advice that I can offer to you is.... normalize...normalize...normalize... Did I say NORMALIZE your database

The issue that you have is precisely because your data is not normalized, you should never ever store list of values in one field, what do you need in this case is create a new table to hold the photo(s) associated with a product, doing that will simplify and take care of the issues that you are trying to solve wrongly with band-aids.

This http://www.youtube.com/watch?v=IiVq8M5DBkk is the first of a decent serie of videos that could help you to understand what database normalization means and how it is correctly implemented (there are around 9 videos in the serie... watch ALL of them for better understanding).

Now, if you insist in maintain your incorrect table design and suffer MYSQL REGEX http://dev.mysql.com/doc/refman/5.1/en/regexp.html could provide you the band-aid that you are looking for.

good luck

Re: How do I check if MySQL field is numeric?

Posted: Fri Apr 12, 2013 10:52 am
by simonmlewis
Thank you for that advice.
Sadly I am not going to redesign the entire database and web site, just for this. It's not really a large section of the site, but it does work nicely for us.

Now, that page you linked is enormous. I understand about 5% of it. I came here for someone to say: yes, use MYSQL REGEX like this> SELECT * FROM products WHERE id = '$id' AND ?????????.......

What is means it's doing a, b and c.
So I can learn what's being said here, and implement. Not spend a day trying to read stuff I don't really get, nor probably need.

Luck definitely needed on this one!!

Re: How do I check if MySQL field is numeric?

Posted: Fri Apr 12, 2013 3:14 pm
by pickle
So the error is being caused when the field contains separators but no filenames? It sounds like you're fine with a quick and dirty solution, so why not just check for the existence of a period ( . ) ?

Re: How do I check if MySQL field is numeric?

Posted: Fri Apr 12, 2013 3:20 pm
by simonmlewis
It's not clean and dirty - it's how I wrote it and it works fine for the requirements.
I was to check for the existence of a numeric value. That's all. 5 answers in, and I've either been moaned at, or given a bent answer.

Sometimes here, it's really very tough to get a straight one. "just put this in the query, and it means..........".

Re: How do I check if MySQL field is numeric?

Posted: Fri Apr 12, 2013 3:36 pm
by pickle
It's in a coder's nature that when we see bad code, to want to fix it. The ideal solution to your problem is not the solution to your question. Most of the answers have addressed the problem, not your question. Don't b*tch about people giving you free help.

Your question asked how to find numeric strings, but your problem is that it croaks on "|||". You never said there would be other, non-numeric filenames in there, so from the information you've given, it can be assumed there are either filenames in the field (which happen to have numbers in them), or there are separators left over from some previous operation. The simplest solution to your problem then, is to be able to tell the difference between a filename and a separator. Since the filenames can change, the only common character is a period. Therefore it could be assumed that if the field has a period, there are valid filenames, if it doesn't, it's a separator.

Another solution is to not care if there are valid filenames. In your delete operation, just check for the existence of the file before deleting.

Re: How do I check if MySQL field is numeric?

Posted: Fri Apr 12, 2013 3:42 pm
by simonmlewis
I have said about the filenames, in fact I said what the content is likely to be:
The field could contain: 234234fred.jpg|24235hello.png|
The | are separators, and I said that the start of it would always be numbers. So I know for 100% certainty that if there are numeric values in there, then there are files stored.

I just need to know, how to spot if there is a DIGIT, and NUMBER, am 0,1,2,3,4,5,6,7,8 or 9 in this field. That is all!!!!

I accept that is it a coders nature to spot other coders, less "proper" code, but this is how it's written, and I just need a means to spot the above.

That is all.

Re: How do I check if MySQL field is numeric?

Posted: Fri Apr 12, 2013 3:50 pm
by pickle
~Christopher said to use regex a few posts back.

Re: How do I check if MySQL field is numeric?

Posted: Fri Apr 12, 2013 3:57 pm
by simonmlewis
He did.
Please see my answer.

Re: How do I check if MySQL field is numeric?

Posted: Fri Apr 12, 2013 4:52 pm
by Christopher
Please see my link:
Christopher wrote:To check for numbers (or |'s) probably regexp:

http://dev.mysql.com/doc/refman/5.1/en/ ... tor_regexp