loops, php and mysql data

**Andy Hassall** · Jul 17 '05, 11:53 AM

Re: loops, php and mysql data

On 5 Mar 2005 15:10:38 -0800, stat_holyday@ho tmail.com (Stat) wrote:
[color=blue]
>Greetings all. I am writing a profile creator script where a user gets
>a URL invite in their mail in the form of;
>
>http://domain.com/profile-create.php...d98jadf098asdf
>
>Things are working well except for a small annoyance in which someone
>might have a solution to.
>
>In the event that someone accesses profile-create.php without an
>access_code the script generates a warning. If there is an access_code
>that matches the access code entered in the MySQL database then they
>are directed to the profile creation page.[/color]

OK, so the access_code was generated when the email was sent and stored in the
MySQL database?
[color=blue]
>My problem is;
>
>If someone accesses profile-create.php with an access_code but it
>doesn't match any entries in the database I would like to generate a
>warning.
>
>When I try to do this with an else statement it produces an error for
>every access code listed in the database which isn't the correct one,
>so I could end up with a successful profile creation page with a bunch
>of errors.
>
>I've played with break and pattern matching but no results. Here is
>the script below.
>[color=green]
> >---------------------------------------------------------------<[/color]
>
><?php
>
>$access_code = $_GET['access_code'];[/color]

That'll raise a warning when access_code isn't passed at all; consider either:

$access_code = isset($_GET['access_code']) ? $_GET['access_code'] : '';

or

$access_code = @$_GET['access_code'];
[color=blue]
>if ($access_code) {
>
> echo db_connect();
>
> $result = mysql_query('SE LECT random_link FROM invites');[/color]

I thought you were looking for a specific access_code? It's somewhat defeating
the point of using a database if you fetch the entire table and match in PHP -
use a WHERE clause in the SQL.
[color=blue]
> while ($row = mysql_fetch_arr ay($result, MYSQL_NUM)) {[/color]

Also, if the invites table is empty, you'll never even get here.
[color=blue]
> $random_link_db = $row[0];
>
> if ($random_link_d b == $access_code) {
> echo profile_creator _page();
> }
>
> // Would like to insert an error warning here
>
> }
>}[/color]

You could replace it with something like:

$result = mysql_query(spr intf(
"SELECT count(*) from FROM invites WHERE random_link = '%s'",
mysql_escape_st ring($access_co de)
) or die(mysql_error ());

// This is a single group aggregate query so you're guaranteed
// one row unless the database is broken.
$row = mysql_fetch_arr ay($result, MYSQL_NUM);

if ($row[0] == 1)
{
echo profile_creator _page();
}
else
{
// Warning - got an access code, but it's not in the DB
}

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

**Jerry Stuckle** · Jul 17 '05, 11:53 AM

Re: loops, php and mysql data

Andy Hassall wrote:[color=blue]
>
> You could replace it with something like:
>
> $result = mysql_query(spr intf(
> "SELECT count(*) from FROM invites WHERE random_link = '%s'",
> mysql_escape_st ring($access_co de)
> ) or die(mysql_error ());
>[/color]

Why go to all the overhead of sprintf just to concatenate a string?
This has much less overhead:

$result = mysql_query(
'SELECT count(*) from FROM invites WHERE random_link = \'' .
mysql_escape_st ring($access_co de) . '/'')
or die(mysql_error ());

**stat_holyday@hotmail.com** · Jul 17 '05, 11:53 AM

Re: loops, php and mysql data

Slick! I'd never looked at it that way before. This opens up a whole
new can of proverbial worms. Thank you gentlemen.

NOTE: for those who might have a similar problem in the future, there's
an extraneous 'from' in;

'SELECT count(*) from FROM invites WHERE random_link = ...

it should be

'SELECT count(*) FROM invites WHERE random_link = ...

**NSpam** · Jul 17 '05, 11:53 AM

Re: loops, php and mysql data

stat_holyday@ho tmail.com wrote:[color=blue]
> Slick! I'd never looked at it that way before. This opens up a whole
> new can of proverbial worms. Thank you gentlemen.
>
> NOTE: for those who might have a similar problem in the future, there's
> an extraneous 'from' in;
>
> 'SELECT count(*) from FROM invites WHERE random_link = ...
>
> it should be
>
> 'SELECT count(*) FROM invites WHERE random_link = ...
>[/color]
bear in mind that one is operating in a stateless asynychronous
environment. Don't make any assumptions as to the content of any
variable passed to the script

**Andy Hassall** · Jul 17 '05, 11:53 AM

Re: loops, php and mysql data

On Sat, 05 Mar 2005 20:10:09 -0500, Jerry Stuckle <jstucklex@attg lobal.net>
wrote:
[color=blue]
>Andy Hassall wrote:[color=green]
>>
>> You could replace it with something like:
>>
>> $result = mysql_query(spr intf(
>> "SELECT count(*) from FROM invites WHERE random_link = '%s'",
>> mysql_escape_st ring($access_co de)
>> ) or die(mysql_error ());[/color]
>
>Why go to all the overhead of sprintf just to concatenate a string?
>This has much less overhead:
>
>$result = mysql_query(
> 'SELECT count(*) from FROM invites WHERE random_link = \'' .
> mysql_escape_st ring($access_co de) . '/'')
> or die(mysql_error ());[/color]

Readability mostly, particularly when you end up with more than one variable.
It's sort of a poor-man's placeholder system.

You've also demonstrated another reason in your response; you've got your
quoting wrong.

The overhead of a call to sprintf is negligable particularly compared with
external calls to a database.

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

**Andy Hassall** · Jul 17 '05, 11:53 AM

Re: loops, php and mysql data

On 5 Mar 2005 19:28:39 -0800, stat_holyday@ho tmail.com wrote:
[color=blue]
>Slick! I'd never looked at it that way before. This opens up a whole
>new can of proverbial worms. Thank you gentlemen.
>
>NOTE: for those who might have a similar problem in the future, there's
>an extraneous 'from' in;
>
>'SELECT count(*) from FROM invites WHERE random_link = ...[/color]

Whoops :-)

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

**Michael Fesser** · Jul 17 '05, 11:54 AM

Re: loops, php and mysql data

.oO(Jerry Stuckle)
[color=blue]
>Why go to all the overhead of sprintf just to concatenate a string?[/color]

The code looks better and is easier to maintain, especially if you have
to embed multiple values into the string.
[color=blue]
>This has much less overhead:[/color]

But is ugly and buggy. And BTW: Who cares about the overhead when
creating a DB query string? That's peanuts.

Micha

**Jerry Stuckle** · Jul 17 '05, 11:54 AM

Re: loops, php and mysql data

Michael Fesser wrote:[color=blue]
> .oO(Jerry Stuckle)
>
>[color=green]
>>Why go to all the overhead of sprintf just to concatenate a string?[/color]
>
>
> The code looks better and is easier to maintain, especially if you have
> to embed multiple values into the string.
>[/color]

In your opinion. I find just the opposite.
[color=blue]
>[color=green]
>>This has much less overhead:[/color]
>
>
> But is ugly and buggy. And BTW: Who cares about the overhead when
> creating a DB query string? That's peanuts.[/color]

Depends on the query and the database. Some database calls can be very
quick, for instance, if handled from the cache, and may have very little
overhead. But sprintf has significantly more overhead than simple
concatenation.
[color=blue]
>
> Micha[/color]

**Jerry Stuckle** · Jul 17 '05, 11:54 AM

Re: loops, php and mysql data

Andy Hassall wrote:[color=blue]
> On Sat, 05 Mar 2005 20:10:09 -0500, Jerry Stuckle <jstucklex@attg lobal.net>
> wrote:
>
>[color=green]
>>Andy Hassall wrote:
>>[color=darkred]
>>> You could replace it with something like:
>>>
>>>$result = mysql_query(spr intf(
>>> "SELECT count(*) from FROM invites WHERE random_link = '%s'",
>>> mysql_escape_st ring($access_co de)
>>>) or die(mysql_error ());[/color]
>>
>>Why go to all the overhead of sprintf just to concatenate a string?
>>This has much less overhead:
>>
>>$result = mysql_query(
>> 'SELECT count(*) from FROM invites WHERE random_link = \'' .
>> mysql_escape_st ring($access_co de) . '/'')
>> or die(mysql_error ());[/color]
>
>
> Readability mostly, particularly when you end up with more than one variable.
> It's sort of a poor-man's placeholder system.
>
> You've also demonstrated another reason in your response; you've got your
> quoting wrong.
>
> The overhead of a call to sprintf is negligable particularly compared with
> external calls to a database.
>[/color]

Depends on the actual query to the database. And sprintf has much more
overhead than simple concatenation.

Yes, there's a minor bug - I used a forward slash where I should have
used a backslash. The parser would catch that bug. A similar bug can
happen in sprintf - but the parser might not catch it. And, BTW, you
had an extra "from" in your statement (which I didn't catch, either).

As for readability - to each his own. I find simple concatenation to be
much easier to read than sprintf. Others may find otherwise.

Jerry

**Andy Hassall** · Jul 17 '05, 11:54 AM

Re: loops, php and mysql data

On Sun, 06 Mar 2005 18:24:24 -0500, Jerry Stuckle <jstucklex@attg lobal.net>
wrote:
[color=blue][color=green]
>> The overhead of a call to sprintf is negligable particularly compared with
>> external calls to a database.[/color]
>
>Depends on the actual query to the database. And sprintf has much more
>overhead than simple concatenation.[/color]

What's your definition of "much more"?

andyh@excession /cygdrive/z/public_html/temp
$ cat test.php
<?php
$access_code = md5(uniqid());
$iters = 100000;

$t1 = microtime(true) ;
for ($i=0; $i<$iters; $i++)
{
$sql = 'SELECT count(*) from FROM invites WHERE random_link = \'' .
mysql_escape_st ring($access_co de) . '\'';
}
$t2 = microtime(true) ;

print '.: ' . ($t2-$t1) . "\n";

$t3 = microtime(true) ;
for ($i=0; $i<$iters; $i++)
{
$sql = sprintf(
"SELECT count(*) from FROM invites WHERE random_link = '%s'",
mysql_escape_st ring($access_co de)
);
}
$t4 = microtime(true) ;

print 'sprintf: ' . ($t4-$t3) . "\n";
?>
andyh@excession /cygdrive/z/public_html/temp
$ d\:/php-5.0.3-Win32/php.exe -q test.php
..: 0.474075078964
sprintf: 0.528407096863

So unless I've got my sums wrong, that's 11% slower, on average taking
0.0000005433201 7899 seconds (543 nanoseconds) more than concatenation. This
really isn't the place to be worried about micro-optimisations.

Personally I don't use sprintf for embedding values in queries; I don't embed
values in queries at all, I use ADOdb which emulates placeholders on databases
that don't support them (MySQL), or uses the database's placeholder features
where they are (Oracle). SQL injection is too easy to get wrong if you embed
values in SQL manually every time.

I'm quite happy swapping a few microseconds in exchange for a cleaner and
safer interface.

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

**Michael Fesser** · Jul 17 '05, 11:54 AM

Re: loops, php and mysql data

.oO(Jerry Stuckle)
[color=blue]
>Michael Fesser wrote:
>[color=green]
>> The code looks better and is easier to maintain, especially if you have
>> to embed multiple values into the string.
>>[/color]
>In your opinion. I find just the opposite.[/color]

OK. But sprintf() can do much more than simple string concatenation.
[color=blue][color=green]
>> But is ugly and buggy. And BTW: Who cares about the overhead when
>> creating a DB query string? That's peanuts.[/color]
>
>Depends on the query and the database. Some database calls can be very
>quick, for instance, if handled from the cache, and may have very little
>overhead.[/color]

Sure, but we are talking about the _creation_ of the query string, not
its execution.
[color=blue]
>But sprintf has significantly more overhead than simple
>concatenatio n.[/color]

Usually a query string is created _one_ time, filled with values and
then executed. So who really cares about the sprintf() overhead there?

Micha

**Jerry Stuckle** · Jul 17 '05, 11:55 AM

Re: loops, php and mysql data

Andy Hassall wrote:[color=blue]
>[/color]

<snip>[color=blue]
> So unless I've got my sums wrong, that's 11% slower, on average taking
> 0.0000005433201 7899 seconds (543 nanoseconds) more than concatenation. This
> really isn't the place to be worried about micro-optimisations.
>
> Personally I don't use sprintf for embedding values in queries; I don't embed
> values in queries at all, I use ADOdb which emulates placeholders on databases
> that don't support them (MySQL), or uses the database's placeholder features
> where they are (Oracle). SQL injection is too easy to get wrong if you embed
> values in SQL manually every time.
>
> I'm quite happy swapping a few microseconds in exchange for a cleaner and
> safer interface.
>
> --
> Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
> <http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool[/color]

And you're sure these number are accurate? To be accurate, you would
have to ensure there is no disk access, you're unning on a single-thread
OS (i.e. DOS) and there were no other interrupts. Any of these can
throw your numbers way off.

--

To reply, delete the 'x' from my email
Jerry Stuckle,
JDS Computer Training Corp.
jstucklex@attgl obal.net
Member of Independent Computer Consultants Association - www.icca.org

**Jerry Stuckle** · Jul 17 '05, 11:55 AM

Re: loops, php and mysql data

Michael Fesser wrote:[color=blue]
>
> .oO(Jerry Stuckle)
>[color=green]
> >Michael Fesser wrote:
> >[color=darkred]
> >> The code looks better and is easier to maintain, especially if you have
> >> to embed multiple values into the string.
> >>[/color]
> >In your opinion. I find just the opposite.[/color]
>
> OK. But sprintf() can do much more than simple string concatenation.
>[/color]

No arguments there. But in this case it was only used for
concatenation.
[color=blue][color=green][color=darkred]
> >> But is ugly and buggy. And BTW: Who cares about the overhead when
> >> creating a DB query string? That's peanuts.[/color]
> >
> >Depends on the query and the database. Some database calls can be very
> >quick, for instance, if handled from the cache, and may have very little
> >overhead.[/color]
>
> Sure, but we are talking about the _creation_ of the query string, not
> its execution.
>[/color]

Hey - YOU brought up the database execurion time, not me.
[color=blue][color=green]
> >But sprintf has significantly more overhead than simple
> >concatenatio n.[/color]
>
> Usually a query string is created _one_ time, filled with values and
> then executed. So who really cares about the sprintf() overhead there?
>[/color]

On a heavily loaded web site, me.
[color=blue]
> Micha[/color]

--

To reply, delete the 'x' from my email
Jerry Stuckle,
JDS Computer Training Corp.
jstucklex@attgl obal.net
Member of Independent Computer Consultants Association - www.icca.org

**Andy Hassall** · Jul 17 '05, 11:55 AM

Re: loops, php and mysql data

On Mon, 07 Mar 2005 16:39:32 -0500, Jerry Stuckle <jstucklex@attg lobal.net>
wrote:
[color=blue]
>Andy Hassall wrote:
>
><snip>[color=green]
>> So unless I've got my sums wrong, that's 11% slower, on average taking
>> 0.0000005433201 7899 seconds (543 nanoseconds) more than concatenation. This
>> really isn't the place to be worried about micro-optimisations.
>>
>> Personally I don't use sprintf for embedding values in queries; I don't embed
>> values in queries at all, I use ADOdb which emulates placeholders on databases
>> that don't support them (MySQL), or uses the database's placeholder features
>> where they are (Oracle). SQL injection is too easy to get wrong if you embed
>> values in SQL manually every time.
>>
>> I'm quite happy swapping a few microseconds in exchange for a cleaner and
>> safer interface.[/color]
>
>And you're sure these number are accurate? To be accurate, you would
>have to ensure there is no disk access, you're unning on a single-thread
>OS (i.e. DOS) and there were no other interrupts.[/color]

That's not an environment that PHP runs under so it's debatable whether that's
any more "accurate". The more practical way of increasing confidence in the
figures is to run enough iterations that the impact of random variances is
reduced.
[color=blue]
>Any of these can throw your numbers way off.[/color]

Here's more numbers, from a slightly modified version that prints results all
on one line for easier comparison:

andyh@excession /cygdrive/z/public_html
$ cat temp/test.php
<?php
$access_code = md5(uniqid());
$iters = 1000000;

$t1 = microtime(true) ;
for ($i=0; $i<$iters; $i++)
{
$sql = 'SELECT count(*) from FROM invites WHERE random_link = \'' .
mysql_escape_st ring($access_co de) . '\'';
}
$t2 = microtime(true) ;

print ($t2-$t1) . " ";
print round((($t2-$t1)*pow(10,9))/$iters) . " ";

$t3 = microtime(true) ;
for ($i=0; $i<$iters; $i++)
{
$sql = sprintf(
"SELECT count(*) from FROM invites WHERE random_link = '%s'",
mysql_escape_st ring($access_co de)
);
}
$t4 = microtime(true) ;

print ($t4-$t3) . " ";
print round((($t4-$t3)*pow(10,9))/$iters) . " ";

print (round(($t4-$t3)/($t2-$t1)*100, 2)) - 100;
print "\n";
?>

Results are in the form:

elapsed seconds for 1000000 iterations of concatenation
mean time per concatenation iteration in nanoseconds
elapsed seconds for 1000000 iterations of sprintf
mean time per sprintf iteration in nanoseconds
percentage difference of concatenation vs. sprintf (positive indicates how many
percent sprintf is slower)

andyh@excession /cygdrive/z/public_html
$ for i in `seq 1 20`; do d\:/php-5.0.3-Win32/php.exe -q temp/test.php; done
3.46693181992 3467 4.07078504562 4071 17.42
3.6450240612 3645 4.1216070652 4122 13.07
3.46677517891 3467 4.02187895775 4022 16.01
3.4193508625 3419 4.01254701614 4013 17.35
3.41461086273 3415 3.99897694588 3999 17.11
3.423391819 3423 4.09130811691 4091 19.51
3.50573992729 3506 4.12481713295 4125 17.66
3.41027116776 3410 4.02023100853 4020 17.89
3.48533082008 3485 3.97850012779 3979 14.15
3.37179088593 3372 3.94379496574 3944 16.96
3.37068414688 3371 3.96650886536 3967 17.68
3.39047694206 3390 4.00161004066 4002 18.02
3.4462480545 3446 4.01158809662 4012 16.4
3.36882615089 3369 3.99399495125 3994 18.56
3.38873291016 3389 3.93956923485 3940 16.25
3.4075729847 3408 4.07144784927 4071 19.48
3.39603614807 3396 3.92458295822 3925 15.56
3.72359609604 3724 4.03145503998 4031 8.27
3.39538192749 3395 3.98116493225 3981 17.25
3.39273285866 3393 3.96062397957 3961 16.74

So, over 20,000,000 iterations of each method the results are reasonably
consistent bearing in mind random context switches and interrupts present on a
modern operating system and hardware. Minimum difference is 8.27%, maximum
19.51%, mean is 16.56%. The biggest difference in time per call was 668ns.
Calculation of confidence intervals of this data is left as an exercise for the
reader as it's too late in the evening to be trying to remember statistics
lessons.

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

loops, php and mysql data

loops, php and mysql data

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment