Collin McCabe Collin McCabe - 14 days ago 7
Ajax Question

sql error when submitting with php

My php files that submits an entry to a database table isn't working and I can't figure out why. It takes in an Ajax submit and I know that the problem isn't with the data, or the Ajax request as it processes as a success. The only issue is that no data is ever submitted to my database. I had this working before I changed to code to concatenate the address string where it was one variable before. Any advice would be great!

Here is the php files
UPDATE:::THIS IS THE UPDATED PHP FILE

<?php
require("dbinfo.php");

// Create connection
$conn = new mysqli('localhost', $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$name = $_POST['user_name'];
$street = $_POST['user_street'];
$city = $_POST['user_city'];
$state = $_POST['user_state'];
$country = $_POST['user_country'];
$zip = $_POST['user_zip'];

$address = $street.', '.$city.', '.$state.', '.$country.', '.$zip;
$shortAdd = $city.', '.$state.', '.$country;

$type = $_POST['user_color'];
$desc = $_POST['user_message'];
$request_url = "http://maps.googleapis.com/maps/api/geocode/xml?address=".$address."&sensor=true";
$xml = simplexml_load_file($request_url) or die("url not loading");
$status = $xml->status;
if ($status=="OK") {
$lat = $xml->result->geometry->location->lat;
$lon = $xml->result->geometry->location->lng;
}
$sql = "INSERT INTO `markers` (`name`, `address`, `lat`, `lng`, `type`, `desc`)
VALUES (?, ?, ?, ?, ?, ?);";

$stmt = $conn->prepare($sql);
$stmt->bind_param('ssssss', $name, $shortAdd, $lat, $lon, $type, $desc);
$stmt->execute();
$conn->close();
?>

Answer

While docliving's answer is correct, please take the extra step and use prepared statements. Your code is vulnerable to SQL injection attacks without it. It just takes a very minor change to convert it to use prepared statements. Here is how to do it with mysqli:

$sql = "INSERT INTO `markers` (`name`, `address`, `lat`, `lng`, `type`, `desc`) 
    VALUES (?, ?, ?, ?, ?, ?);";

$stmt = $conn->prepare($sql);
$stmt->bind_param('ssssss', $name, $shortAdd, $lat, $lon, $type, $desc);
$stmt->execute();
Comments