mysql remove spaces and single quotes from string

[Bicklo]

I like to remove single quotes from a mysql string but no luck so far.

It's javascript but it has to work for phpmyadmin database and I did not know where to place this.





<script type="text/javascript">
$(document).on("click",".item", function(e) {
var filename = $(this).text().toLowerCase() + '.php';

filename = filename.replace(/\s+/g, '').replace(''', ''); /* remove spaces and non-alpha characters from string */


$('#uitkomst').load(filename);
e.preventDefault();
});
</script>

[jscheuer1]

Replace will work without a regular expression, but you cannot use the same delimiter as the character you wish to replace. There could also be other problems, but it might be as simple as that (choosing proper delimiters, double quotes in this case):


filename = filename.replace(/\s+/g, '').replace("'", '');

In any case, that much must be done.

If you want more help, please provide a link to the page on your site that contains the problematic code so we can check it out.

[Bicklo]

Thank you, I did try this but it looks like there is another problem in the database I find words with strange letters like financiëlenotasenprocedures.

In my code I use



mysql_query("set names 'utf8'");


My collation-server=utf8_general_ci

ENGINE = InnoDB

[jscheuer1]

That I don't know about precisely. But I do know that you have to encode everything the same all the way through the process, through generating the data, to its storage and retrieval, and to presentation. Otherwise you will have to convert or strip at certain points, perhaps losing data in the process.

Also, I just realized, I gave you bad advice on the replace, not using a regular expression will only get rid of the first instance of the thing to be replaced, and, since you're already using a regular expression, you should be able to combine them:


filename = filename.replace(/\s+|'/g, '');

If you also want to also get rid of all those odd looking characters, try:


filename = filename.replace(/\s+|[^0-z]/g, '')

which will still also remove ' , and will also remove " - if you want to keep " do:


filename = filename.replace(/\s+|[^0-z"]/g, '')

But a better option might be:



filename = filename.replace(/\W/g, '')

which gets rid of everything that's not a word character or a number.

[Bicklo]

Thank you Sir but no result.

The problem is that when I try to open a php page with



<script type="text/javascript">
$(document).on("click",".item", function(e) {
var filename = $(this).text().toLowerCase() + '.php';

filename = filename.replace(/\s+/g, '').replace(''', ''); /* remove spaces and non-alpha characters from string */


$('#uitkomst').load(filename);
e.preventDefault();
});
</script>


The page opens but no results from the database items with the same name.

[styxlawyer]

That is no surprise as you haven't even bothered to make the basic change recommended in post #2!

[Bicklo]

I did try the codes from post #2, the first one did work but not as I hoped, the other 3 blocked my script that's why I put the first one back.

I still think it has to do with the database itself.

In phpmyadmin this is what I see,

coördinatorenoverleg.php should be coördinatorenoverleg.php

financiëlenotasenprocedures.php should be financiëlenotasenprocedures.php

klantentevredenheidenquête.php should be klantentevredenheidenquête.php

So every word with a non alphanumeric character gives a problem.

[jscheuer1]

What encoding is the page served in? Looks like it's ISO-8859-1 (or some variant). If it were UTF-8, then (for example):

coördinatorenoverleg.php

would usually be:

coördinatorenoverleg.php

What does it look like on the page?

[Bicklo]

I just noticed that only the file names have strange characters on the server.

5995


The encoding is


5996



Thank you and merry christmas.

[jscheuer1]

I mean the encoding on the page, what is it served as?

If you want more help, please put up a live demo of the problem.

[DyDr]

Sorry to jump in here, but what you are showing for code isn't how you would display product information on a web page (you wouldn't have individual .php files and you would use the item id (auto-increment integer) value from a product database table to reference/request the data to display through a single .php file) and you have mentioned database code (btw - phpmyadmin is not a database server it's a php scrip that's used to manage a mysql database) not working but haven't shown any code for this.

There's also two problems with this - mysql_query("set names 'utf8'"); The php mysql_ extension is obsolete and executing a set names ... query doesn't set the character set used for the connection, it just tells the db server what character set it should expect the connection to use. You should switch to use the php PDO extension, where you can set the character set used for the connection when you make the connection.

Mod's Note:
By all means jump in. You probably know more about the mysql/php side of this than I do. It's obviously an encoding issue though. From what I can tell, the text displayed on the page is not matching the filenames. But the OP hasn't even shown us that much for certain. If that's all that it is, however, the page simply needs to be served in UTF-8 - many server emulators still do ISO by default. It could be the page or the server itself (we can't really see the required detail in the images). In any case, just getting everything doing everything in UTF-8 is likely the solution.

[Bicklo]

Hello, sorry for the late reply but the holidays you know.
I am new to php and my English is not that good so to do a lot of reading would take forever, that why I try to learn by making something for a family member.

The problem with some pages that did not open had to do with single quotation mark ' in a query should be '' .

For now everything works and I move on the the next problem.

Thank you all and have a great 2017!!