1

I want to dynamically determine the data type of input values I am getting from my HTML input form.this will assist me to bind parameter values of data I am inserting into my database using PHP.

This information will assist me to determine the values of the string 'sss' in the code below for MySQL prepared statement, I want to create an insert data class and the input values can be of the type 'sdib' for the types string, double,integer,or Blob.

 if($stmt = $mysqli->prepare($sql)) {
    $stmt->bind_param("sss", $first_name, $last_name, $email);
 }

I have tried the code below and even tried some other codes in PHP manual that uses call_user_func_array() in vain.

I understand input values from HTML forms are always strings.

// SECTION USING PREPARED STATEMENT IN INSERT QUERY
if ($stmt = $mysqli->prepare($sql)) {
    $stmt->bind_param("sss", $first_name, $last_name, $email);
}

//CODE SHOWING HOW I AM TRYING TO GET DATA TYPE OF HTML INPUT VALUES
if (gettype($v) === "string") {
    return "s";
} elseif (gettype($v) === "float") {
    return "d";
} elseif (gettype($v) === "integer") {
    return "i";
} elseif (gettype($v) === "binary") {
    return "b";
} else {
    $e = '..could not establish data type';
    $this->setError($e);
    echo $e;
}

I want to be able to get datatype part 'sss' DYNAMICALLY from HTML input forms. the statement I want to construct is similar to this 

$stmt->bind_param("sddsii", $first_name, $last_name, $email);
Dharman
  • 30,962
  • 25
  • 85
  • 135
MARTIN GITAU
  • 81
  • 7
  • 2
    They are all strings anyway, just use s – Dharman Nov 08 '19 at 19:58
  • Dharman does it mean if i specify type s for a data that is interger in the database i will not get errors? – MARTIN GITAU Nov 08 '19 at 20:03
  • thanks Dharman . Let me use the string and see how far it will take me . – MARTIN GITAU Nov 08 '19 at 20:13
  • When you bind a parameter, you're looking up some field in your database based on user input. You should already know the type; it's the type of the field you're looking up. – IceMetalPunk Nov 08 '19 at 20:19
  • Hi Ice i want to use a class that will be used by many tables which will have different data types and columns . i will not know the data types in advance. – MARTIN GITAU Nov 08 '19 at 20:32
  • I personally hate `switch` blocks for their bloated syntax, but I really don't think that you should be asking php to make repeated evaluations with `gettype($v)` on the exact same, unchanged value. Score one for the `switch-case` block this time. – mickmackusa Jan 30 '20 at 12:55

2 Answers2

2

As pointed out in another answer all your values coming from HTML are strings. However, you might be having input coming from other sources, which you would like to pass to your statement. What would happen if you used string binding all the time?

Best way is to test it. 

$data1 = '1';
$data2 = 2;
$data3 = '0.5-a';
$stmt = $mysqli->prepare('SELECT ?=1 AS f1, ?="2" AS f2, ?=0.5 AS f3');
$stmt->bind_param('sss', $data1, $data2, $data3); // binding string
$stmt->execute();
$row = $stmt->get_result()->fetch_assoc();
var_dump($row); // all true

In these simple examples, there is no difference. Why? Because MySQL will cast a string to appropriate type if it is used in a numeric context.

Why specify a type explicitly if almost everything can be sent as a string?
Because if you know the data type of your values, you shouldn't be casting them back and forth. If you have integers and you use them in MySQL in numeric context then there is no reason to cast them to a string in MySQLi bind_param().

Another important use case is when MySQL really makes a distinction in the types, e.g. in ORDER BY clause. If you bind a string instead of number, the order will be different. MySQL can either take an ordinal number representing the column number in SELECT or a column name(which cannot be bound) or a string literal. 

$data1 = '2';
// I want the records to be ordered by `name`, which is the second column
$stmt = $mysqli->prepare('SELECT id, name FROM products ORDER BY ?');
$stmt->bind_param('i', $data1); // binding integer instead of string

Related post: mysqli_stmt::bind_param() - specify another data type than “s” for each parameter

When it comes to binary or blob, they are just binary strings. This means they have no character set, and sorting and comparison are based on the numeric values of the bytes in column values.
If you ever find yourself needing to insert a blob value, it will most likely not come from HTML form as a string, but rather from reading in a file and you will know that you need to use b.

Dharman
  • 30,962
  • 25
  • 85
  • 135
  • A query parameter always acts like a constant value, not a column identifier. It's legal to `ORDER BY 'string'` because you can use any expression for ordering, including an expression that is only a single constant value. But such an expression has the same value for every row it tries to order, so they'll all be tied, and then the order will be arbitrary. – Bill Karwin Nov 09 '19 at 16:17
  • @BillKarwin Exactly, which is why `2` and `'2'` will be treated differently by MySQL in `ORDER BY`. This is where setting the correct type makes total sense. – Dharman Nov 09 '19 at 16:20
  • 1
    Oh yes, I see what you mean, because `ORDER BY` accepts an ordinal column number. Though this is usage is deprecated and the syntax has been removed from the SQL standard, so we shouldn't use it. – Bill Karwin Nov 09 '19 at 16:23
0

I want to dynamically determine the data type of input values i am getting from my HTML

The "T" in HTML stands for "text". There is only one data-type in HTML, string. Any code that receives input originating from HTML must already be aware of what type of data to expect and convert the string input into that type.

If your database expects a user's age to be an Integer, for example, then when your code receives that user input (a string), it must convert it to an Integer and then that Integer can be passed into the database.

Scott Marcus
  • 64,069
  • 6
  • 49
  • 71