Stopping PHP script after X amount of SQL rows inserted.

NeXEA

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.

Weedpacket

Sure. You can count. When you get to four hundred, or run out of rows to add, stop.

NeXEA

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.

Servyces

Depending on what your queries should look like, you could consider executing queries inside a for-loop.

NeXEA

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 :]]

Weedpacket

$records_added=0;
$ts = 1;
$tt = 3;
$a = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890!@#$&#37;^&*?";

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++;
}

NeXEA

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.

HalfaBee

You need to stop it on a change of letter.

$records_added=0;
$ts = 1;
$tt = 3;
$a = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890!@#$&#37;^&*?";

while($ts < $tt){
    $Array=permutations($a,$ts);
    for($i=0; $i < count($Array); $i++) {

Query here

        $records_added++;
}     

if($records_added>400) break;


$ts++;
}

NeXEA

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.

HalfaBee

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.

NeXEA

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.

Roger_Ramjet

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

NeXEA

😕
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 25000

and 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.

Roger_Ramjet

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

NeXEA

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.

Roger_Ramjet

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?

NeXEA

It shoot back 5,041 total so I know it worked cause thats 71squared.

Roger_Ramjet

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

NeXEA

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