Inserting integers into SQL using PHP/phpmyadmin

Question

I am trying to store '$phone' as in my SQL database however if i set the COLUMN type as 'INT' in phpmyadmin, no data is entered. as soon as i change the COULMN type to 'VARCHAR' the data can then be pushed

in form.php:

<fieldset>
   <input placeholder="Your Phone Number" type="text" name="phone" value="<?= $phone ?>" tabindex="4">
   <span class="error"><?= $phone_error ?></span>
</fieldset>

$phone is declared in formprocess.php :

$fname_error = $lname_error = $email_error = $phone_error = $job_error = $nameauth_error = $privacy_error = $decline_error = "";
$fname = $lname = $email = $phone = $message = $jobtitle = $nameauth = $success = $privacy = "";

if (empty($_POST["phone"])) {
$phone_error = "Phone is required";
  } else {
 $phone = test_input($_POST["phone"]);
 // check if e-mail address is well-formed
  if (!preg_match("/^(\d[\s-]?)?[\(\[\s-]{0,2}?\d{3}[\)\]\s-]{0,2}?\d{3} 
  [\s-]?\d{4}$/i",$phone)) {
  $phone_error = "Invalid phone number"; 
  }
 }`

if ($privacy == 'agree') {

if ($fname_error == '' and $lname_error == '' and $email_error == '' and $phone_error == '' and $privacy_error == '' and $nameauth_error == '' and $job_error == '') 
    {   
        $sql = "INSERT INTO data (phoneNo) VALUES ('$phone');";
        mysqli_query($con, $sql);
        $success = "Enjoy your game!!";
        $fname = $lname = $email = $phone = $message = $jobtitle = $nameauth = "";
    }   

} else if ($privacy == '') {
    $privacy_error = "Please Accept or Decline Our Policy";
} else if ($privacy == 'disagree') {
    $decline_error = "Please Accept Our Privacy Policy to Continue";
}   

}

this code works perfect if the column datatype in phpmyadmin is varchar but breaks if i use INT

Does it have to do with the fact that i initialise the variable as ="" which makes it a varchar?

Do i have to initialize my INT values as =0; ?

HI all, i managed to get the result i want by using the following code: — kayzarh, Aug 31 '18 at 11:13
please go through https://stackoverflow.com/questions/24353778/which-is-best-data-type-for-phone-number-in-mysql-and-what-should-java-type-mapp — Dr Manish Lataa-Manohar Joshi, Aug 31 '18 at 11:13

kayzarh · Answer 1 · 2018-08-31T11:27:52.177

Hi all i managed to suss it out with the following code, it now works: if ($fname_error == '' and $lname_error == '' and $email_error == '' and $phone_error == '' and $privacy_error == '' and $nameauth_error == '' and $job_error == '') { $intphone = (int)$phone; $sql = "INSERT INTO data (phoneNo) VALUES ('$intphone');"; mysqli_query($con, $sql); $success = "Enjoy your game!!"; $fname = $lname = $email = $phone = $message = $jobtitle = $nameauth = ""; I got the value of the $phone and converted it into an int by: $intphone = (int)$phone;

assuming a phone number of: 99999999999 in my db i am getting : 2147483647

which i believe is the highest value a INT field can show, and because the filtering in my phone number only allows 11 numbers and no less, i guess this converts to a value higher than INT can show.

i selected my INT(40) in phpmyadmin, however seems this is not the actual character length but some form of bit representation (which i will read up on!)

thanks all, and if there are any pointers in any direction you can recommend i will read up!

thanks all!

I will recommend you to use this field as a `varchar`, as you said, INT max value is 2,147,483,647, and that's why you are getting that error — FedeCaceres, Aug 31 '18 at 11:35

score 0 · Accepted Answer · answered Aug 31 '18 at 11:16

0

You have to cast it in order to use it as a int.

$phone = (int) $phone.

Also, phone numbers are always string, as they might have "+" or start with "0".

answered Aug 31 '18 at 11:16

FedeCaceres

158
1
11

score -1 · Answer 3 · answered May 31 '22 at 11:43

-1

In your phpmyadmin try to change your datatype to longtext.enter image description here

answered May 31 '22 at 11:43

Mark Jerich Taboada

1

`enter image description here` is a *placeholder* you are supposed to replace with an actual description. – Eric Aya May 31 '22 at 13:50
The image isn't appearing correctly – ancyrweb May 31 '22 at 14:14
I'm not talking about image inlining. I'm talking about replacing the placeholder with an actual description. – Eric Aya May 31 '22 at 18:00
While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/late-answers/31906825) – Semih Arslanoglu Jun 03 '22 at 10:46

Inserting integers into SQL using PHP/phpmyadmin

3 Answers3