Problems with FOR loop

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Problems with FOR loop

Post by kdidymus »

Nope. Still no luck. Here's the new version of the code:

Code: Select all

...
<body>
URN: $mainurn<br/>$forename $middlenames $surname ($yearofbirth)<br/><br/>"; // JUST TO TEST THAT dB IS ACCESSED AND WORKING

for ($i=1;$i<=16;$i++) {
    $sibquery = '$sibling' . $i . 'urn'; // THIS ECHOES FINE
    $fields = "urn,surname,forename,middlenames,yearofbirth,bloodline";
// EXTRACT DATA AND DISPLAY
    $query = "SELECT $fields FROM tree WHERE urn = '$sibquery'";
    $result = mysql_query($query)
          or die ("Couldn't execute query");
    while($row = mysql_fetch_assoc($result)) {
        extract($row);
        if (strlen($i) < 2) {
            $prefix="0";
        } else {
            $prefix="";
        }
        if ($bloodline == "AA0000") {
            echo "&nbsp;<tt>$prefix$i:</tt><a href='$link$urn' class='bloodline'>$forename $middlenames $surname ($yearofbirth)</a>&nbsp;<br/>";
        } elseif ($bloodline != "AA0000") {
            echo "&nbsp;<tt>$prefix$i:</tt><a href='$link$urn' class='footer'>$forename $middlenames $surname ($yearofbirth)</a>&nbsp;<br/>";
        }
    }
}
I've echoed the $sibquery string and it definitely displays correctly as $sibling1urn. If I take away the MySQL section and allow the code to loop, I get $sibling1urn to $sibling16urn displayed.

The problem is STILL how to get PHP to ignore the $ sign at the end of the $query line. I've tried using the HTML code for the dollar sign (&#36;) but this doesn't work either.

Three days I've spent playing with this and I'm still no closer.

Just to reiterate, the above code (as clunky as it is) works perfectly without the loop. It displays the data fine (when combined with the CSS of my site).

But throw the FOR loop in to the mix and it all goes wrong.

Please, PLEASE can anybody help?

Kris.
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Problems with FOR loop

Post by kdidymus »

Had a brainwave then. But it STILL doesn't work!

Code: Select all

$sibquery = '$' . 'sibling' . $i . 'urn';
If I echo $sibquery it still shows $sibling1urn etc. but in terms of running the MySQL query, not a chance.

Kris.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Problems with FOR loop

Post by social_experiment »

try the syntax below for the query;

Code: Select all

<?php
$query = "SELECT $fields FROM tree WHERE urn = '" . $sibquery ."' ";
?>
kdidymus wrote:The problem is STILL how to get PHP to ignore the $ sign at the end of the $query line.
do you mean the dollar sign in $sibquery?
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Problems with FOR loop

Post by kdidymus »

I never meant for you to share my misery but sadly that didn't work either.

Tomorrow I'm going to change the query back to a manual entry of $sibling1urn to see if it fetches the details of the first sibling. If it DOES, then I'll know for certain that the problem lies with the way PHP is handling the string.

I was thinking earlier there must be a way to escape the $ sign so that PHP queries MySQL by the literal string defined by $sibquery. But I'm all out of ideas. Of course, I can run that same script 16 times in a row but my code would be far shorter this way. Plus I don't give up that easy!
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Problems with FOR loop

Post by social_experiment »

kdidymus wrote:there must be a way to escape the $ sign so that PHP queries
using single quotes renders $ as a dollar sign instead of the start of variable;

another link relating to the dollar sign and escaping it
http://stackoverflow.com/questions/2557 ... s-variable

all the best with the script and post back when you solve it :)
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Problems with FOR loop

Post by Christopher »

I reformatted the code in your posts so they are more readable. Your non-standard formatting will obscure what the code is doing from most programmers. And it may be muddling your brain as well. ;)

My guess is that you don't need a loop within a loop, or the dollar sign in the value (it wasn't in your original post), or the extract, or the other confusing things in this code. Snap out of it man! :)

It would be helpful to show us what the data in the table looks like, and what you want the result set to look like. Things like whether values in a column are unique or not are critical to this kind of code.

Try this:

Code: Select all

for ($i=1;$i<=16;$i++) {
    $prefix = $i < 10 ? '0' : '';
    $fields = "urn,surname,forename,middlenames,yearofbirth,bloodline";
    $query = "SELECT $fields FROM tree WHERE urn = 'sibling{$i}urn'";
    $result = mysql_query($query)
          or die ("Couldn't execute query");
    if (!mysql_errno($result)) {
        while($row = mysql_fetch_assoc($result)) {
            if ($row['bloodline'] == "AA0000") {
                $class = 'bloodline';
            } else {
                $class = 'footer';
            }
            echo "&nbsp;<tt>$prefix$i:</tt><a href=\"{$row['link']}{$row['urn']}\" class=\"$class\">{$row['forename']} {$row['middlenames']} {$row['surname']} ({$row['yearofbirth']})</a>&nbsp;<br/>";
        }
    } else {
        echo "DB ERROR: " . mysql_errmsg($result);
    }
}
(#10850)
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Problems with FOR loop

Post by kdidymus »

Christopher.

Thank you for the amended code which has greatly simplified and reduced the size of this section of my PHP.

I think perhaps, as you suggested, I should go back and explain exactly what I'm doing.

My genealogy site uses a file called "display.php" which is passed a Unique Reference Number (URN) as follows:

display.php?urn=11164 where urn is the reference for the person being viewed. I'll use 11164 for this example. I then set $urn as $mainurn so that this remains constant throughout the rest of the page using:

Code: Select all

$mainurn = mysql_real_escape_string($_GET['urn']);
$mainurn = ucfirst($mainurn);
$invalidChars = array("/",".","\\","\"",";","http",":","!","*","&");
$mainurn = str_replace($invalidChars,"",$mainurn);
A query is sent which selects ALL fields from my database where the urn is 11164 ($mainurn).

The PHP goes on to echo HTML populating a table with details such as forename, middle names, surname, date of birth etc. etc.

When an individual has brothers or sisters, there are rows in my table called sibling1urn through to sibling16urn. The data in these rows is simply the URN for that individual.

So my code up until this point has downloaded all of the information for the person with urn 11164. Including the urn of their brothers and sisters and these are stored in the strings $sibling1urn through to $sibling16urn.

What I want this code to do is this...

Download from the table the selected fields ($fields) where the URN matches that downloaded as $sibling1urn to $sibling16urn from the original query.

For example:

Kris DIDYMUS (Original person viewed)

$forename = Kris
$Middlenames = Paul
$Surname = Didymus
...
$sibling1run = 29783 (these are not incremental numbers by design)
$sibling2urn = 83983

So I want this code to set up a FOR loop where $i is the incremental number in the $sibling..urn string.

Then I want it to interogate my MySQL database and fetch the rows defined in $fields before echoing them as a hyperlink.

The following code WORKS but will (obviously) only download the details of the first sibling:

Code: Select all

for ($i=1;$i<=16;$i++)
{
$prefix = $i < 10 ? '0' : '';
$fields = "urn,surname,forename,middlenames,yearofbirth,bloodline";
// EXTRACT DATA AND DISPLAY
$query = "SELECT $fields FROM tree WHERE urn = '$sibling1urn'";
$result = mysql_query($query)
          or die ("Couldn't execute query");
echo "$sibquery"; // I use this simply to check that the increments are correct
while($row = mysql_fetch_assoc($result))
{
extract($row);
if ($row['bloodline'] == "AA0000") {
$sibclass = 'bloodline';
} else {
$sibclass = 'footer';
}
echo "&nbsp;<tt>$prefix$i:</tt><a href='$link$urn' class='$sibclass'>$forename $middlenames $surname ($yearofbirth)</a>&nbsp;<br/>";}
}
This displays:
$sibling1urn 01:Richard John LUGG (1885)
$sibling2urn 02:Richard John LUGG (1885)
$sibling3urn 03:Richard John LUGG (1885)
$sibling4urn 04:Richard John LUGG (1885)
$sibling5urn 05:Richard John LUGG (1885)
$sibling6urn 06:Richard John LUGG (1885)
$sibling7urn 07:Richard John LUGG (1885)
$sibling8urn 08:Richard John LUGG (1885)
$sibling9urn 09:Richard John LUGG (1885)
$sibling10urn 10:Richard John LUGG (1885)
$sibling11urn 11:Richard John LUGG (1885)
$sibling12urn 12:Richard John LUGG (1885)
$sibling13urn 13:Richard John LUGG (1885)
$sibling14urn 14:Richard John LUGG (1885)
$sibling15urn 15:Richard John LUGG (1885)
$sibling16urn 16:Richard John LUGG (1885)
The problem occurs when I try to have the FOR loop automate the process of stepping through each sibling.

The code I now have for doing this is:

Code: Select all

for ($i=1;$i<=16;$i++)
{
$prefix = $i < 10 ? '0' : '';
$sibquery = '$sibling' . $i . 'urn';
$fields = "urn,surname,forename,middlenames,yearofbirth,bloodline";
// EXTRACT DATA AND DISPLAY
$query = "SELECT $fields FROM tree WHERE urn = '$sibquery'";
$result = mysql_query($query)
          or die ("Couldn't execute query");
echo "$sibquery";
while($row = mysql_fetch_assoc($result))
{
extract($row);
if ($row['bloodline'] == "AA0000") {
$sibclass = 'bloodline';
} else {
$sibclass = 'footer';
}
echo "&nbsp;<tt>$prefix$i:</tt><a href='$link$urn' class='$sibclass'>$forename $middlenames $surname ($yearofbirth)</a>&nbsp;<br/>";}
}
I don't get any errors but no data is fetched.

I know therefore that whereas I'm asking my code to fetch the fields listed from an incremental string ($sibling{$i}urn) it's treating the first part ($sibling) as a variable and not as a straight forward string.

I really hopes this makes sense!
spencerharry80
Forum Newbie
Posts: 2
Joined: Mon Feb 04, 2013 2:59 am

Re: Problems with FOR loop

Post by spencerharry80 »

I am not sure about this problem but you can try by changing your variable names.
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Problems with FOR loop

Post by kdidymus »

Right. I've confirmed the problem. All I need now is to work out how to solve it. And I'm gonna need some help!

I've echoed the query being sent for both the manual and looped versions of my code.

Using:

Code: Select all

$query = "SELECT $fields FROM tree WHERE urn = '$sibling1urn'";
I get:
SELECT urn,surname,forename,middlenames,yearofbirth,bloodline FROM tree WHERE urn = '1810121885'
Using:

Code: Select all

$query = "SELECT $fields FROM tree WHERE urn = '$sibquery'";
I get:
SELECT urn,surname,forename,middlenames,yearofbirth,bloodline FROM tree WHERE urn = '$sibling1urn'
So the query that's ACTUALLY being sent having been incrementally produced from my FOR loop is:

SELECT urn,surname,forename,middlenames,yearofbirth,bloodline FROM tree WHERE urn = '$sibling1urn'

It's not searching for the VALUE of $sibling1urn, it's literally searching for that string. Which of course it won't find.

HELP!
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Problems with FOR loop

Post by social_experiment »

when you don't use the for loop, how do you create $sibling1urn in the query below? it seems to generate a value which you can use, if i understand your explanation correctly

Code: Select all

<?php
$query = "SELECT $fields FROM tree WHERE urn = '$sibling1urn'";
?>
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Problems with FOR loop

Post by kdidymus »

The strings $sibling1urn to $sibling16urn have already been fetched earlier in my page. The database is queried before the HTML starts with SELECT * from the $mainurn.

At the point you see my code I break the HTML to have PHP fetch further details from the database relating to the individual's siblings.

All I need to do is pass the VALUE of $sibling1urn ... $sibling16urn in the query. But I can't seem to do this.

I did think about using arrays but I'm not sure if this would work either.

I'm certain that PHP must have a function to concatenate and produce an incremental string using a FOR loop.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Problems with FOR loop

Post by Christopher »

kdidymus wrote:Thank you for the amended code which has greatly simplified and reduced the size of this section of my PHP.
It can be reduced and improved much more. There is no nice way to say that the design and code are pretty bad.
kdidymus wrote:I think perhaps, as you suggested, I should go back and explain exactly what I'm doing.
Sadly, I think I understand now.

Code: Select all

    $query = "SELECT * FROM tree WHERE urn = '$mainurn'";
    $result = mysql_query($query);
    if (!mysql_errno($result)) {
        $mainrow = mysql_fetch_assoc($result);
		// display the mainurn's information here

        // build WHERE conditions to fetch child records
        $where = array();
        for ($i=1;$i<=16;$i++) {
            $field = 'sibling' . $i . 'urn';
            if (isset($mainrow[$field])) {
                $where[] = "urn='{$mainrow[$field]}'";
            } 
        }
		if ($where) {		// there are siblings
			$query = "SELECT * FROM tree WHERE " . implode(' OR ', $where);
			$result = mysql_query($query);
			if (!mysql_errno($result)) {
			while($row = mysql_fetch_assoc($result)) {
				if ($row['bloodline'] == "AA0000") {
					$class = 'bloodline';
				} else {
					$class = 'footer';
				}
				echo "&nbsp;<tt>$prefix$i:</tt><a href=\"{$row['link']}{$row['urn']}\" class=\"$class\">{$row['forename']} {$row['middlenames']} {$row['surname']} ({$row['yearofbirth']})</a>&nbsp;<br/>";
			}
		} else {
			echo "DB ERROR 2: " . mysql_errmsg($result);
		}
    } else {
        echo "DB ERROR 1: " . mysql_errmsg($result);
    }
}
There are many things wrong with your design and code, but the worst two are:

1) At a minimum separate your PHP between when you use PHP as a programming language and when you use it as a templating language. Actually MVC separation would be best.

2) Use a link table for the person<->sibling relationships (and parent<->child as well).
(#10850)
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Problems with FOR loop

Post by kdidymus »

Thank you. I really appreciate your help.

My problem is I learned PHP and MySQL from a "For Dummies" book, built my website (http://www.didymus.org.uk) on a trial and error basis and although the site works fine I'm updating it and want to refine and improve my code and knowledge as I do so.

I will test the code you have provided over the next few days and update the forum with my progress.

With nearly 3,000 records already in my MySQL database I would like to make the best of what I have as opposed to going right back to the drawing board.

Thank you again.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Problems with FOR loop

Post by Christopher »

kdidymus wrote:My problem is I learned PHP and MySQL from a "For Dummies" book, built my website (http://www.didymus.org.uk) on a trial and error basis and although the site works fine I'm updating it and want to refine and improve my code and knowledge as I do so.
Maybe you should try a book "For Smarties" next time! ;)
kdidymus wrote:With nearly 3,000 records already in my MySQL database I would like to make the best of what I have as opposed to going right back to the drawing board.
The number of records is really not an issue -- because they are all the same. You could easily extract the values in the sibling1..16urn fields into a link urn_sibling table that had two columns: urn and siblingurn. Then you just need to "SELECT tree.* FROM urn_sibling JOIN tree ON urn_sibling.siblingurn=tree.urn WHERE urn_sibling.urn='$mainurn'". Then you can remove those 16, almost always empty, fields in the tree table. And get rid of the whole watch 1..16 loop that caused your problems in the first place. That part is not PHP but basic relational database design.

As for the code, we can certainly help you improve it. I really did not get into modularizing your code or reducing side-effects. You just need some basic Best Practices. Well designed code makes you much less afraid to take sections of it "back to the drawing board."
(#10850)
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Problems with FOR loop

Post by kdidymus »

Christopher.

You're a star fella. Your code, with a few tweaks (owing to some unexpected errors caused I think by my hosting company) works almost perfectly.

Here it is in its final form:

Code: Select all

<?php

include_once("../../*************.php");
$cxn = mysql_connect($host,$user,$password)
       or die ("Couldn't connect to server");
mysql_select_db($database);

$mainurn = mysql_real_escape_string($_GET['urn']);
$mainurn = ucfirst($mainurn);
$invalidChars = array("/",".","\\","\"",";","http",":","!","*","&");
$mainurn = str_replace($invalidChars,"",$mainurn);
$fields = "urn,surname,forename,middlenames,yearofbirth,bloodline";
$dlink = "display.php?urn=";

// QUERY THE MYSQL DATABASE FOR TARGET INDIVIDUAL

    $query = "SELECT * FROM tree WHERE urn = '$mainurn'";
    $result = mysql_query($query);
    $mainrow = mysql_fetch_assoc($result);

// START BUILDING HTML

echo "
<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Strict//EN'
'http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd'>
<html xmlns='http://www.w3.org/1999/xhtml'>

<head>
<meta http-equiv='Content-Language' content='en-gb' />
<meta http-equiv='Content-Type' content='text/html; charset=windows-1252' />
<meta http-equiv='content-type' content='text/javascript' />
<script type='text/javascript' src='../js/jquery.js'></script>
<script type='text/javascript' src='../js/jquery.alerts.js'></script>
<link rel='stylesheet' type='text/css' href='../css/jquery.alerts.css' 
media='screen' />
<link rel='stylesheet' href='../css/main.css' type='text/css' media='screen' />
<title>www.didymus.org.uk | Family Tree | $forename $middlenames $surname ($born)</title>
</head>

<body>

SURNAME: {$mainrow['surname']}<br/>FORENAME: {$mainrow['forename']}<br/>MIDDLE NAMES: {$mainrow['middlenames']}<br/>YEAR OF BIRTH: ({$mainrow['yearofbirth']})<br/>";

// build WHERE conditions to fetch child records

        $where = array();
        for ($i=1;$i<=16;$i++) {
            $field = 'sibling' . $i . 'urn';
            if (isset($mainrow[$field])) {
                $where[] = "urn='{$mainrow[$field]}'";
            } 
        }

                if ($where) {           // there are siblings
                        $query = "SELECT $fields FROM tree WHERE " . implode(' OR ', $where);
                        $result = mysql_query($query);
if(mysql_num_rows($result)!=0)                        

{                        while($sibrow = mysql_fetch_assoc($result)) {
                                if ($sibrow['bloodline'] == "AA0000") {
                                        $class = 'bloodline';
                                } else {
                                        $class = 'footer';
                                }

                                echo "&nbsp;<tt>";
$p=1;
$prefix = $p < 10 ? '0' : '';
echo "$prefix$p:</tt><a href=\"$dlink{$sibrow['link']}{$sibrow['urn']}\" class=\"$class\">{$sibrow['forename']} {$sibrow['middlenames']} {$sibrow['surname']} ({$sibrow['yearofbirth']})</a>&nbsp;<br/>";
$p++;
                        }
                } else {
                        echo "DB ERROR 2: " . mysql_errmsg($result);
                }
    } else {
        echo "DB ERROR 1: " . mysql_errmsg($result);
    }

echo "</body></html>";
?>
It fetches the data and displays it like this:
SURNAME: LUGG
FORENAME: Henrietta
MIDDLE NAMES: May
YEAR OF BIRTH: (1894)
01:Jack LUGG (c.1909)
01:Percy LUGG (c.1903)
01:Richard John LUGG (1885)
01:Beatrice Lily LUGG (c.1899)
01:Vera Elizabeth LUGG (c.1897)
01:Wilfred LUGG (c.1908)
01:Charles LUGG (c.1891)
01:Clarissa Emily LUGG (c.1890)
01:Eileen LUGG (c.1905)
01:Edith Bessie LUGG (c.1887)
01:Emily Bartley LUGG (c.1896)
01:George LUGG (c.1902)
I have two further (and final) questions:

1) How would I sort the results by YEAR OF BIRTH? This is something I've been thinking about for a while but now that the results are in an array it SHOULD be easily done. I've researched usort, asort etc. but can't seem to get it to apply to my code!
2) How do I ensure the prefix is an incremental number? At the moment every entry is shown as 01!! I think this is because the results are fetched as an array and "dumped" all at once.

Your help has been truly appreciated. You have no idea how much you're helped me.
Post Reply