Pushing Data to a MySQL Database from a Form

Question

I am trying to send data from an HTML form to a MySQL database in phpmyadmin. I have a database named bhs2018 and a table called game 1. Here are the contents of that table.

Here is my form:

<form name="game" action="insert.php" method="post">
  <p> <select id="player" name = 'player'>
    <option value="b">B</option>
    <option value="n">N</option>
    <option value="a">A</option>
    <option value="c">C</option>
    <option value="m">M</option>
    <option value="j">J</option>
    <option value="ja">Ja</option>

  </select>
  <select id="what" name = 'what'>
    <option value="shoton">Shot on Cage</option>
    <option value="shotoff">Shot off Cage</option>
    <option value="goal">Goal</option>
    <option value="countergoal">Goal on Counter</option>
    <option value="countershot">Shot on Counter</option>
    <option value="assist">Assist</option>
    <option value="block">Block</option>
    <option value="steal">Steal</option>
    <option value="turnover">Turnover</option>
    <option value="drawn">Ejection Drawn</option>
    <option value="ejected">Ejected</option>
  </select>
  <select id="where" name = 'where'>
    <option value="set">Set</option>
    <option value="navy">Navy</option>
    <option value="leftwing">1/2 side past 5</option>
    <option value="rightwing">4/5 side past 5</option>
    <option value="point">Point/3</option>
    <option value="lefttwo">1/2 side 2 meter</option>
    <option value="righttwo">4/5 side 2 meter</option>
    <option value="1">6 on 5 1</option>
    <option value="2">6 on 5 2</option>
    <option value="3">6 on 5 3</option>
    <option value="4">6 on 5 4</option>
    <option value="5">6 on 5 5</option>
    <option value="6">6 on 5 6</option>
  </select>
  <select id="quarter" name = 'quarter'>
    <option value="q1">Quarter 1</option>
    <option value="q2">Quarter 2</option>
    <option value="q3">Quarter 3</option>
    <option value="q4">Quarter 4</option>
  </select>
    <select id="time" name = 'time'>
    <option value="0:30">0:30</option>
    <option value="1:00">1:00</option>
    <option value="1:30">1:30</option>
    <option value="2:00">2:00</option>
    <option value="2:30">2:30</option>
    <option value="3:00">3:00</option>
    <option value="3:30">3:30</option>
    <option value="4:00">4:00</option>
    <option value="4:30">4:30</option>
    <option value="5:00">5:00</option>
    <option value="5:30">5:30</option>
    <option value="6:00">6:00</option>
    <option value="6:30">6:30</option>
    <option value="7:00">7:00</option>
  </select>

  Notes: <input type="text" id = 'notes' name = 'notes'>

  <button type="submit" onclick="save()"> Save </button> </p>
</form>

Whenever I click my "Save" button, the insert.php script loads. Instead of echoing something, it just shows the code. Here is insert.php.

<?php
    $con = mysqli_connect('127.0.0.1','root','password'(my actual password is here);
    if(!$con){
        echo 'Not Connected to Server';
    }

    if (!mysqli_select_db($con,'bhs2018')){
            echo 'Not Selected';
    }

    $Player = $_POST['player'];
    $Quarter = $_POST['quarter'];
    $Time = $_POST['time'];
    $Where = $_POST['where'];
    $Notes = $_POST['notes'];
    $What = $_POST['what'];

    $sql = "INSERT INTO game1 (player,quarter,time1,where1,notes,what) VALUES ('$Player', '$Quarter', '$Time', '$Where','$Notes','$What')";
    if(!mysqli_query($con,$sql)){
        echo'Not Inserted';
    }
    else{
        echo 'Inserted';
    }

    header('refresh:2; url=index.html');


?>

What is looking wrong with my code? Why does it not run the php script? Thank you so much!

@sanojlawrence There is no error message. I just see the insert.php script in a browser window. — tfs, Jul 27 '18 at 16:40
Your code is vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. You should use prepared statements with bound parameters, via either [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php). [**This post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has some good examples. — Alex Howansky, Jul 27 '18 at 16:46
@AlexHowansky To make it invulnerable, I just need to add "mysql_real_escape_string" before each variable? — tfs, Jul 27 '18 at 16:49
No, `mysql_real_escape_string()` is not always sufficient. You must use prepared statements with bound parameters. — Alex Howansky, Jul 27 '18 at 16:50
I ran your code in my personal webserver and everything worked fine. Although, I did make a few changes since I am on a local server. I changed the server to "localhost" and I changed the password. Are you running this on a local server? — karafar, Jul 27 '18 at 16:52
@FaridKaradsheh I am running this on a local server. I believe something is wrong with my setup. I have php running (it shows the screen when I go to https/localhost), mysql running, apache running, and phpmyadmin running. Do I need something else running? — tfs, Jul 27 '18 at 16:56
You should not be logging into your mysql server with Root, that's a huge security breach (Unless this application is not public), but that won't fix your problem. — GrumpyCrouton, Jul 27 '18 at 16:58
@GrumpyCrouton This app is not public, I am using it for learning. And it is a .php file. — tfs, Jul 27 '18 at 16:59
@FaridKaradsheh That won't really solve OPs problem, because OP said it's just displaying the PHP code in the webpage, meaning it is not executing. — GrumpyCrouton, Jul 27 '18 at 16:59
@GrumpyCrouton Well, I think he stated it is downloading the file. So, it may be that his local server is off. — karafar, Jul 27 '18 at 17:01
@Tej Singh, check your database. Your `PRIMARY KEY` `player` should be an `integer` like `int(30)` or `bigint(200)` but not `varchar`. Create and new column for `PRIMARY KEY`. Also when primary key is an integer, there is no manual insertion option for inputting it's value in your form. So make your primary key is set to `AUTO_INCREMENT`. — Klanto Aguntuk, Jul 27 '18 at 17:40

score 0 · Answer 1 · answered Jul 27 '18 at 17:49

Check your database. Your PRIMARY KEY player should be an integer like int(30) or bigint(200) but not varchar. Create a new column for PRIMARY KEY something like player_id and shift player to next column. Also when primary key is an integer, there is no manual insertion option for inputting it's value in your form. So make your PRIMARY KEY is set to AUTO_INCREMENT in case you don't want to insert it's value manually.

Pushing Data to a MySQL Database from a Form

1 Answers1