Post reply

Reply to the message by Auge

Name:

E-mail: (optional, won't be displayed directly)

Leave this field empty:

Homepage: (optional)

Leave this field empty:

Location: (optional)

Remember me (cookie)

Category:

Subject:

Formatting help

skip to input

format text bold: [b]bold text[/b]
format text italic: [i]italic text[/i]
insert hyperlink: [link=http://example.com/]link text[/link] / [link]http://example.com/[/link]
set text color: [color=#rgb]colored text[/color]
font size: [size=small]small text[/size]; [size=large]large text[/size]
insert list: [list][*]list item[/list]
insert image: [img]http://example.com/image.jpg[/img]; left: [img=left]http://example.com/image.jpg[/img]; right: [img=right]http://example.com/image.jpg[/img]; thumbnail: [img=thumbnail]http://example.com/image.jpg[/img]; thumbnail left: [img=thumbnail-left]http://example.com/image.jpg[/img]; thumbnail right: [img=thumbnail-right]http://example.com/image.jpg[/img]
upload image: upload image ...
insert TeX code: [tex]TeX code[/tex]
insert code: [inlinecode]code[/inlinecode]; [code]code[/code]; [code=css]code[/code]; [code=html]code[/code]; [code=javascript]code[/code]; [code=perl]code[/code]; [code=php]code[/code]; [code=sql]code[/code]; [code=xml]code[/code]

:-)
;-)
:-P
:-D
:-|
:-(
:yes:
:no:
:ok:
:lol:
:lol2:
:lol3:
:cool:
:surprised:
:angry:
:crying:
:waving:
:confused:
:lookaround:
:clap:
:love:
:tick:

Message:

> Hello
> 
> > I suggest that the field "user_name" should have a unique index, but if doing so, we might have other forums with duplicate user names.
> > 
> > There is a simple way to see if this is a problem. This SQL query will show any dublicate user names.
> > 
> > [code=sql]SELECT `user_name`, count(`user_name`) FROM `mlf2_userdata` group by `user_name` having count(`user_name`) > 1[/code]
> 
> I build a (until now) non functional prototype of the check for duplicates/non-unique names.
> 
> [code=php]if (empty($update['errors']) && in_array($settings['version'],array('2.3.5', '2.3.6', '2.3.6.1', '2.3.7', '2.3.99.1', '2.3.99.2', '2.3.99.3', '2.4', '2.4.1', '2.4.2'))) {
> 	$resCountNames = mysqli_query($connid, "SELECT `user_name`, COUNT(`user_name`) AS cnt FROM `mlf2_userdata` GROUP BY `user_name` HAVING COUNT(`user_name`) > 1");
> 	if ($resCountNames === false) {
> 		$update['errors'][] = 'Database error in line '.__LINE__.': ' . mysqli_error($connid);
> 	}
> 	if (empty($update['errors'])) {
> 		if (mysqli_num_rows($connid, $resCountNames) > 0) {
> 			# list the non-unique user names
> 			$update['errors'][]  = '<h3><strong>Attention</strong>: Found non-unique user names!</h3>';
> 			$update['errors'][] .= '<p>Please make the names unique and inform the users in question about the changes.</p>';
> 			$update['errors'][] .= '<pre>';
> 			while ($row = mysqli_fetch_assoc($resCountNames)) {
> 				$update['errors'][] .= htmlspecialchars($row['user_name']) .": ". htmlspecialchars($row['cnt']) ."\n";
> 			}
> 			$update['errors'][] .= '</pre>';
> 			mysqli_free_result($result);
> 		} else {
> 			if(!@mysqli_query($connid, "ALTER TABLE `".$db_settings['userdata_table']."` MODIFY `user_name` varchar(255) COLLATE utf8_bin;")) {
> 				$update['errors'][] = 'Database error in line '.__LINE__.': ' . mysqli_error($connid);
> 			}
> 			/* a few queries to add indexex to a few fields in a few tables */
> 			if(!@mysqli_query($connid, "ALTER TABLE `".$db_settings['userdata_table']."` ADD INDEX(`user_type`);")) {
> 				$update['errors'][] = 'Database error in line '.__LINE__.': ' . mysqli_error($connid);
> 			}
> 			if(!@mysqli_query($connid, "ALTER TABLE `".$db_settings['userdata_table']."` ADD INDEX(`user_name`);")) {
> 				$update['errors'][] = 'Database error in line '.__LINE__.': ' . mysqli_error($connid);
> 			}
> 		}
> 	}
> }[/code]
> 
> In the block [inlinecode]if (mysqli_num_rows($connid, $resCountNames) > 0)[/inlinecode], where I want to list the questionable names, we should add an additional info about names, that can be listed as identic but are not. This can be a case like you described it ("Daniel" vs. "Däniel" (wrote it with German "ä")) or similar. Do you or anyone else have a proposal for a few suitable sentences?
> 
> > If an upgrade script is adding a uniqe index to a text field in the database, I strongly recommends that the query above is used before to check if the field is uniqe. It is much better to get an error before the upgrade script does any changes than to get the error in the middle of the upgrade.
> 
> FACK, thatswhy I defined the check as the first step of the upgrade process.
> 
> Tschö, Auge

E-mail notification on reply of this posting