How to insert values into a mysql database using php

Question

I need help understanding how to insert values into a mysql database. I understand I will need to write a INSERT statement of the data that I get from the user. But I dont really understand where to put this insert statement and how to get it to run. Do I use pg_prepare and pg_execute? If someone could just help me set my code up to where I would run the insert statement I would greatly appreciate it! Thanks for the help in advance.

HTML code

<!DOCTYPE html>
<html>
<body>

<form method="POST", action="Blast.php">
<select id="database" name="database" value='Select a Database'>
    <option value="UniprotKB">UniProtKB</option>
    <option value="GenBank">GenBank</option>
    <option value="RelSeq">RelSeq</option>
</select>
<select id="evalue" name="evalue" value='Select evalue'>
    <option value="0.0001">0.0001</option>
    <option value="0.001">0.001</option>
    <option value="0.01">0.01</option>
    <option value="0.1">0.1</option>
    <option value="1">1</option>
    <option value="10">10</option>
    <option value="100">100</option>
    <option value="1000">1000</option>
</select>

<input id="BlastSearch" type="text" name="BlastSearch" value='' />
<input type='submit' name='submit' value='Run BLAST' />
<button type="reset" value="Clear">Clear</button>

</form>

So there are basically 3 values that are inserted by the user, and I want to insert them all into the database when the submit button is pressed!

PHP Code

<?php
    require_once '../secure/database.php';
    $mysqli = new mysqli($dbhost,$dbuser,$dbpass,$dbname);

    if($mysqli->connect_error){
            exit('CON Error: ' . $mysqli->connect_errno . ' ' . $mysqli->connect_error);
    }

 $db = $_POST['database'];
 $evalue = $_POST['evalue'];
 $sequence = $_POST['BlastSearch'];


    print "Connected! Host info: " . $mysqli->host_info . "<br>\n";
    $mysqli->close();

?>

First, select you database using [mysqli::select_db] (http://php.net/manual/en/mysqli.select-db.php) and then you can simple run a insert query with [mysqli:query] (http://php.net/manual/en/mysqli.query.php) — Guilherme Ferreira, Apr 22 '15 at 14:25
Dont I already connect to the database though? Or do I need to do it again — ryan lizarga, Apr 22 '15 at 14:27
Okey but, you have to select a database and then run the query (insert) to you table (inside your main database). — Guilherme Ferreira, Apr 22 '15 at 14:28
Tell me this isn't yours http://stackoverflow.com/q/29786983/ it's under a different member ([jimmy bean](http://stackoverflow.com/users/4792036/jimmy-bean)) and was deleted. I remember the question/code being 99% identical. You need to read tutorials; we're not a school. Read up on INSERT http://dev.mysql.com/doc/en/insert.html — Funk Forty Niner, Apr 22 '15 at 14:29
@Fred-ii- I just made this account the other day and this is my first question ever posting here. But thanks for directing me to a blank page and looking out for the site! — ryan lizarga, Apr 22 '15 at 14:31
People create new accounts because of a question being downvoted and not getting answers they want to get. We see too many of those. As for the blank page; it's there alright, you just need 10k+ to see it ;-) — Funk Forty Niner, Apr 22 '15 at 14:32
@Fred-ii- if you read the question I dont need help writing the insert statement..... INSERT INTO tables VALUES (). I just need help figuring out how to run this securely!! I believe I use pg_prepare ad execute — ryan lizarga, Apr 22 '15 at 14:33
Securely; Stack has a Q&A right here => http://stackoverflow.com/q/60174/ and [**`mysqli` with prepared statements**](http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php), or [**PDO with prepared statements**](http://php.net/pdo.prepared-statements), the manuals you can read up on. — Funk Forty Niner, Apr 22 '15 at 14:34
@ryanlizarga *I just made this account the other day* You are member since today! -> http://stackoverflow.com/users/4819879/ryan-lizarga And the other question got magically delete 10 minutes before you posted this question?! And how is it possible that another person writes so many lines almost exactly the same as you do? — Rizier123, Apr 22 '15 at 14:42

score 1 · Answer 1 · edited Apr 22 '15 at 14:35

1

$dsn = 'mysql:host=localhost;dbname=your_db_name';
$username = 'username';
$password = 'password';
$pdo = new PDO($dsn, $username, $password, $options);
$stm = $pdo->prepare('INSERT INTO table (col1, col2, col3) VALUES (?,?,?)');
$stm->execute(array($col1_value, $col2_value, $col3_value));

It is as easy as that.

edited Apr 22 '15 at 14:35

hedi

1,476
2
11
14

answered Apr 22 '15 at 14:26

lshas

1,691
1
19
39

can I just use pg_prepare and pg_execute? – ryan lizarga Apr 22 '15 at 14:29
@ryanlizarga and no, you can not use any pg_* functions, unless you are using a PostgreSQL database, but from what you have tagged the post as I understand you are using MySQL? PDO can work with both PostgreSQL and MySQL (and lots of others). I recommend you use PDO. – lshas Apr 22 '15 at 14:33
@hedi and ishas. I already connect in my php code to my database. Do I still need to connect again? – ryan lizarga Apr 22 '15 at 14:37
@ryanlizarga No, you just have to query the database. http://php.net/manual/en/mysqli.query.php – lshas Apr 22 '15 at 14:44

score 0 · Answer 2 · answered Apr 22 '15 at 14:35

0

Basically you have to do two steps:

(1) Setup you connection, (2) Select your database and run your INSERT.

$mysqli = new mysqli("localhost", "my_user", "my_password", "database");
$mysqli->query("INSERT INTO ...");

answered Apr 22 '15 at 14:35

Guilherme Ferreira

153
1
11

So In my code I already connect to the database. Im confused because everyone tells me I need to connect. Do I need to connect again? – ryan lizarga Apr 22 '15 at 14:38
No, what you have to do is replace the $dbname variable with your $_POST['database'] and then you can run $mysqli->query(); – Guilherme Ferreira Apr 22 '15 at 14:40
The variable that is from POST['database'] isnt actually what databse you use to get the information from. It has to do with the program itself. Theres basically 2 libraries the user picks from to use. – ryan lizarga Apr 22 '15 at 14:53
So just run you INSERT with $mysql->query() ... :) – Guilherme Ferreira Apr 22 '15 at 14:55

score 0 · Accepted Answer · answered Apr 22 '15 at 14:43

    /*
 * SQL
 CREATE TABLE `NewTable` (
`id`  int NOT NULL AUTO_INCREMENT ,
`col1`  varchar(255) NOT NULL ,
`col2`  varchar(255) NOT NULL ,
`col3`  varchar(255) NOT NULL ,
PRIMARY KEY (`id`)
)
;


 * 
 * /SQL
 */

 $mysqli = new mysqli("localhost", "DB_USERNAME", "DB_PASSWORD", "DB_NAME");
    if($mysqli->connect_error)
    {
      die('Connect Error' . $mysqli->connect_error);
    } 
$mysqli->query("SET NAMES 'utf8'");
$mysqli->query("SET CHARACTER SET 'utf8'");
$mysqli->query("SET COLLATION_CONNECTION='utf8_general_ci'");
$mysqli->query("SET character_set_results = 'utf8'");
$mysqli->query("SET character_set_server = 'utf8'");
$mysqli->query("SET character_set_client = 'utf8'");
//connection  END
if(isset($_POST['database']) and isset($_POST['evalue']) and isset($_POST['BlastSearch'])){
     $db = htmlspecialchars(strip_tags($_POST['database']));
    $evalue = htmlspecialchars(strip_tags($_POST['evalue']));
    $sequence = htmlspecialchars(strip_tags($_POST['BlastSearch']));
$mysqli->query("INSERT INTO TABLE_NAME (col1,col2,col3) VALUES('$db','$evalue','$sequence')");
}

$mysqli->query("SET NAMES 'utf8'"); $mysqli->query("SET CHARACTER SET 'utf8'"); $mysqli->query("SET COLLATION_CONNECTION='utf8_general_ci'"); $mysqli->query("SET character_set_results = 'utf8'"); $mysqli->query("SET character_set_server = 'utf8'"); $mysqli->query("SET character_set_client = 'utf8'"); What do these do? — ryan lizarga, Apr 22 '15 at 14:44
This doesn't prevent SQL injection which is what the question is about. — Funk Forty Niner, Apr 22 '15 at 14:46
gotcha. Can you explain what they actually do? If I dont include them what are the effects? — ryan lizarga, Apr 22 '15 at 14:51

score 0 · Answer 4 · answered Apr 22 '15 at 14:49

You can try this:

<form action="send.php" method="post" enctype="multipart/form-data">
                <table>
                    <tr>
                        <td>Value1:</td>
                        <td><input type="text" name="value1" required><br></td>
                    </tr>

                    <tr>
                        <td>value2:</td>
                        <td><input type="text" name="value2" required><br></td>
                    </tr>
                    <tr>
                        <td>value3:</td>
                        <td><input type="text" name="value3" required><br></td>
                    </tr>
                </table>
</form>

Now on the send.php you have this:

<?php
        require 'database.php';

        //information is send to the database//
        $add_info= $database->information();
?>

The class is in ur database.php wich looks like this:

    <?php
    class database {
    private $pdo;

    public function __construct() {
        // Connection information
        $host = 'urhostname';
        $dbname = 'dbname';
        $user = 'username';
        $pass = 'password';

        // Attempt DB connection
        try
            {
                $this->pdo = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
                $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                //echo 'Successfully connected to the database!';
            }
                catch(PDOException $e)
            {
                echo $e->getMessage();
            }
        }



    function information() {
                $sql = "INSERT INTO tablename"
            . "(value1, value2, value3)"
            . "VALUES (:value1, :value2, :value3) ";
                $sth = $this->pdo->prepare($sql);
                $sth->bindParam(':value1', $_POST['value1'], PDO::PARAM_STR);
                $sth->bindParam(':value2', $_POST['value2'], PDO::PARAM_STR);
                $sth->bindParam(':value3', $_POST['value3'], PDO::PARAM_STR);
                $sth->execute();
        }
}

How to insert values into a mysql database using php

4 Answers4