PHP MySQLi While Loop

Question

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;

I suggest to use an ORM, such as, Doctirine or Yii's ActiveRecord — Ali MasudianPour
– Ali MasudianPour, Commented Feb 10, 2014 at 20:51
This is what I get (tables, rows and such were translated from danish to english before though): object(stdClass)#6 (22) { ["id"]=> string(1) "2" ["kunde_id"]=> string(1) "2" ["faktura_aar"]=> string(4) "2014" ["faktura_nr"]=> string(3) "006" ["dato"]=> string(10) "2014-02-10" ["debitor"]=> NULL ["reference"]=> NULL ["forsikringsselskab"]=> NULL ["attention"]=> NULL ["email"]=> string(21)} — dinkode.dk
– dinkode.dk, Commented Feb 10, 2014 at 21:00
Sorry to point this out but, you're using unsanitised $_GET data in your queries, which leaves you at risk to SQL injection: ie1.php.net/manual/en/mysqli.real-escape-string.php — Darragh Enright
– Darragh Enright, Commented Feb 10, 2014 at 21:05

kguest · Accepted Answer · 2014-02-10 21:13:11Z

1

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.

answered Feb 10, 2014 at 21:13

kguest

3,8713 gold badges31 silver badges32 bronze badges

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

2 Comments

dinkode.dk Over a year ago

Thank you very much! One question though, why should I use JOIN instead?

kguest Over a year ago

Using JOIN is easier to read than looking at a set of WHERE criteria. As well as that, if you use JOIN (and are combining rows from more than 2 tables) you can control the order in which the data gets combined together and finally you can allow for scenarios where some of the data may not be present in both tables concerned.

Iansen · Accepted Answer · 2014-02-10 20:55:03Z

0

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

answered Feb 10, 2014 at 20:55

Iansen

1,2681 gold badge10 silver badges14 bronze badges

3 Comments

dinkode.dk Over a year ago

Yes that works perfectly, but isn't their a way to SELECT * FROM those tables instead of having to type in every single field?

Iansen Over a year ago

Try SELECT invoices.email as email1, customers.email as email2, * ... I don't know if it works :P

dinkode.dk Over a year ago

Yes, seems to be working fine, but still a mess to type in every field.. By the way, I had to type SELECT invoices.email as email1, customers.email as email2, invoices.*, customers.* FROM...

Bit_hunter · Accepted Answer · 2014-02-10 21:04:03Z

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.

Quixrick · Accepted Answer · 2014-02-10 21:07:44Z

0

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.

answered Feb 10, 2014 at 21:07

Quixrick

3,2101 gold badge16 silver badges17 bronze badges

Collectives™ on Stack Overflow

PHP MySQLi While Loop

4 Answers 4

2 Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related