Page 1 of 1
Problems with a WHILE... loop.
Posted: Sat Oct 17, 2009 3:27 am
by kdidymus
Been a while since I last posted. Hope you're all well.
Here's my problem.
I've written a WHILE.. loop to interrogate my MySQL dB. If a tuple exists, the loop SHOULD fetch the row, format the cell and display the information. Then it goes back and does the same for the next row. And so on, until it runs out of rows.
This is my code:
Code: Select all
$i=2;
while($i<=17)
{
$result = mysql_query($mainquery)
or die ("Couldn't execute query");
$query = "SELECT $fields FROM tree WHERE urn='$spouse1child$i$end'";
$result = mysql_query($query)
or die ("Couldn't execute query");
while($row = mysql_fetch_assoc($result))
{
extract($row);
if ($i<=10)
{$prefix = "0";}
else
{$prefix = "";}
echo "<tr>
<td> </td><td> </td><td ";
if ($bloodline == "AA0000")
{
echo "class='subinfobl'>$prefix$i$colon <a href='$link$urn'>$forename $middlenames $surname ($yearofbirth)</a></td></tr>";
}
elseif ($bloodline != "AA0000")
{
echo "class='subinfo'>$prefix$i$colon <a href='$link$urn'>$forename $middlenames $surname ($yearofbirth)</a></td></tr>";
}
}
$i++;
}
Any ideas why it's not working? I'm not getting any error messages. I'm just not getting anything back from it. The table which SHOULD be filled is empty.
Thanks in advance.
KD.
Re: Problems with a WHILE... loop.
Posted: Sat Oct 17, 2009 5:02 am
by kdidymus
Okay.
So I've tweaked this and despite my best efforts I can't get it to work. Here's a slightly longer version of the section of code...
Code: Select all
<tr>
<td class='heading' $ar>CHILDREN:</td>
<td class='subspc'> </td>
<td ";
if ($spouse1child1urn == "" OR $spouse1child1urn == NULL)
{echo "class='subinfo'>None</td></tr>";}
else
{
$query = "SELECT $fields FROM tree WHERE urn='$spouse1child1urn'";
$result = mysql_query($query)
or die ("Couldn't execute query");
while($row = mysql_fetch_assoc($result))
{
extract($row);
if ($bloodline == "AA0000")
{
echo "class='subinfobl'>01: <a href='$link$urn'>$forename $middlenames $surname ($yearofbirth)</a></td></tr>";
}
elseif ($bloodline != "AA0000")
{echo "class='subinfo'>01: <a href='$link$urn'>$forename $middlenames $surname ($yearofbirth)</a></td></tr>";}
}}
$i=2;
while($i<=16)
{
if ($i<=9)
{$prefix = "0";}
else
{$prefix = "";}
$query = "SELECT $fields FROM tree WHERE urn='$spouse1child$i$end'";
$result = mysql_query($query)
or die ("Couldn't execute query");
while($row = mysql_fetch_assoc($result))
{
extract($row);
echo "<tr>
<td> </td><td> </td><td ";
if ($bloodline == "AA0000")
{
echo "class='subinfobl'>$prefix$i$colon<a href='$link$urn'>$forename $middlenames $surname ($yearofbirth)</a></td></tr>";
}
elseif ($bloodline != "AA0000")
{
echo "class='subinfo'>$prefix$i$colon<a href='$link$urn'>$forename $middlenames $surname ($yearofbirth)</a></td></tr>";
}}
$i++;
}
echo "</table></td>";
I am tearing my hair out here! If you want to see the code in action, go to:
http://www.didymus.org.uk/new_tree/disp ... ?urn=11164
Thanks so much for any help.
KD.
Re: Problems with a WHILE... loop.
Posted: Sat Oct 17, 2009 6:21 am
by kdidymus
Okay. So I figure it's something to do with the $string I'm extracting.
I've set the following variables at the start of my PHP...
Code: Select all
$end = "urn";
$colon = ": ";
$s1c = "$spouse1child";
All well and good. Now when I...
... in the middle of my loop it shows $spouse1child2urn through to $spouse1child16urn
But the query itself still doesn't work.
Once again, the query is:
Code: Select all
$query = "SELECT $fields FROM tree WHERE urn='$s1c$i$end'";
Aaaarrrgh!
Re: Problems with a WHILE... loop.
Posted: Sat Oct 17, 2009 6:35 am
by kdidymus
Code: Select all
$query = "SELECT $fields FROM tree WHERE urn='$spouse1child'+$i+'urn'";
I thought that would work too. But it doesn't. I'm pretty sure now it has something to do with the syntax of the above code. I'm using a variable in the middle of two constants. And I just don't know how to code it.
KD.
Re: Problems with a WHILE... loop.
Posted: Sat Oct 17, 2009 7:56 am
by duncanwilkie
Might be well off the mark, but should you be using . instead of +?
$query = "SELECT $fields FROM tree WHERE urn='$spouse1child'+$i+'urn'";
$query = "SELECT $fields FROM tree WHERE urn='$spouse1child'.$i.'urn'";
Re: Problems with a WHILE... loop.
Posted: Sat Oct 17, 2009 8:22 am
by kdidymus
Code: Select all
$query = "SELECT $fields FROM tree WHERE urn='$spouse1child'.$i.'urn'";
Nope.

This causes the die statement "Couldn't execute query.
Any other ideas?
I need to extract the row where URN = $spouse1child2 in my MySQL. Then $spouse1child3 and so on right up to $spouse1child16!
Re: Problems with a WHILE... loop.
Posted: Sat Oct 17, 2009 8:36 am
by duncanwilkie
kdidymus wrote:Code: Select all
$query = "SELECT $fields FROM tree WHERE urn='$spouse1child'.$i.'urn'";
Nope.

This causes the die statement "Couldn't execute query.
Any other ideas?
I need to extract the row where URN = $spouse1child2 in my MySQL. Then $spouse1child3 and so on right up to $spouse1child16!
Shouldn't this be?:
Code: Select all
$query = "SELECT $fields FROM tree WHERE urn='$spouse1child'.$i";
//Edited since first posted.
with your $i being the incremented counter?
Also try using:
Code: Select all
$sUrn ='$spouse1child1'.$i;
$query = "SELECT $fields FROM tree WHERE urn=$sUrn;
echo $sUrn;
//Edited since first posted.
to echo your urn value - and see if its correct.
Re: Problems with a WHILE... loop.
Posted: Sat Oct 17, 2009 11:26 am
by kdidymus
Okay. My bad.
The rows in my MySQL are called:
$spouse1child1urn, $spouse1child2urn, $spouse1child3urn and so on until we get to $spouse1child16urn
So what I need to do is specify a constant of $spouse1child, add the incremental $i and then suffix it with urn.
No matter how I try to do this, nothing seems to happen. Will keep playing and see if I can stumble across the answer!
KD.
Re: Problems with a WHILE... loop.
Posted: Sat Oct 17, 2009 11:42 am
by kdidymus
How about this for a bit of lateral thinking...
It doesn't work but am I getting
CLOSE to solving this problem?
Code: Select all
$end = "urn";
$start = "$";
$sc1 = "spouse1child";$childurn = "$start$sc1$i$end";
$i=2;
while($i<=16)
{
if ($i<=9)
{$prefix = "0";}
elseif ($i>=10)
{$prefix = "";}
$childurn = "$start$sc1$i$end";
$result = mysql_query($mainquery)
or die ("Couldn't execute main query");
$query = "SELECT $fields FROM tree WHERE urn='$childurn'";
So I've specified a string called
$childurn which changes with each loop.
SHOULD this work or am I overlooking something simple?
Re: Problems with a WHILE... loop.
Posted: Sun Oct 18, 2009 3:28 am
by kdidymus
Er. I'm getting myself confused now.
No. The row in the table is named
spouse1child1urn. My website is a family tree site. Each individual is assigned a Unique Reference Number (urn). So in the case of the URL I gave before, the PHP is using SELECT * on the row with my URN to extract the whole row. I then display each field in the PHP using $forename, $dateofbirth etc.
On the old version of the site (
http://www.didymus.org.uk/tree/display.php?urn=11164) I used a laborious long-hand method of checking for EACH child of an individual and if they existed, echoing their details. This method worked.
Now I want to automate this process. Set $i as a variable which steps from 2 to 16 (child 1 is dealt with differently as you'll see in the code below) and have a loop SELECT the relevant rows from the database and display various info ($fields) for each.
Here is a longer snippet of my code. I hope this helps somebdy to set me straight as I'm all out of ideas!....
Code: Select all
<td colspan='6' class='$spouseclass$tabb' $al><table border='0' cellspacing='0' cellpadding='0' id='spouse1'>
<tr>
<td class='heading' $ar>NAME:</td>
<td class='subspc'> </td>
<td ";
if ($spouse1urn == "404")
{echo "class='subinfo' $al>? </td>";}
else
{$query = "SELECT $fields FROM tree WHERE urn='$spouse1urn'";
$result = mysql_query($query)
or die ("Couldn't execute query");
while ($row = mysql_fetch_assoc($result))
{
extract($row);
if ($bloodline == "AA0000")
{
echo "class='subinfobl' $al><a href='$link$urn'>$forename $middlenames $surname";
if ($yearofbirth == "" OR $yearofbirth == "?" OR $yearofbirth == NULL)
{echo "</a> </td>";}
else
{echo " ($yearofbirth)</a> </td>";}
}
elseif ($bloodline != "AA0000")
{
echo "class='subinfo' $al><a href='$link$urn'>$forename $middlenames $surname";
if ($yearofbirth == "" OR $yearofbirth == "?" OR $yearofbirth == NULL)
{echo " </td>";}
else
{echo " ($yearofbirth) </td>";}}}}
echo " </tr>
<tr>
<td class='heading' $ar>DATE OF MARRIAGE:</td>
<td class='subspc'> </td>
<td class='subinfo'>";
if ($source7 == "")
{echo "$spouse1md";}
else
{echo "<a href='#' onclick=\"alert('$source7')\" title='$srcettl'>$spouse1md</a>";}
echo "</td>
</tr>
<tr>
<td class='heading' $ar>PLACE OF MARRIAGE:</td>
<td class='subspc'> </td>
<td class='subinfo'>";
if ($source8 == "")
{echo "$spouse1mp";}
else
{echo "<a href='#' onclick=\"alert('$source8')\" title='$srcettl'>$spouse1mp</a>";}
echo "</td>
</tr>
<tr>
<td class='heading' $ar>CHILDREN:</td>
<td class='subspc'> </td>
<td ";
$result = mysql_query($mainquery)
or die ("Couldn't execute query");
if ($spouse1child1urn == "" OR $spouse1child1urn == NULL)
{echo "class='subinfo'>None</td></tr>";}
else
{
$query = "SELECT $fields FROM tree WHERE urn='$spouse1child1urn'";
$result = mysql_query($query)
or die ("Couldn't execute query");
while($row = mysql_fetch_assoc($result))
{
extract($row);
if ($bloodline == "AA0000")
{
echo "class='subinfobl'>01: <a href='$link$urn'>$forename $middlenames $surname ($yearofbirth)</a></td></tr>";
}
elseif ($bloodline != "AA0000")
{echo "class='subinfo'>01: <a href='$link$urn'>$forename $middlenames $surname ($yearofbirth)</a></td></tr>";}
}}
$i=2;
while($i<=16)
{
if ($i<=9)
{$prefix = "0";}
elseif ($i>=10)
{$prefix = "";}
$childurn = "$start$sc1$i$end";
$query = "SELECT $fields FROM tree WHERE urn='\$spouse1child.$i.urn'";
$result = mysql_query($query)
or die ("Couldn't execute query");
while($row = mysql_fetch_assoc($result))
{
extract($row);
echo "<tr>
<td> </td><td> </td><td ";
if ($bloodline == "AA0000")
{
echo "class='subinfobl'>$prefix$i$colon<a href='$link$urn'>$forename $middlenames $surname ($yearofbirth)</a></td></tr>";
}
elseif ($bloodline != "AA0000")
{
echo "class='subinfo'>$prefix$i$colon<a href='$link$urn'>$forename $middlenames $surname ($yearofbirth)</a></td></tr>";
}}
$i++;
}
echo "</table></td>";
TIA.
KD.
Re: Problems with a WHILE... loop.
Posted: Sun Oct 18, 2009 3:41 am
by kdidymus
I've added an echo line to the base of the page now so that I can experiment but I just can't seem to figure this out.
Code: Select all
$query = "SELECT $fields FROM tree WHERE urn=[color=#FF0000]'\$spouse1child.$i.urn'[/color]";
returns:
$spouse1child.17.urn
I know that it's gone up to 17 because I've added the echo line at the base of my page and it's passed the
$i++ statement once more than it otherwise would have done. This time we're close but the dots are in there. I need to get rid of them. So I tried:
Code: Select all
$query = "SELECT $fields FROM tree WHERE urn=[color=#FF0000]'\$spouse1child'.$i.'urn'[/color]";
There's something SO wrong with that line I don't know where to start!
Basically I need help with my escaped characters. What I NEED the SELECT lines to read is:
Code: Select all
$query = "SELECT $fields FROM tree WHERE urn=[color=#FF0000]'$spouse1child2urn'[/color]";
Code: Select all
$query = "SELECT $fields FROM tree WHERE urn=[color=#FF0000]'$spouse1child3urn'[/color]";
And so on right up to spouse1child16urn.
Aaaarrrgghhh!
Re: Problems with a WHILE... loop.
Posted: Wed Oct 28, 2009 9:53 am
by duncanwilkie
McInfo wrote:I'm not really sure what your overall database design looks like based on what you have posted, but when a table has an forever-expanding list of fields with names like x1, x2, x3, ..., it usually means that two tables should be used instead, especially if the number of occupied fields in each row is variable (not fixed).
I agree.
Re: Problems with a WHILE... loop.
Posted: Wed Oct 28, 2009 1:28 pm
by kdidymus
I agree too. The problem is I started the site a long time ago when I had a limited knowledge of MySQL. And now the prospect of re-doing it all fills me with dread.
Anyway, I've given up on the PHP loop. It simply doesn't work no matter what I try. I've done it the long-winded way. You can see the results of my labour at:
http://www.didymus.org.uk/new_tree/disp ... ?urn=11164
Thank you all for your help.
Kris.