Re: [PHP] Problem with inserting numbers...
On 8/11/2011 9:34 AM, Jason Pruim wrote: > > Hey Jim, > > Would that still hold true with inserting into a database which is the true > end of it? This is going to be a one time thing I'm doing and I'm trying to > make it a learning experience as I go since that's what everything should be > right? > Yes, taking one of your other emails, you would do something like this. You should see... INSERT INTO Test (fullnumber) VALUES ('907-200-') VALUES ('907-200-0001') VALUES ('907-200-0001') etc... What this allows you to do is have one long string generated in memory then inserted into the DB. If you have any type of indexes on this table/column then it would only require one re-indexing of the table for the single INSERT statement vs 1 re-indexes for 1 separate INSERT statements. Cuts the DB processing time down a lot. Also, just so you know, if you place set_time_limit(0); at the top of the script, it will allow the script to run as long as it needs to. See: http://php.net/manual/en/function.set-time-limit.php Jim Lucas -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with inserting numbers...
Jason Pruim pru...@gmail.com On Aug 11, 2011, at 11:52 AM, Jim Lucas wrote: > On 8/10/2011 6:22 PM, Jason Pruim wrote: >> So here I am attempting to generate some numbers to be inserted into a >> database... eventually they will make up a phone number (Which I've emailed >> about before and know about the bad ideas with it.. But it's the customer >> :)) >> >> Here is the code I am working with: >> >> > function number_pad($number,$n) { >> return str_pad((int) $number,$n,"0",STR_PAD_LEFT); >> } >> >> $SQL = "SELECT * FROM Test WHERE `areacode` = '907' AND `prefix` = '200' >> LIMIT 5"; >> >> $result = mysql_query($SQL); >> //$num = ""; >> //foreach ($result as $key => $value) { >> //echo $key . "-" . $value . "-" . number_pad($num, "4") . ""; >> //$num++; >> //} >> >> while ($num != "1") { >>while($row = mysql_fetch_assoc($result)) { >>$padnum = number_pad($num, "4"); >>echo $row['areacode'] . "-" . $row['prefix'] . "-" . $padnum . ""; >>$num++; >>} >> >> >> } >> >> ?> >> >> basically all I'm trying to do is generate the last 4 digits starting at >> and going up to . for testing purposes I'm just echoing back but >> will eventually insert the complete number back into the database as a 7 >> digit string. >> >> The error I'm getting is: >> >> Fatal error: Maximum execution time of 30 seconds exceeded in >> /home/pruimpho/public_html/jason/dev/clients/flewid/Phone/config/phoneareacodes.php >> on line25 >> >> which is where the second while starts... >> >> Does anyone know a better way to do this? >> >> I'm off to finish searching google while waiting for some good news :) >> >> Thanks Everyone! >> >> >> Jason Pruim >> pru...@gmail.com >> >> >> > > Jason, > > Here is my rendition of what you should do. > > > $SQL = "SELECT areacode, prefix >FROMTest >WHERE `areacode` = '907' >AND `prefix` = '200'"; > > $result = mysql_query($SQL); > > $values = ''; > > while( $row = mysql_fetch_assoc($result) ) { > foreach ( range(0, ) AS $n ) { >$values .= $row['areacode'] . '-' . $row['prefix'] . '-' . > str_pad($n, 4, '0', STR_PAD_LEFT); > } > } > > echo $values; > > ?> > > It will be much faster if you build the complete string in memory then echo > out > the built string. Hey Jim, Would that still hold true with inserting into a database which is the true end of it? This is going to be a one time thing I'm doing and I'm trying to make it a learning experience as I go since that's what everything should be right? > > Jim Lucas -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with inserting numbers...
Jason Pruim pru...@gmail.com On Aug 11, 2011, at 9:35 AM, Andrew Ballard wrote: > On Wed, Aug 10, 2011 at 9:22 PM, Jason Pruim wrote: >> So here I am attempting to generate some numbers to be inserted into a >> database... eventually they will make up a phone number (Which I've emailed >> about before and know about the bad ideas with it.. But it's the customer :)) >> >> Here is the code I am working with: >> >> > function number_pad($number,$n) { >> return str_pad((int) $number,$n,"0",STR_PAD_LEFT); >> } >> >> $SQL = "SELECT * FROM Test WHERE `areacode` = '907' AND `prefix` = '200' >> LIMIT 5"; >> >> $result = mysql_query($SQL); >> //$num = ""; >> //foreach ($result as $key => $value) { >> //echo $key . "-" . $value . "-" . number_pad($num, "4") . ""; >> //$num++; >> //} >> >> while ($num != "1") { >>while($row = mysql_fetch_assoc($result)) { >>$padnum = number_pad($num, "4"); >>echo $row['areacode'] . "-" . $row['prefix'] . "-" . $padnum . ""; >>$num++; >>} >> >> >> } >> >> ?> >> >> basically all I'm trying to do is generate the last 4 digits starting at >> and going up to . for testing purposes I'm just echoing back but >> will eventually insert the complete number back into the database as a 7 >> digit string. >> >> The error I'm getting is: >> >> Fatal error: Maximum execution time of 30 seconds exceeded in >> /home/pruimpho/public_html/jason/dev/clients/flewid/Phone/config/phoneareacodes.php >> on line25 >> >> which is where the second while starts... >> >> Does anyone know a better way to do this? >> >> I'm off to finish searching google while waiting for some good news :) >> >> Thanks Everyone! >> >> >> Jason Pruim >> pru...@gmail.com > > You could always push it off to MySQL. I don't have a MySQL instance > available to test right now, but I ran this on SQL Server and it > should be generic enough to port without much change. Basically, you > just load a table with a single tinyint column with the digits between > 0 and 9, and then allow the database to cross join that table 4 times > to get 1 resulting rows numbered through . > The cross join on this database server executes in around 44ms. > > > -- Create a temporary table containing the digits 0 through 9 > CREATE TABLE Digits ( >n tinyint NOT NULL PRIMARY KEY >CHECK (n BETWEEN 0 AND 9) > ) > > INSERT INTO Digits > VALUES > (0), > (1), > (2), > (3), > (4), > (5), > (6), > (7), > (8), > (9) > > > > -- Cross join the digits table 4 times to load into a table called numbers. > > SELECT CONVERT(char(1), Thousands.n) + >CONVERT(char(1), Hundreds.n) + >CONVERT(char(1), Tens.n) + >CONVERT(char(1), Ones.n) AS Number > INTONumbers > FROMDigits AS Thousands, >Digits AS Hundreds, >Digits AS Tens, >Digits AS Ones > ORDER BY >Thousands.n, Hundreds.n, Tens.n, Ones.n > > > SELECT * > FROMNumbers > > -- Drop the temporary digits table > DROP TABLE Digits > > > > Andrew Hey Andrew, Interesting idea... I'm so used to doing it all from PHP that I forget a database can be more then just a storage engine sometimes :) I'll definitely give it a try tonight! Thanks Andrew! > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with inserting numbers...
On 8/10/2011 6:22 PM, Jason Pruim wrote: > So here I am attempting to generate some numbers to be inserted into a > database... eventually they will make up a phone number (Which I've emailed > about before and know about the bad ideas with it.. But it's the customer :)) > > Here is the code I am working with: > > function number_pad($number,$n) { > return str_pad((int) $number,$n,"0",STR_PAD_LEFT); > } > > $SQL = "SELECT * FROM Test WHERE `areacode` = '907' AND `prefix` = '200' > LIMIT 5"; > > $result = mysql_query($SQL); > //$num = ""; > //foreach ($result as $key => $value) { > //echo $key . "-" . $value . "-" . number_pad($num, "4") . ""; > //$num++; > //} > > while ($num != "1") { > while($row = mysql_fetch_assoc($result)) { > $padnum = number_pad($num, "4"); > echo $row['areacode'] . "-" . $row['prefix'] . "-" . $padnum . ""; > $num++; > } > > > } > > ?> > > basically all I'm trying to do is generate the last 4 digits starting at > and going up to . for testing purposes I'm just echoing back but will > eventually insert the complete number back into the database as a 7 digit > string. > > The error I'm getting is: > > Fatal error: Maximum execution time of 30 seconds exceeded in > /home/pruimpho/public_html/jason/dev/clients/flewid/Phone/config/phoneareacodes.php > on line25 > > which is where the second while starts... > > Does anyone know a better way to do this? > > I'm off to finish searching google while waiting for some good news :) > > Thanks Everyone! > > > Jason Pruim > pru...@gmail.com > > > Jason, Here is my rendition of what you should do. It will be much faster if you build the complete string in memory then echo out the built string. Jim Lucas -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with inserting numbers...
On Wed, Aug 10, 2011 at 9:22 PM, Jason Pruim wrote: > So here I am attempting to generate some numbers to be inserted into a > database... eventually they will make up a phone number (Which I've emailed > about before and know about the bad ideas with it.. But it's the customer :)) > > Here is the code I am working with: > > function number_pad($number,$n) { > return str_pad((int) $number,$n,"0",STR_PAD_LEFT); > } > > $SQL = "SELECT * FROM Test WHERE `areacode` = '907' AND `prefix` = '200' > LIMIT 5"; > > $result = mysql_query($SQL); > //$num = ""; > //foreach ($result as $key => $value) { > // echo $key . "-" . $value . "-" . number_pad($num, "4") . ""; > // $num++; > //} > > while ($num != "1") { > while($row = mysql_fetch_assoc($result)) { > $padnum = number_pad($num, "4"); > echo $row['areacode'] . "-" . $row['prefix'] . "-" . $padnum . ""; > $num++; > } > > > } > > ?> > > basically all I'm trying to do is generate the last 4 digits starting at > and going up to . for testing purposes I'm just echoing back but will > eventually insert the complete number back into the database as a 7 digit > string. > > The error I'm getting is: > > Fatal error: Maximum execution time of 30 seconds exceeded in > /home/pruimpho/public_html/jason/dev/clients/flewid/Phone/config/phoneareacodes.php > on line25 > > which is where the second while starts... > > Does anyone know a better way to do this? > > I'm off to finish searching google while waiting for some good news :) > > Thanks Everyone! > > > Jason Pruim > pru...@gmail.com You could always push it off to MySQL. I don't have a MySQL instance available to test right now, but I ran this on SQL Server and it should be generic enough to port without much change. Basically, you just load a table with a single tinyint column with the digits between 0 and 9, and then allow the database to cross join that table 4 times to get 1 resulting rows numbered through . The cross join on this database server executes in around 44ms. -- Create a temporary table containing the digits 0 through 9 CREATE TABLE Digits ( n tinyint NOT NULL PRIMARY KEY CHECK (n BETWEEN 0 AND 9) ) INSERT INTO Digits VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) -- Cross join the digits table 4 times to load into a table called numbers. SELECT CONVERT(char(1), Thousands.n) + CONVERT(char(1), Hundreds.n) + CONVERT(char(1), Tens.n) + CONVERT(char(1), Ones.n) AS Number INTONumbers FROMDigits AS Thousands, Digits AS Hundreds, Digits AS Tens, Digits AS Ones ORDER BY Thousands.n, Hundreds.n, Tens.n, Ones.n SELECT * FROMNumbers -- Drop the temporary digits table DROP TABLE Digits Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with inserting numbers...
Jason Pruim wrote: >Replies below > > >Jason Pruim >li...@pruimphotography.com > > > >On Aug 10, 2011, at 11:08 PM, Ken Robinson wrote: > >> At 09:22 PM 8/10/2011, Jason Pruim wrote: >>> So here I am attempting to generate some numbers to be inserted into >a database... eventually they will make up a phone number (Which I've >emailed about before and know about the bad ideas with it.. But it's >the customer :)) >>> >>> Here is the code I am working with: >>> >>> >> function number_pad($number,$n) { >>> return str_pad((int) $number,$n,"0",STR_PAD_LEFT); >>> } >>> >>> $SQL = "SELECT * FROM Test WHERE `areacode` = '907' AND `prefix` = >'200' LIMIT 5"; >>> >>> $result = mysql_query($SQL); >>> >>> while ($num != "1") { >>>while($row = mysql_fetch_assoc($result)) { >>>$padnum = number_pad($num, "4"); >>>echo $row['areacode'] . "-" . $row['prefix'] . "-" . $padnum >. ""; >>>$num++; >>>} >>> >>> >>> } >>> >>> ?> >> >> Try to avoid putting a database query in a loop. > >But that's exactly what I need to do... onceI have the code working >with the echo I need to update the database with the numbers being >displayed... > >$fullnumber = $row['areacode'].$row['prefix'].$padnum; > >$SQL = INSERT INTO Test ('fullnumber'( VALUES($fullnumber); > > >So I want that to be in a loop since it will be inserting roughly >10,000 records for every areacode/prefix combination :) > > >> In the query only ask for the fields you are going to use, it's >faster. Why use your own function to pad a string, when there is a >built-in function? > >Because when I was doing my searching I came across the function I'm >using before I saw the range function :) > >> >> Try this code (untested): >> > $nums = range(0,); >> $q = "SELECT areacode, prefix FROM Test WHERE `areacode` = '907' AND >`prefix` = '200' LIMIT 5"; >> $rs = mysql_query($q); >> while ($row = mysql_fetch_assoc($rs)) { >>foreach ($nums as $n) { >>echo >sprintf('%03d-%03d-%04d',$row['areacode'],$row['prefix'],$n) "\n"; >>} >> } >> ?> >> > >I will try this later today after the day job gets done... > >Thanks for the help! > > > >-- >PHP General Mailing List (http://www.php.net/) >To unsubscribe, visit: http://www.php.net/unsub.php You might need to insert 10,000 rows, but that doesn't mean you need to perform 10,000 separate inserts. Use bulk inserts to ease the load. Also, try to run the script over the cli if you can, it will use less memory (no Apache and its posse) and it won't time out. Thanks, Ash http://www.ashleysheridan.co.uk -- Sent from my Android phone with K-9 Mail. Please excuse my brevity. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with inserting numbers...
Le 11/08/2011 13:08, Tamara Temple a écrit : On Aug 10, 2011, at 8:22 PM, Jason Pruim wrote: while ($num != "1") { Problem is here ^ You are testing a numeric $num with a string "1", which $num will *never* equal. Leave off the quotes on the number. Hum, I suggest you read this page properly : http://www.php.net/manual/en/types.comparisons.php exemple : print ($num != 2 ? "different" : "equal") . PHP_EOL . ($num != "2" ? "different" : "equal") . PHP_EOL; print ($num !== 2 ? "different" : "equal") . PHP_EOL . ($num !== "2" ? "different" : "equal") . PHP_EOL; result : equal equal equal different -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with inserting numbers...
On Aug 10, 2011, at 8:22 PM, Jason Pruim wrote: So here I am attempting to generate some numbers to be inserted into a database... eventually they will make up a phone number (Which I've emailed about before and know about the bad ideas with it.. But it's the customer :)) Here is the code I am working with: $SQL = "SELECT * FROM Test WHERE `areacode` = '907' AND `prefix` = '200' LIMIT 5"; $result = mysql_query($SQL); //$num = ""; //foreach ($result as $key => $value) { //echo $key . "-" . $value . "-" . number_pad($num, "4") . ""; //$num++; //} while ($num != "1") { Problem is here ^ You are testing a numeric $num with a string "1", which $num will *never* equal. Leave off the quotes on the number. while($row = mysql_fetch_assoc($result)) { $padnum = number_pad($num, "4"); echo $row['areacode'] . "-" . $row['prefix'] . "-" . $padnum . ""; $num++; } } ?> basically all I'm trying to do is generate the last 4 digits starting at and going up to . for testing purposes I'm just echoing back but will eventually insert the complete number back into the database as a 7 digit string. The error I'm getting is: Fatal error: Maximum execution time of 30 seconds exceeded in /home/ pruimpho/public_html/jason/dev/clients/flewid/Phone/config/ phoneareacodes.php on line25 which is where the second while starts... Does anyone know a better way to do this? I'm off to finish searching google while waiting for some good news :) Thanks Everyone! Jason Pruim pru...@gmail.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with inserting numbers...
Replies below Jason Pruim li...@pruimphotography.com On Aug 10, 2011, at 11:08 PM, Ken Robinson wrote: > At 09:22 PM 8/10/2011, Jason Pruim wrote: >> So here I am attempting to generate some numbers to be inserted into a >> database... eventually they will make up a phone number (Which I've emailed >> about before and know about the bad ideas with it.. But it's the customer :)) >> >> Here is the code I am working with: >> >> > function number_pad($number,$n) { >> return str_pad((int) $number,$n,"0",STR_PAD_LEFT); >> } >> >> $SQL = "SELECT * FROM Test WHERE `areacode` = '907' AND `prefix` = '200' >> LIMIT 5"; >> >> $result = mysql_query($SQL); >> >> while ($num != "1") { >>while($row = mysql_fetch_assoc($result)) { >>$padnum = number_pad($num, "4"); >>echo $row['areacode'] . "-" . $row['prefix'] . "-" . $padnum . ""; >>$num++; >>} >> >> >> } >> >> ?> > > Try to avoid putting a database query in a loop. But that's exactly what I need to do... onceI have the code working with the echo I need to update the database with the numbers being displayed... $fullnumber = $row['areacode'].$row['prefix'].$padnum; $SQL = INSERT INTO Test ('fullnumber'( VALUES($fullnumber); So I want that to be in a loop since it will be inserting roughly 10,000 records for every areacode/prefix combination :) > In the query only ask for the fields you are going to use, it's faster. Why > use your own function to pad a string, when there is a built-in function? Because when I was doing my searching I came across the function I'm using before I saw the range function :) > > Try this code (untested): > $nums = range(0,); > $q = "SELECT areacode, prefix FROM Test WHERE `areacode` = '907' AND `prefix` > = '200' LIMIT 5"; > $rs = mysql_query($q); > while ($row = mysql_fetch_assoc($rs)) { >foreach ($nums as $n) { >echo > sprintf('%03d-%03d-%04d',$row['areacode'],$row['prefix'],$n) "\n"; >} > } > ?> > I will try this later today after the day job gets done... Thanks for the help! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Problem with inserting numbers...
While no tusing just one while loop? $num = 0; while ( ( $row = mysql_fetch_assoc($result) ) && $num++ <= 1000 ) { //do whatever you want and you'll get all results or max. 1000 lines ( whatever comes first ) } Cheers, Tom -Original Message- From: Tim Streater [mailto:t...@clothears.org.uk] Sent: Thursday, August 11, 2011 11:22 AM To: Jason Pruim Cc: PHP General List Subject: Re: [PHP] Problem with inserting numbers... On 11 Aug 2011 at 02:22, Jason Pruim wrote: > while ($num != "1") { >while($row = mysql_fetch_assoc($result)) { >$padnum = number_pad($num, "4"); >echo $row['areacode'] . "-" . $row['prefix'] . "-" . $padnum . ""; >$num++; >} > > > } This is certain to fail. You've got the $num++ in the *inner* loop, and are checking its value in the *outer* loop. Think about it: suppose you enter the inner loop with $num being 9998. Suppose also that you then go round the inner loop 5 times. What is the value of $num when you then exit the inner loop in order to do the test against 1 in the outer loop? You need to rework that logic. -- Cheers -- Tim -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with inserting numbers...
On 11 Aug 2011 at 02:22, Jason Pruim wrote: > while ($num != "1") { >while($row = mysql_fetch_assoc($result)) { >$padnum = number_pad($num, "4"); >echo $row['areacode'] . "-" . $row['prefix'] . "-" . $padnum . ""; >$num++; >} > > > } This is certain to fail. You've got the $num++ in the *inner* loop, and are checking its value in the *outer* loop. Think about it: suppose you enter the inner loop with $num being 9998. Suppose also that you then go round the inner loop 5 times. What is the value of $num when you then exit the inner loop in order to do the test against 1 in the outer loop? You need to rework that logic. -- Cheers -- Tim -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with inserting numbers...
At 09:22 PM 8/10/2011, Jason Pruim wrote: So here I am attempting to generate some numbers to be inserted into a database... eventually they will make up a phone number (Which I've emailed about before and know about the bad ideas with it.. But it's the customer :)) Here is the code I am working with: $SQL = "SELECT * FROM Test WHERE `areacode` = '907' AND `prefix` = '200' LIMIT 5"; $result = mysql_query($SQL); while ($num != "1") { while($row = mysql_fetch_assoc($result)) { $padnum = number_pad($num, "4"); echo $row['areacode'] . "-" . $row['prefix'] . "-" . $padnum . ""; $num++; } } ?> Try to avoid putting a database query in a loop. In the query only ask for the fields you are going to use, it's faster. Why use your own function to pad a string, when there is a built-in function? Try this code (untested): $q = "SELECT areacode, prefix FROM Test WHERE `areacode` = '907' AND `prefix` = '200' LIMIT 5"; $rs = mysql_query($q); while ($row = mysql_fetch_assoc($rs)) { foreach ($nums as $n) { echo sprintf('%03d-%03d-%04d',$row['areacode'],$row['prefix'],$n) "\n"; } } ?> Ken -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with inserting numbers...
when $num++; executed $num will never be '1'; you may change the while loop to while ($num < 1) { while ($row = ... 2011/8/11 Jason Pruim > On Aug 10, 2011, at 9:36 PM, MUAD SHIBANI wrote: > > > Basically you can increase time limit for this file to handle your > request by using > > set_time_limit function ... > > Hi Maud, > > Looked into set_time_limit and even tried running it with a value of "120" > but it didn't help... > > I'm affraid I have a problem with the way the 2 while loops are interacting > and creating a infinite loop... > > BUT... I'm thinking there must be a better way... Maybe a while with a > foreach? Now I'm just thinking outloud :) > > Jason Pruim > pru...@gmail.com >
Re: [PHP] Problem with inserting numbers...
> > basically all I'm trying to do is generate the last 4 digits starting at > and going up to . for testing purposes I'm just echoing back but will > eventually insert the complete number back into the database as a 7 digit > string. > > The error I'm getting is: > > Fatal error: Maximum execution time of 30 seconds exceeded in > /home/pruimpho/public_html/jason/dev/clients/flewid/Phone/config/phoneareacodes.php > on line25 > I'm not sure the to while loops is the best control structure. Why not try the following and see if it gets you the results you want? __untested__ while($row = mysql_fetch_assoc($result)) { if ($num != "1") && $padnum = number_pad($num, "4") { echo $row['areacode'] . "-" . $row['prefix'] . "-" . $padnum . ""; $num++; } -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with inserting numbers...
On Aug 10, 2011, at 9:36 PM, MUAD SHIBANI wrote: > Basically you can increase time limit for this file to handle your request by > using > set_time_limit function ... Hi Maud, Looked into set_time_limit and even tried running it with a value of "120" but it didn't help... I'm affraid I have a problem with the way the 2 while loops are interacting and creating a infinite loop... BUT... I'm thinking there must be a better way... Maybe a while with a foreach? Now I'm just thinking outloud :) Jason Pruim pru...@gmail.com