Why is this sql query inserting garbage value into database instead of number value [POST method]
I am working on this form, but it is handling the garbage value in the "phone" field in the database, where I give the correct number. what's wrong with the code? I first set the length of this field to 11 and thought this might be the problem. but when I increased that to 50 the problem came up again. How to solve this?
PS: neglect the protective part of the form, this is just for testing purposes.
<table border="1" id="tab1">
<form action="create.php" method="POST" enctype="multipart/form-data">
<tr align='center'><td>Ad Title:</td><td><input type='text' name='ad_title' id="ad_title" placeholder='type ad title'/></td></tr>
<tr align='center'><td>Ad Description:</td><td><textarea name='ad_details' id="ad_details" placeholder='type ad details' cols="40" rows="10"></textarea></td></tr>
<tr align="center"></tr>
<tr align="center"><td>Category:</td><td>
<select name="category">
<option value="mobile">Mobile Phone</option>
<option value="computer">Computer</option>
<option value="book">Books</option>
<option value="fashion">Fashion and Beauty</option>
</select>
</td></tr>
<tr align='center'><td>Phone Number:</td><td><input type='number' id="phone" name='phone' placeholder='type your Phone Number'/></td></tr>
<tr align='center'><td>Email:</td><td><input type='text' name='email' id="email" placeholder='type your email'/></td></tr>
<tr align='center'><td>Price:</td><td><input type='text' name='price' id="price" placeholder='enter your desired Price'/></td></tr>
<tr align="center"><td>Negotiable:</td><td><input type="radio" name="nego" value="yes"/>YES<input type="radio" name="nego" value="no"/>NO</td></tr>
<tr align="center"><td>City/Town:</td><td>
<select name="city">
<option value="Jalpaiguri">Jalpaiguri</option>
<option value="Siliguri">Siliguri</option>
</select>
</td></tr>
<tr align='center'><td>Location:</td><td><input type='text' id="location" name='location' placeholder='type your Location'/></td></tr>
<tr><td>Choose Photo for Ad:</td><td><input type='file' name='ad_pic'/></td></tr>
<tr align='center'><td colspan="7"><input type='submit' id="submit" name ='submit' value='Create Ad'/></td></tr>
</form>
</table>
php code looks like this:
<?php
session_start();
if(!isset($_SESSION['username'])){
header("location:login.php");
}
$user = $_SESSION['username'];
$conn = new mysqli("localhost","root","","pending");
if($conn->connect_error){
echo "<script>document.getElementById('noti').innerHTML='Database Error.Please Try again !';</script>";
}
if(isset($_POST['submit'])){
$ad_title = $_POST["ad_title"];
$ad_details = $_POST["ad_details"];
$category = $_POST["category"];
$phone = $_POST["phone"];
$email = $_POST["email"];
$nego = $_POST["nego"];
$city = $_POST["city"];
$location = $_POST["location"];
$price = $_POST["price"];
$sql = $conn->query("INSERT INTO ads(ad_id,user_name,ad_title,ad_details,price,category,phone,email,negotiable,city,location) VALUES('','$user','$ad_title','$ad_details','$price','$category','$phone','$email','$nego','$city','$location')");
$pid = $conn->insert_id;
$newname = "$pid.jpg";
move_uploaded_file($_FILES['ad_pic']['tmp_name'], "ad_images/$newname");
}
? >
source to share
There is a problem with your input request.
in the database field phone
you specified type
as INT
. And in your request, you are trying to insert a row '$phone'
.
Correct way: you should only use $phone
.
Explanation Why
Why is this statement wrong in your case. ( '$phone'
and $phone
)
- Anything between single quotes is considered a string, not a number. When you speak
'500'
, MySQL reads it as three characters of text , but when you speak500
, MySQL reads it as one number . - The BETWEEN ... AND operators are for testing if a value is between a range of numbers (dates are also considered numbers inside). Trying to use it in strings will not work as expected.
- By the way, this is a big mistake . A huge waste of space. When you store an integer as a VARCHAR, the storage requirements for each letter in the number are equal to the general storage requirements if stored as INTs.
So your request should appear as
$sql = $conn->query("INSERT INTO ads(ad_id,user_name,ad_title,ad_details,price,category,phone,email,negotiable,city,location) VALUES('','$user','$ad_title','$ad_details','$price','$category',$phone,'$email','$nego','$city','$location')");
Get an easy idea
change this '$phone'
to$phone
source to share