mysql infinite recursion - on purpose!

schlagle · May 27, 2010

I need a recursive function that will pull column names from a table and, based on that, pull column names from another table and keep doing this for an unknown depth.

For example I have these 4 tables:

(tables names and column names)

allele

locus

sequence

I want to pull the column names from table "allele". If the column name ends in "id" then I want to pull the column names from the table derived from that column name minus the "id" and if those columns end in "id" ... ad infinitum.

Eventually the recursion will stop but I can't seem to get my version of it working past the first iteration of each column that ends in "id";

Here's what I have so far:

function get_col_names($data,$name, $class){

global $connection,$database;
mysql_select_db($database, $connection);

$result = mysql_query("SHOW COLUMNS FROM $class where Field != \"id\"");

while($child_col_name = mysql_fetch_array($result)) {
		$data .= $class . " - " . $child_col_name[0] . "\n";
	if(preg_match('/id$/',$child_col_name[0])){
		$newclass = preg_replace('/id$/','',$child_col_name[0]); //table name derived by removing "id" from columnname
		return get_col_names($data,$name, $newclass);
	}
}

if($data){return $data;}
}

jdavidbakr · May 27, 2010

Eventually the recursion will stop but I can't seem to get my version of it working past the first iteration of each column that ends in "id";
		return get_col_names($data,$name, $newclass);

I believe the problem is that you are returning from the function when you find the first column, so once you find locusid it will exit the function and not continue on to probeid.

schlagle · May 27, 2010

I believe the problem is that you are returning from the function when you find the first column, so once you find locusid it will exit the function and not continue on to probeid.

Originally that line was just:

get_col_names($data,$name, $newclass);

But then I was getting mysql errors because the $newclass var was not being passed to the function. Which seems weird to me. It would call the function alright, but not pass the vars on.

jdavidbakr · May 27, 2010

Try echoing or error_logging your variables right before the recursive call. Make sure $newclass is indeed what you expect it to be at each iteration. Nothing jumps out at me as to why it wouldn't be working.

schlagle · May 27, 2010

I've already tried that. $newclass is indeed correct. If I print out the value of $newclass before the recursive call it is what I want it to be. If I print it out after the recursive call it is empty but the print statement is working and the mysql_fetch_array($result) is complaining because there is no table name variable. So I know that the call is being made. The var is just not being sent.

BTW, thanks for taking the time to help.

jdavidbakr · May 27, 2010

So does it never work or does it work to a certain depth?

schlagle · May 27, 2010

So does it never work or does it work to a certain depth?

Sorry that post above was a bit ambiguous. An example might work better.

Right before

return get_col_names($data,$name, $newclass);

I have this:

$data .= "newclass = $newclass\n";

When I run the script I get this output:

PHP Notice: Undefined variable: newclass in /www/cgi-bin/swish/test4.php on line 33
PHP Notice: Undefined variable: newclass in /www/cgi-bin/swish/test4.php on line 33
PHP Notice: Undefined variable: newclass in /www/cgi-bin/swish/test4.php on line 33
PHP Notice: Undefined variable: newclass in /www/cgi-bin/swish/test4.php on line 33
newclass =
allele - name
allele - size
allele - locusid
newclass = locus
newclass =
locus - name
locus - sequenceid
newclass = sequence
newclass =
sequence - name
sequence - dnaid
newclass = dna
newclass =
dna - name
dna - sequence

It seems to be working great for the first column in each table that the regex '/id$/' hits. But it will not do the rest of the columns in the table. So I can get all the way to the end of recursion but only for 1 column out of each table.

schlagle · May 27, 2010

I should also mention to ignore the "PHP Notice: Undefined variable: newclass in /www/cgi-bin/swish/test4.php on line 33" and the blank "newclass = " lines. They are from another print statement I was using to test the script.

real output from the script would be:

newclass = allele

allele - name

allele - size

allele - locusid

newclass = locus

locus - name

locus - sequenceid

newclass = sequence

sequence - name

sequence - dnaid

newclass = dna

dna - name

dna - sequence

which is correct. Just not deep enough

jdavidbakr · May 27, 2010

Where is the "newclass = " line? Put it right after the preg_replace, and output $child_col_name[0] right before it too.

schlagle · May 27, 2010

I'll just post the script and output.

Script first:

#!/usr/bin/php -q
<?php

$database = "mydatabase";
$connection = mysql_connect("localhost",'user','pass');

$classes = array('allele');
$path = '/www/cgi-bin/path/classlists/';

foreach($classes as $class){
$data = "";
$cfile = $path . $class . ".txt";
$file_handle = fopen($cfile, "r");
while (!feof($file_handle)) {
	$name = rtrim(fgets($file_handle));
	$record = get_col_names($data,$name,$class);
	print_r ($record);die;
}
}

function get_col_names($data,$name, $class){
global $connection,$database;
mysql_select_db($database, $connection);

$result = mysql_query("SHOW COLUMNS FROM $class where Field != \"id\"");

while($child_col_name = mysql_fetch_array($result)) {
		$data .= $class . " - " . $child_col_name[0] . "\n";
	if(preg_match('/id$/',$child_col_name[0])){
		$newclass = preg_replace('/id$/','',$child_col_name[0]); //table name derived by removing "id" from columnname
		$data .= "child_col_name = " . $child_col_name[0] . "\n";
		$data .= "newclass = $newclass\n";
		return get_col_names($data,$name, $newclass);

	}

}
if($data){return $data;}
}
?>

That outputs this:

allele - name
allele - size
allele - locusid
child_col_name = locusid
newclass = locus
locus - name
locus - sequenceid
child_col_name = sequenceid
newclass = sequence
sequence - name
sequence - dnaid
child_col_name = dnaid
newclass = dna
dna - name
dna - sequence

Now that is fine as far as it goes but the table "allele" has another column probeid that should also be followed. And the table "locus" has several columns ending in "id" that should be followed.

jdavidbakr · May 27, 2010

Your "return" is still bailing out of the loop

schlagle · May 27, 2010

Yes it is. But removing "return" from that line outputs this:

PHP Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /www/cgi-bin/swish/test5.php on line 27

allele - name

allele - size

allele - locusid

child_col_name = locusid

newclass = locus

allele - probeid

child_col_name = probeid

newclass = probe

$newclass is being definied but not passed to the next iteration.

jdavidbakr · May 27, 2010

You mentioned that you have 4 tables and list three - but you have more than 4 representations of XXXid in your schema you mentioned. Do you have a probe table?

Add this:

  $result = mysql_query("SHOW COLUMNS FROM $class where Field != \"id\"");
  if (mysql_error()) {
    echo mysql_error();
  }

Also, change your $data .= into echos so you can see exactly when those error messages are occurring, and since you're running this from the command line.

schlagle · May 27, 2010

Oh boy. Listing the error has given me lots of debugging to do. I just assumed the errors were the blank $newclass var so I didn't print the mysql error. Seems there is more going on with those tables then I thought.

Thanks for your help. I'll have to tackle these issues and see if that helps at all.

Sign In

mysql infinite recursion - on purpose!

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information