Checking my database

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

Checking my database

Post 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.
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Checking my database

Post 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?
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Checking my database

Post 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.
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Checking my database

Post by kdidymus »

Okay. The problem SEEMS to be that my search is not operating on ANY term with less than FOUR characters.

HAYLP!

KD.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: Checking my database

Post 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
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Checking my database

Post 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.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: Checking my database

Post by Kieran Huggins »

with varchar columns you use LIKE to find stuff, not MATCH AGAINST

MATCH is for text fields only, IIRC
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Checking my database

Post 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.
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Checking my database

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Checking my database

Post 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.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: Checking my database

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Checking my database

Post 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.
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Checking my database

Post 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.
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Checking my database

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Checking my database

Post 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".
Post Reply