Using PDO in this script

Question

I am trying to rewrite this script using PDO:

Get values ​​from the database

mysql_select_db($database_db_FPF, $db_FPF); $query_rsWorksMenu = "SELECT works.year, GROUP_CONCAT(workstitle.title) as titulos, GROUP_CONCAT(workstitle.id_title) as links FROM works JOIN workstitle ON works.id_year = workstitle.id_year GROUP BY works.year ORDER BY works.year DESC"; $rsWorksMenu = mysql_query($query_rsWorksMenu, $db_FPF) or die(mysql_error()); $row_rsWorksMenu = mysql_fetch_assoc($rsWorksMenu); 

Show values

  <ul> <?php do { ?> <li><span><?php echo $row_rsWorksMenu['year']; ?></span> <ul> <?php $titulos = explode(",", $row_rsWorksMenu['titulos']); $links = explode(",", $row_rsWorksMenu['links']); foreach(array_combine($links, $titulos) as $link => $titulo){ ?> <li><span><a href="works.php?id=<?php echo $link; ?>"><?php echo $titulo; ?></a></span></li> <?php } ?> </ul> </li> <?php }while ($row_rsWorksMenu = mysql_fetch_assoc($rsWorksMenu)); ?> </ul> 

What I got so far:

Get values ​​from the database:

 $stmt2 = $conn->prepare('SELECT works.year, GROUP_CONCAT(workstitle.title) as titulos, GROUP_CONCAT(workstitle.id_title) as links FROM works JOIN workstitle ON works.id_year = workstitle.id_year GROUP BY works.year ORDER BY works.year DESC'); $stmt2->execute(); $result2 = $stmt2->setFetchMode(PDO::FETCH_OBJ); 

Show values

  <ul> <?php do { ?> <li><span><?php echo $result2->year; ?></span> <ul> <?php $titulos = explode(",", $result2->titulos); $links = explode(",", $result2->links); foreach(array_combine($links, $titulos) as $link => $titulo){ ?> <li><span><a href="works.php?id=<?php echo $link; ?>"><?php echo $titulo; ?></a></span></li> <?php } ?> </ul> </li> <?php }while ($row_rsWorksMenu = mysql_fetch_assoc($rsWorksMenu)); ?> </ul> 

The problem is that I do not know what to do with this line:

 }while ($row_rsWorksMenu = mysql_fetch_assoc($rsWorksMenu)); 

UPDATE:

I am changing my code:

 $stmt2 = $conn->prepare('...query...'); $stmt2->execute(); $result2 = $stmt2->fetchAll(); }while ($row_rsWorksMenu = $stmt2->fetchAll()); 

But nothing happens.

UPDATE 2

I am trying this code:

  <?php do { ?> <li><span><?php echo $result2->year; ?></span> <ul> <?php $titulos = explode(",", $result2->titulos); $links = explode(",", $result2->links); foreach(array_combine($links, $titulos) as $link => $titulo){ ?> <li><span><a href="works.php?id=<?php echo $link; ?>"><?php echo $titulo; ?></a></span></li> <?php } ?> </ul> </li> <?php }while($result2 = $stmt2->fetch()); ?> 

And my DOM shows the correct <li> numbers. But no values. Why?

Answer 1

The easiest way is something like this:

 $sql = 'SELECT name, color, calories FROM fruit ORDER BY name'; foreach ($conn->query($sql) as $row) { print $row['name'] . "\t"; print $row['color'] . "\t"; print $row['calories'] . "\n"; } 

The PDO query method returns a PDOStatement , which you can iterate directly.

http://www.php.net/manual/de/pdo.query.php

Answer 2

Remove the while loop completely. You also do not need to report separately, as your request does not use any query parameters.

Do this instead:

 // Get all your DB results in one place $rsWorksMenu = $conn->query($sql)->fetchAll(PDO::FETCH_OBJ); 

And later, to display the results:

 <?php if ($rsWorksMenu):?> <ul> <?php foreach($rsWorksMenu as $r): $links_titulos = array_combine( explode(",", $r->titulos), explode(",", $r->links) ); ?> <li><span><?=h($r->year)?></span> <?php if ($links_titulos):?> <ul> <?php foreach($links_titulos as $link => $titulo):?> <li><span><a href="works.php?id=<?=h(urlencode($link))?>"><?=h($titulo)?>a></span></li> <?php endforeach // $links_titulos?> </ul> <?php endif //$links_titulos?> </li> <?php endforeach // $rsWorksMenu?> </ul> <?php endif // $rsWorksMenu?> 

You must also change your sql statement - it will break if there are commas in your links or names. ( GROUP_CONCAT is usually a bad idea).

Instead, collect the ungrouped values ​​and group them in the php array yourself, as in the get_worksmenu function below:

 <?php function get_worksmenu(PDO $db) { $rsWorksMenu = array(); $sql = 'SELECT works.year, workstitle.title AS titulos, workstitle.id_title AS links ' .'FROM works INNER JOIN workstitle USING (id_year) ORDER BY year DESC'; // Could this be a single-table query instead? // SELECT id_year AS year, title AS titulos, id_title AS links // FROM workstitle ORDER BY id_year DESC $rs = $db->query($sql); $rs->setFetchMode(PDO::FETCH_ASSOC); foreach ($rs as $r) { // calculating url here simplifies your display code // and makes it easy to change urls later $year = $r['year']; $r['url'] = '/works.php?id='.urlencode($r['links']); unset($r['year']); $result[$year][] = $r; } $rs->closeCursor(); return $rsWorksMenu; } function h($s) { // escape a string for html return htmlspecialchars($s, ENT_QUOTES, 'UTF-8'); } $rsWorksMenu = get_worksmenu($conn); <?php if ($rsWorksMenu):?> <ul> <?php foreach($rsWorksMenu as $year => $works):?> <li><span><?=h($year)?></span> <ul> <?php foreach($works as $work):?> <li><span><a href="<?=h($work['url'])?>"><?=h($work['titulos'])?></a></span></li> <?php endforeach //$work?> </ul> <?php </li> <?php endforeach // $rsWorksMenu?> </ul> <?php endif // $rsWorksMenu?>