MySQL connection with a php class

Question

I am trying to create a simple catalog. I have a sqlConnect.php file that contains the following

<?php

$host = 'localhost';
$db = 'books';
$user = 'root';
$pass = 'password';

$con = mysqli_connect($host, $user, $pass, $db);

if ($con) {
  echo 'Successfully connected to database!';
} else{
  die('Did not connect');
}

?>

I then have the actual book.php (index page) that contains the following code:

<?php

  include_once 'sqlConnect.php';

 ?>

<!doctype html>
<html lang="en">

<head>

  <title> Library Catalog </title>

</head>

  <style>

    h1 {
      color: #08298A;
    }

  </style>

  <body>

    <h1> <center> Library Catalog </center> </h1>


<h4> <center> Add a New Book </center> </h4>

<center>
<form method="POST">
    <input type="text" name="title" placeholder="Title" id="title">
    <input type="text" name="author" placeholder="Author" id="author">
    <input type="text" name="genre" placeholder="Genre" id="genre">
    <input type="text" name="quantity" placeholder="Quantity" id="quantity">
  <input type="submit" name="submit" value="Submit"/>
    <!-- <button type="submit" name="submit"> Submit</button> -->

</form>
</center>

<?php
$title = $_POST['title'];
$author = $_POST['author'];
$genre = $_POST['genre'];
$quantity = (int)$_POST['quantity'];
$submit = $_POST['submit'];

if ($submit) {
  $sql = "INSERT INTO catalog (id, title, author, genre, quantity) VALUES (NULL, '$title', '$author', '$genre', '10');";
  mysqli_query($con, $sql);
}

?>

</body>
</html>

When I enter in values on the page and hit submit, nothing happens. I have tested to make sure the query is acceptable. I ran into the issue that "quantity" is actually set to a string not an int like it wants in database so i just hard coded in a 10 for now. I can get the query code to work if I place it in sqlConnect.php but it will not work inside of book.php. Am I not connecting to the database correctly by including the sqlConnect.php class?

Any help would be greatly appreciated!

Add `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` to the top of your script. This will force any `mysqli_` errors to generate an Exception that you can see on the browser and other errors will also be visible on your browser. — RiggsFolly, Jan 20 '18 at 22:33
Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Jan 20 '18 at 22:34
@RiggsFolly I added those statements, still get nothing. Have tried isset as well still does not work either. — glockner, Jan 20 '18 at 22:41
Are you getting any error messages after adding the error display code I suggested — RiggsFolly, Jan 20 '18 at 22:42
Are you checking the database after running this script. Because all this script will do (if it works) is throw you the form again — RiggsFolly, Jan 20 '18 at 22:45
@RiggsFolly Yes, every time I refresh my browser, enter values into text fields, hit submit, then go to database and check and nothing has been added. — glockner, Jan 20 '18 at 22:47
is this a hosted site or local? if local, you doing `http://localhost` or `file:///`? You should be getting undefined indexes right off the bat here. — Funk Forty Niner, Jan 20 '18 at 22:52
plus, if `id` is an AI'd column, your NULL value failed, it should be just `''`. You're not checking for errors on the query. — Funk Forty Niner, Jan 20 '18 at 22:53
put `mysqli_error($con)` in a conditional and make sure you are connected. You're not checking for errors there neither. — Funk Forty Niner, Jan 20 '18 at 22:55
try just `include` instead of `include_once` also. Too many things can go wrong. Check the file paths also. — Funk Forty Niner, Jan 20 '18 at 22:56
Table Definition: create table catalog (id int not null primary key auto_increment, title char(50), author char(50), genre char(50), quantity int(9)); — glockner, Jan 20 '18 at 22:57
it's hard to tell what page/file is what. you say book.php (index page). are the form and php in 2 different files? try and add an `action="whatever_file_that_is.php"`; nothing else I can do for you here except check if this isn't being cached, sorry. — Funk Forty Niner, Jan 20 '18 at 23:03
@FunkFortyNiner No I only have the connection class and the book class (the two classes posted in this thread. The php and form are all in the book.php file. — glockner, Jan 20 '18 at 23:10
@FunkFortyNiner sorry, just the two files sqlConnection and books — glockner, Jan 20 '18 at 23:13

score 1 · Answer 1 · answered Jan 20 '18 at 23:56

I took your code and added some enhancements for my own purposes. I tested this on my own system. If it does not work for you then there is some system issue on your side.

Triple check your database credentials and permissions.

This code is going to write to debug.log.

book.php

<?php
include_once 'Log.php';
include_once 'sqlConnect.php';
?>
<!doctype html>
<html lang="en">

<head>

<title> Library Catalog </title>

</head>

<style>

    h1 {
    color: #08298A;
    }

</style>

<body>

    <h1> <center> Library Catalog </center> </h1>


<h4> <center> Add a New Book </center> </h4>

<center>
<form method="POST">
    <input type="text" name="title" placeholder="Title" id="title">
    <input type="text" name="author" placeholder="Author" id="author">
    <input type="text" name="genre" placeholder="Genre" id="genre">
    <input type="text" name="quantity" placeholder="Quantity" id="quantity">
    <input type="submit" name="submit" value="Submit"/>
    <!-- <button type="submit" name="submit"> Submit</button> -->

</form>
</center>

<?php
\Log\Log::debug('_POST ' . print_r($_POST, true));

$title = $_POST['title'] ?? null;
$author = $_POST['author'] ?? null;
$genre = $_POST['genre'] ?? null;
$quantity = (int) ($_POST['quantity'] ?? 0);
$submit = $_POST['submit'] ?? null;

if ( $submit ) {
    $sql = "INSERT INTO catalog (title, author, genre, quantity) VALUES ('$title', '$author', '$genre', $quantity);";
    \Log\Log::debug($sql);

    if ( ! mysqli_query($con, $sql) ) {
        \Log\Log::debug(mysqli_error ( $con ));  
    }
}

?>

</body>
</html>

Log.php

<?php
namespace Log;
class Log {
    static function debug($msg) {
        $file = 'debug.log';
        file_put_contents($file, strftime('%Y-%m-%d %T ') . $msg . "\n", FILE_APPEND);
    }
}

I used this code and still nothing is happening. Nothing is being output to Log.php when I run this code either. I am running on Ubuntu 16.04 with apache 2. I am using firefox quantum as my browser. My credentials are correct because if I run that query in my sqlConnect.php file and run `php sqlConnect.php` the query adds to the database with no problem. Not sure why my books.php file is having such a hard time with this. — glockner, Jan 21 '18 at 01:35

James Ezeh · Answer 2 · 2018-01-21T08:37:15.073

0

Try this

If (isset($_POST('submit')){

$title = mysqli_real_escape_string($con,$_POST('title'));

$author = mysqli_real_escape_string($con, $_POST('author'));

$genre = mysqli_real_escape_string($con,$_POST('genre'));

$quantity = mysqli_real_escape_string($con, $_POST('quantity'));

$query = "INSERT INTO catalog (title, author, genre, quantity) VALUES ('$title', '$author', '$genre', '$quantity');

$ret = mysqli_query($con, $query);

If(!$ret){

die( mysqli_error($con));

}

else{

 echo 'query was successful ';

}

else{

     echo 'post is not set);

}

I hope this helps.

edited Jan 21 '18 at 08:37

answered Jan 20 '18 at 23:19

James Ezeh

5
5

Thanks for this, tried with these new changes and still am not able to update database. – glockner Jan 20 '18 at 23:30
Fix your if statement. If (isset($_POST('submit'){ should be If (isset($_POST['submit'])){ – ryantxr Jan 20 '18 at 23:34

score 0 · Answer 3 · answered Jan 21 '18 at 00:05

Change this code:

<?php
$title = $_POST['title'];
$author = $_POST['author'];
$genre = $_POST['genre'];
$quantity = (int)$_POST['quantity'];
$submit = $_POST['submit'];

if ($submit) {
  $sql = "INSERT INTO catalog (id, title, author, genre, quantity) VALUES 
(NULL, '$title', '$author', '$genre', '10');";
  mysqli_query($con, $sql);
}

?>

to this:

<?php
$title = $author = $genre = $quantity = $submit = '';   

if ($_SERVER["REQUEST_METHOD"] == "POST") {
$title = $_POST["title"];
$author = $_POST["author"];
$genre = $_POST["genre"];   
$quantity =  $_POST["quantity"];

  $sql = "INSERT INTO catalog (id, title, author, genre, quantity) VALUES 
(NULL, '$title', '$author', '$genre', '10');";
  mysqli_query($con, $sql);
}

?>

After that works, you should use run some kind of security function on your input like this:

// handles form input security
function test_input($data) {
  $data = trim($data);
  $data = stripslashes($data);
  $data = htmlspecialchars($data);
  return $data;
}

You can run test_input() on each $_POST[] data to prevent security problems

MySQL connection with a php class

3 Answers3