Page 1 of 1

A better way of adding up?!

Posted: Thu Mar 05, 2009 6:56 am
by kdidymus
To cut a long story short, I am in the process of re-vamping my family tree website.

http://www.didymus.org.uk/new/tree/disp ... ?urn=11164 if anybody wants to see what I've done so far!

My MySQL database stores the filenames of photographs of individuals. What I WANT to do is modify my search engine code so that it counts the number of photographs for each person and returns the count in a column.

i.e.
URN SURNAME FORENAME(S) PHOTOGRAPHS ....
11164 Didymus Kristian Paul 5
I've come up with the following code but there SURELY must be a more efficient way of running the enquiry. For your info, the database either stores the word blank or the first part of the filename (minus the .gif suffix).

Code: Select all

if ($photo1urn != "blank")
{ $pc1 = "1";}
else
{$pc1 = "0";}
 
if ($photo2urn != "blank")
{ $pc1 = "1";}
else
{$pc1 = "0";}
 
if ($photo3urn != "blank")
{ $pc1 = "1";}
else
{$pc1 = "0";}
 
if ($photo4urn != "blank")
{ $pc1 = "1";}
else
{$pc1 = "0";}
 
if ($photo5urn != "blank")
{ $pc1 = "1";}
else
{$pc1 = "0";}
 
if ($photo6urn != "blank")
{ $pc1 = "1";}
else
{$pc1 = "0";}
 
if ($photo7urn != "blank")
{ $pc1 = "1";}
else
{$pc1 = "0";}
 
if ($photo8urn != "blank")
{ $pc1 = "1";}
else
{$pc1 = "0";}
 
if ($photo9urn != "blank")
{ $pc1 = "1";}
else
{$pc1 = "0";}
 
if ($photo10urn != "blank")
{ $pc1 = "1";}
else
{$pc1 = "0";}
 
$photocount = $pc1 + $pc2 + $pc3 + $pc4 + $pc5 +$pc6 +$pc7 +$pc8 +$pc9 +$pc10;
 
Any ideas how I can compress this down as I want to do the same thing for other things too!

Many thanks in advance.

KD.

Re: A better way of adding up?!

Posted: Thu Mar 05, 2009 7:07 am
by Reviresco
The way you've got it set up, it will always only return 1 or 0 -- each time you're setting $pc1 -- I don't see $pc2, $pc3, etc.

Does each person have ten photo fields in the table? Or do those ten variables represent ten different people?

Re: A better way of adding up?!

Posted: Thu Mar 05, 2009 9:59 am
by kdidymus
Sorry.

Looks like you spotted my deliberate error. Ahem! :oops:

Code: Select all

if ($photo1urn != "blank")
{ $pc1 = "1";}
else
{$pc1 = "0";}
 
if ($photo2urn != "blank")
{ $pc2 = "1";}
else
{$pc2 = "0";}
 
if ($photo3urn != "blank")
{ $pc3 = "1";}
else
{$pc3 = "0";}
 
if ($photo4urn != "blank")
{ $pc4 = "1";}
else
{$pc4 = "0";}
 
if ($photo5urn != "blank")
{ $pc5 = "1";}
else
{$pc5 = "0";}
 
if ($photo6urn != "blank")
{ $pc6 = "1";}
else
{$pc6 = "0";}
 
if ($photo7urn != "blank")
{ $pc7 = "1";}
else
{$pc7 = "0";}
 
if ($photo8urn != "blank")
{ $pc8 = "1";}
else
{$pc8 = "0";}
 
if ($photo9urn != "blank")
{ $pc9 = "1";}
else
{$pc9 = "0";}
 
if ($photo10urn != "blank")
{ $pc10 = "1";}
else
{$pc10 = "0";}
 
$photocount = $pc1 + $pc2 + $pc3 + $pc4 + $pc5 +$pc6 +$pc7 +$pc8 +$pc9 +$pc10;
Each person in the tree can have UP TO 10 images.

For example, in MY row in the MySQL dB:

$photo1urn = 11164-1
$photo2urn = 11164-2
$photo3urn = 11164-3
$photo4urn = 11164-4
$photo5urn = 11164-5
$photo6urn = 11164-6
$photo7urn = blank
$photo8urn = blank
$photo9urn = blank
$photo10urn = blank

So if I run the above code on my row, it SHOULD return 6.

Hope this helps. And thanks for pointing out my elementary error. The wonders (and pitfalls) of copy and paste!

KD.

Re: A better way of adding up?!

Posted: Thu Mar 05, 2009 10:10 am
by pickle
It would really be advantageous if you normalized that table. Rather than having 10 columns for photos in that table, build a new table that matches an image to a URN. Then you can have an infinite number of pictures, and you can do the count with a simple count() in a database query.

If that's not possible, just use a counter rather than 11 different variables:

Code: Select all

$photocount = 0;
$photocount += ($photo1urn != 'blank') ? 1 : 0;
$photocount += ($photo2urn != 'blank') ? 1 : 0;
...

Re: A better way of adding up?!

Posted: Thu Mar 05, 2009 1:29 pm
by php_east

Code: Select all

 
for ($i=1;$i<=10;$i++)
{
eval('if ($photo'.$i.'urn != "blank") { $pc'.$i.' = "1";} else {$pc'.$i.' = "0";}');
}
 
$photocount=0;
for ($i=1;$i<=10;$i++)
{
eval('$photocount+=intval($pc'.$i.');');
}
 
echo $photocount;
 
 

Re: A better way of adding up?!

Posted: Thu Mar 05, 2009 1:39 pm
by pickle
php_east wrote:

Code: Select all

 
for ($i=1;$i<=10;$i++)
{
eval('if ($photo'.$i.'urn != "blank") { $pc'.$i.' = "1";} else {$pc'.$i.' = "0";}');
}
 
$photocount=0;
for ($i=1;$i<=10;$i++)
{
eval('$photocount+=intval($pc'.$i.');');
}
 
echo $photocount;
 
 
Why use eval()? That's dangerous & unnecessary. If you want to use variable variables, do it like this:

Code: Select all

for($i=1;$i<=10;$i++)
{
  if(${'photo'.$i.'urn'})
    ...
}

Re: A better way of adding up?!

Posted: Thu Mar 05, 2009 1:39 pm
by kdidymus
Thank you both for your responses. You've both given me some food for thought. I will consider normalising the table but to be honest my focus right now is on intelligently using the existing data rather than changing the dB itself.

In the meantime you've both given me a clever solution to my problem.

Thank you.

KD.

Re: A better way of adding up?!

Posted: Thu Mar 05, 2009 1:47 pm
by php_east
pickle wrote: Why use eval()? That's dangerous & unnecessary. If you want to use variable variables, do it like this:

Code: Select all

for($i=1;$i<=10;$i++)
{
  if(${'photo'.$i.'urn'})
    ...
}
this way is much nicer looking and readable i agree.
but i cannot see how eval can be dangerous ? in what way ?

many thanks.

Re: A better way of adding up?!

Posted: Thu Mar 05, 2009 1:58 pm
by pickle
Granted, in your particular example, there's nothing dangerous. If you get in the habit of using it though, there's a chance that eventually you'll put something in your eval() call that came from the user. If that's not 100% checked, you could be opening yourself up to a security hole.

Re: A better way of adding up?!

Posted: Thu Mar 05, 2009 2:05 pm
by php_east
ah, i would agree absolutely, yes. wooo!

Re: A better way of adding up?!

Posted: Fri Mar 06, 2009 2:34 am
by kdidymus
I'm having real trouble getting this to work.

php_east's suggestion worked fine. Flawlessly. Straight away. And then I tried to implement the changes suggested by pickle and that's when things went wrong.

I've left the second eval statement in place as I'm not sure how to iradicate this (part of my problem I'm guessing) but I'm getting a
Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in /home/kdidymus/public_html/new/tree/photocount2.php on line 50
error when I try to execute the following code:

Code: Select all

<?php
/* Program: photocount.php
 * Version: 2.01
 * Desc:    Downloads data from MySQL and displays genealogical details of individual.
 * Author:  Kris Didymus
 * Date:    05/03/2009
 */
 
// CONNECT TO MYSQL DATABASE
 
include_once("../../general.inc.php"); // Get parameters for MySQL connection from secure file
$cxn = mysql_connect ($host,$user,$password)
       or die ("Couldn't connect to the server"); // Use parameters to make connection
mysql_select_db($database); // Select database using final parameter
 
// GET MAIN URN FROM ORIGINATING URL
 
$mainurn = mysql_real_escape_string($_GET['urn']); //Set $mainurn according to URL passed
$mainurn = ucfirst($mainurn); // Set any letters in $mainurn to uppercase to avoid 404 errors
 
// QUERY THE MYSQL DATABASE
 
$mainquery = "SELECT * FROM tree WHERE urn='$mainurn'"; // Specify $mainquery for re-use throughout
$result = mysql_query($mainquery)
          or die (mysql_error()); 
 // Specify URN to be downloaded
 
// IF THE SPECIFIED URN DOESN'T EXIST LOAD THE ERROR PAGE
 
if(mysql_num_rows($result)==0)
{
header("Location: urn404.php"); // Direct browser to error page for unrecognised URN
exit;
}
 
// IF THE SPECIFIED URN DOES EXIST PROCEED TO EXECUTE REMAINDER OF PROGRAM
 
else {
 
// CREATE AND EXECUTE LOOP FOR DOWNLOADING URN DATA
 
while($row = mysql_fetch_assoc($result))
{
extract($row); // Row is now extracted and ready for formatting and display
 
for ($i=1;$i<=10;$i++)
{
if (${'photo'.$i.'urn'} != "blank")
{
$pc'.$i.' = "1";
}
else
{
$pc'.$i.' = "0";
}
 
$photocount=0;
for ($i=1;$i<=10;$i++)
{
eval('$photocount+=intval($pc'.$i.')');
}}
 
echo $photocount;
?>
Sorry. I know I'm a numpty and very new to PHP but I'm not sure what's causing this. My best guess would be a misplaced ; or a mismatched bracket but try as I might I can't get this working!

Thanks for your patience.

KD.

Re: A better way of adding up?!

Posted: Fri Mar 06, 2009 4:34 am
by php_east

Code: Select all

for ($i=1;$i<=10;$i++)
{
 if (${'photo'.$i.'urn'} != "blank")
     {
     ${'pc'.$i} = "1";
     }
 else
     {
     ${'pc'.$i} = "0";
     }
}
 
/* example to get print out using the same coding style */
for ($i=1;$i<=10;$i++)
{
echo ${'pc'.$i};
}
 
both ways are a little cryptic in the sense that a variable name is derived, not typed into the program.

which way best for you is your personal choice. i tend to use curly braces for something else so i frequent eval ( and thus am used to the risks mentioned ). other coders have other preferences and style. there is no right and wrong, so long as it runs.

back to the example, php would try to evaluate ( eval ) the contents of the curly braces much like the eval command, and so it sees a

echo ${'pc'.$i};

as

echo $pc???; where ??? is whatever is carried by $i;

using the {} or the eval method, you get enormous flexibilty and much coding economics.
hope this helps.

p/s left out the last totaling part for you. don't want to take the fun away :).

Re: A better way of adding up?!

Posted: Fri Mar 06, 2009 4:52 am
by kdidymus
Thank you. I can now retain what little hair I have left.

Thanks again.

KD.