CODEWITHSUNDEEP
phpmysqlsqlmysqli
dinkode.dk
dinkode.dk

Reputation: 57

PHP MySQLi While Loop

I am facing some problems when creating my while loop within PHP. I want to be able to pull data out from multiple tables within a while loop. For example I have a table called Invoices and a second table called Customers. Both tables have a field named Email, and whenever I am trying to print or echo that row, then I only get one of them.. Maybe you could have a look at my code.

    <?
    $invoice_edit_query = $db->query("SELECT * FROM invoices, customers 
    WHERE invoice.customer_id = kunder.id AND
    invoices.kunde_id = '$_GET[edit]' AND
    invoices.invoice_year = '$_GET[year]' AND
    invoices.invoice_nr = '$_GET[nr]'") or die(mysqli_error($db));

    while($row = $invoice_edit_query->fetch_object()){
        ?>
        <td>Email: <input type="text" name="email" value="<? print $row->email; ?>"></td>
        <?
    }
    ?>

Is there a way to print the values of my tables like the WHERE statement invoices.email or customers.email? Maybe instead of writing:

    $row->email;

then writing:

    $row->invoices.email;

Upvotes: 1

Views: 7197

Answers (4)

kguest
kguest

Reputation: 3844

You need to alias the fields so that you can reference them seperately:

<?php
$id = (int) $_GET['edit'];
$year = (int) $_GET['year'];
$nr = (int) $_GET['nr'];

$sql = "SELECT customer.email as customer_email, invoices.email as invoices_email, * FROM invoices  
JOIN customers on  customer.customer_id = invoices.id 
WHERE 
invoices.kunde_id = '$id' AND
invoices.invoice_year = '$year' AND
invoices.invoice_nr = '$nr'";
$invoice_edit_query = $db->query($sql);
while($row = $invoice_edit_query->fetch_object()){
    ?>
    <td>Email: <input type="text" name="email" value="<? print $row->customer_email; ?>"></td>
    <?
}
?>

Note I've also added some rudimentary testing to prevent SQL injections.

Upvotes: 1

Quixrick
Quixrick

Reputation: 3200

You can use MySQL's 'DESCRIBE' functionality to get all of the field names.

https://dev.mysql.com/doc/refman/5.0/en/getting-information.html

You can 'DESCRIBE' a table and it will give you each of the fields in the 'Field' column. Loop through each field and add it to your query.

Upvotes: 0

Bit_hunter
Bit_hunter

Reputation: 809

Hey I have done same thing in my project. 

protected function _dynamicBindResult($stmt)
{
    $parameter = array();
    $result = array();

    //get list of all columns in Parameter array
    $count = $stmt->columnCount();
    for ($i = 0; $i < $count; $i++) {
        $temp = $stmt->getColumnMeta($i);
        $parameter[] = $temp['name'];
    }

    //now filter row on basis of parameter array
    while ($row = $stmt->fetch()) {
        $x = array();

        foreach ($parameter as $key => $column) {
            $x[$column] = $row[$column];
        }
        $result[] = $x;
    }

    return $result;
}

Call this function _dynamicBindResult in your project & pass $row as parameter. Then you can use $result->column_name.

Try it. I hope it will surely help you.

Upvotes: 0

Iansen
Iansen

Reputation: 1268

Try "SELECT invoices.email as email1, customers.email as email2, other fields FROM"... and then print those out.

Upvotes: 0

Related Questions