Re: Problem with inserting numbers...
| From: | Andrew Ballard | Date: | Thu, 11 Aug 2011 13:35:01 +0000 |
| Subject: | Re: Problem with inserting numbers... | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-314494@lists.php.net to get a copy of this message | ||
On Wed, Aug 10, 2011 at 9:22 PM, Jason Pruim <pruimj@gmail.com> 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:
>
> <?PHP
> 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 = "0000";
> //foreach ($result as $key => $value) {
> // echo $key . "-" . $value . "-" . number_pad($num, "4") .
> "<BR>";
> // $num++;
> //}
>
> while ($num != "10000") {
> while($row = mysql_fetch_assoc($result)) {
> $padnum = number_pad($num, "4");
> echo $row['areacode'] . "-" . $row['prefix'] .
> "-" . $padnum . "<BR>";
> $num++;
> }
>
>
> }
>
> ?>
>
> basically all I'm trying to do is generate the last 4 digits starting at 0000 and going up
> to 9999. 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
> pruimj@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 10000 resulting rows numbered 0000 through 9999.
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
INTO Numbers
FROM Digits AS Thousands,
Digits AS Hundreds,
Digits AS Tens,
Digits AS Ones
ORDER BY
Thousands.n, Hundreds.n, Tens.n, Ones.n
SELECT *
FROM Numbers
-- Drop the temporary digits table
DROP TABLE Digits
Andrew