Page 1 of 2
Why doesn't mysql allow *apostrophies* into fields?
Posted: Sun Jul 19, 2009 7:04 am
by simonmlewis
I have a form that needs to accept apostrophies, otherwise uses may enter data and it just get rejected.
I have found methods to stop it being typed in, but this doesn't prevent copy/paste methods.
Does anyone know how to allow it in?
So I can enter [for example]
David's name isn't John
without it being rejected.
Re: Why doesn't mysql allow *apostrophies* into fields?
Posted: Sun Jul 19, 2009 9:02 am
by Weirdan
MySQL per se does not disallow storing any kind of data, you just need to format it properly before putting it to your query (read up on mysql_real_escape_string() in php manual). And it applies to any data, not only those fields where you expect apostrophes to appear.
Re: Why doesn't mysql allow *apostrophies* into fields?
Posted: Sun Jul 19, 2009 10:49 am
by simonmlewis
Ohhhh.... so if I put the field (textarea) that this aposophy may be in, into that, it should fix it?
Re: Why doesn't mysql allow *apostrophies* into fields?
Posted: Sun Jul 19, 2009 11:36 am
by simonmlewis
Mmmm this is strange.
It works when the field is "TEXT", but if someone enter a number, ie 4,999.99 into an INT field, it just enters the 4.
I'm assuming the answer is to have a 'value' field to be a TEXT field too, but I need to do mathematic with the contents of that field, and don't know if it works when the field type is TEXT.
Any suggestions?
People are likely to enter 4,999.99 rather than just 4999.99.
Re: Why doesn't mysql allow *apostrophies* into fields?
Posted: Sun Jul 19, 2009 1:19 pm
by califdon
simonmlewis wrote:Mmmm this is strange.
It works when the field is "TEXT", but if someone enter a number, ie 4,999.99 into an INT field, it just enters the 4.
I'm assuming the answer is to have a 'value' field to be a TEXT field too, but I need to do mathematic with the contents of that field, and don't know if it works when the field type is TEXT.
Any suggestions?
People are likely to enter 4,999.99 rather than just 4999.99.
You have to handle this before you store the data. Commas and dollar signs etc. are NOT numbers, so if you try to store something with such characters embedded as a number (integer or floating point), it will stop when it hits a non-numeric character. Your script needs to
validate every entry before storing it, anyway. Then you can either fix it (like removing any commas) or notify the user that an entry is invalid.
Re: Why doesn't mysql allow *apostrophies* into fields?
Posted: Sun Jul 19, 2009 1:58 pm
by simonmlewis
I know how to check if a variable is empty, or has a particular word, but how do you ask if a variable contents "contains" something?
Pseudo:
if ($price does not contain £ OR , OR'....), { do the job}.
Re: Why doesn't mysql allow *apostrophies* into fields?
Posted: Sun Jul 19, 2009 2:37 pm
by califdon
simonmlewis wrote:I know how to check if a variable is empty, or has a particular word, but how do you ask if a variable contents "contains" something?
Pseudo:
if ($price does not contain £ OR , OR'....), { do the job}.
There are several useful functions, but the most flexible are surely the regex (regular expressions) functions. Read these:
http://www.phpro.org/tutorials/Introduc ... Regex.html
http://us2.php.net/manual/en/ref.regex.php
Re: Why doesn't mysql allow *apostrophies* into fields?
Posted: Sun Jul 19, 2009 3:48 pm
by simonmlewis
Mmm I am getting there.
Code: Select all
$pricetest = $_POST['price'];
// create a string
$string = $pricetest;
// match our pattern containing a special sequence
preg_match_all("/[\d]/", $string, $matches);
// loop through the matches with foreach
foreach($matches[0] as $price)
{
echo $price;
}
This will find only numbers, but that means 1999.99 will result with 199999.
I can't see how to find anything illegal and remove it and provide the result with the illegal character removed.
or.... making it something that's a STR or something similar?..... that goes..
remove anything but numbers, then insert a fullstop third in from the left.
Any ideas?
Re: Why doesn't mysql allow *apostrophies* into fields?
Posted: Sun Jul 19, 2009 3:55 pm
by califdon
Did you read about preg_replace()?
Re: Why doesn't mysql allow *apostrophies* into fields?
Posted: Sun Jul 19, 2009 4:33 pm
by simonmlewis
I did, but couldn't find a solution that was clear, on how to remove everything except a particular character.
I did find this elsewhere and it works, but if there is a shorter way, I'd like to hear it:
Code: Select all
$price = $_POST['price'];
$price = eregi_replace(",","",$price);
$price = eregi_replace("£","",$price);
$price = eregi_replace("$","",$price);
$price = eregi_replace("€","",$price);
The result of this, is when £1,999.00 is pasted to $price, the output is this:
1999.00
Re: Why doesn't mysql allow *apostrophies* into fields?
Posted: Sun Jul 19, 2009 5:45 pm
by califdon
You got it. Of course, if you need to do the same thing to several form values, you could write it as a function, passing the value to the function for each value.
Re: Why doesn't mysql allow *apostrophies* into fields?
Posted: Mon Jul 20, 2009 2:33 am
by simonmlewis
Oh?
Not sure I'd know how to do that.
But the escape string fixes the paragraph of text, and this function fixes the figures.
Re: Why doesn't mysql allow *apostrophies* into fields?
Posted: Mon Jul 20, 2009 12:13 pm
by califdon
Nice examples, McInfo! I learned a couple things, there.
Re: Why doesn't mysql allow *apostrophies* into fields?
Posted: Mon Jul 20, 2009 12:22 pm
by simonmlewis
Well I'll be damned - god that's simpler!
Beats my several line-job.
Cheers.
Re: Why doesn't mysql allow *apostrophies* into fields?
Posted: Mon Jul 20, 2009 12:35 pm
by simonmlewis
Well here's something bizarre.
The form has the field named 'price'.
It's passed to this page, and I now use your method:
Code: Select all
$var = $_POST['price'];
$price = sprintf('%0.2f', preg_replace('/[^0-9.]/', '', $var));
I am updating the database with $price, yet it doesn't do anything. ie. £17,999.45 is remaining and being inserted as £17,999.45.
Yet when I tested it with this:
Code: Select all
<?php
$var = $_POST['price'];;
$clean = sprintf('%0.2f', preg_replace('/[^0-9.]/', '', $var));
echo $clean;
?>
.... it worked.
Any ideas?