insert data into mysql after scanning for already used username

Question

I am very new to php and I am trying to insert data into my database. I already have atable called "Locate" in my database that I am trying to update, it has the columns "Longitude", "Latitude" and "ID".

Firstly is my code below suitable to update this table.

Secondly, I want to add code that will check the database and make sure the ID is not one that has already been used, thanks in advance.

<?php

$longitude = $_GET['longitude'];
$latitude = $_GET['latitude'];  
$username = $_GET['username']; 

// Create connection
$con=mysqli_connect("localhost","dbuser","password","yviewdb");

// Check connection
if (mysqli_connect_errno())
{
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

 $sql = "UPDATE CFP SET Longitude =  '$longitude' AND Latitude = '$latitude' WHERE ID = '$username';";

  $res = mysql_query($sql,$con) or die(mysql_error());

 // Close connections
mysqli_close($con);
 if ($res) {
 echo "success";
}else{
 echo "failed";
}
?>

Answer 1

There are a few things wrong with your code, being these lines:

$sql = "UPDATE CFP SET Longitude =  '$longitude' AND Latitude = '$latitude' WHERE ID = '$username';";

$res = mysql_query($sql,$con) or die(mysql_error());

You are mixing MySQL APIs using mysql_ and mysqli_, they do not mix together.

Sidenote: The DB connection variable comes first in mysqli_, so that has been changed to reflect it. See the fix below.

Plus, you are using AND instead of a comma (as a seperator) in your SET to seperate both columns to be updated.

$sql = "UPDATE CFP SET Longitude =  '$longitude', Latitude = '$latitude' WHERE ID = '$username';";

$res = mysqli_query($con,$sql) or die(mysqli_error($con));

You're also closing your DB connection too early, place it after you've checked if the query was successful:

 if ($res) {
 echo "success";
}else{
 echo "failed";
}

mysqli_close($con);

Also make sure that your form reflects your GET method for your variables.

Plus, your present code is open to SQL injection. Use prepared statements, or PDO with prepared statements, they're much safer.

---

Visit the MySQL.com website for syntax on UPDATE:

• http://dev.mysql.com/doc/refman/5.0/en/update.html

---

However, your question's title read as "insert data..." and in your question "and I am trying to insert data into my database"

If you want to insert rather than update, then do

$sql = "INSERT INTO CFP (Longitude, Latitude) VALUES ('$longitude', '$latitude')";

For syntax on how to INSERT into a table, visit:

• http://dev.mysql.com/doc/refman/5.6/en/insert.html

---

Error reporting

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// rest of your code

Sidenote: Error reporting should only be done in staging, and never production.

Answer 2

First and foremost, you should never use the GET request to make changes to a database. GET requests from the server should only be used to view data, as in a search request or something like that.

Next, you aren't escaping your submitted information for malicious code.

Finally, you are mixing the mysql and mysqli API's which isn't yielding you the behaviour you'd like.

Anyway, I don't know the intricacies of your application, so I'll just go along with what you have and make the corrections necessary:

<?php
$longitude = $_GET['longitude'];
$latitude = $_GET['latitude'];  
$username = $_GET['username']; 

// Create connection
$con=mysqli_connect("localhost", "dbuser", "password", "yviewdb");

// Check connection
if (mysqli_connect_errno()){
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql = "UPDATE CFP SET Longitude =  '$longitude', Latitude = '$latitude' WHERE ID = '$username'";

$res = mysqli_query($con, $sql) or die(mysql_error());

// Close connections
mysqli_close($con);
if($res){
   echo "success";
}else{
   echo "failed";
}
?>

Changes: 1. you don't put a semi-colon at the end of the query to the $sql variable, you just close the parenthesis and put the semi-colon at the end of the regular PHP statement.

2. I used some extra formatting to make your code more legible.

3. Use commas, not 'AND' when setting multiple values in an UPDATE statement for MySQL.