Page 1 of 2

Checking my database

Posted: Tue Jun 24, 2008 4:24 am
by kdidymus
My family tree site is growing at an exponential rate. And I'm a little stuck with something.

Each relative is assigned a Unique Reference Number (URN) based on their initials and year of birth. This isn't, as it turns out, ideal but I'm so far through now I'm kind of stuck with it.

What I want to do is build a PHP page which will search my ENTIRE database for an entered URN and tell me if it's already used.

I know that MySQL will not allow me to duplicate the main URN but each row contains parents, siblings and children's URN as well and this is where mistakes could be easily made.

The columns I need to check are:
urn
motherurn
fatherurn
sibling1urn - sibling16urn (inclusive)
spouse1urn
spouse2urn
spouse1child1urn - spouse1child16urn (inclusive)
spouse2child1urn - spouse2child16urn (inclusive)
If the program could just return a "USED" or "AVAILABLE" after checking against a URN sent to it by a form using the GET protocol this would be great.

Any idea how I could do this?

Many thanks.

KD.

Re: Checking my database

Posted: Tue Jun 24, 2008 7:06 am
by kdidymus
Okay. I THINK I've solved this one...

Code: Select all

<?php
/* Program: check.php
 * Desc:    Checks for used URN.
 */
  // Get the search variable from URL
  $var = $_GET['q']; //search term
  $var = ucwords ($var);
 
  // rows to return
$limit=500; 
 
  //connect to your database
  include_once("../*******.inc.php");
  mysql_connect("$host","$user","$password"); //(host, username, password)
 
  //specify database
  mysql_select_db($database) or die("Unable to select database");
 
// Build SQL Query  
$query = "SELECT urn,motherurn,fatherurn,sibling1urn,sibling2urn,sibling3urn,sibling4urn,sibling5urn,sibling6urn,sibling7urn,sibling8urn,sibling9urn,sibling10urn,sibling11urn,sibling12urn,sibling13urn,sibling14urn,sibling15urn,sibling16urn,spouse1urn,spouse1child1urn,spouse1child2urn,spouse1child3urn,spouse1child4urn,spouse1child5urn,spouse1child6urn,spouse1child7urn,spouse1child8urn,spouse1child9urn,spouse1child10urn,spouse1child11urn,spouse1child12urn,spouse1child13urn,spouse1child14urn,spouse1child15urn,spouse1child16urn,spouse2urn,spouse2child1urn,spouse2child2urn,spouse2child3urn,spouse2child4urn,spouse2child5urn,spouse2child6urn,spouse2child7urn,spouse2child8urn,spouse2child9urn,spouse2child10urn,spouse2child11urn,spouse2child12urn,spouse2child13urn,spouse2child14urn,spouse2child15urn,spouse2child16urn FROM tree WHERE MATCH (urn,motherurn,fatherurn,sibling1urn,sibling2urn,sibling3urn,sibling4urn,sibling5urn,sibling6urn,sibling7urn,sibling8urn,sibling9urn,sibling10urn,sibling11urn,sibling12urn,sibling13urn,sibling14urn,sibling15urn,sibling16urn,spouse1urn,spouse1child1urn,spouse1child2urn,spouse1child3urn,spouse1child4urn,spouse1child5urn,spouse1child6urn,spouse1child7urn,spouse1child8urn,spouse1child9urn,spouse1child10urn,spouse1child11urn,spouse1child12urn,spouse1child13urn,spouse1child14urn,spouse1child15urn,spouse1child16urn,spouse2urn,spouse2child1urn,spouse2child2urn,spouse2child3urn,spouse2child4urn,spouse2child5urn,spouse2child6urn,spouse2child7urn,spouse2child8urn,spouse2child9urn,spouse2child10urn,spouse2child11urn,spouse2child12urn,spouse2child13urn,spouse2child14urn,spouse2child15urn,spouse2child16urn) AGAINST ('$var' IN BOOLEAN MODE)";
 
 $numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);
 
// URN Available.
 
if ($numrows == 0)
  {
  echo "<font face='Arial' size='4'>URN $var is <font color='#00FF00'>AVAILABLE</font></font>";
exit;
  }
 
 
// next determine if s has been passed to script, if not use 0
  if (empty($s)) {
  $s=0;
  }
 
// get results
  $query .= " limit $s,$limit";
  $result = mysql_query ($query) or die(mysql_error());
 
// begin to show results set
$count = 1 + $s ;
 
// now you can display the results returned
 
  /* Display results in a table */
  $i=0;
  while ($row = mysql_fetch_array($result))
{
  extract($row);
  $bg = ($i%2) ? $rowht : $rowcl;
    $i++;
  $count++ ;
  echo "<font face='Arial' size='4'>URN $var is <font color='#FF0000'>NOT AVAILABLE</font></font>";
exit; 
 
  }
?>
It seems to work fine, interrogating my entire MySQL database and returning a message dependant on what it finds.

Can anybody see any potential problems with this script?

Re: Checking my database

Posted: Tue Jun 24, 2008 7:25 am
by kdidymus
Okay. I've found a problem.

When I set up a search engine some time ago I switched my MySQL database from an InnoDB type to a MyISAM type.

It would appear that all of the data in the table entered BEFORE I made this switch is being wrongly reported as "available" when I know it's already been taken.

Is this because I'm using a FULL TEXT search and if so, how do I modify my code to search some other way?

Thanks in advance.

KD.

Re: Checking my database

Posted: Tue Jun 24, 2008 8:51 am
by kdidymus
Okay. The problem SEEMS to be that my search is not operating on ANY term with less than FOUR characters.

HAYLP!

KD.

Re: Checking my database

Posted: Tue Jun 24, 2008 9:39 am
by Kieran Huggins
if it's often less than 4 characters, you should use a field type that reflects that. varchar comes to mind.

text columns don't index anything smaller than 4 characters by default, but varchar doesn't impose any such limitations

Re: Checking my database

Posted: Tue Jun 24, 2008 10:02 am
by kdidymus
All of the above columns are VARCHAR. The search still doesn't work.

I've tried taking off the BOOLEAN MODE and creating an index of the above columns but I'm limited to 16 keys. This is clearly not enough.

I'm really stuck this time!

KD.

Re: Checking my database

Posted: Tue Jun 24, 2008 10:07 am
by Kieran Huggins
with varchar columns you use LIKE to find stuff, not MATCH AGAINST

MATCH is for text fields only, IIRC

Re: Checking my database

Posted: Tue Jun 24, 2008 10:26 am
by kdidymus
Kieran.

I did that. And the MySQL Administrator gave an error which said something like OPERAND and could only run the enquiry on one column blah blah blah..

How can I run a SELECT * from tree where ..... like ('$var') enquiry on more than one column?

Thanks in advance. Sorry I'm such a PHP muppet!

KD.

Re: Checking my database

Posted: Tue Jun 24, 2008 10:46 am
by kdidymus
Okay. So I found the solution but it STILL doesn't work. Tried:

SELECT * FROM tree where urn like '$var' or motherurn like '$var' or fatherurn like '$var' etc.

It brought up an error concerning mysql_num_row.

I'm lost.

The boolean search that I have at the moment works BRILLIANTLY but sadly it only works on strings of four or more characters.

KD.

Re: Checking my database

Posted: Tue Jun 24, 2008 11:56 am
by califdon
Aren't all your URNs (motherurn, fatherurn, etc.) except the primary key URN just foreign keys? Unless I missed the point, you only need to check the primary key column, URN. You should never have a foreign key for which there is no primary key.

Re: Checking my database

Posted: Tue Jun 24, 2008 12:26 pm
by Kieran Huggins
I just laughed until I cried.

It took me several reads until I realized the SQL didn't say:

Code: Select all

SELECT * FROM tree WHERE, um, like $var or somethin like $var or ....
tears. really.

I'm curious why you're trying to match against multiple columns.. but regardless, you may need to rebuild your indexes after changing column types.

Re: Checking my database

Posted: Tue Jun 24, 2008 12:38 pm
by Benjamin
I'd seriously start tracking the members by assigning them a unique id from an auto_increment field. You're going to run into untold problems unless you do.

Re: Checking my database

Posted: Tue Jun 24, 2008 2:07 pm
by kdidymus
Astions.

Absolutely. You're right. But the input of ancestors in to the database is a manual process and so long as I have the facility to quickly check each proposed URN for duplicates, it won't be a problem. For an entirely different reason I have paper records of each inidividual added so between the two, the problem won't occur.

Califdon.

Intrigued. I see now what you mean. If I search the primary key (URN) then entries in any column which match $var should be returned.

My problem is still, how do I code the query?

The first bit is simple: SELECT * FROM tree but what comes next?

KD.

Re: Checking my database

Posted: Tue Jun 24, 2008 2:17 pm
by kdidymus
Folks.

In particular, Califdon (yet again) you have saved my bacon. As if to answer my own question, it seems the logic circuits of my brain suddenly kicked in..

Code: Select all

$query = "SELECT * FROM tree WHERE urn LIKE '$var'";
And it works like a dream. Searches the entire database and returns an AVAILABLE or NOT AVAILABLE. So now I can quickly check each proposed URN.

Thank you. Should I ever start again on this project (and let's face it, why not?!) then I'll use an auto increment for the URN but for now, you've given me a nudge in the right direction and I've finally got my head around how MySQL queries operate.

KD.

Re: Checking my database

Posted: Tue Jun 24, 2008 7:47 pm
by califdon
Glad the light came on! ;) Since you're looking for an exact match, it would simply be:

Code: Select all

$query = "SELECT urn FROM tree WHERE urn='$var'";
Then if any rows are returned, it means it found that URN in the table; if zero rows are returned, it means that that URN is not in the table, so it is "available".