Connect to mySQL server from PHP and JQuery

Question

Good evening SO. I'm a complete newbie when it comes to HTML, CSS, JS, PHP, and the like, so I need some help, particularly with some PHP and JQuery.

I'm trying to submit a simple email from an HTML form. Here's the HTML/ JQuery snippet of that happening:

<form action="" id="emailform" name="emailform">
          <input type="email" class="form-control" name="email_submit_field" id="email_submit_field" placeholder="type your email here">
          <input id="email_submit_button" class="btn btn-default" role="button" type="submit" value="Submit"></input>
</form>
<script>    
    $("form").submit(function(event){
            event.preventDefault();
            $.post("email.php", $("#emailform").serialize());
    });
</script>

From there, it is supposed to open up email.php. God knows if it is even doing that right, let me know please. But anyways, once it gets into email.php, it aims to post that email value to a mySQL database running on localhost. Here's the PHP I have to accomplish that:

<?php

$dbname = "test";
$host = "localhost";
$usr = "root";
$pass = "password"; //obviously not, but you get the point

$email = $_POST["email_submit_field"];

$cxn = new mysqli($host, $usr, $pass, $dbname);

$query = "INSERT INTO emails VALUES('$email')";
mysqli_query($query);
mysqli_close();
?>

The big problem that I have with all of this is that it seems like every single tutorial does this in a different way, none of which work for me. Long story short, the mySQL database never receives the data, and I have no idea why. Any and all assistance is appreciated, and hopefully someone can help me understand how (in a simple and consistent way) to access a mySQL database via PHP. Thank you in advance SO.

score 2 · Accepted Answer · edited May 23 '17 at 11:43

You forgot what column (or table?) your value is going to be inserted.

INSERT INTO emails (column_name) VALUES('$email')

or should it be

INSERT INTO table_name (emails) VALUES ('$email')

Check your syntax. When using mysqli_* like you use mysql_*, you should include your connection variable, which is $cxn in your given example, when executing a query.

mysqli_query($cxn, $query);

And also, use at least *_real_escape_string to sanitize the value and prevent some SQL injections.

$email = mysqli_real_escape_string($cxn, $_POST["email_submit_field"]);

But it would be better if you use mysqli prepared statement. You can refer to this:

if($stmt = $cxn->prepare("INSERT INTO emails (column_name) VALUES (?)")){ /* CHECK IF THE QUERY IS TRUE */
  $stmt->bind_param("s",$_POST["email_submit_field"]); /* BIND THE VARIABLE TO YOUR QUERY */
  $stmt->execute(); /* EXECUTE THE QUERY */
  $stmt->close();
}

And for your script, you wanted to submit the form without refreshing the page, right? Then, you should use AJAX for this case.

<script>    
    $(document).ready(function(){
      $("#email_submit_button").click(function(e){ /* WHEN THE BUTTON IS CLICKED OR THE FORM IS SUBMITTED */
        e.preventDefault(); /* STOP THE PAGE FROM REFRESHING */
        var email = $("#email_submit_field").val(); /* STORE THE VALUE OF THE EMAIL FIELD */
        var dataString = "email="+email; /* STORE THE EMAIL TO A DATA STRING */
        $.ajax({ /* CALL AJAX */
          type: "POST",
          url: "email.php", /* SUBMIT THE DATA TO THIS PAGE */
          data: dataString,
          success: function(data){
            $("#email_submit_field").val(""); /* EMPTY THE EMAIL FIELD */
          }
        });
        return false;
      });
    });
</script>

Wow! This was quite the response. A few hours later, I managed to finally get it to work with a lot of assistance from this. Thanks for helping me out, especially with the AJAX part. — user5047031, Aug 05 '15 at 20:59

Mauker · Answer 2 · 2015-08-05T03:52:55.550

1

You have mixed procedural functions with object orientation. And you also forgot to mark the fields on your query. I don't know how your table looks like, but I'm assuming it has one fields "Field_1".

Try this for OO style:

$cxn = new mysqli($host, $usr, $pass, $dbname);

$query = "INSERT INTO emails (`Field_1`) VALUES('$email')";
$cxn->query($query);
$cxn->close();

Or change it all to procedural

$cxn = mysqli_connect($host, $usr, $pass, $dbname);
if (mysqli_connect_errno($cnx)) {
   trigger_error('Database connection failed: '  . mysqli_connect_error(), E_USER_ERROR);
}
$query = "INSERT INTO emails (`Field_1`) VALUES('$email')";
$result = mysqli_query($cxn, $query)

More info on mysqli here.

edited Aug 05 '15 at 03:52

answered Aug 05 '15 at 03:14

Mauker

11,237
7
58
76

I'm pretty sure binding two column fields with only one value will result to this error `Column count doesn't match value count at row 1` – Logan Wayne Aug 05 '15 at 03:51
No worries. And BTW, I'm not the OP. – Logan Wayne Aug 05 '15 at 03:53
Thanks for the response! I'm still learning PHP and all, so I'm not surprised that I managed to mix up OO and procedural. Your response was very helpful though, thank you. – user5047031 Aug 05 '15 at 20:58

score 0 · Answer 3 · answered Aug 05 '15 at 02:57

0

I think you need to change this code:

mysqli_query($query);
mysqli_close();

to this:

$cxn->query($query);

answered Aug 05 '15 at 02:57

Neil Villareal

627
9
14

score 0 · Answer 4 · answered Aug 05 '15 at 03:14

Found this, hope it helps:

// Check connection
if ($cxn->connect_error) {
    die("Connection failed: " . $cxn->connect_error);
} 

$sql = "INSERT INTO emails VALUES('$email')";

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

$cxn->close();
?>

better explained here: http://www.w3schools.com/php/php_mysql_insert.asp

Connect to mySQL server from PHP and JQuery

4 Answers4

Linked