Inserting into mysql database not working from php

Question

I am having trouble inserting into my database. One of my insert statements works because when I do a Select * from table; i get the new records. However the other table that I want to be inserted doesnt have any values when I select * from it.

The DDL of the 2 tables that I want the values inserted are

CREATE TABLE Job(uid INTEGER,job_id INTEGER AUTO_INCREMENT,
input varchar(500),status varchar(100),start_time time,finish_time time,
FOREIGN KEY(uid) REFERENCES User(uid) ON DELETE CASCADE,
PRIMARY KEY(job_id))ENGINE = InnoDB;

CREATE TABLE BLAST(database varchar(100),evalue varchar(100),job_id INTEGER, 
FOREIGN KEY (job_id) REFERENCES Job(job_id))ENGINE = InnoDB;

The table that has issues is the BLAST Table. I believe it is because there is a foreign key of job_id and I dont actually insert that value in. I thought it would just do the same job_id as the job_id in the Job table but I guess im wrong. If someone could explain to me what to do I would greatly appreciate it!

PHP code

<select id="database" name="database">
    <option selected="selected" disabled="disabled">Database</option>
    <option value="Archaea">Archaea</option>
    <option value="Bacteria">Bacteria</option>
</select>

<select id="evalue" name="evalue">
    <option selected="selected" disabled="disabled"> evalue <option>
    <option value="0.0001">0.0001</option>
    <option value="0.001">0.001</option>
</select>

<select id="hits" name="hits">
    <option selected="selected" disabled="disabled"> Hits</option>
    <option value="50">50</option>
    <option value="100">100</option>
</select>

<input id="BlastSearch" type="text" name="BlastSearch" value='' />
<input type="submit" name="submit" value="submit" />
<button type="reset" value="Clear">Clear</button>
</form>
<?php
    session_start();
    require_once '../secure/database.php';
    $mysqli = new mysqli($dbhost,$dbuser,$dbpass,$dbname);
    }

    //Insert the values into the database

    if(isset($_POST['submit'])){

            //declare variables to what the user defines them as
            $db = $_POST['database'];
            $evalue = $_POST['evalue'];
            $sequence = $_POST['BlastSearch'];

            //insert the values into the database
            $mysqli->query("INSERT INTO `Job` (`uid`, `input`, `status`, `start_time`, `finish_time`) VALUES ('1', '" . $sequence . "', 'running' , NOW(), NOW())");

            $mysqli->query("INSERT INTO `BLAST`(`database`, `evalue`, `job_id`) VALUES ('" . $db . "','" . $evalue . "', '1')") or die(mysqli_error($db));



    }
  ?>

ERROR I AM GETTING: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''job_id',database, evalue) VALUES ('56','0.001', 'Eukaryota')' at line 1

I'm pretty sure that second query is going to result in an error. Since you have a string literal as a column name and then back-ticked identifier called "1" in the values. — David
– David, Commented May 3, 2015 at 17:44
That was just me being dumb sorry. I changed them back and it still doesnt work :( — jefferymicheals
– jefferymicheals, Commented May 3, 2015 at 17:47

VerySeriousSoftwareEndeavours · Accepted Answer · 2015-05-04 00:03:37Z

3

if($mysqli->query("INSERT INTO `BLAST`(`database`, `evalue`, `job_id`) VALUES ('" . $db . "','" . $evalue . "', '".$mysqli->insert_id."')")) {
  echo "Error description: " . mysqli_error($mysqli);
}

1) Change hard coded '1' to use $mysqli->insert_id. You need to insert the proper job_id that is being generated by your first query:

See: http://php.net/manual/en/mysqli.insert-id.php

2) Change back ticks to single quotes in wrapper of job_id value: Change: `".$mysqli->insert_id."` to ' ".$mysqli->insert_id." '. Notice we are changing the back ticks wrapper to a single quote wrapper.

3) Pass in correct variable to the mysqli_error($mysqli) function. Pass in $mysqli instead of $db.

4) Change the job_id wrapper in your insert query to use back ticks, not single quotes.

edited May 4, 2015 at 0:03

answered May 3, 2015 at 17:45

VerySeriousSoftwareEndeavours

1,7634 gold badges33 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

jefferymicheals Over a year ago

So i just add $mysqli->insert_id after?

VerySeriousSoftwareEndeavours Over a year ago

Add $mysqli->insert_id in place of the hard coded '1' you have in the second query. Also, you need to change the back ticks to apostrophes that you are wrapping the query value in. (See example above.)

jefferymicheals Over a year ago

it still tells me empty set :(

VerySeriousSoftwareEndeavours Over a year ago

It tells you that on what? Post the SELECT query you are trying to run that gives you no results.

jefferymicheals Over a year ago

Im about to drop out of college and change career paths because I cant figure this out

|

Collectives™ on Stack Overflow

Inserting into mysql database not working from php

1 Answer 1

17 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

17 Comments

Your Answer

Sign up or log in

Post as a guest

Related