字符串值在MySQL数据库中累积空格

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.

我有一个网页,管理员用户可以在页面上编辑文本。但是当他们将文本插入到mysql数据库中时,它有时会在acual内容之前添加越来越多的空格。如果您将cursur放在页面上的第一个单词之前,并将垃圾邮件退格一段时间,则数据库中的空白会消失。但随着时间的推移,您继续编辑页面越多,就会再次添加越来越多的空白。

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.

over_ons.php - 显示数据库中的编辑按钮和页面内容。

//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:

javascript.js - 将页面内容发送到php文件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:

最后,sent_data.php - 从javascript,js获取页面内容并插入到数据库中:

<?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

额外信息:PHP版本:5.5.15 jQuery版本:1.11.1在浏览器中测试:Chrome数据库:phpMyAdmin 5.5.39内容以VARCHAR类型插入,空格为10000个字符

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).

感谢很多人的大力帮助,特别是@avnishkgaur,它现在运作得很好。这是我调整的内容(也在上面的代码中更改了它,所以现在是工作代码)。

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).

1.删​​除我在

和

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

这是一种意想不到的解决方案,所以我认为这也可以帮助其他人。

4 个解决方案

#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).

至于它为什么添加额外的空格,我认为这是因为你直接将数据库中的文本插入到div中(其中包含html代码中的一些空格,在呈现页面时会将其删除)。

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.

另一种解决方案是在插入db之前修剪文本。您可以在javascript post阶段或在插入mysql db之前执行此操作。

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

在jQuery中,(你正在使用)你可以实现这个:

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.

或者在sent_data.php中,您可以在更新查询中使用TRIM mysql函数。

#2

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.

首先,我强烈建议使用mysqli或PDO而不是PHP中的mysql函数,因为这些函数已被弃用。请在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.

至于你的问题,我没有试图重现这个问题。我建议你记录并检查一步一步发生的事情,例如记录div_inhoud var,这个阶段已包含的空格了吗?等等。

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)

如果你赶时间,你也可以在sent_data.php中的$ tekst var上使用PHP函数ltrim,这将修剪左侧的所有空格(或者你想要从字符串中修剪的任何字符)

#3

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

尝试在send_data.php中添加trim()函数,这将帮助您去除空格。

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

#4

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.

我的猜测是

和?>之后的换行以及over_ons.php中

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

您可以在将空白保存到数据库之前修剪空白,或者在ajax调用之前修剪空白,或两者兼而有之。

In php, trim():

在php中,trim():

sent_data.php

$tekst = trim($tekst);

Or javascript, str.trim():

或者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.

此外,您可能需要考虑使用PHP数据对象,而不是直接将变量插入到SQL语句中。它实际上非常容易使用,在我看来,使代码更容易阅读和更可重用。 Tuts +有一个很棒的教程,可以让学习和入门变得简单。这将确保您不会意外地允许SQL注入问题进入您的应用程序。