Print from PostgreSQL database

Question

I have database which contains persons and every person can have several contacts (for example ICQ, e-mail etc. ). And I need to print this on my page.

I'm working with PostgreSQL database which contains table kontakty and this table have rows with columns id_osoby (foreign key REFERENCES osoby), id_typy_kontaktu (foreign key REFERENCES typy_kontakty) and kontakt .

$stmt = $db->query("SELECT * FROM kontakty WHERE id_osoby = $row[id_osoby]");
            $row_kontakty = $stmt->fetch();

            $stmt = $db->query("SELECT * FROM typy_kontaktu WHERE id_typy_kontaktu = $row_kontakty[id_typy_kontaktu]");
            $row_id_typy_kontaktu = $stmt->fetch();

          echo "<tr align='left'>";
          echo "<td><span style='color:white'><strong>Kontakt: </strong></span></td>";
          echo "<td><em><strong>&nbsp&nbsp$row_id_typy_kontaktu[nazev]:</strong></em> $row_kontakty[kontakt]</td>";
          echo "</tr>";

But my code print only one person contact, but persons have several contacts and I need to print all of them.

Hope you understand what I wanted to say. Thx for answers :)

anyway, try using $stmt->fetchAll() instead of $stmt->fetch() — Alex Ackerman
– Alex Ackerman, Commented Jan 5, 2013 at 2:59
It sounds like you just need a JOIN or a second query to get the details. — Craig Ringer
– Craig Ringer, Commented Jan 5, 2013 at 5:05

Tometzky · Accepted Answer · 2013-01-07 08:52:54Z

3

You need to learn some basics on how to use database from PHP, as your code is very bad. Find a tutorial for basics of SQL, using database from PHP.

Your code should:

use a join in SQL query and only query once;
use a loop in PHP code to iterate over rows in query results, something like
```
while ( $row = $stmt->fetch() )
{
  echo ...;
}
```
escape data with htmlspecialchars() to avoid cross-site scripting vulnerability;
use prepared statements with bound parameters to avoid SQL injection vulnerabilities (if your code is vulnerable or not depends on who can control $row[id_osoby] variable, but you should always use bound parameters just in case).

edited Jan 7, 2013 at 8:52

answered Jan 5, 2013 at 21:33

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

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

Comments

greg · Accepted Answer · 2013-01-06 12:20:07Z

+1 with Tometzky and I would add you'd better name your tables in plain english using singuar form to ovoid irregular plurals (that's my point of view but it is a way simpler when asking help on internet and I am not an english native speaker either).

Using an existing Object Model Manager like Pomm would make your life a way easier. If you know you are using Postgresql in production, you may want to benefit from its object oriented features and fetch the whole join in one query.

Using Pomm, you could write:

$sql = <<<SQL
SELECT 
  c.*,
  array_agg(ct) AS contact_types
FROM 
  kontakty c
    LEFT JOIN typy_kontaktu ct USING (id_typy_kontaktu)
WHERE 
    id_osoby = ?
GROUP BY
  c.id_typy_kontaktu
SQL;

$results = $connection
    ->getMapFor('Database\Schema\Kontakty')
    ->find($sql, array($row[id_osoby]));
?>
<ul>
<?php foreach($results as $kontakt): ?>
  <li>
    <p><?php echo $kontact['name'] ?>:
      <ul>
<?php foreach($kontakt['contact_types'] as $contact_type): ?>
        <li><?php echo $contact_type['type'] ?> : <?php echo $contact_type['value'] ?></li>
<?php endforeach ?>
      </ul>
    </p>
  </li>
<?php endforeach ?>
</ul>

Collectives™ on Stack Overflow

Print from PostgreSQL database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related