Why is my item not inserting into a database table?

Question

0.00/5 (No votes)

See more:

Hello thanks for taking your time to read my question! I am still learning php and I am fairly new to it. I feel like I am writing the insert code correct and have all the values and strings write but for some reason I am not able to get it to insert, it always echo's "Insert all values or product hasn't been added:
Thank you again for your response!

Inserting item code below:

HTML

<html>
<head>
  <title>L&B Furniture - New furniture piece Entry</title>
</head>

<body>
  <h1>L&B Furniture - New furniture piece Entry</h1>

  <form action="insertitem.php" method="post">
    <table border="0">
      <tr>
        <td>Product Name</td>
         <td><input type="text" name="Name" maxlength="13" size="13"></td>
      </tr>
      <tr>
        <td>Category</td>
        <td> <input type="text" name="Category" maxlength="30" size="30"></td>
      </tr>
      <tr>
        <td>Price</td>
        <td> <input type="text" name="Price" maxlength="60" size="30"></td>
      </tr>
      <tr>
        <td>Quantity</td>
        <td><input type="text" name="Quantity" maxlength="7" size="7"></td>
      </tr>
      <tr>
        <td>Image</td>
        <td><input type="text" name="Image" maxlength="7" size="7"></td>
      </tr>
      <tr>
        <td colspan="2"><input type="Submit" value="Add Item"></td>
      </tr>
    </table>
  </form>
</body>
</html>

<?php 

$servername = "localhost";
$dbUsername = "db_USER";
$dbPass = "db_PASS";
$dbName = "db_name";

$conn = new mysqli($servername, $dbUsername, $dbPass, $dbName);
mysqli_select_db($conn, 'products');


if(isset($_POST["Submit"])){
  @$Name = $_POST['Name'];
  @$Category = $_POST['Category'];
  @$Price = $_POST['Price'];
  @$Quantity = $_POST['Quantity'];
  @$image = $_POST['Image'];
}

if($Name =="" || $Category =="" || $Price =="" || $Quantity =="" || $image ==""){
  echo "Insert all values into the fields";
}
else{
 $query = "insert into products values ('$Name', '$Category', $Price, 
  $Quantity, '$image')";
  $query_run = mysqli_query($conn, $query);
  if($query_run){
    echo "Product has been entered!";
  }
  else{
    echo "Product has not been inserted";
  }
}
?>

products table sql code below (I have ID set to primary_key and auto_increment)

SQL

CREATE TABLE `products` (
  `ID` int(20) NOT NULL,
  `Name` varchar(128) DEFAULT NULL,
  `Category` varchar(128) DEFAULT NULL,
  `Price` float DEFAULT NULL,
  `Quantity` int(11) DEFAULT NULL,
  `image` varchar(128) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

What I have tried:

I played around with changing all my values in my table to varchars(128) didnt work. I changed the order of which they insert into the database. I put single quotes around all the insert values, even though they weren't strings. I don't know what else it could be

Posted 11-Dec-21 6:45am

Bryan Woodruff

Updated 11-Dec-21 8:15am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Luc Pattyn · Accepted Answer · 2021-12-11T08:15:00

Solution 1

Hi, I see several mistakes and some weak points in your code:

1.
When calling a function one should always check for success; and take appropriate action when something fails, e.g. emitting an error message. Without any error handling, you're flying blind (and possibly creating a big mess).

2.
mysql offers a function mysqli_error() to get more information when something went wrong.
When trying to connect however, one should use mysqli_connect_error().

3.
Your mysqli_select_db($conn, 'products'); selects another database (not another table); read the doc, then drop the statement.

4.
I doubt an HTML input of type Submit will generate a 'Submit' field in $_POST; I tend to specify a name attribute for submits, and then the chosen name does appear in $_POST.

5.
Uninitialized PHP variables don't hold the empty string "" so your quintuple test doesn't catch every bad situation.

6.
It is very bad style using an SQL insert statement that doesn't explicitly name the fields you intend to give a value: what if the field set isn't matching your value list? What if later someone changes the table structure, e.g. prepends a new field?

7.
And finally, someone is bound to tell you string concatenation for SQL queries isn't a very good idea either (Little Bobby Tables - explain xkcd[^]). I won't. For now.

In summary, make your code more defensive, provide intermediate results (especially in error cases), and don't forget to read the documentation on everything you want to use but aren't familiar with.

:)

Posted 11-Dec-21 8:15am

Luc Pattyn

Comments

Bryan Woodruff 11-Dec-21 14:40pm

For setting the submit field to something different I've watched multiple videos on this and it seems they all put : if(isset($_POST["Submit"])). What do you reccomend doing instead of that.

Luc Pattyn 11-Dec-21 15:09pm

Most coding videos are crap, and watching them is a big waste of time.

I just performed a simple test: a submit button without a name doesn't put anything in the $_GET or $_POST array. Add a name attribute (e.g. name='btnInsert'), as I said earlier.

The print_r function (click here!) shows the contents of its first parameter, so you can use

print_r($_POST);

to see everything that gets passed to your page when a POST action occurs. It will show the textbox contents and whatever the submit adds to it.

Bryan Woodruff 11-Dec-21 15:31pm

THank you for the help I got it to work! first off I didn't put the '' around products.... Minor mistake on my part. I also changed $_POST["submit"] to just $_POST. Changing to just $_POST isn't bad practice correct?

Luc Pattyn 11-Dec-21 16:01pm

1.I don't see anything wrong about products needing/not needing quotes, so I have no clue as to what you mean.

2.
Testing whether $_POST exists is a bad hack; I don't see why you wouldn't do it the proper way.

I've told you to give your button a name, and test for that name to appear in the $_POST array. I told you twice. Why don't you take good advice? Did you try print_r?

3.
"It works" does not imply it is any good. Do you want to drive a car that needs service every other day? If you want reliability, then apply good practices, and avoid all hacks.

4.
I bet your database table already contains several empty rows (all five fields empty, ID auto-incrementing). The less strict you make your tests (=your defense), the more garbage you will collect.

PS:
In the mean time, I looked at your earlier question about passwords, and was surprised to see how you had a first, not so good, attempt to defend against SQL injection and somehow found the correct way to handle it. But then, you don't do anything about it today?????

Bryan Woodruff 11-Dec-21 17:24pm

You're 100% Luc I should research more and I guess the past couple videos I have been watching in order to learn weren't the greatest Thank you for your time you we're really helpful. I have been trying to teach myself php and mysql for my final project and since the class I am in for school isn't the greatest. I dont know if you have any recommended resources but in the mean Ill try to find better ones. H

Luc Pattyn 11-Dec-21 19:23pm

You're welcome.

Becoming proficient at software development takes time. If it is all new to you, I recommend a real book for studying a first language. Just read all of it from page 1 for say one week. Read a chapter, then do the given exercises; rinse and repeat. You might skip the chapters you're not interested in (e.g. graphics, web, ...). After a year, read it again.

Microsoft books seem to be fine, e.g. the ones by Petzold on C#.
O'Reilly is good too. I own the second edition (2006) of their "Learning PHP & MySQL" by Davis & Phillips, I think that was my home base back then for web stuff. Yes both PHP and MySQL have evolved since, and the book hasn't AFAIK, but even today I'd rather spend a week studying the book than watching cheap and anonymous Youtube video's.

If your first language (hopefully a strongly typed, well structured one) is sufficiently understood, extra languages can be learned from an e-book in a matter of a few evenings, as it mainly is getting to know new syntax for known concepts.

However, programming is about much more than languages, and that takes practice, experience, hence time. No book recommendations, however you might want to read some excellent articles here on CodeProject; make sure you only pick ones with lots of high votes and/or authors with good reputation score. Look for design aspects, best practices, and look at their code!

MTFBWY