1

I have an existing table and I'm trying to insert data to it from a form submission.

I think I need another set of eyes to look over my code. There is an issue with it, at this point I'm not sure what.

I fill out the form, it appears to submit, I check the table and I get nothing. No data gets inserted.

If some of the values aren't being used right now, can that hinder the data from being inserted into the table? Shouldn't the form inputs that are on the form be inserted anyway? I tried to use only the values within the form, and that still did not work.

For example, right now the form inputs are only for first, last, title, genre, about, and picture. The rest only exist within the table. Data for the remaining fields cannot be currently entered at this time.

Hopefully someone has a solution?

$servername = "server";
$username = "user";
$password = "pass";
$dbname = "db";

if (isset($_POST['submit'])){
$conn = mysqli_connect($servername,$username,$password,$dbname);
if (!$conn) {
    die("Connection failed: " . mysqli_error());
}


$first      = mysqli_real_escape_string($conn, $_POST['First']);
$last       = mysqli_real_escape_string($conn, $_POST['Last']);
$title      = mysqli_real_escape_string($conn, $_POST['Title']);
$storylink  = mysqli_real_escape_string($conn, $_POST['StoryLink']);
$genre      = mysqli_real_escape_string($conn, $_POST['Genre']);
$about      = mysqli_real_escape_string($conn, $_POST['About']);
$link       = mysqli_real_escape_string($conn, $_POST['Link']);
$picture    = mysqli_real_escape_string($conn, $_POST['Picture']);
$alt        = mysqli_real_escape_string($conn, $_POST['ALT']);

$sql = "INSERT INTO ContrTemp (`First`,`Last`,`Title`,`StoryLink`,`Genre`,`About`,`Link`,`Picture`,`ALT`)
VALUES ('$first','$last','$title','$storylink','$genre','$about','$link','$picture','$alt')";

mysqli_query($conn, $sql) or die('Error: ' . mysqli_error($conn));
mysqli_close($conn);
}

Here is one input field from the form. The others are pretty much the same.

<input type="text" id="ContrTitle" name="Title" placeholder="Title" class="inputFields" style="width:650px;" />

Could there be an issue with the name within the input?

sql table structure:

CREATE TABLE IF NOT EXISTS `ContrTemp` (
  `ID` int(5) NOT NULL AUTO_INCREMENT,
  `First` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `Last` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `Title` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `StoryLink` varchar(140) COLLATE utf8_unicode_ci NOT NULL,
  `Genre` varchar(11) COLLATE utf8_unicode_ci NOT NULL,
  `About` varchar(2000) COLLATE utf8_unicode_ci NOT NULL,
  `Link` varchar(125) COLLATE utf8_unicode_ci NOT NULL,
  `Picture` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `ALT` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

Now I'm actually hitting the database, a record shows up, but no data was inserted. Before, No record was inserted at all.

Marlon
  • 111
  • 2
  • 11
  • 1
    you already connect your database through `musqli_connect`, so no need of `mysqli_select_db($conn,$dbname);` echo your `$sql` and run in the `phpmyadmin` to check what is the problem. – Murad Hasan Apr 27 '16 at 04:21
  • please echo your $sql query and run into phpmyadmin. – Ankit Sompura Apr 27 '16 at 04:37
  • I'm getting this error: 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 '$sql = "INSERT INTO ContrTemp (`First`, `Last`, `Title`, `StoryLink`, `Genre`, `' at line 1 – Marlon Apr 27 '16 at 04:41
  • This has two parts. First try to test your sql with static data. Then debug the form data by `var_dump ($_POST)` somewhere at the beginning of the code. – Navid Apr 27 '16 at 05:23
  • `FIRST` is reserved word in mysql, so add ` around – mitkosoft Apr 27 '16 at 12:34
  • 1
    @mitkosoft FIRST is a keyword. It's not reserved. – Strawberry Apr 27 '16 at 13:55
  • @Marlon Note that all that real_escaping stuff is redundant if you use prepared statements, and you should ALWAYS use prepared statements. – Strawberry Apr 27 '16 at 13:57
  • @NMoeini I received an array(0) { } when I placed var_dump in my code – Marlon Apr 27 '16 at 22:00
  • 1
    So you need to fix the form. Looks like nothing is received by php. – Navid Apr 27 '16 at 22:09
  • Add `method="POST"` to your `
    `. Use a `
     – Ismael Miguel Apr 28 '16 at 00:56

4 Answers4

2

You can try this one for inserting data 

$sql = 'INSERT INTO ContrTemp  (First,Last,Title,StoryLink,Genre,About,Link,Picture,ALT) VALUES ("'.$first.'","'.$last.'","'.$title.'","'.$storylink.'","'.$genre.'","'.$about.'","'.$link.'","'.$picture.'","'.$alt.'")';
ram singh
  • 117
  • 5
2

I think you have done pretty well but one last try put these symbols [backticks] ` around your fields to understand mysql they are just fields.

$sql = "INSERT INTO ContrTemp (`First`,`Last`,`Title`,`StoryLink`,`Genre`,`About`,`Link`,`Picture`,`ALT`)
VALUES ('$first','$last','$title','$storylink','$genre','$about','$link','$picture','$alt')";
Martin
  • 22,212
  • 11
  • 70
  • 132
Sanooj T
  • 1,317
  • 1
  • 14
  • 25
  • I actually had tried this as I have another table that uses back ticks, ticks. Unfortunately this didn't work either. – Marlon Apr 27 '16 at 12:33
1

Your mysqli_error() is incorrect syntax, you need to specify the connection so have mysqli_error($conn) to properly feedback to you the SQL errors. As mentioned by Mitkosoft in comments some of your column names are MySQL keywords. It could be a good habit to get into to always encase column names in backticks.

So:

$sql = "INSERT INTO ContrTemp (`First`,`Last`,`Title`,`StoryLink`,`Genre`,`About`,`Link`,`Picture`,`ALT`)
VALUES ('$first','$last','$title','$storylink','$genre','$about','$link','$picture','$alt')";

mysqli_query($conn, $sql) or die('Error: ' . mysqli_error($conn));

Also you don't need to select db with mysqli_select_db($conn,$dbname); because the database is selected on the login command mysqli_connect above.

Further queries:

  • Is your database access account allowed to INSERT to this database?
  • Can you insert the same data as in your form directly into the database using an interface such as PHPMyAdmin, MySQL Workbench (Or whichever one you use to view the DB)?
Martin
  • 22,212
  • 11
  • 70
  • 132
  • I've updated my code to what I have now, it still doesn't insert data into the table upon submission of the form. – Marlon Apr 27 '16 at 12:55
  • @Marlon but you get no error feedback? Have you also corrected your other [first] mysqli_error() instance? Turn on [PHP error reporting](http://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display). – Martin Apr 27 '16 at 12:57
  • @Marlon I have aded some questions to my answer for you. – Martin Apr 27 '16 at 13:06
  • There are no reserved words here, and besides, they're all escaped anyway. – Strawberry Apr 27 '16 at 13:54
  • @Strawberry Marlon updated his question with my points from my answer, the reserved word is "First" as a column name and they were previously not escaped. Please see the revisions to the question. Cheers – Martin Apr 27 '16 at 14:29
  • @Martin That's my point. FIRST is not a reserved word. Which is why statements like `CREATE TABLE first(first INT NOT NULL);` are OK – Strawberry Apr 27 '16 at 14:33
  • I've inserted data into this same database multiple times, I use PHPMyAdmin as the interface. I can also freely enter data into the table in question. – Marlon Apr 27 '16 at 15:36
0

I had hoped this had been as simple as the code I provided above, thus the reason I did not mention that this code was being used as part of a WordPress template. I did not think that would be come an issue as the template is pretty light weight.

With that said, I simply took my block of php code that handles the data insertion, and I placed it at the top of the template. Worked like a charm..

Marlon
  • 111
  • 2
  • 11