Page 1 of 3
Problems with FOR loop
Posted: Mon Feb 11, 2013 6:14 am
by kdidymus
Long time no speak folks. Hope you're all well.
I'm having trouble producing a FOR loop for my genealogy website.
I have a MySQL database which I need to automate the interrogation of.
What I WANT the script to do is download a set amount of information from rows called sibling1urn... through to sibling16urn and display it automatically.
Here is my code:
Code: Select all
$fields = "urn,surname,forename,middlenames,yearofbirth,bloodline";
$suffix = "urn";
for ($i=1;$i<=16;$i++) {
if (strlen($i) < 2) {
$prefix="0";
} else {
$prefix="";
}
//EXTRACT HERE
$query = "SELECT $fields FROM tree WHERE urn='sibling.$i.$suffix'";
$result = mysql_query($query)
or die ("Couldn't execute query");
extract($row);
if (sibling.$i.$suffix == "" OR sibling.$i.$suffix == NULL) {
break;
} else {
//EXTRACT ENDS
echo "$prefix$i $forename<br/>";
}
}
But it doesn't work. It keeps echoing the forename of the person I'm viewing. I get sixteen perfectly numbered lines of the same name like this...
01 Paul
02 Paul
03 Paul
...
16 Paul
Any ideas what I've done wrong?
Thanks in advance.
Kris.
Re: Problems with FOR loop
Posted: Mon Feb 11, 2013 11:54 am
by twinedev
Hi Kris, the main issue with your code is no where in it do you actually extract data from the result, so each time you use
$forename, you are using the data that populated somewhere else.
Also, the following line:
Code: Select all
if (sibling.$i.$suffix == "" OR sibling.$i.$suffix == NULL)
in terms of the code you provided makes no sense to me. I'm assuming this was your way of trying to get it to skip empty records? In the code below, that is what I'm using.
Here is your code the way I would have done it (well I would have done it with mysqli since mysql is being removed)
Code: Select all
$fields = "`urn`,`surname`,`forename`,`middlenames`,`yearofbirth`,`bloodline`";
$suffix = "urn";
for ($i=1;$i<=16;$i++) {
$query = 'SELECT '.$fields.' FROM `tree` WHERE `urn`="sibling'.$i.$suffix.';';
$result = mysql_query($query);
if ($result && mysql_num_rows($result)>0) {
$row = mysql_fetch_assoc($result);
if ($row['forename']!='') {
echo str_pad($i,2,'0',STR_PAD_LEFT),' ',$row['forename'],'<br/>';
}
}
}
Re: Problems with FOR loop
Posted: Mon Feb 11, 2013 3:02 pm
by kdidymus
What can I say apart from thank you?! I shall mess around with this later and am now far more confident about it actually working!
As a side note, my website has been running for four years now and was built around MySQL. When is MySQL being phased out and would changing over simply be a case of changing entries reading mysql to mysqli?
Re: Problems with FOR loop
Posted: Mon Feb 11, 2013 3:30 pm
by social_experiment
kdidymus wrote:hanging entries reading mysql to mysqli?
not quite, many mysqli_ functions require a database connection resource to function properly
PHP manual wrote:mixed mysqli_query ( mysqli $link , string $query [, int $resultmode ] )
Re: Problems with FOR loop
Posted: Mon Feb 11, 2013 3:52 pm
by twinedev
I started changing over to mysqli, and it wasn't too bad, the bug thing was changing all the functions to use the resource. Doing search/replaces helped
search for:
mysql_query(
replace with:
mysqli_query($dbConn,
and similar for other functions took care of most things :)
Re: Problems with FOR loop
Posted: Wed Feb 13, 2013 12:12 am
by kdidymus
Sorry folks but still no joy. I don't think I quite explained myself fully last time.
I run a genealogy website. It uses PHP and MySQL. My database consists of a single entry for each member of our family, all with a Unique Reference Number (URN). Siblings are contained in columns in my database entitled sibling1urn, sibling2urn etc. all the way through to sibling 16urn.
At the point at which I need the following code to work, the data for the "target" individual has already been extracted and echo'd. What I need my site to do now is (using a FOR loop), extract further data from MySQL from strings already extracted.
So I need the loop to download $fields where urn = '$sibling1urn' etc. $sibling1urn is a string already extracted from the database.
I guess what I'm really stuck with is the structure of my SELECT statement. What I need to do is concatenate somehow. When I extract this data MANUALLY, it works fine. It's only when I add the $i in to the equation that things stop working. No errors, just no data returned.
MANUALLY my script looks like this:
Code: Select all
$query = "SELECT $fields FROM tree WHERE urn='$sibling1urn'";
$result = mysql_query($query)
or die ("Couldn't execute query");
while($row = mysql_fetch_assoc($result))
{
extract($row);...
And it works. But having this block repeated SIXTEEN times for each of the siblings is nonsensical!
MY FOR LOOP (WHICH DOESN'T WORK) looks like this:
Code: Select all
for ($i=1;$i<=16;$i++)
{
// EXTRACT DATA AND DISPLAY
$sibsearch = "$sibling{$i}urn";
$query = "SELECT $fields FROM tree WHERE urn='$sibsearch'";
$result = mysql_query($query)
or die ("Couldn't execute query");
while($row = mysql_fetch_assoc($result))
{
extract($row);...
Any ideas as to what I'm doing so wrong in the WHERE part of this code? I've tried \"$sibsearch{$i}urn\", I've tried '$sibsearch{$i}urn', I've tried apostrophes, full stops and still my code resolutely refuses to work....
Thanks in advance...
Re: Problems with FOR loop
Posted: Wed Feb 13, 2013 12:41 am
by social_experiment
Code: Select all
<?php
$sibsearch = $sibling . $i . "urn";
?>
try the line above, hth
Re: Problems with FOR loop
Posted: Wed Feb 13, 2013 3:32 am
by kdidymus
I will happily try that. However, two questions.
1) My select line is already enclosed within quotes i.e. "SELECT $fields FROM tree WHERE urn='$sibling1urn'"; so will the additional quotes in your line of code work?
2) $sibling doesn't exist. What I need PHP to do is generate the phrase $sibling1urn through to $sibling16urn by using $i in place of the number. What I SUSPECT I need to do is somehow have PHP ignore the first $ in order that the exact phrase is generated. I feel that PHP is trying to find the string $sibling which doesn't exist. Kind of like:
'$sibling'.$i.'urn' but this doesn't appear to work.
Aaargh!!
Re: Problems with FOR loop
Posted: Wed Feb 13, 2013 3:35 am
by kdidymus
Would:
<?php
$sibsearch = "$sibling".$i."urn";
?>
work?
Re: Problems with FOR loop
Posted: Wed Feb 13, 2013 5:49 am
by social_experiment
kdidymus wrote:1) My select line is already enclosed within quotes i.e. "SELECT $fields FROM tree WHERE urn='$sibling1urn'"; so will the additional quotes in your line of code work?
the quotes aren't included in the variables value (unless you have an additional quote in the actual variable).
Code: Select all
<?php
$a = "This";
$b = "is";
echo $a . $b . 'simple'; // yields Thisissimple
?>
kdidymus wrote:2) $sibling doesn't exist. What I need PHP to do is generate the phrase $sibling1urn through to $sibling16urn by using $i in place of the number. What I SUSPECT I need to do is somehow have PHP ignore the first $ in order that the exact phrase is generated. I feel that PHP is trying to find the string $sibling which doesn't exist. Kind of like:
am i correct in saying that you don't want $sibling to act as a variable but as part of a string called i.e $sibling14urn ?
Basic variable info
^ read this; it will give you more insight into variables
kdidymus wrote:Would:
<?php
$sibsearch = "$sibling".$i."urn";
?>
work?
No because php will try interpret "$sibling" as a variable; to treat $sibling as a string containing $ use ' and '
Re: Problems with FOR loop
Posted: Wed Feb 13, 2013 6:43 am
by kdidymus
am i correct in saying that you don't want $sibling to act as a variable but as part of a string called i.e $sibling14urn ?
Yep. Spot on. I want $sibling to act as the first part of the string followed immediately by the value of $i and then the word urn. You mention AND. I've read the PHP manual but I'm afraid I'm still unclear.
This is frustrating as I usually get there by trial and error. But no matter what I do I can't get the output for the query correct.
I'm guessing it MIGHT be something like:
Am I close?!!!
Re: Problems with FOR loop
Posted: Wed Feb 13, 2013 7:02 am
by social_experiment
what output do you get if you try this code
Code: Select all
<?php
echo '$sibling' . $i . 'urn';
?>
Re: Problems with FOR loop
Posted: Wed Feb 13, 2013 7:11 am
by kdidymus
I'll try this as soon as I get home this evening and let you know. It's frustrating because it's such a simple thing and once I work out how to produce the query using the correct syntax, the rest of my code will work fine.
Thanks for all of your help so far.
Re: Problems with FOR loop
Posted: Wed Feb 13, 2013 7:25 am
by kdidymus
Done that. Giving $i a value of 6 the code
gives me
$sibling6urn
Which is exactly what I need in my MySQL query. Could it really be that simple? By my reckoning my code for this needs to be:
Code: Select all
$query = "SELECT $fields FROM tree WHERE urn = '$sibling' . $i . 'urn'";
I'm sure I tried this already but have lost track.
Re: Problems with FOR loop
Posted: Wed Feb 13, 2013 7:28 am
by kdidymus
Or would I need to use a new string such as:
Code: Select all
$sibquery = '$sibling' . $i . 'urn';
$query = "SELECT $fields FROM tree WHERE urn = '$sibquery';
or
Code: Select all
$sibquery = '$sibling' . $i . 'urn';
$query = "SELECT $fields FROM tree WHERE urn = $sibquery;
Any thoughts would be greatly appreciated.