[RESOLVED] Removing Illeagal Characters From MySQL

Judgey

Hello Everyone,

I use the following php to make sure people can't use illeagal characters in thier username.

if(!ctype_alnum($username)) {
    header("register.php?terms=accepted&error=usernamenotallowed");
}

However, if users have already added non numerical characters including spaces in thier username how would i remove them via PHP or MySQL command.

This is rather than doing it manually as I have many members on my site and I don't feel like going through them all.

Thank you very much.

Daniel

bradgrafelman

One way would be to use MySQL's Pattern Matching features like so:

SELECT * from myTable WHERE col1 REGEXP '[^a-zA-Z0-9]'

If you don't care about viewing the records, you could change that to a DELETE query instead.

Judgey

Thanks for the reply bud, appreciate it. How would i use that as an update mysql command but keep thier username and only remove the illeagal characters.

e.g

mysql_query("UPDATE users SET username = username(col1 REGEXP '[^a-zA-Z0-9])");

Thanks again 🙂

Daniel

Judgey

Okay i have now marked this thread as resolved and I have also posted my solution which can me modified to suit your needs. With thanks to bradgrafelman.

<?php

// Remove all non alphanumeric characters and spaces from a row in MySQL
// Code by Daniel Judge of www.judgeysoft.me/php
// With thanks to bradgrafelman of www.phpbuilder.com

include("includes/database/connect.php");

$select = mysql_query("SELECT * FROM users WHERE username REGEXP '[^a-zA-Z0-9]'");
while($fetch = mysql_fetch_array($select)) {
	$username = preg_replace("/[^a-zA-Z0-9]/", "", $fetch['username']);
	$oldUsername = $fetch['username'];
	$selectNew = mysql_query("SELECT * FROM users WHERE username = '$username'");
	$check = mysql_num_rows($selectNew);
	if($check == "1") {
		mysql_query("DELETE FROM users WHERE username = '$username'");
	}
	mysql_query("UPDATE users SET username = '$username' WHERE username = '$oldUsername'");
	echo "RENAMING... $check - $username DONE...";
	if($check == "1") {
		echo "DELETED OLD ACCOUNT WITH USERNAME <b>$oldUsername</b> ...";
	}
	echo "<br />";
}

?>

bradgrafelman

Note that as a matter of simplification, you could replace this:

    $selectNew = mysql_query("SELECT * FROM users WHERE username = '$username'"); 
    $check = mysql_num_rows($selectNew); 
    if($check == "1") { 
        mysql_query("DELETE FROM users WHERE username = '$username'"); 
    }

with this:

mysql_query("DELETE FROM users WHERE username = '$username'");
$check = mysql_affected_rows();

In other words, just perform the DELETE query and see if it did anything... no need to do a SELECT first.

Judgey

Cool thank you for you help 🙂