it seems like array gets unset in php

Question

I have a long php script that runs on a linux backend to update a database with certain values from another. However in my loop it gets undefined offset the second time around, when I only use the code snippet for loop it works perfect, not sure what else it can be

PHP Snippet:

$schema = $con->query("SELECT INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME, users.customers.Name, users.customers.Server from INFORMATION_SCHEMA.SCHEMATA inner join users.customers on INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME=users.customers.Name order by Server asc;");
while( $row = mysqli_fetch_assoc($schema)){                                                                                                                                     
$sResult[] = $row;                                                                                                                                                          
}

$sLenght = sizeof($sResult);

$rConnectzaTwo = pg_connect("host=*** dbname=*** user=*** password=***") or die("Could not connect");
$rConnectzaFour = pg_connect("host=*** dbname=*** user=hein password=***") or die("Could not connect");
$rConnectzaFive = pg_connect("host=*** dbname=*** user=*** password=***") or die("Could not connect");

for ($i = 0; $i < $sLenght; $i++) {
    $companyName = $sResult[$i]['SCHEMA_NAME'];

    if ($sResult[$i]['Server'] == 'ZA02') {
       $rConnect = $rConnectzaTwo;
    } elseif ($sResult[$i]['Server'] == 'ZA04') {
       $rConnect = $rConnectzaFour;
    } elseif ($sResult[$i]['Server'] == 'ZA05') {
       $rConnect = $rConnectzaFive;
}

PHP Edit Code:

$schema = $con->query("SELECT INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME, users.customers.Name, users.customers.Server from INFORMATION_SCHEMA.SCHEMATA inner join users.customers on INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME=users.customers.Name order by Server asc;");
while( $row = $schema->fetch_assoc()){                                                                                                                                     
$sResult[] = $row;                                                                                                                                                          
}

$sLenght = sizeof($sResult);

$rConnectzaTwo = pg_connect("host=*** dbname=*** user=*** password=***") or die("Could not connect");
$rConnectzaFour = pg_connect("host=*** dbname=*** user=hein password=***") or die("Could not connect");
$rConnectzaFive = pg_connect("host=*** dbname=*** user=*** password=***") or die("Could not connect");

for ($i = 0; $i < $sLenght; $i++) {
    $companyName = $sResult[$i]['SCHEMA_NAME'];

    if ($sResult[$i]['Server'] == 'ZA02') {
       $rConnect = $rConnectzaTwo;
    } elseif ($sResult[$i]['Server'] == 'ZA04') {
       $rConnect = $rConnectzaFour;
    } elseif ($sResult[$i]['Server'] == 'ZA05') {
       $rConnect = $rConnectzaFive;
}

Possible duplicate of [PHP: "Notice: Undefined variable", "Notice: Undefined index", and "Notice: Undefined offset"](https://stackoverflow.com/questions/4261133/php-notice-undefined-variable-notice-undefined-index-and-notice-undef) — Qirel, Jan 19 '18 at 07:36

score 0 · Answer 1 · answered Jan 19 '18 at 07:04

you're mixing mysqli object oriented with procedural

change like this

$schema = $con->query("SELECT INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME, users.customers.Name, users.customers.Server from INFORMATION_SCHEMA.SCHEMATA inner join users.customers on INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME=users.customers.Name order by Server asc;");
while($row = $schema->fetch_assoc()){                                                                                                                                     
$sResult[] = $row;                                                                                                                                                          
}

Thank you, I changed it but still getting PHP Notice: Undefined offset: 5 — Dreamsword981, Jan 19 '18 at 07:26
Mixing procedural and object oriented interfaces in MySQLi works perfectly fine, even though it's not recommended - but it does work. ;-) — Qirel, Jan 19 '18 at 07:36

score 0 · Answer 2 · answered Feb 22 '18 at 21:32

You can clean your code right up and make it simple to manage and read by writing a $con_config lookup array prior to looping your resultset.

$con_config=[
    'ZA02'=>['host'=>'***','dbname'=>'***','user'=>'***','password'=>'***'],
    'ZA04'=>['host'=>'***','dbname'=>'***','user'=>'hein','password'=>'***'],
    'ZA05'=>['host'=>'***','dbname'=>'***','user'=>'***','password'=>'***'],
];

if(!$schema=$con->query("SELECT A.SCHEMA_NAME, B.Name, B.Server FROM INFORMATION_SCHEMA.SCHEMATA A INNER JOIN users.customers B ON A.SCHEMA_NAME=B.Name ORDER BY Server ASC;")){
    // handle the error as you wish
}else{
    while($row=$schema->fetch_assoc()){                      
        if(!isset($con_config[$row['Server']])){
            // handle unlisted server value; no connection credentials for this server  
        }else{
            $credentials=$con_config[$row['Server']];
            if(!$rConnect=pg_connect("host={$credentials['host']} dbname={$credentials['dbname']} user={$credentials['user']} password={$credentials['password']}"){
                // handle the error as you wish: $error = error_get_last(); echo "Connection failed. Error was: ". $error['message']
            }else{
                // ... continue your script and use $rConnect as you like
            }
        }
    }
}

it seems like array gets unset in php

2 Answers2