Database issue [Solved]

[eluser]eiderbrant[/eluser]
Hello!

This is an output from my mysql db

Code:

mysql> SELECT parameterId, dataValue FROM Parameter, Data, NetworkPlan, NetworkPlanRevision
WHERE networkPlanId = 385
AND networkPlanId = networkPlanRevisionNetworkPlan_FK
AND dataNetworkPlan_FK = networkPlanId
AND dataParameter_FK = parameterId
AND networkPlanRevisionRevisionNr = networkPlanRevHead
AND networkPlanRevisionRealLifeStatus LIKE "In Use"
AND parameterName LIKE "%Unique";

+-------------+-------------+
| parameterId | dataValue   |
+-------------+-------------+
|         112 | lidul0688   |
|         112 | LieNb0688   |
|         112 | Lidul0688   |
|         112 | lidul0688   |
|         117 | lienb0688   |
|         117 | LieNb0688   |
|         117 | LieNb0688   |
|         177 | 10.75.0.188 |
|         177 | 10.75.14.26 |
|         190 | Lidul0688   |
|         190 | lidul0688   |
|         190 | LieNb0688   |
|         316 | lienb0688   |
|         316 | lienb0687   |
|         414 | lidul0688   |
+-------------+-------------+
15 rows in set (0.00 sec)

It returns the expected result, but when I make a db query from the PHP code in CI I get zero lines from the db.

Code:

private function isUniqueDataColliding($nnpId)
    {
        $result = array();
        
        // Get all unique parameters to compare
        $sql = "SELECT parameterId, dataValue FROM $this->parameterTable, $this->dataTable, $this->networkPlanTable, $this->networkPlanRevisionTable
                 WHERE networkPlanId = $nnpId
                 AND networkPlanId = networkPlanRevisionNetworkPlan_FK
                 AND dataNetworkPlan_FK = networkPlanId
                 AND dataParameter_FK = parameterId
                 AND networkPlanRevisionRevisionNr = networkPlanRevHead
                 AND networkPlanRevisionRealLifeStatus LIKE \"In Use\"
                 AND parameterName LIKE \"%Unique\" ORDER BY parameterId;";
                
        $this->writeToDebugFile("isUniqueDataColliding - first query: $sql");
        
        $query = $this->CI->db->query($sql);
        $parameters = $query->result();
        
        $this->writeToDebugFile("isUniqueDataColliding - number of parameters to compare in nnp $nnpId : " . $query->num_rows());
        
        if($query->num_rows() > 0)
        {
            
            foreach($parameters as $parameter)
            {
                $sql2 = "SELECT DISTINCT parameterName, dataValue, networkPlanName FROM $this->parameterTable, $this->dataTable, $this->networkPlanTable, $this->networkPlanRevisionTable
                    WHERE networkPlanId != $nnpId
                    AND parameterId = ?
                    AND dataValue LIKE ?
                    AND parameterId = dataParameter_FK
                    AND dataNetworkPlan_FK = networkPlanId
                    AND networkPlanRevisionRevisionNr = networkPlanRevHead
                    AND networkPlanRevisionRealLifeStatus LIKE \"In Use\"
                    AND parameterName LIKE \"%Unique\"";
                    
                $this->writeToDebugFile("isUniqueDataColliding - second query: $sql2");
                
                $query2 = $this->CI->db->query($sql2, array($parameter->parameterId, $parameter->dataValue));
                
                if($query2->num_rows > 0)
                {

                    foreach($query2->result() as $existParameter)
                    {
                        $fault = "<br>STP: $existParameter->networkPlanName <br>
                                    Parameter: $existParameter->parameterName <br>
                                    Parameter value: $existParameter->dataValue";
                        $this->writeToDebugFile("isUniqueDataColliding - fault result: $fault");
                        array_push($result, $fault);
                    }
                    
                    $this->writeToDebugFile("isUniqueDataColliding - Throwing exception");
                    $this->throwCollideException($result);
                }
            }
        }
        
    }

Any ideas?

I´m lost...

[eluser]LuckyFella73[/eluser]
I would recommend to use the active record class of Codeigniter:
http://ellislab.com/codeigniter/user-gui...ecord.html

At first I would check if the variables you have in your method
have the values they should have.

Put this in your controller:

Code:

$this->output->enable_profiler(TRUE);

then you should see the generated query string in your footer.

[eluser]eiderbrant[/eluser]
Thanks for your reply!

I know that the variables are correct.
The database query, posted in the post above, comes from the source code above.
This will produce the query in the debug printout:

Code:

$this->writeToDebugFile("isUniqueDataColliding - first query: $sql");

See after the first sql query.

[eluser]Mirge[/eluser]
[quote author="eiderbrant" date="1309864362"]

Code:

$query = $this->CI->db->query($sql);
        $parameters = $query->result();
        
        $this->writeToDebugFile("isUniqueDataColliding - number of parameters to compare in nnp $nnpId : " . $query->num_rows());
        
        if($query->num_rows() > 0)
        {
            
            foreach($parameters as $parameter)
            {

[/quote]

Change your foreach to foreach($query->result() as $parameter) {...}

At the moment, you're iterating through ONE row only.

[eluser]eiderbrant[/eluser]
Hello.

I´m not iterating over one row:

$parameters = $query->result();

I have solved the problem:

The "networkPlanRevisionRealLifeStatus" state was altered during execution and the consequence was no reply in the execution.

Code:

AND networkPlanRevisionRealLifeStatus LIKE \"In Use\"

Thanks for the replies!