schlagle Posted May 27, 2010 Share Posted 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 |id | name | locusid | probeid | locus |id | name | dnaid | sequenceid | sequence |id | name | germplasmid | libraryid | 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;} } Quote Link to comment Share on other sites More sharing options...
jdavidbakr Posted May 27, 2010 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
schlagle Posted May 27, 2010 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
jdavidbakr Posted May 27, 2010 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
schlagle Posted May 27, 2010 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
jdavidbakr Posted May 27, 2010 Share Posted May 27, 2010 So does it never work or does it work to a certain depth? Quote Link to comment Share on other sites More sharing options...
schlagle Posted May 27, 2010 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
schlagle Posted May 27, 2010 Author Share Posted 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 newclass = locus locus - name locus - sequenceid newclass = sequence newclass = sequence sequence - name sequence - dnaid newclass = dna newclass = dna dna - name dna - sequence which is correct. Just not deep enough Quote Link to comment Share on other sites More sharing options...
jdavidbakr Posted May 27, 2010 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
schlagle Posted May 27, 2010 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
jdavidbakr Posted May 27, 2010 Share Posted May 27, 2010 Your "return" is still bailing out of the loop Quote Link to comment Share on other sites More sharing options...
schlagle Posted May 27, 2010 Author Share Posted 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 PHP Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /www/cgi-bin/swish/test5.php on line 27 PHP Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /www/cgi-bin/swish/test5.php on line 27 PHP Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /www/cgi-bin/swish/test5.php on line 27 PHP Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /www/cgi-bin/swish/test5.php on line 27 PHP Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /www/cgi-bin/swish/test5.php on line 27 PHP Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /www/cgi-bin/swish/test5.php on line 27 PHP Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /www/cgi-bin/swish/test5.php on line 27 PHP Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /www/cgi-bin/swish/test5.php on line 27 PHP Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /www/cgi-bin/swish/test5.php on line 27 PHP Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /www/cgi-bin/swish/test5.php on line 27 PHP Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /www/cgi-bin/swish/test5.php on line 27 PHP Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /www/cgi-bin/swish/test5.php on line 27 PHP Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /www/cgi-bin/swish/test5.php on line 27 PHP Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /www/cgi-bin/swish/test5.php on line 27 PHP Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /www/cgi-bin/swish/test5.php on line 27 PHP Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /www/cgi-bin/swish/test5.php on line 27 PHP Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /www/cgi-bin/swish/test5.php on line 27 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. Quote Link to comment Share on other sites More sharing options...
jdavidbakr Posted May 27, 2010 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
schlagle Posted May 27, 2010 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.