Trying to insert data into a database using PHP / Mysqli

Question

I'm trying to execute an Insert query to write data into a Database. I'm using Mysqli and PHP. The code looks OK for me. However, every time I go to the webpage to check if the form works, the query gets executed an a new row is created in the DB (empty).

I'm pretty sure there is something wrong with the last if statement. Could you advise?

BTW, the snippet is only for the PHP to execute the sql query, since the form is working just fine.

Thanks!

$servername = "localhost";
$username = "root";
$password = "mysqlpassword";
$dbname = "bowieDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$album = $_POST['album'];
$relyear = $_POST['relyear'];
$label = $_POST['label'];
$chart = $_POST['chart']; 
$track1 = $_POST['track1'];
$track2 = $_POST['track2'];
$track3 = $_POST['track3'];
$track4 = $_POST['track4'];
$track5 = $_POST['track5'];

$sql = "INSERT INTO Albums (album, relyear, label, chart, track1, track2,     track3, track4, track5)
VALUES ('$album', '$relyear', '$label', '$chart', '$track1', '$track2',     '$track3', '$track4', '$track5')";

$result = mysqli_query($conn, $sql);
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();

I am curious, what else you expect from this code? What I see here is "insert into a database every time this script is run" in plain PHP. It does exactly what you wrote. — Your Common Sense
– Your Common Sense, Commented Jul 11, 2016 at 16:04
Hi. Yeap, but unfortunately I expect that the insert statement runs only when clicking submit, and previously checking if no information is missing. — Emilio Zaidman
– Emilio Zaidman, Commented Jul 11, 2016 at 16:06
I would advise starting from the common problems. First, do a var_dump on your $_POST data, to ensure they are correctly "filled", then check if your statement resolves without problems on your database. Finally, as far as "SQL injections" goes, your script is vulnerable. — Bonatti
– Bonatti, Commented Jul 11, 2016 at 16:07
Something is missing in your explanation. You say this code runs when the form page is first visited, even before submit is clicked. Is the code you've shown in the same file as your form code? If so, why not move the code you're showing to a separate file and post your form to it? If it's already in a separate file, something is posting your form when you first visit the page. We can't find that for you. — devlin carnate
– devlin carnate, Commented Jul 11, 2016 at 16:24

Mm-Art-In · Accepted Answer · 2016-07-11 16:27:41Z

0

You are mixing Procedural and Object Orientated SQL interactions.

This is Procedural:

$result = mysqli_query($conn, $sql);

This is Object Orientated:

$conn->query($sql)

You can not use both with the same connection details, you should do one or the other throughout your code. The best one to use is Object Orientated approach, so rework the Procedural code to:

$result = $conn->query($sql);
if ($result) {
...

So actually you can simply remove the line starting $result = ... and let the IF statement query you already have handle itself.

Other notes:

Use MySQL error feedback such as checking if(!empty($conn->error)){print $conn->error;} after SQL statements. See example code below...
Use the following PHP error feedback too, set at the very top of your PHP page:

...

  error_reporting(E_ALL);
  ini_set('display_errors',0);
  ini_set('log_errors',1);

you need to read up and be aware of SQL injection that can destory your database should someone POST data that also happens to be MySQL commands such as DROP.

Code for Comment:

if ($_SERVER['REQUEST_METHOD'] == "POST") {
     //run SQL query you already have coded and assume
     // that the form has been filled in. 
    $result = $conn->query($sql);
    if ($result) { 
        //all ok
     }
     if(!empty($conn->error)) {
        print "SQL Error: ".$conn->error;
     } 
}

edited Jul 11, 2016 at 16:27

answered Jul 11, 2016 at 16:05

Mm-Art-In

23k13 gold badges81 silver badges144 bronze badges

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

4 Comments

Emilio Zaidman Over a year ago

Thanks, yes! I tried both, but I've decided to use Object Oriented. However, if I delete the $result variable, the script runs every time I go to the webpage. I previously developed a form that checks (via PHP) if the information is valid. So, I'd like that the script only run when clicking submit, previous checked if no data is missing.

Mm-Art-In Over a year ago

You can't mix both, you need to stick with the OO approach. You can wrap the whole databas insert inside an IF statement and then set something like IF($_POST['submit'] == "save!"){...} <== assuming the button to submit the data has a value attribute of "save!" @EmilioZaidman

Emilio Zaidman Over a year ago

Thanks Martin. I'm only using OO now. Honestly, I would not know how to do what you suggest. Sorry, I'm an amateur trying to learn! How could I wrap the SQL statement inside an IF, so it'd check if the insert query is completed before it gets executed?

Mm-Art-In Over a year ago

@EmilioZaidman I have added code to the bottom of my answer which should be an example for how you can structure your code.

score 0 · Accepted Answer · 2016-07-11 16:33:56Z

0

use

1. if(isset($_POST['Submit'])){//your code here }

and

2. if($result){...

if you are using procedural method

edited Jul 11, 2016 at 16:33

answered Jul 11, 2016 at 16:23

user6214813

5 Comments

Mm-Art-In Over a year ago

this does not answer the original question. This should be a comment

user6214813 Over a year ago

"However, every time I go to the webpage to check if the form works, the query gets executed an a new row is created in the DB (empty)"

Mm-Art-In Over a year ago

Ok, fair enough, but If you could expand your answer and explain it a bit more, it avoids this sort of ambiguity. cheers.

devlin carnate Over a year ago

This assumes that there is a form element with the name 'Submit'.

user6214813 Over a year ago

@devlincarnate yes.

Collectives™ on Stack Overflow

Trying to insert data into a database using PHP / Mysqli

2 Answers 2

Other notes:

4 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Other notes:

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related