[SOLVED] How to enter height in to MySQL data base

[WebMachine]

That was my first instinctive response when you first posted the question. Enter the feet and inches in two separate fields in the database - that would solve your issues. But I didn’t say anything then because I’m not an expert in PHP or mySQL and thought I would wait to see if the experts had a slicker way of doing it.

[Lemon_Juice]

@sand12

I feel like this thread is going in to a PDO vs MySQLi debate please just help me to solve my mentioned issue

Sorry for going off topic!

@sand12

I’m thinking of getting these 2 as separate values and entering them as 2 values in to the DB then display them together in my main site.

I think this is bad idea. Height is one entity, why complicate things by splitting it in two? By using this logic you could also choose to store date and time in 6 separate fields (year, month, day, hour, minute, second) instead of one DATETIME column - doesn’t make sense unless you have really strong reasons for doing so.

@Rubble

Another way be to change the ’ and " to something like _ when it is saved to the database and either string replace() or use explode() to change it back again.

I think this is bad idea, too - why complicate things with weird replacements back and forth?

As to the solution - you have already been given good solutions in this thread so I don’t think there’s anything more to add!

[droopsnoot]

Exactly, as has been said before, convert it to inches when the value is input, and convert it back again when you display it - that way you get around the quotes problem, and you can also sort by height, extract records below or above a certain height, all things you can’t easily do if you save it formatted. You’ll have to write a validation function when the user enters the data, to standardise the format and make sure they don’t type nonsense values, so once you’ve decided it’s valid data, it’s a small extra step to convert to inches.

[felgall]

@droopsnoot

You’ll have to write a validation function when the user enters the data, to standardise the format and make sure they don’t type nonsense values

That’s the most important thing to do with all user inputs before you make any attempt to use anything they have entered.

[AtliThor]

@Lemon_Juice

… but for me personally there is no much difference. Even if I use a database extension that allows me to use prepared statements I have to remember to bind values. If I don’t use prepared statements then I have to escape values. In either case I have to remember to do something with those values because even PDO will allow me to inject insecure stuff by using simple string concatenation.

The difference is that the standard practice for PDO and MySQLi is to use placeholders in the queries, and bind the values. There is no risk of accidentally injecting an insecure value, because you never inject any values whatsoever.

You don’t really need to remember to do something different, you just need to stop injecting values. Completely. Without exceptions.

Even if you’re working on legacy code, where you would traditionally inject escaped values directly into he query, I would urge you to try avoid that. It’s not difficult. Even something as simple as the following function lets you avoid it completely, and stick to using placeholders in the query syntax:

function mysql_bind_value($sql, $placeholder, $value, $dbLink=null, $quoteValue=true) {
    // The escape call generates a warning if you pass NULL 
    // as param 2, thus the seemingly pointless if clause.
    if (is_resource($dbLink)) {
        $escaped = mysql_real_escape_string($value, $dbLink);
    } else {
        $escaped = mysql_real_escape_string($value);
    }
    if ($quoteValue) {
        $escaped = "'" . $escaped . "'";
    }
    return str_replace($placeholder, $escaped, $sql);
}

With that you can use PDO-like parameterized query syntax in your legacy code.

$sql = "INSERT INTO tbl(name, email)
        VALUES (:name, :email)";
        
$sql = mysql_bind_value($sql, ":name", $_POST['name']);
$sql = mysql_bind_value($sql, ":email", $_POST['email']);

$result = mysql_query($sql) or die("MySQL API, DIE!")

It’s also not difficult to extend that to allow numbered placeholders as well, if you prefer that.

[sand12]

Ok guys thank you all for your help and lemon your method worked like a charm no need to convert or anything. now I can finish this site and move on.

Also like to ask here that I’ve posted above my data all of those are unknown are entered as fresh set of data how should I verify them against what.

[felgall]

You should code the imput as follows (or using $_GET if the values are passed in the querystring - never use $_REQUEST or people can set a cookie and override the form content.

$name = validName($_POST["name"]);
$niName = validNicname($_POST["nicname"]);

and so on for the rest of the fields. Each function should validate the field for what you would reasonably expect a field to contain.

For example for age you’d validate that what is entered is an integer between 0 and 125. For eyes you’d validate that the value entered is a recognised eye colour probably by checking against a list - in which case the same list could be used to make the field on the form a select list so that people know what colours are acceptable to enter. For names you’d validate that it contains only letters spaces hyphens and apostrophes (few if any people would have a name containing anything else). For nicname you could just test for alphabetic. And so on - test each field for what it should reasonably be allowed to contain.

After all do you really want someone whose age is “DROP TABLE modeldetails” entering their info in your form?

[sand12]

@felgall

After all do you really want someone whose age is “DROP TABLE modeldetails” entering their info in your form?

LoL hell no nice one, thx guys I really learned a lot more than I’ve learned from my teachers thank you very much I will take your advice and make a validation system for the process using PHP.