String value accumulating empty spaces in MySQL database

Question

I have a webpage where admin users can edit the text on the page. But when they insert the text into the mysql database, it sometimes adds more and more white spaces before the acual content.
If you place your cursur before the first word on the page and spam backspace for a while, the whitespace in the database dissappears. But over time, the more you keep editing the page, more and more whitespaces are added again.

I did a lot of trouble shooting, but I just can't figure out what causes the whitespaces to be added. It does not always happen making it really difficult to troubleshoot.

Here's my code:

As my code is pretty long, I tried to translate most of the content to english.
If you want to translate something that in't already translated, the original language is Dutch.

over_ons.php - Shows edit button and page content from the database.

//Active page:
$pagina = 'over_ons'; ?>
<input type='hidden' id='pagina' value='<?php echo $pagina; ?>'> <!--Show active page to javascript--><?php
//Active user:
if(isset($_SESSION['correct_ingelogd']) and $_SESSION['functie']=='admin'){
$editor = $_SESSION['gebruikersnaam']; ?>
<input type='hidden' id='editor' value='<?php echo $editor; ?>'> <!--Show active user to javascript--><?php  
} ?>

<!--Editable DIV: -->
<div class='big_wrapper'><?php
        //Get eddited page content from the database
        $query=mysql_query("SELECT inhoud FROM paginas WHERE naam_pagina='" .$pagina. "'");
        while($inhoud_test=mysql_fetch_array($query)){
            $inhoud=$inhoud_test[0];
        }

    //Show Content
    ?><div id='editedText'><?php echo $inhoud; ?></p></div>

<!--Show edit button-->
<?php
if(isset($_SESSION['correct_ingelogd']) and $_SESSION['functie']=='admin')
{?>
    <div id='sidenote'>
        <input type='button' value='Bewerken' id='sent_data' class='button' />
        <div id="feedback" />
    </div>
<?php }

javascript.js - Sents page content to the php file sent_data.php:

//If the system is in edit mode and the user tries to leave the page,
//let the user know it is not so smart to leave yet.
$(window).bind('beforeunload', function(){
var value = $('#sent_data').attr('value'); //change the name of the edit button

if(value == 'Verstuur bewerkingen'){
    return 'Are you sure you want to leave the page? All unsaved edits will be lost!';
}
});

//Make content editable and send page content
$('#sent_data').click(function(){
    var value = $('#sent_data').attr('value'); //change the name of the edit button

    if(value == 'Bewerken'){
        $('#sent_data').attr('value', 'Verstuur bewerkingen');  //change the name of the edit button
        var $div=$('#editedText'), isEditable=$div.is('.editable'); //Make div editable
        $div.prop('contenteditable',!isEditable).toggleClass('editable')
        $('#feedback').html('<p class="opvallend">The content from<BR>this page is now<BR>editable.</p>');
    }else if(value == 'Verstuur bewerkingen'){
                var pagina = $('#pagina').val();
                var editor = $('#editor').val();
                var div_inhoud = $("#editedText").html();
                $.ajax({
                type: 'POST',
                url:  'sent_data.php',
                data: 'tekst=' +div_inhoud+ '&pagina=' +pagina+ '&editor=' +editor,
                success: function(data){
                Change the div back tot not editable, and change the button's name
                    $('#sent_data').attr('value', 'Bewerken');  //change the name of the edit button
                    var $div=$('#editedText'), isEditable=$div.is('.editable'); //Make div not editable
                    $div.prop('contenteditable',!isEditable).toggleClass('editable')

                //Tell the user if the edditing was succesfully
                    $('#feedback').html(data);
                    setTimeout(function(){
                        var value = $('#sent_data').attr('value'); //look up the name of the edit button
                        if(value == 'Bewerken'){ //Only if the button's name is 'bewerken', take away the help text
                        $('#feedback').text('');
                        }
                        }, 5000);
                    }
                    }).fail(function() {
                    //If there was an error, let the user know
                        $('#feedback').html('<p class="opvallend">There was an error.<BR>Your changes have<BR>not been saved.<BR>Please try again.</p>');
                    });
    }
});

And finally,
sent_data.php - Get page content from javascript,js and insert into database:

<?php
session_start();
include_once('./main.php');
include($main .'connectie.php');

//Look up which page has to be edited
$pagina=$_POST['pagina'];
//Get the name of the person who eddited the page
$editor=$_POST['editor'];
//Get content:
$tekst=$_POST['tekst'];
$tekst = mysql_real_escape_string($tekst);
$tekst = trim($tekst);

$query="UPDATE paginas SET naam_editer='" .$editor. "', inhoud='" .$tekst. "' WHERE naam_pagina='" .$pagina. "'";

}
    if(mysql_query($query)){
        echo "<p class='opvallend'>Successfully saves changes.</p>";
    }else{
        echo "<p class='opvallend'>Saving of changes failed.<BR>
        Please try again.</p>";
    }
?>

Extra information:
PHP version: 5.5.15
jQuery version: 1.11.1
Testing in browser: Chrome
Database: phpMyAdmin 5.5.39
The content is inserted in a VARCHAR type with space for 10000 caracters

Thanks in advance for you help!

SOLUTION

Thanks to the great help of a lot of people, and especially @avnishkgaur, it is working perfectly now. Here is what I ajusted (also changed it in my code above, so that's working code now).

1. Removed all the white spaces I had in my code between <div id='editable'> and <?php
2. Added $tekst = trim($tekst); to my PHP file to remove white spaces (didn't work)
3. Placed the editable text into another div as the code for getting the data from the database (was in the same div before)
4. Renamed the ID from the editable div to editedText. Also changed the name in the javascript file. This solution made it work perfectly (it was 'editable' before).

This was kind of an unexpected solution, so I think this could help others too.

Well, the strange thing is, not each time. Most of the times extra white space is added, but not always. I'm almost 100% sure I'm doing the same when it happens and when it doesn't. — Samuël Visser
– Samuël Visser, Commented Oct 7, 2014 at 11:06

Avnish Gaur · Accepted Answer · 2014-10-07 08:33:27Z

1

As to why it is adding extra whitespace, I think it is because you are inserting the text from database into div directly (which contains some white space in html code, which is removed when page is rendered).

One efficient solution would be to insert your content in

tag, probably like this:

<div class='big_wrapper'>
<p id='editable'></p>
</div>

Another solution is to trim the text before inserting into db. You can do that either at javascript post stage, or right before inserting in mysql db.

In jQuery, (which you are using) you can implement this :

data: 'tekst=' +$.trim(div_inhoud)+ '&pagina=' +pagina+ '&editor=' +$.trim(editor),

or in sent_data.php, you can use TRIM mysql function in your update query.

answered Oct 7, 2014 at 8:33

Avnish Gaur

4891 gold badge4 silver badges16 bronze badges

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

11 Comments

Samuël Visser Over a year ago

Thanks for your help. I tried the trim as a suggestion from br3nt, but strangely it doesn't work. Now trying your other solution.

Samuël Visser Over a year ago

Tried to do what you asked, is what i edited in my question what you ment? Because it does strange things right now, it's adding the <p id='editable'>.....</p> to the database every time i save the ajustments. So another paragraph is added every time i save my edits. I think we are colse to the problem now, because thius would explain why white spaces where added every time.

Avnish Gaur Over a year ago

no, in your edit you have placed id="editable" on both div and p.. There should be only one id in one page.. try placing it only on p tag..

Samuël Visser Over a year ago

ow, yes, that's my edit :) I did it right in my local version

Avnish Gaur Over a year ago

just one more thing, in the previous approach (when it was inserting p tag as well in db), can you try using this $("#editable").text() instead of $("#editable").html()

|

Francesco de Guytenaere · Accepted Answer · 2014-10-07 08:18:30Z

0

First of all, I would strongly suggest to use mysqli or PDO instead of the mysql functions in PHP, as these are deprecated. Please look into this on PHP.net.

As for your problem, I have no tried to reproduce the issue. I suggest you log and check what happens step by step, for example logging the div_inhoud var, are the spaces included at this stage already? And so on.

If you are in a hurry, you could also use the PHP function ltrim on the $tekst var in your sent_data.php, which would trim all spaces on the left side (Or any characters you would want to be trimmed from the string)

answered Oct 7, 2014 at 8:18

Francesco de Guytenaere

4,9633 gold badges27 silver badges37 bronze badges

2 Comments

Samuël Visser Over a year ago

Thanks for you awnser! First of all, I did try to use mysqli before, but i'm just used to the old method to much :) As long as mysql is working i will probally keep using it. I'm not making professional websites at all, they are just personal and for my parents :)

Samuël Visser Over a year ago

I got enough time. I am going to do this, will let you know if I found anything.

Community · Accepted Answer · 2023-11-17 19:39:04Z

0

Try to add a trim() function in your send_data.php, this will help you to strip white spaces.

$tekst = trim($_POST['tekst']);

edited Nov 17, 2023 at 19:39

CommunityBot

11 silver badge

answered Oct 7, 2014 at 8:23

Daniel Carvajal

1131 silver badge9 bronze badges

1 Comment

Samuël Visser Over a year ago

thanks for your help. I tried this, but it didn't work.

br3nt · Accepted Answer · 2014-10-07 08:33:01Z

0

My guess is that the newlines after the <div id='editable'> and ?> and the indentation of <?php in over_ons.php is adding the extra whitespace. Specifically, a couple of extra /n's and also spaces from the indentation within the file.

You could trim the whitespace before saving it to your database, or alternatively, before the ajax call, or both.

In php, trim():

sent_data.php

$tekst = trim($tekst);

Or javascript, str.trim():

javascript.js

var div_inhoud = $("#editable").html().trim();

Also, you may want to consider using PHP Data Objects, instead of inserting variables directly into your SQL statements. It's actually really easy to use and in my opinion makes code easier to read and more reusable. There is a fantastic tutorial by Tuts+ that makes it easy to learn and get started. This will ensure you don't accidentally allow SQL injection issues into your application.

edited Oct 7, 2014 at 8:33

answered Oct 7, 2014 at 8:25

br3nt

9,7543 gold badges47 silver badges67 bronze badges

2 Comments

Samuël Visser Over a year ago

Thanks for the info. I did edit my code a bit, also edited my questioin code. I will look into the data objects later, I first want to know how to fix this :) There is no diference in the result I'm getting after making these ajustments. Is there any more I could be doing wrong?

br3nt Over a year ago

Glad you got the problem sorted :)

Collectives™ on Stack Overflow

String value accumulating empty spaces in MySQL database

4 Answers 4

11 Comments

2 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

11 Comments

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related