Hello.
I am wondering how you would do this. I was thinking of doing a mysql_num_rows but that seems like it would take up to much processing power.
Could this be done by "counting" the number of queries and then stopping after lets say 400 queries have been finished.
Stopping PHP script after X amount of SQL rows inserted.
Sure. You can count. When you get to four hundred, or run out of rows to add, stop.
Im not sure how I can do it. Would I have to do a mysql_num_rows() after each insertion.
Or is there a better way to do this.
Depending on what your queries should look like, you could consider executing queries inside a for-loop.
Well here is my script
$ts is the starting length and $tt is the ending length. like 1 + 3 would be all letter combinations from a to ??
$ts = 1;
$tt = 3;
$a = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890!@#$%^&*?";
while($ts < $tt){
$Array=permutations($a,$ts);
for($i=0 ; $i < 5 ; $i++) {
mysql_query("INSERT INTO `pass` (
`id` ,
`anValue`
)
VALUES (
NULL , '$Array[$i]')
") or die(mysql_error());
}
$ts++;
}
Somehow it doubles the number when I set it in the for statement. Like how it is set now, it would come out to ten rows instead of five. Is there an error somewhere.
Not that it's to much of a problem, I just like it to be simple :]]
$records_added=0;
$ts = 1;
$tt = 3;
$a = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890!@#$%^&*?";
while($ts < $tt){
$Array=permutations($a,$ts);
for($i=0 ; $i < 5 ; $i++) {
mysql_query("INSERT INTO `pass` (
`id` ,
`anValue`
)
VALUES (
NULL , '$Array[$i]')
") or die(mysql_error());
$records_added++;
if($records_added==400) break 2;
}
$ts++;
}17 days later
There seems to be a problem when they are inserted. I use
$records_added=0;
$ts = 1;
$tt = 3;
$a = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890!@#$%^&*?";
while($ts < $tt){
$Array=permutations($a,$ts);
for($i=0; $i < count($Array); $i++) {
Query here
$records_added++;
if($records_added==400) break 2;
}
$ts++;
}
and it goes up to "eS" then I change the $i to 400 so it starts there.
The first row inserted from there is "fT" so it skips the entire rest of the "e$" section.
Anyone know a way to fix this.
You need to stop it on a change of letter.
$records_added=0;
$ts = 1;
$tt = 3;
$a = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890!@#$%^&*?";
while($ts < $tt){
$Array=permutations($a,$ts);
for($i=0; $i < count($Array); $i++) {
Query here
$records_added++;
}
if($records_added>400) break;
$ts++;
}Ok I feel that I need to say this as I believe it is getting confusing.
What I want to do example.
I want the combinations up to 50000 combinations. I want to do this is two tries instead of one, or multiple tries instead of one. So I would like to be able to do the first 25,000 rows, and then start at the 25,001th combination, and continue on to the 50,000th one.
$records_added=0;
$ts = 1;
$tt = 3;
$a = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890!@#$%^&*?";
while($ts < $tt){
$Array=permutations($a,$ts);
for($i=0; $i < count($Array); $i++) {
Query here
$records_added++;
}
if($records_added>400) break;
$ts++;
} That code still does the same thing except with two extra rows in the end.
It should allow you to start at the next letter boundary.
Even if it only added 2 extra rows, it was 2 extra rows for the last letter.
If you want more rows, change the 400 and $tt.
Well It should allow me to go from eT to eU just by counting up to where it left off but it isn't. I feel like there is something that isn't getting across right with the script.
Im going to add in the functions for "permutations" as maybe there is something in there that is wrong.
function permutations($letters,$num){
$last = str_repeat($letters{0},$num);
$result = array();
while($last != str_repeat(lastchar($letters),$num)){
$result[] = $last;
$last = char_add($letters,$last,$num-1);
}
$result[] = $last;
return $result;
}
function char_add($digits,$string,$char){
if($string{$char} <> lastchar($digits)){
$string{$char} = $digits{strpos($digits,$string{$char})+1};
return $string;
}else{
$string = changeall($string,$digits{0},$char);
return char_add($digits,$string,$char-1);
}
}
function lastchar($string){
return $string{strlen($string)-1};
}
function changeall($string,$char,$start = 0,$end = 0){
if($end == 0) $end = strlen($string)-1;
for($i=$start;$i<=$end;$i++){
$string{$i} = $char;
}
return $string;
}
and the script shown above in included in the file.
Thanks for all the help so far.
Yet again all you lovers of php arrays have forgotten what sql is and does.
the problem:
Take every permutation of "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890!@#$%&*?" and insert n of them into a table.
So, generate the permutations in sql
First make life easy by creating a 1 column table with each char in a row. You can do that with a php loop if you want but personally:
INSERT INTO tbl(col) VALUES('a'),('b'),('c') ....
Now we can use a cartesian join to generate every permutation
SELECT CONCAT(t1.col, t2.col) as perm FROM tbl AS t1, tbl AS t2 ORDER BY perm LIMIT 25000
Then later you can run
SELECT CONCAT(t1.col, t2.col) as perm FROM tbl AS t1, tbl AS t2 ORDER BY perm LIMIT 25000, 25000
Simple, fast and robust.
[edit]
And of course this become an insert query thus
INSERT INTO real_table(col)
SELECT CONCAT(t1.col, t2.col) as perm FROM tbl AS t1, tbl AS t2 ORDER BY perm LIMIT 25000, 25000
Ok a little much as I am as beginner as it gets in SQL. I know the bare basics, but if I read that right, I would have
INSERT INTO perms(anValue) VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'),('k'),('l'),('m'),('n'),('o'),('p'),('q'),('r'),('s'),('t'),('u'),('v'),('w'),('x'),('y'),('z'),('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('0'),('!'),('@'),('#'),('$'),('%'),('^'),('&'),('*'),('?') So I do that and it turns out fine in the table perms
Then I created the second table perms2 and executed this
SELECT CONCAT(t1.anValue, t2.anValue) as perm FROM perms AS t1, perms2 AS t2 ORDER BY perm LIMIT 25000and receive
MySQL returned an empty result set (i.e. zero rows). (Query took 0.0003 sec)
Did I replace something wrong, or is it wrong in the first place.
btw I also need another row for hashes, and i know no way of doing this with sql. Or is there a way of doing it.
Well, you did not need to create 2 tables, self-joining 1 table is enough.
Now, what do you get when you SELECT * FROM perms ? Should just be the list of characters.
a
b
c
etc
If that is OK then try the cartesian join
SELECT * FROM perms AS t1, perms AS t2
Should get every combination in 2 columns.
a, a
a, b
a, c
etc etc
If that is OK then add the CONCAT
alright everything works up until the CONCAT
I do the cartesian join for my table perms and then try the CONCAT as
anValue is in perms and anValue2 is in perms2
SELECT CONCAT(t1.anValue, t2.anValue2) as perm FROM perms AS t1, perms2 AS t2 ORDER BY anValue LIMIT 25000, 25000 Maybe I have this all wrong. I just comes back with an empty result.
Thanks so much for helping me this far.
Well you should be getting a syntax error or some rows. Dunno what is wrong wqhen you don't get either.
Did you run the basic query
SELECT * FROM perms AS t1, perms AS t2
If so what did it return?
It shoot back 5,041 total so I know it worked cause thats 71squared.
OK, so it's the CONCAT that is the problem ??? Dunno, it is a standard mysql function. Test it with the manual example
SELECT CONCAT('My','S','QL')
Should return 'MySQL'. If not then it is your mysql server.
By the way, if you want more permutations you can continue to self-join
SELECT * FROM perms AS t1, perms AS t2, perms AS t3
Will return the sequence
a,a,a
a,a,b
a,a,c
etc
71 cubed = 357911 rows
a month later
Thanks for everything.
I have decided to go a different way than just using SQL. I have used some of the functions here to provide me with a more efficient way of doing what I needed it for.
Thanks for all the help