WPRARrRV

1mysql_query("DELETE FROM users WHERE ???") or die(mysql_error());
2
3randomtest
4randomtest
5randomtest
6nextfile
7baby
8randomtest
9dog
10anothertest
11randomtest
12baby
13nextfile
14dog
15anothertest
16randomtest
17randomtest
18	
19CREATE TABLE  `users` (
20  `id` int(10) unsigned NOT NULL auto_increment,
21  `username` varchar(45) NOT NULL,
22  PRIMARY KEY  (`id`)
23) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
24	
25DELETE users
26  FROM users INNER JOIN
27  (SELECT MIN(id) as id, username FROM users GROUP BY username) AS t
28  ON users.username = t.username AND users.id > t.id
29	
30create table tmp as select distinct name from users;
31drop table users;
32alter table tmp rename users;
33	
34DELETE FROM Users
35WHERE ID NOT IN (
36    SELECT MIN(ID)
37    FROM Users
38    GROUP BY User
39)
40	
41users
42-----------------
43| id | username |
44-----------------
45|  1 | joe      |
46|  2 | bob      |
47|  3 | jane     |
48|  4 | bob      |
49|  5 | bob      |
50|  6 | jane     |
51-----------------
52	
53CREATE TEMPORARY TABLE IF NOT EXISTS users_to_delete (id INTEGER);
54
55INSERT INTO users_to_delete (id)
56    SELECT MIN(u1.id) as id
57    FROM users u1
58    INNER JOIN users u2 ON u1.username = u2.username
59    GROUP BY u1.username;
60
61DELETE FROM users WHERE id NOT IN (SELECT id FROM users_to_delete);
62	
63CREATE TEMPORARY TABLE Tmp (ID int);
64INSERT INTO Tmp SELECT ID FROM USERS GROUP BY User;
65DELETE FROM Users WHERE ID NOT IN (SELECT ID FROM Tmp);
66	
67CREATE TEMPORARY TABLE Keep (ID int, User varchar(45));
68CREATE TEMPORARY TABLE Remove (OldID int, NewID int);
69INSERT INTO Keep SELECT ID, User FROM USERS GROUP BY User;
70INSERT INTO Remove SELECT u1.ID, u2.ID FROM Users u1 INNER JOIN Keep u2 ON u2.User = u1.User WHERE u1.ID NOT IN (SELECT ID FROM Users GROUP BY User);
71	
72UPDATE MYTABLE t INNER JOIN Remove r ON t.UserID = r.OldID
73SET t.UserID = r.NewID;
74	
75DELETE FROM Users WHERE ID NOT IN (SELECT ID FROM Keep);
76	
77DROP TABLE KEEP;
78DROP TABLE REMOVE;
79	
80SELECT * FROM `users` LEFT JOIN (
81        SELECT `name`, COUNT(`name`) AS `count`
82        FROM `users` GROUP BY `name`
83    ) AS `grouped`
84    WHERE `grouped`.`name` = `users`.`name`
85    AND `grouped`.`count`>1
86	
87create table tmp like users;
88insert into tmp select distinct name from users;
89drop table users;
90alter table tmp rename users;
91	
92<?php
93// session and includes, you know the drill.
94session_start();
95include_once('connect/config.php');
96
97// create a temp file with session id and current date
98$datefile =  date("m-j-Y");
99$file = "temp/$setprofile-$datefile.txt";
100
101$f = fopen($file, 'w'); // Open in write mode
102
103// call the user and pass via SQL and write them to $file
104$sql = mysql_query("SELECT * FROM _$setprofile ORDER BY user DESC");
105while($row = mysql_fetch_array($sql))
106{
107$user = $row['user'];
108$pass = $row['pass'];
109
110$accounts = "$user:$pass "; // the white space right here is important, it defines the separator for the dupe check function
111fwrite($f, $accounts);
112
113}
114fclose($f);
115
116
117// **** Dupe Function **** //
118
119// removes duplicate substrings between the seperator
120function uniqueStrs($seperator, $str) {
121// convert string to an array using ' ' as the seperator
122$str_arr = explode($seperator, $str);
123// remove duplicate array values
124$result = array_unique($str_arr);
125// convert array back to string, using ' ' to glue it back
126$unique_str = implode(' ', $result);
127// return the unique string
128return $unique_str;
129}
130
131// **** END Dupe Function **** //
132
133
134// call the list we made earlier, so we can use the function above to remove dupes
135$str = file_get_contents($file);
136// seperator
137$seperator = ' ';
138// use the function to save a unique string
139$new_str = uniqueStrs($seperator, $str);
140
141
142
143// empty the table
144mysql_query("TRUNCATE TABLE _$setprofile") or die(mysql_error());
145
146// prep for SQL by replacing test:test with ('test','test'), etc.
147// this isn't a sufficient way of converting, as  i said, it works for me.
148$patterns = array("/([^s:]+):([^s:]+)/", "/s++(/");
149$replacements = array("('$1', '$2')", ", (");
150
151
152// insert the values into your table, and presto! no more dupes.
153$sql = 'INSERT INTO `_'.$setprofile.'` (`user`, `pass`) VALUES ' . preg_replace($patterns, $replacements, $new_str) . ';';
154$product = mysql_query($sql) or die(mysql_error()); // put $new_str here so it will replace new list with SQL formatting
155
156// if all goes well.... OR wrong? :)
157if($product){ echo "Completed!";
158} else {
159echo "Failed!";
160}
161
162unlink($file); // delete the temp file/list we made earlier
163?>