By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,635 Members | 2,060 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,635 IT Pros & Developers. It's quick & easy.

loops, php and mysql data

P: n/a
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. 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.
---------------------------------------------------------------<
<?php

$access_code = $_GET['access_code'];

if ($access_code) {

echo db_connect();

$result = mysql_query('SELECT random_link FROM invites');

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {

$random_link_db = $row[0];

if ($random_link_db == $access_code) {
echo profile_creator_page();
}

// Would like to insert an error warning here

}
}

else {
echo "Error!"
}
function profile_creator_page() {
echo "All's well!";
}

?>
---------------------------------------------------------------<


Any ideas?

Regards,
Luc
Jul 17 '05 #1
Share this Question
Share on Google+
15 Replies


P: n/a
On 5 Mar 2005 15:10:38 -0800, st**********@hotmail.com (Stat) wrote:
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.
OK, so the access_code was generated when the email was sent and stored in the
MySQL database?
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.
---------------------------------------------------------------<
<?php

$access_code = $_GET['access_code'];


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'];
if ($access_code) {

echo db_connect();

$result = mysql_query('SELECT random_link FROM invites');
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.
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
Also, if the invites table is empty, you'll never even get here.
$random_link_db = $row[0];

if ($random_link_db == $access_code) {
echo profile_creator_page();
}

// Would like to insert an error warning here

}
}


You could replace it with something like:

$result = mysql_query(sprintf(
"SELECT count(*) from FROM invites WHERE random_link = '%s'",
mysql_escape_string($access_code)
) 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_array($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 / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Jul 17 '05 #2

P: n/a


Andy Hassall wrote:

You could replace it with something like:

$result = mysql_query(sprintf(
"SELECT count(*) from FROM invites WHERE random_link = '%s'",
mysql_escape_string($access_code)
) or die(mysql_error());


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_string($access_code) . '/'')
or die(mysql_error());
Jul 17 '05 #3

P: n/a
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 = ...

Jul 17 '05 #4

P: n/a
st**********@hotmail.com wrote:
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 = ...

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
Jul 17 '05 #5

P: n/a
On Sat, 05 Mar 2005 20:10:09 -0500, Jerry Stuckle <js*******@attglobal.net>
wrote:
Andy Hassall wrote:

You could replace it with something like:

$result = mysql_query(sprintf(
"SELECT count(*) from FROM invites WHERE random_link = '%s'",
mysql_escape_string($access_code)
) or die(mysql_error());


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_string($access_code) . '/'')
or die(mysql_error());


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 / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Jul 17 '05 #6

P: n/a
On 5 Mar 2005 19:28:39 -0800, st**********@hotmail.com wrote:
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 = ...


Whoops :-)

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Jul 17 '05 #7

P: n/a
.oO(Jerry Stuckle)
Why go to all the overhead of sprintf just to concatenate a string?
The code looks better and is easier to maintain, especially if you have
to embed multiple values into the string.
This has much less overhead:


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

Micha
Jul 17 '05 #8

P: n/a


Michael Fesser wrote:
.oO(Jerry Stuckle)

Why go to all the overhead of sprintf just to concatenate a string?

The code looks better and is easier to maintain, especially if you have
to embed multiple values into the string.


In your opinion. I find just the opposite.
This has much less overhead:

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


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.

Micha

Jul 17 '05 #9

P: n/a


Andy Hassall wrote:
On Sat, 05 Mar 2005 20:10:09 -0500, Jerry Stuckle <js*******@attglobal.net>
wrote:

Andy Hassall wrote:
You could replace it with something like:

$result = mysql_query(sprintf(
"SELECT count(*) from FROM invites WHERE random_link = '%s'",
mysql_escape_string($access_code)
) or die(mysql_error());


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_string($access_code) . '/'')
or die(mysql_error());

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.


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
Jul 17 '05 #10

P: n/a
On Sun, 06 Mar 2005 18:24:24 -0500, Jerry Stuckle <js*******@attglobal.net>
wrote:
The overhead of a call to sprintf is negligable particularly compared with
external calls to a database.


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


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_string($access_code) . '\'';
}
$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_string($access_code)
);
}
$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.00000054332017899 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 / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Jul 17 '05 #11

P: n/a
.oO(Jerry Stuckle)
Michael Fesser wrote:
The code looks better and is easier to maintain, especially if you have
to embed multiple values into the string.
In your opinion. I find just the opposite.


OK. But sprintf() can do much more than simple string concatenation.
But is ugly and buggy. And BTW: Who cares about the overhead when
creating a DB query string? That's peanuts.


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.


Sure, but we are talking about the _creation_ of the query string, not
its execution.
But sprintf has significantly more overhead than simple
concatenation.


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

Micha
Jul 17 '05 #12

P: n/a
Andy Hassall wrote:

<snip> So unless I've got my sums wrong, that's 11% slower, on average taking
0.00000054332017899 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 / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool


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.
js*******@attglobal.net
Member of Independent Computer Consultants Association - www.icca.org
Jul 17 '05 #13

P: n/a
Michael Fesser wrote:

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


OK. But sprintf() can do much more than simple string concatenation.


No arguments there. But in this case it was only used for
concatenation.
But is ugly and buggy. And BTW: Who cares about the overhead when
creating a DB query string? That's peanuts.


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.


Sure, but we are talking about the _creation_ of the query string, not
its execution.


Hey - YOU brought up the database execurion time, not me.
But sprintf has significantly more overhead than simple
concatenation.


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


On a heavily loaded web site, me.
Micha


--

To reply, delete the 'x' from my email
Jerry Stuckle,
JDS Computer Training Corp.
js*******@attglobal.net
Member of Independent Computer Consultants Association - www.icca.org
Jul 17 '05 #14

P: n/a
On Mon, 07 Mar 2005 16:39:32 -0500, Jerry Stuckle <js*******@attglobal.net>
wrote:
Andy Hassall wrote:

<snip>
So unless I've got my sums wrong, that's 11% slower, on average taking
0.00000054332017899 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.
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.


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.
Any of these can throw your numbers way off.


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_string($access_code) . '\'';
}
$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_string($access_code)
);
}
$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 / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Jul 17 '05 #15

P: n/a
.oO(Jerry Stuckle)
Michael Fesser wrote:

Sure, but we are talking about the _creation_ of the query string, not
its execution.

Hey - YOU brought up the database execurion time, not me.


Yep, because that's what matters. The overhead of sprintf() has nothing
to do with that.

Micha
Jul 17 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.