PHP Form Posts to MySQL Database Successfully, But Adds Blank Rows

Question

I have searched, incessantly, for a fix to the problem of a PHP form causing blank rows in a MySQL database (of which my search query was, "form submitting blank rows to mysql database" here on SO). I find that I'm having the problem REGARDLESS of the fact that I constructed a suitable "post/redirect/get" pattern;

PHP Form Page (input.php) ---> 
PHP Form Processing Page (backend.php) ---> 
Database Entry Results Display page (thanks.php - return link to index.php set also)

The problem of the blank row submission escapes me as I've attempted to add validation to the form so that there are no empty submissions, whether the page was reloaded (via back button or otherwise) and do the standard isset() checks.

PHP Form (input.php)

    <form name="input" action="backend.php" method="post">
    <font color="#0000CC" face="arial black">Article/News Title:</font><br>
    <input type="text" name="Title" size="35" />
    <br><br>
    <font color="#0000CC" face="arial black">Article/News URL:</font><br>
    <input type="text" name="Link" value="http://" size="40" />
    <br><br>
    <font color="#0000CC" face="arial black">Article/News description</font> <font size="4" color="#0000CC">(300 Characters Max)</font><br>
    <TABLE style="BORDER-RIGHT: #000000 1px ; BORDER-TOP: #000000 1px ; BORDER-LEFT: #000000 1px ; WIDTH: 100px; BORDER-BOTTOM: #000000 1px ; BORDER-COLLAPSE: separate; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0 alignment="">
   <TBODY>
   <TR>
   <TD style="BORDER-RIGHT: #000000 1px solid; PADDING-RIGHT: 3px; BORDER-TOP: #000000 1px solid; PADDING-LEFT: 3px; PADDING-BOTTOM: 3px; BORDER-LEFT: #000000 1px solid; PADDING-TOP: 3px; BORDER-BOTTOM: #000000 1px solid">
    <textarea id="bob" rows="8" name="Body" cols="60"></textarea>
    <script language="JavaScript">
    generate_wysiwyg("bob");
    </script>
    </TD></TR></TBODY></TABLE>
    <br>
    <font color="#0000CC" face="arial black">Article/News Category?</font><br>
    <SELECT name="category">
    <OPTION>games
    <OPTION>hardware
    <OPTION>humor
    <OPTION>movies
    <OPTION>music
    <OPTION>online
    <OPTION>politics
    <OPTION>programming
    <OPTION>radio
    <OPTION>science
    <OPTION>social
    <OPTION>software
    <OPTION>sports
    <OPTION>technology
    <OPTION>television
    </SELECT>
    <br><br>
    <input type="submit" name="submit" value="Input"></form>

PHP Form Processing (backend.php)

    // Connect to database
   $con=mysql_connect("localhost","db_user","pwd") or die(mysql_error("Cannot Connect To Database"));
   mysql_select_db('db_name', $con) or die(mysql_error("Cannot Select Database"));
   if (isset($_POST['submit'])) {

   // Define query variables
   $ArticleTitle = mysql_real_escape_string(strip_tags($_POST['Title']));
   $ArticleBody = mysql_real_escape_string(stripslashes($_POST['Body']));
   $ArticleLink = mysql_real_escape_string(strip_tags($_POST['Link'],'<a>'));
   $ArticleCategory = mysql_real_escape_string($_POST['category']);

   // Sets the timestamp format
   $Datum = date('D - M. j - g:ia');
   }

    //CHECK FOR EMPTY FIELDS 
    if (empty($_POST["Title"]) or empty($_POST["Body"]) or empty($_POST["Links"]) or empty($_POST["category"])) {

    $error =''; 
    if (trim($_POST['Title'])==''){ 
     $error .= "<li>Please enter a title</li>"; 

    } elseif (trim($_POST['Body'])==''){ 
      $error .= "<li>Please enter some content</li>"; 

    } elseif (trim($_POST['Link'])==''){ 
      $error .= "<li>Please enter a URL</li>"; 

    } elseif (trim($_POST['category'])==''){ 
      $error .= "<li>Please enter a category</li>"; 

    } else {

    //IF NO ERRORS DO SQL QUERY
    $query1=sprintf("INSERT INTO articles (title, link, body, date, category) VALUES ('%s', '%s', '%s', '%s', '%s')", $ArticleTitle, $ArticleLink, $ArticleBody, $Datum, $ArticleCategory);

    // Execute the query or die and echo an error message
    mysql_query($query1) or die("Unable to execute query:" . mysql_error());

    if ($query1) {
    include("thanks.php");
    }
    else {
    header('Location: input.php');
        }
      }
    }
    mysql_close($con);

Entry Result Display Page (thanks.php)

    echo "Your Article/News URL Has Been Posted - Thank You!";
    echo "<br /><br /><b>";
    echo $_POST['Title'];
    echo "</b><br /><br /><b>";
    echo $_POST['Body'];
    echo "</b><br /><br /><b>";
    echo $_POST['Link'];
    echo "</b><br /><br /></b>";
    echo $_POST['Date'];
    echo "</b><br /><br /><b>";
    echo $_POST['category'];
    echo "</b><br /><br />";
    echo "<a href='index.php'><font face='arial black' size='2' color='#0000CD'><u>Return To Articles</u></font></a>";

I have no problem with the end display, and this PRG pattern works insanely well except for the blank row additions which manifest several minutes to hours later. When debugging, I would let 24 hrs go by sometimes, and all was good. But the blank row would multiply (all with stated NULL values when I accessed PHPMyAdmin). I'm convinced that the logic in backend.php is skewed in some weird fashion. I have rewritten that page at least twenty times (four different versions of the page gave me my desired db output). There has got to be something that I'm missing, but after nearly thirty days of straight "search-and-code", I haven't been able to suss it out.

Yes, I know I'm working with deprecation, but I can't begin a complete PDO rewrite on my app until I can get this last issue sorted, and (believe me), it's BEGGING for it (lol!). I think it's relevant to mention also that input.php is protected by a PDO login script preventing random site visitors from posting willy-nilly. The only two problems that I can think that I'm having is that;

1) The visitation of input.php while in a non-logged state is tripping the form submission process (or some searchbot traffic, for that matter),

or

2) Correct coding/wrong placement

All I need is a pointer to how I could solve this. I appreciate any and every reply. Thanks.

EDIT

OK, you know what, I've noticed that the other ancillary pages carrying information to the categories and their sub-headings (i.e - category:Programming / Sub-Headings: new/old/most/least - the php pages under the particular category topic - each one having its' own folder to keep those four pages grouped together), NEVER EVER have displayed the problem that the index.php has just errored out on so I may just have to change how the data comes out of the database by trashing the idea of trying to display the code on the front page. That would actually give me lattitude to do something uber-cool and include in some insanely small app that I've battle-tested and KNOW that works in the trenches, since, even as propellor-headed and code-tweakled I am, I'm still trying to upgrade my ability to make myself a living and actually continue to eat off this enterprise we call "Web Development" (lol!)

I don't see anything in your code that would cause "mystery" inserts. Add columns to the table to track timestamp and IP of visitor. That'll tell you whether it is bot traffic. — Dave
– Dave, Commented Dec 18, 2013 at 21:26
Sure, Ignacio: CREATE TABLE IF NOT EXISTS articles` ( id int(11) NOT NULL AUTO_INCREMENT, title varchar(255) DEFAULT NULL, link varchar(255) DEFAULT NULL, category varchar(30) DEFAULT NULL, clicks int(11) NOT NULL DEFAULT '0', date varchar(50) DEFAULT NULL, body varchar(400) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=340 ;` - did I do that right? Hope so (lol!) — HomeOffice
– HomeOffice, Commented Dec 18, 2013 at 21:28
Dave, I'd like to hold off on any db column additions until I can be sure it is NOT the code. I only mentioned this because I read somewhere it COULD be a factor. Not sure at all - totally appreciate that approach tip and will incorporate at l8r date. — HomeOffice
– HomeOffice, Commented Dec 18, 2013 at 21:33
If I'm not mistaken, I saw that both a Leonardo and a Fred -ii- had replied and I want to apologize if you were both in contention for answering this posted question and somehow got jacked. I think there was some sort of glitch when I refreshed, since I saw you both. Thank you for your replies. — HomeOffice
– HomeOffice, Commented Dec 19, 2013 at 3:33

Giacomo1968 · Accepted Answer · 2013-12-19 05:03:56Z

1

Well, there is advice on your overall database structure, but your data filtering sanitizing—and overall chain of logic connected to the data being inserted—could be improved to truly have content if there only if there is content. So I would change this:

   $ArticleTitle = mysql_real_escape_string(strip_tags($_POST['Title']));
   $ArticleBody = mysql_real_escape_string(stripslashes($_POST['Body']));
   $ArticleLink = mysql_real_escape_string(strip_tags($_POST['Link'],'<a>'));
   $ArticleCategory = mysql_real_escape_string($_POST['category']);

To this:

   // Let’s set all the variables to null to begin with.
   $ArticleTitle = $ArticleBody = $ArticleLink = $ArticleCategory = null;

   // Here is an array of variables from '$_POST' as the key with final variables as a value.
   $post_variables = array('Title' => 'ArticleTitle', 'Body' => 'ArticleBody', 'Link' => 'ArticleLink', 'category' => 'ArticleCategory');

   // Here is an array of items that are links, so they should be parsed differently.
   $link_variables = array('Link');

   // Roll through the '$post_variables'.
   foreach ($post_variables as $post_key => $post_variable) {

     // Check if the keys exist in $_POST.
     if (array_key_exists($post_key, $_POST) && !empty(trim($_POST))) {

       // Check if the key needs special handling in $link_variables.
       if (in_array($post_key, $link_variables)) {
         $$post_variable = mysql_real_escape_string(strip_tags($_POST[$post_variable]),'<a>');
       }
       // If it doesn't need special handling, set it as normal.
       else {
         $$post_variable = mysql_real_escape_string(strip_tags($_POST[$post_variable]));
       }

     }

   }

This new method does a few things. First, it restructures your variable sanitizing logic to be in arrays to avoid repeated code. But more importantly, it makes sure your values are set only if the content is truly there and not a stray empty space submitted in a form. If that happens, the variable comes out as simply null and at that point it can be inserted into your MySQL table as such.

Also the logic for CHECK FOR EMPTY FIELDS should be changed to factor in the sanitizing like so. I mean, we have already checked the $_POST so why do it again? Work with the data that is now known to be clean & valid. Also, I find it best for the if to be a positive outcome and the else to be the fallback. I mean, the goal is not to have errors, the if is basically confirming the positive condition of a data validation chain, right?

// If the fields are not empty, insert it.
if (!empty($ArticleTitle) || !empty($ArticleBody) || !empty($ArticleLink) || !empty($ArticleCategory)) {

  $query1 = sprintf("INSERT INTO articles (title, link, body, date, category) VALUES ('%s', '%s', '%s', '%s', '%s')", $ArticleTitle, $ArticleLink, $ArticleBody, $Datum, $ArticleCategory);

  // Execute the query or die and echo an error message
  mysql_query($query1) or die("Unable to execute query:" . mysql_error());

  if ($query1) {
    include("thanks.php");
  }
  else {
    header('Location: input.php');
  }
  mysql_close($con);
}
else {
  $error = ''; 
  if (empty($ArticleTitle)) { 
   $error .= "<li>Please enter a title</li>"; 
  }
  elseif (empty($ArticleBody)) { 
    $error .= "<li>Please enter some content</li>"; 
  }
  elseif (empty($ArticleLink)) { 
    $error .= "<li>Please enter a URL</li>"; 
  }
  elseif (empty($ArticleCategory)){ 
    $error .= "<li>Please enter a category</li>"; 
  }
}

EDIT Also, adding some overall advice on PHP form processing you have in backend.php. In general, it seems muddled. I mean, all of it works, but it is also causing you problems, right? Okay, so this is the basic flow of what it should be:

Sanitize the data.
If after sanitizing the data is valid by your criteria, do the MySQL work.
If after sanitizing, the data is invalid, generate error messages. 3.

I know this sounds obvious, but when I see your code is making a MySQL connection prior to validating, the only question that comes to mind is: Why? Why open a connection when you have no idea the data is valid? Only set a MySQL connection when you know you need it.

edited Dec 19, 2013 at 5:03

answered Dec 19, 2013 at 4:38

Giacomo1968

25.3k11 gold badges78 silver badges106 bronze badges

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

6 Comments

HomeOffice Over a year ago

Fatal error: Can't use function return value in write context in /home/promodro/public_html/backend.php on line 46 and my submission form refuses to proceed to thanks.php :(

HomeOffice Over a year ago

Line 46: if (array_key_exists($post_variable, $_POST && !empty(trim($_POST))) {

HomeOffice Over a year ago

in regard to your edit message, it's the reason why I rewrote backend.php over 20 times - because I wasn't happy with the logic progression and tried to address this issue by scouring the manual (to no avail, ugh). I do appreciate your sequencing clarifications, though. I'm just going to have to try and jack the code a bit harder :) I'll keep you in the loop once I can get results satisfying the points addressed in your edit.

HomeOffice Over a year ago

thank you for all your help, but - Fatal error: Can't use function return value in write context (8:30PM - MTL time)

HomeOffice Over a year ago

where is $post_key supposed to come from, and what value to $_POST am I attaching? I'm apologizing beforehand for not seeing where you're pointing me and not disputing the validity of your posting. I've acknowledged that I'll need to go deeper with what I'm being presented, thank you.

|

HomeOffice · Accepted Answer · 2014-01-06 07:04:17Z

Two things helped me in the process of finding my answer;

A) The conversion of my PHP processing page to PDO;

    <?php
    if (isset($_POST['submit'])) {
    $ArticleTitle = $ArticleLink = $ArticleBody = $ArticleCategory = $Datum = '';

    // Define a boolean and set to true before validating
    $formValid = true;

    // Data pulled from input form
    $ArticleTitle = $_POST['title'];
    $ArticleLink = $_POST['link'];
    $ArticleBody = $_POST['body'];
    $ArticleCategory = $_POST['category'];

    //Set date parameters
    $Datum = date('D - M. j - g:ia');

    if ($_SERVER['REQUEST_METHOD'] == "POST") {
    if ($_POST['title'] == null OR trim($_POST['title']) ==''|| $_POST['link'] == null OR trim($_POST['link']) =='' || $_POST['body'] == null OR trim($_POST['body']) =='' || $_POST['category'] == null OR trim($_POST['category']) =='') {
        echo '<br><br><div align="center"><a href="input.php"><font color="#0000CC" face="arial black">Please return to form and supply required data!</font></a><br><img src="http://www.promodrone.com/images/myimage.gif"></div>';
        $formValid = false; // Invalid input - set the flag to false
        exit;
        } else {

        if ($formValid){

        //PDO connection string details
        try {
        $dsn = 'mysql:host=localhost;dbname=dbname';
        $username ='user_name';
        $password ='pwd';
        $options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', PDO::ATTR_ERRMODE =>PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES, false); 
        $dbh = new PDO($dsn, $username, $password, $options);
        } catch (Exception $e) {
        echo "WTF?!" . $e->getMessage('Cannot connect: ' . mysql_error());
        }
         //Insert data into db
            try {
            $sth = $dbh->prepare('INSERT INTO articles(title, link, body, category, date) VALUES (?, ?, ?, ?, ?)');
            $sth->execute(array($ArticleTitle, $ArticleLink, $ArticleBody, $ArticleCategory, $Datum));

            //If successful redirect to display database insertion details
            if($sth){
            exit(include('thanks.php'));
            }                 
            else{
            echo '<a href="input.php"><font color="#0000CC" face="arial black">No record added. Return to form, please!</font></a>';
            }
            } catch (Exception $e) {
            echo "Glugh?!" . $e->getMessage('Cannot continue:  <a href="input.php"><font color="#0000CC" face="arial black" size="3">Return to form, please!</font></a>' . mysql_error());
            }
            }
            }
            }
            } 
            $dbh = NULL;
            ?>

and, B) that you have to check SUPER-THOROUGHLY so you don't commit errors caused by overlooking.

On my index.php page, I didn't close a table correctly and, somehow, that kept helping to insert an extra (empty) row in the database. I was only led there this afternoon, after I took a spin at another coding website I have an account with. For some reason, I was compelled to look at the HTML table structure. My forehead is STILL sore from the nuclear facepalm after my realization;

             echo "<p style='font-size:95%'><b>Category -</b><b>";
             echo "</b></p></table></center><br>";

NOT

             echo "<p style='font-size:95%'><b>Category -</b><b>";
             echo "</p></b></font></table></center><br>";

The only thing that I'm NOT happy about is I had to destroy my nice little "P/R/G" pattern, since I couldn't (no matter what I tried), get the vars from the processing page (backend.php) to be echoed from the thanks.php page. The reason for this is that when a user finishes entering the details of their article/news URL, the thanks php is supposed to present them with their input;

             $ArticleTitle    (Article/News title)
             $ArticleLink     (Article/News link)
             $ArticleBody     (Article/News content/image body)
             $ArticleCategory (Article/News category)

stacked exactly in the above fashion. Since earlier this afternoon, I've played the complete paranoiac (lol), because I jumped the gun and celebrated too early the night before. My solution has held for me since this afternoon, and the same error described above in the HTML table had been replicated to all the other category index pages. After changing them all in one fell swoop via my IDE, I tested rigorously by adding ten more entries back to back (about three hours ago), and I haven't even seen the problem pop-up again. :deep sigh: (lol!). Hope this helps someone. I don't know if this is even SLIGHTLY off-topic (and I'm sure that someone will let me know), but the time of MySQL is FINISHED. I understand why many people aren't gung-ho on PHP Data Objects, but after a period of seven days straight on it, it began to "talk" to me. I'M - NEVER - GOING - BACK.....now, to rewrite the rest of the application (lol!).

Collectives™ on Stack Overflow

PHP Form Posts to MySQL Database Successfully, But Adds Blank Rows

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related