PHP getting data of table in another way?

Question

I have a total of 42 records in the table. The problem is my one of my column, which indicates the Type of Leave, is call $empType. In my column of that table, the 24th record of $empType is called "Medical Leave" But because of the while loop, the $empType only shows the 42th record, therefore the whole if statement does not work.

I do not want it to show just 24th record, cause I know odbc_fetch_row will work as well, but I want it to loop all the way and capture all the data from each row.

$conn=odbc_connect("employee","","") or die (odbc_errormsg());

$sql1="SELECT * FROM employee WHERE Status='Pending'";
$rs1=odbc_exec($conn,$sql1);
while (odbc_fetch_row($rs1))
{
$leaveID=odbc_result($rs1,"Leave ID");
$empID=odbc_result($rs1,"empID");
$empType=odbc_result($rs1,"TypeOfLeave");
}

if ($status == "Approved" && $empType == "Medical Leave")
{
my code
}

echo $empType;

Can anyone help me get through this? I seriously need to get this done.

I'm using Microsoft access database ODBC.

The code inside the loop basically does nothing, and the code you want to run on each row is outside the loop. What more is there to say? — Jon
– Jon, Commented May 10, 2012 at 9:42
I tried putting the while loop so that it ends after my if loop. But it does not work — user127886
– user127886, Commented May 10, 2012 at 9:44

Berry Langerak · Accepted Answer · 2012-05-10 10:07:39Z

1

<?php
$conn = odbc_connect("employee","","") or die (odbc_errormsg());

$sql1 = "SELECT * FROM employee WHERE Status='Pending'";
$rs1 = odbc_exec($conn,$sql1);

while(odbc_fetch_row($rs1)) {
    $leaveID=odbc_result($rs1,"Leave ID");
    $empID=odbc_result($rs1,"empID");
    $empType=odbc_result($rs1,"TypeOfLeave");
    $status = odbc_result($rs1,"Status"); // added this.


    // moved to the while loop.
    if( $empType === 'Medical Leave' && $status === 'Approved' ) {
        // your code.
    }
}

Also, PHP's ODBC API looks scary, with all the odbc_fetch_row, odbc_result going on. Perhaps it's a good idea to use PDO for this instead? That way, the code would look like this:

<?php
$dbh = new Pdo( 'odbc:MSSQLServer', 'username', 'password' );

$results = $dbh->query( 'SELECT * FROM employee', PDO::FETCH_ASSOC );

foreach( $results as $result ) {
    if( $result['TypeOfLeave'] === 'Medical Leave' && $result['Status'] === 'Approved' ) {
        // your code here.
    }
}

I've not tried using PDO with ODBC so I'm not familiar with bugs, but from what I can tell; any other API than the one you're using is an improvement.

EDIT: If you want to use all rows later (for looping, etc), this is a good alternative:

<?php
$conn = odbc_connect("employee","","") or die (odbc_errormsg());

$sql1 = "SELECT * FROM employee WHERE Status='Pending'";
$rs1 = odbc_exec($conn,$sql1);

$rows = array( );

while(odbc_fetch_row($rs1)) {
    $rows[] = array(
        'leave ID' => odbc_result( $rs1, 'Leave ID' ),
        'empID' => odbc_result( $rs1, 'empID' ),
        'empType' => odbc_result( $rs1, 'empType' ),
        'status' => odbc_result( $rs1, 'Status' ),
    );
}

// $rows now contains *all* rows, which you can loop over later.

// some more code here.

foreach( $rows as $row ) {
    if( $row['status'] === 'Approved' && 'empType' === 'Medical Leave' ) {
        // your code here.
    }
}

edited May 10, 2012 at 10:07

answered May 10, 2012 at 10:01

Berry Langerak

18.9k5 gold badges49 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Berry Langerak Over a year ago

@user127886 No problem. I do see a small issue with the code, though: your query contains "WHERE Status = 'Pending'". Later on, we're checking $status == 'Approved'. It can never be 'Approved', because those results are filtered from the query.

Robot Woods · Accepted Answer · 2012-05-10 09:47:47Z

0

Well as Jon pointed out, your if needs to be inside the while, but also you never define $status so the if would never run no matter where it is

answered May 10, 2012 at 9:47

Robot Woods

5,6872 gold badges24 silver badges30 bronze badges

2 Comments

user127886 Over a year ago

I forgot to put the $status in the code. I guess I will have to figure out something out, i'm currently too dazed to do anything, thank you anyway sir :).

Robot Woods Over a year ago

Do you mean it's really there, just not in the version you posted? Aside from that you just need to move the closing bracket of the while to the end

user849137 · Accepted Answer · 2012-05-10 09:54:40Z

0

You're looping through each row of data in that while yet you have an if statement that holds a condition for a var that is re-declared in each loop of the while outside of the while loop...

You need to have the if inside the while:

$conn=odbc_connect("employee","","") or die (odbc_errormsg());

$sql1="SELECT * FROM employee WHERE Status='Pending'";
$rs1=odbc_exec($conn,$sql1);
while (odbc_fetch_row($rs1))
{
$leaveID=odbc_result($rs1,"Leave ID");
$empID=odbc_result($rs1,"empID");
$empType=odbc_result($rs1,"TypeOfLeave");

if ($status == "Approved" && $empType == "Medical Leave")
{
my code
}//end of if

}//end of while

echo $empType;

answered May 10, 2012 at 9:54

user849137

Collectives™ on Stack Overflow

PHP getting data of table in another way?

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related