PHP and sql issue

Question

I have been trying to make my scoreboard system for some online game , It works correctly , So I have added an HTML form that interacts with a php script that connects to my sql database and inserts a name with a percentage into my sql scoreboard table , If I add the names/percentages manually via phpMyAdmin , It works perfectly on the scoreboard , But when I made the html form that asks the user to insert his name and then adds his name into the scoreboard with a percent , IT doesn't add , So here's my html form .

<center><form method="post" action="">
<font color="green">Your name Max length is 15</font> <input type="text" name="username" maxlength="15">
<button style="background-color:red; name="Enter" type="submit" value="HTML">Enter</button></center>
</form>

My PHP Form

<?php
if (isset($_POST['username'])) 
{
$getname = $_POST['username'];
$percentage = "10";
$link = mysqli_connect("myhost","myusername","mypw","mydatabase") or die("Error " . mysqli_error($link));

$query = "INSERT INTO scoreboard (name,percent) VALUES ('$getname','$percentage');" or die("Error in the consult.." . mysqli_error($link));

$result = mysqli_query($link, $query);
}
?>

Scoreboard is the name of my table , Columns are name/percent , Name accepts texts and Percent accepts Integers , Thanks in advance :) .

Answer 1

I tested your code and found that your submit button was one of the things at fault, including an improperly place semi-colon in:

$query = "INSERT INTO scoreboard (name,percent) VALUES ('$getname','$percentage');"

Which should read as:

$query = "INSERT INTO scoreboard (name,percent) VALUES ('$getname','$percentage')";

Tested using the following form and PHP

<form method="post" action="">
<center>
<font color="green">Your name Max length is 15</font>
<input type="text" name="username" maxlength="15">
<input type="submit" name="submit" value="Submit">
</center>
</form>

<?php
if (isset($_POST['username']))
{
$link = mysqli_connect("myhost","myusername","mypw","mydatabase") or die("Error " . mysqli_error($link));
// $getname = $_POST['username'];
$getname = mysqli_real_escape_string($link,$_POST['username']);
$percentage = "10";
$query = ("INSERT INTO scoreboard (name,percent) VALUES ('$getname',$percentage)");
$result = mysqli_query($link, $query);
if(!$result){
  printf("Error message: %s", mysqli_error($link));
}
else {
echo "Data properly inserted with the value of <b>$getname</b> and <b>$percentage</b>";
     }
}
?>

NOTE: You will be better off using the code below in order to check if the field is empty. Otherwise, clicking on the submit button without anything inside, will produce an entry in DB with a blank name field.

if (empty($_POST['username'])) {

die("<div align='center'>Enter your name</div>");

}

else

{
// rest of code

Plus as stated by Hanky 웃 Panky, you should sanitize your variables like this, as done in my working example:

$getname = mysqli_real_escape_string($link,$_POST['username']);

---

Here is a safer (parametrized) method as taken from an example on SO here

Quick note: If you are going to use $percentage = 10; instead of $percentage = "10";

then you will need to use $stmt->bind_param("si", $unsafe_variable,$percentage); otherwise, your percentage will be treated as a string, as opposed to an integer and will be thrown an error. s is for string and i is for integer.

<form method="post" action="">
<center>
<font color="green">Your name Max length is 15</font>
<input type="text" name="username" maxlength="15">
<input type="submit" name="submit" value="Submit">
</center>
</form>

<?php
if (empty($_POST['username'])) {
die("<div align='center'>Enter your name</div>");
}
else
{
    $mysqli = new mysqli("myhost","myusername","mypw","mydatabase");
    // Check that connection was successful.

    if($mysqli->connect_errno > 0) {
      die('Connection failed [' . $mysqli->connect_error . ']');
    }

    $percentage = "10";
    $unsafe_variable = $_POST["username"];
    $stmt = $mysqli->prepare("INSERT INTO scoreboard (name,percent) VALUES (?,?)");
    // TODO check that $stmt creation succeeded
    // "s" means the database expects a string
    $stmt->bind_param("ss", $unsafe_variable,$percentage);
    $stmt->execute();
    $stmt->close();
    $mysqli->close();
    echo "<div align='center'>Data written to DB</div>";
}
?>

Answer 2

Whenever in doubt about the correct usage of any function, take help from php.net first, it has so many examples on the pages about this. Have a look at

http://www.php.net/manual/en/mysqli.real-escape-string.php

http://php.net/mysqli_error

http://www.php.net/manual/en/mysqli.query.php

First sanitize your input value

$getname = mysqli_real_escape_string($link,$_POST['username']);
$percentage = 10;

Then

$query = "INSERT INTO scoreboard (name,percent) VALUES ('$getname',$percentage)";
$result = mysqli_query($link, $query);
if(!$result){
  printf("Error message: %s", mysqli_error($link));
}   

Answer 3

This line needs fixed

$query = "INSERT INTO scoreboard (name,percent) VALUES ('$getname','$percentage');" or die("Error in the consult.." . mysqli_error($link));

I've changed it to this

$query = "INSERT INTO scoreboard VALUES ('$getname','$percentage')";

then add or die("Error in the consult.." . mysqli_error($link)); to your query line.

You had a compiler line break in the middle of your variable and you don't need to state the order that your naming values, you just have to put the values in the correct places and it will automatically add them in order.