Jump to content

captaink

New Members
  • Posts

    3
  • Joined

  • Last visited

captaink's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. Hiya, "requinix", thanks for your kind code snippets and suggestions! I got the loop working now after a some re-iterative re-works with your code!!! I know you spent a bit of time on this so greatly appreciated. "mac_gyver" , thanks for your update as well as it helped to figure out where the $Fields was coming from. it seems that if its not set in the calling script before primary.php ( eg. explode("@", $Tables[$TableID]); ) it is not available for primary.php. $Tables[$TableID] is related back to a config.php that has all the Tables and their columns list with incremental TableID for each table. Seems to me that doing this is superfluous because we are passing in $TableID to the function in primary.php which gets all these columns anyways. So i just removed the whole $Fields from primary.php. I know i have said thanks before, but, i just want to give sincere thanks for both of your dedication and to this forum and helping us with lesser skills. It's really great when you can ask for help and get such a rapid response, care in providing breadcrumbs and suggestions to get us along the road further. Now, after all the searching and trying so many things i think i finally am getting a handle on array() and how to access and search them. Would not have been able to do it without your help. With my very best regards, K ps. here's the final code, and it works on all our pages now. //--- primary.php <?php require_once("database.php"); function get_primary($tbl) { $q1 = "SHOW INDEXES FROM `$tbl`"; //------DEBUG ---------------------------------------------- // print($q1. "\n"); //------DEBUG ---------------------------------------------- $r1 = mysql_query($q1); $rows = array( mysql_fetch_assoc($r1)) ; //------DEBUG ---------------------------------------------- // print_r($rows); //------DEBUG ---------------------------------------------- foreach ( $rows as $Array ) { if ($Array['Key_name'] == 'PRIMARY') { $primary[] = $Array['Column_name'] ; //------DEBUG ---------------------------------------------- // print_r($primary); // echo $Array['Key_name'], " is in the primary key\n"; // echo $Array['Column_name'], " is in the Column_name\n"; //------DEBUG ---------------------------------------------- }; return $primary; }; ?>
  2. Hi 'requinix', thanks for your quick reply and code advice, really greatly appreciated. I have done a recursive grep on the whole site sub-dirs and was not able to find out where the authors had set the "global $Fields". I also set the $primary = array();. No luck. I have included the full code you requested further below. eg database.php & primary.php. Being an oracle/mysql/iAS apps DBA and linux admin for many years i went back to basics and thought about the underlying assumptions of what we're trying to achieve. 1) Run a PHP script - creates a function to check if a table has primary key. 2) Execute Function - from any script ( via requires(primary.php) ) 3) Function Action - "return $primary" [mandatory format] (which needs to be this array so all alther site scripts keep working) 4) RDBMS Data - Is it the best result set to be interigated? 5) K.I.S.S - do we really need a complex array() initialisation? So after some playing around it did notice something that might hint to why the authors had used "if ($ARR[3]== "PRI"&in_array($ARR[0],$Fields))". Assuming we dont need to express all the DB_CONNECT stuff and focus on the array() output, there is a diffence between the Array created by either of these mysql_fetch_a* : 1) mysql_fetch_array($r1) // $r1 = 'SHOW COLUMNS FROM PRODUCT_IMAGE'; Array ( [0] => IMAGE_ID [Field] => IMAGE_ID [1] => int(50) [Type] => int(50) [2] => NO [Null] => NO [3] => PRI [Key] => PRI [4] => [Default] => [5] => auto_increment [Extra] => auto_increment ) 2) mysql_fetch_assoc($r1) // $r1 = 'SHOW COLUMNS FROM PRODUCT_IMAGE'; Array ( [Field] => IMAGE_ID [Type] => int(50) [Null] => NO [Key] => PRI [Default] => [Extra] => auto_increment ) Putting my DBA hat on for a minute it seems to me the original author used a crude mysql query to create the arrray. The better option would be to actually query the table Indexes. A table can only have one PRIMARY KEY so any other indexes can be excluded, or if there are NO INDEXES on the table then Rows = 0. I'm pretty ok to read through most ksh/bash/TCL/SQL/PL-SQL/perl/php scripts to get a gist of what they are doing and "tweak" here and there, I've written so many server scripts in ksh/bask/SQL over the years to do this, however, PHP/PERL arrays.... I just can't seem to get my head around them inside loops. Sure i can explode/print them (php.org/phphelp.org tutorials/forums etc) but have not seen anything that does what i've been trying to do. So the better MySQL query (assumes same DB_CONNECT as above): $tbl = 'PRODUCT_IMAGE'; $q1 = "SHOW INDEXES FROM `$tbl`"; $r1 = mysql_query($q1); $arr = mysql_fetch_assoc($r1); print_r($arr); Heres the results: Array ( [Table] => PRODUCT_IMAGE [Non_unique] => 0 [Key_name] => PRIMARY [Seq_in_index] => 1 [Column_name] => IMAGE_ID [Collation] => A [Cardinality] => 8 [Sub_part] => [Packed] => [Null] => [Index_type] => BTREE [Comment] => [Index_comment] => ) [Key_name] => PRIMARY will only ever have one row IF a primary key for the table EXISTS, because Tables can only have one PRIMARY key. If not, even with FK etc INDEXES, string = PRIMARY will not be returned into the array(). So i tried to a few ways to write a while loop (even using your code advice ) but all to no avail. If you don't mind if I use PHP code and some pseudo-code to illustrate: <?php require_once("database.php"); //--- this one is below $tbl = 'PRODUCT_IMAGE'; //--- hard coded for testing function get_primary($tbl) { $q1 = "SHOW INDEXES FROM `$tbl`"; $r1 = mysql_query($q1); $primary = array(); while($Array = mysql_fetch_assoc($r1)) { if ($Array['Key_name'] == in_array($Array['Key_name'],'PRIMARY') //-- I know this is wrong, just want to loop thru, find 'Key_name', test for 'PRIMARY'' { $primary[] = array([0] => '1'); // not sure if this is correct way to convert a string to array. echo "primary=1 \n"; // so i can see if the test works } else { $primary[] = array([0] => '0'); echo "primary=0 \n"; }; return $primary; }; ?> Thanks again 'requinix' for your help( and patience) it is greatly appreciated. If i can get this to work then i will replace the guts of the Function get_primary($tbl) and do some testing from our www front end as well. Have great weekend. Best regards, k ps. Here's the original database.php/primary.php scripts <?php //--- database.php $db = mysql_connect("domain_name.com","username", "password"); mysql_select_db("domain_db_name", $db); ?> <?php //--- primary.php require_once("database.php"); function get_primary($tbl) { global $Fields; $q1 = "show columns from `$tbl`"; $r1 = mysql_query($q1); while($ARR = mysql_fetch_array($r1)) { if ($ARR[3]== "PRI"&in_array($ARR[0],$Fields)) { $primary[] = $ARR[0] ; } } return $primary; } ?>
  3. Platform Type: Debian MySQL Version: 5.5.32 Perl Version : 5.8.8 PHP Version : 5.3 Hi phpfreakers! I've been looking for days (really) to try and figure this out, so now i humbly ask for you help! Hopefully i have included all the things needed as in the "DO's and Don'ts" post. I have search many posts here but have not found anything that helps me to disambiguate the problem IF statement below. Please let me know if any further debug or information is required, I hope i have included enough not to waste anyones time! Many thanks and appreciation for any assistance. Best regards, k Back Story I understand PHP version is old, we can't change that due to contract with domain.com. Yep I'm shocked its so old. I understand mysql_* functions are replaced by PDO, yet again Domain.com does not support PDO. They reckon they are working on it now. The Problem The following code was part of the original site built and appears not to be finding "PRI" string inside its array() construction. I've been trying to figure out how the "if" block works and i am at a loss. From what i can figure out in later tests the in_array() is getting the PRI value but its not doing anything with it. In a Nutshell Is there an easier/simpler way to rewrite the following lines so that i can more readily determine/interrogate the data and return 1 or 0? I.e. If you see the Array() output further below, what i want to do is: 1) Access the " [Key] => PRI " field 2) Search for "PRI" 3) IF EXISTS THEN set $PRS=1 ELSE set $PRS=0 FI Here's the "problem code", I have left out the mysql_ db connect / select db for brevity, it does work just fine though. <?php $tbl = 'PRODUCT_IMAGE'; $q1 = "show columns from `$tbl`"; $r1 = mysql_query($q1); $arr = mysql_fetch_assoc($r1); global $Fields; while($ARR = mysql_fetch_assoc($r1)) { if ($ARR[3]== "PRI"&in_array($ARR[0],$Fields)) //this is line:64 { $primary[] = $ARR[0] ; } return $primary; };?> ERR: PHP Warning: in_array() expects parameter 2 to be array, null given in /uga/www/primary.php on line 64... So from this ERR it appears $Fields is empty, well that's what i think is the problem, honestly I'm not sure. I normally do this stuff very simply in ksh/bash, SQL etc but I'm struggling with this IF statement's syntax. $primary normally returns either 0 or 1 but for "some tables" that have a PRIMARY KEY defined it doesnt work. If i run the following (using the same $tbl,$q1,$r1, $arr from above) like this: var_dump(in_array("PRI", $arr)); echo "\n"; print_r($arr); Here's the results: bool(true) Array ( [Field] => IMAGE_ID [Type] => int(50) [Null] => NO [Key] => PRI [Default] => [Extra] => auto_increment ) I have also tested with strpos like this: $string = $arr['Key']; if (strpos($string, 'PRI') !== false) { $PRS = 1 ; echo "PRS=$PRS \n"; } else { $PRS = 0 ; print("<b>PRS = $PRS</b> "); }; echo '<row>'; echo "\n"; print( $string ); echo "\n"; echo "</row> \n"; Here's the results: PRS=1 <row> PRI </row>
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.