PHP:MYSQL -- Array not detecting string value

[captaink]

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>

[requinix]

$Fields being empty is one problem, but you haven't posted enough code for us to figure out why.

 

The other is that $ARR is an associative row. That means there is no [0] or [3] and instead, as you can see in the print_r() output, it has a [Field] and [Key]. So

if ($ARR["Key"] == "PRI" && in_array($ARR["Field"], $Fields))
{
	$primary[] = $ARR["Field"];
}

You also need to initialize $primary to an empty array for the case of tables that don't have primary keys. Otherwise your function will return null.

[captaink]

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;
}
?>

Edited July 29, 2016 by captaink

[requinix]

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.

Look just for a case-sensitive "$Fields". It has to be in there somewhere... but if it wasn't then that would explain why the value is null and instead you have to figure out what value should be getting used in there. But it feels like that code is all broken anyways and what you're doing is the correct solution.

 

there is a diffence between the Array created by either of these mysql_fetch_a*

Yup. Generally an associative array is better to work with (you can reference fields by name) but sometimes the indexed or hybrid versions are more useful to have in code.

 

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.

If the code only needs the primary key then yeah, querying the indexes would be better.

 

[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.

No and yes. There can only be one primary key but it could have multiple columns (be a composite key). So in general you would need to get all the rows and possibly reassembly the key using the [seq_in_index]. Which you need to do anyways because of other non-primary keys.

 

 

//-- I know this is wrong

 

...then why are you doing it? It is quite wrong: in_array() checks if a value is in an array (both of those arguments are strings) and returns true or false accordingly (you're comparing the return value with the key name). All you need is a simple comparison:

if ($Array['Key_name'] == '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";          
    };

 

I'm not even sure what you're trying to do there, but I'm pretty sure the function is supposed to return an array of key names. Right now you're making an array of arrays containing '0's and '1's.

 

If the key is part of the primary key then

$primary[] = $Array['Column_name'];

That's it. $primary[] = will add a new value to the array, and that value is the [Column_name]. Using that echo for debugging is fine but it would be more helpful if you outputted the column name at the same time. Like

echo $Array['Column_name'], " is in the primary key\n";

[mac_gyver]

the $Fields array is probably being MAGICALLY created by an extract() statement or register_globals action (thanks php).

 

the OP is showing the tail end of the problem, the code that doesn't work. the code that's requiring primary.php is where to look, and it will take looking at the code or knowing how it is being invoked, since there may be no explicate reference to 'Fields' in it.

 

how is this whole script being invoked? is there a form that you are entering something into a 'Fields' form field? does the main code have an sql query that's using extract() on data that's being fetched from a result set?

 

note: there are a couple of other functions, like extract, that magically create variables, though i don't recall what they are off of the top of my head.

[captaink]

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;  
};
?>