| Will this work? [message #172244] |
Mon, 07 February 2011 23:03  |
Derek Turner
Messages: 48
Registered: October 2010
Karma: 0
|
Member |
|
|
How long and complicated can a while condition be?
I want to:
define a string variable ($i) starting at "01"
use this in a mysql query
increment the string
dropout of the while when a zero result comes back from mysql
So, would something like:
while ($sql="some multi-lined mysql query containing $i twice" &&
$result=mysql($conn, $sql)){
do some stuff including incrementing $i;
}
presumably the first condition will always return true, will the second
return false if no matches are found? Can a while condition run over
several lines like this? The manual doesn't seem to say.
|
|
|
|
| Re: Will this work? [message #172245 is a reply to message #172244] |
Mon, 07 February 2011 23:28  |
Robert Heller
Messages: 60
Registered: December 2010
Karma: 0
|
Member |
|
|
At 7 Feb 2011 23:03:15 GMT Derek Turner <frderek(at)cesmail(dot)net> wrote:
>
> How long and complicated can a while condition be?
>
> I want to:
>
> define a string variable ($i) starting at "01"
> use this in a mysql query
> increment the string
> dropout of the while when a zero result comes back from mysql
>
> So, would something like:
>
> while ($sql="some multi-lined mysql query containing $i twice" &&
> $result=mysql($conn, $sql)){
>
> do some stuff including incrementing $i;
>
> }
Wouldn't this be a more sensable solution:
function largequery($conn,$i) {
$sql="some multi-lined mysql query containing $i twice";
return mysql($conn, $sql);
}
function increment(&$i) {
/* do a string increment of $i */
}
for ($i="01";$result=largequery($conn,$i);increment($i)) {
do some stuff;
}
Functions exist for a reason.
>
> presumably the first condition will always return true, will the second
> return false if no matches are found? Can a while condition run over
> several lines like this? The manual doesn't seem to say.
Yes, a while condition can run over several lines. Mind your
continuation characters... OTOH, if you have something like that, it
usually makes sense to put such things in functions. Makes the code
much easier to read and maintain. Bottom line: multi-line conditions are
possible, but there is little real need to do that.
>
--
Robert Heller -- 978-544-6933 / heller(at)deepsoft(dot)com
Deepwoods Software -- http://www.deepsoft.com/
() ascii ribbon campaign -- against html e-mail
/\ www.asciiribbon.org -- against proprietary attachments
|
|
|
|
| Re: Will this work? [message #172247 is a reply to message #172244] |
Tue, 08 February 2011 01:27 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 07/02/11 23:03, Derek Turner wrote:
> How long and complicated can a while condition be?
>
> I want to:
>
> define a string variable ($i) starting at "01"
> use this in a mysql query
> increment the string
> dropout of the while when a zero result comes back from mysql
>
> So, would something like:
>
> while ($sql="some multi-lined mysql query containing $i twice" &&
> $result=mysql($conn, $sql)){
>
> do some stuff including incrementing $i;
>
> }
>
> presumably the first condition will always return true, will the second
> return false if no matches are found? Can a while condition run over
> several lines like this? The manual doesn't seem to say.
From the manual:
> For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning
> resultset, mysql_query() returns a resource on success, or FALSE on
> error.
Note that an empty result set is still a result set!
This might be neater, and allows you to use an integer for your loop:
$conn = mysql_connect($dbserver,$dbusername,$dbpassword);
$1 = 1;
$qryfmt = "select blah blah blah %02d blah blah blah %02d blah blah";
while ($result = mysql_query(sprintf($qryfmt,$i,$i),$conn) &&
mysql_num_rows($result) != 0) {
// do stuff with result
$i++;
}
|
|
|
|
| Re: Will this work? [message #172253 is a reply to message #172247] |
Tue, 08 February 2011 13:06 |
Derek Turner
Messages: 48
Registered: October 2010
Karma: 0
|
Member |
|
|
On Tue, 08 Feb 2011 01:27:46 +0000, Denis McMahon wrote:
> Note that an empty result set is still a result set!
>
> This might be neater, and allows you to use an integer for your loop:
<snip>
Thanks but I'm searching for a string like "02-" so $i needs to be a
string.
$sql = "SELECT `workName`, `composer`, SUBSTRING(`Reference`,LOCATE(\'08-
\',`Reference`)) as TRACK FROM `work` WHERE `Reference` LIKE \'%08-%\'
ORDER BY TRACK";
works fine but I want to search on "01-" ... "99-" stopping when I get a
zero result. Thanks for the heads-up on zero-result != false, that, and
your fix are very helpful.
|
|
|
|
| Re: Will this work? [message #172257 is a reply to message #172253] |
Tue, 08 February 2011 13:36 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 2/8/2011 8:06 AM, Derek Turner wrote:
> On Tue, 08 Feb 2011 01:27:46 +0000, Denis McMahon wrote:
>
>> Note that an empty result set is still a result set!
>>
>> This might be neater, and allows you to use an integer for your loop:
>
> <snip>
>
> Thanks but I'm searching for a string like "02-" so $i needs to be a
> string.
>
> $sql = "SELECT `workName`, `composer`, SUBSTRING(`Reference`,LOCATE(\'08-
> \',`Reference`)) as TRACK FROM `work` WHERE `Reference` LIKE \'%08-%\'
> ORDER BY TRACK";
>
> works fine but I want to search on "01-" ... "99-" stopping when I get a
> zero result. Thanks for the heads-up on zero-result != false, that, and
> your fix are very helpful.
>
Which is exactly what Dennis's code does. Did you try it?
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Will this work? [message #172263 is a reply to message #172253] |
Tue, 08 February 2011 15:25 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 08/02/11 13:06, Derek Turner wrote:
> On Tue, 08 Feb 2011 01:27:46 +0000, Denis McMahon wrote:
>
>> Note that an empty result set is still a result set!
>>
>> This might be neater, and allows you to use an integer for your loop:
>
> <snip>
>
> Thanks but I'm searching for a string like "02-" so $i needs to be a
> string.
>
> $sql = "SELECT `workName`, `composer`, SUBSTRING(`Reference`,LOCATE(\'08-
> \',`Reference`)) as TRACK FROM `work` WHERE `Reference` LIKE \'%08-%\'
> ORDER BY TRACK";
>
> works fine but I want to search on "01-" ... "99-" stopping when I get a
> zero result. Thanks for the heads-up on zero-result != false, that, and
> your fix are very helpful.
The sprintf makes the integer value into a 2 digit string.
try this:
<?php
$fmt = "Number '%d' rendered as 2 digit string is '%02d'\n";
for ($i = 0; $i < 100; $i++) echo sprintf($fmt,$i,$i);
?>
Rgds
Denis McMahon
|
|
|
|
| Re: Will this work? [message #172264 is a reply to message #172253] |
Tue, 08 February 2011 15:35 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 08/02/11 13:06, Derek Turner wrote:
> $sql = "SELECT `workName`, `composer`, SUBSTRING(`Reference`,LOCATE(\'08-
> \',`Reference`)) as TRACK FROM `work` WHERE `Reference` LIKE \'%08-%\'
> ORDER BY TRACK";
Here's a variation using a for loop instead of a while one, note again
that the sprintf statement takes the integer value of $i and inserts it
into the string so that for the sql statement it is a string value.
<?php
$fmt = "SELECT `workName`, `composer`, SUBSTRING(`Reference`,
LOCATE(\'%02d-\', `Reference`)) as TRACK FROM `work` WHERE `Reference`
LIKE \'%%%02d-%%\' ORDER BY TRACK";
for ($i = 1; $i < 100; $i++) {
$result = $mysql_query(sprintf($fmt,$i,$i));
if (!$result) {
// print an error message, maybe break
}
if (mysql_num_rows($result) == 0) break; // empty result set, finished
// process $result here
}
?>
Rgds
Denis McMahon
|
|
|
|
| Re: Will this work? [message #172265 is a reply to message #172264] |
Tue, 08 February 2011 16:36 |
Derek Turner
Messages: 48
Registered: October 2010
Karma: 0
|
Member |
|
|
On Tue, 08 Feb 2011 15:35:05 +0000, Denis McMahon wrote:
> <?php
>
> $fmt = "SELECT `workName`, `composer`, SUBSTRING(`Reference`,
> LOCATE(\'%02d-\', `Reference`)) as TRACK FROM `work` WHERE `Reference`
> LIKE \'%%%02d-%%\' ORDER BY TRACK";
>
> for ($i = 1; $i < 100; $i++) {
> $result = $mysql_query(sprintf($fmt,$i,$i)); if (!$result) {
> // print an error message, maybe break
> }
> if (mysql_num_rows($result) == 0) break; // empty result set, finished
> // process $result here
> }
>
> ?>
>
> Rgds
>
> Denis McMahon
Cool! very many thanks, it works. One more question: why the double %% ?
|
|
|
|
| Re: Will this work? [message #172266 is a reply to message #172265] |
Tue, 08 February 2011 17:23 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 08/02/11 16:36, Derek Turner wrote:
> On Tue, 08 Feb 2011 15:35:05 +0000, Denis McMahon wrote:
>
>
>> <?php
>>
>> $fmt = "SELECT `workName`, `composer`, SUBSTRING(`Reference`,
>> LOCATE(\'%02d-\', `Reference`)) as TRACK FROM `work` WHERE `Reference`
>> LIKE \'%%%02d-%%\' ORDER BY TRACK";
>>
>> for ($i = 1; $i < 100; $i++) {
>> $result = $mysql_query(sprintf($fmt,$i,$i)); if (!$result) {
>> // print an error message, maybe break
>> }
>> if (mysql_num_rows($result) == 0) break; // empty result set, finished
>> // process $result here
>> }
>>
>> ?>
> Cool! very many thanks, it works. One more question: why the double %% ?
Because in *printf format strings, % starts a format specifier, so you get:
%02d = integer with leading 0 if only 1 digit
%% = % symbol
and you want a % symbol in the output as a wildcard to LIKE
Thus, the format specifier "%%%02d-%%" when passed the number 3
generates the string "%03-%"
The %% in a *printf is like the \\ in a regex, you have to repeat the
escaping character to include it as a literal.
Rgds
Denis McMahon
|
|
|
|
| Re: Will this work? [SOLVED] [message #172290 is a reply to message #172244] |
Thu, 10 February 2011 17:05 |
Derek Turner
Messages: 48
Registered: October 2010
Karma: 0
|
Member |
|
|
On Mon, 07 Feb 2011 23:03:15 +0000, Derek Turner wrote:
> How long and complicated can a while condition be?
>
> I want to:
>
> define a string variable ($i) starting at "01" use this in a mysql query
> increment the string
> dropout of the while when a zero result comes back from mysql
Many thanks for help received here. Final working code is:
<?php
// first connect to the database table (cantabi1_choir)
include("/home/cantabi1/member_connect.php");
//create a queries in for/next loop
$fmt = "SELECT `workName`, `composer`, SUBSTRING(`Reference`,
LOCATE('%02d-', `Reference`)) as TRACK FROM `work` WHERE `Reference`
LIKE '%%%02d-%%' ORDER BY TRACK";
// Now we look for disk numbers from 01 to 99
for ($i = 1; $i < 100; $i++){
$result = mysql_query(sprintf($fmt,$i,$i)) or die(mysql_error());
if (mysql_num_rows($result) == 0) break; // empty result set,
finished //
//process each $result
....
?>
The sprintf() function made the backslashes (needed in the simple assign
string) redundant (in fact they caused a MySQL error that took me a while
to debug). But it is all working perfectly now and the 'customers'[1] are
delighted.
So thanks again to the group and especially Denis.
1. in fact I'm doing it for the love of it, for my choir.
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]