Why doesn't mysql allow *apostrophies* into fields?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

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

Why doesn't mysql allow *apostrophies* into fields?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Why doesn't mysql allow *apostrophies* into fields?

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Why doesn't mysql allow *apostrophies* into fields?

Post by simonmlewis »

Ohhhh.... so if I put the field (textarea) that this aposophy may be in, into that, it should fix it?
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: Why doesn't mysql allow *apostrophies* into fields?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Why doesn't mysql allow *apostrophies* into fields?

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Why doesn't mysql allow *apostrophies* into fields?

Post 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}.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Why doesn't mysql allow *apostrophies* into fields?

Post 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
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Why doesn't mysql allow *apostrophies* into fields?

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Why doesn't mysql allow *apostrophies* into fields?

Post by califdon »

Did you read about preg_replace()?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Why doesn't mysql allow *apostrophies* into fields?

Post 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
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Why doesn't mysql allow *apostrophies* into fields?

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Why doesn't mysql allow *apostrophies* into fields?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Why doesn't mysql allow *apostrophies* into fields?

Post by califdon »

Nice examples, McInfo! I learned a couple things, there.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Why doesn't mysql allow *apostrophies* into fields?

Post by simonmlewis »

Well I'll be damned - god that's simpler!
Beats my several line-job.

Cheers.
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: Why doesn't mysql allow *apostrophies* into fields?

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply