CODEWITHSUNDEEP
phpmysqlsql
bagofmilk
bagofmilk

Reputation: 1550

PHP MySQL JOIN tables, while loop

I've been reading about MySQL Foreign Keys and using Parent/Child relationship tables.

Below is the table relationship (phpmyadmin). Table "dpuchanges" has a foreign key (PARENT) relationship with table "opendpu", column (ECRNUM).

For some reason I am not pulling any data (see php code below). I'm thinking its my SQL statement, but I cannot figure out where it went wrong. I'm not receiving any PHP errors. Can anyone help?

enter image description here

enter image description here

<!DOCTYPE html>
<html>
<head></head>
<body>
  <table>
  <?php
  require ('config.php');

  $db = null;
  $limit = 10;
  $counter = 0;

  while (true) {
  try {
    $db = new PDO($dsn, $uname, $pword);
    $db->exec( "SET CHARACTER SET utf8" );
    $db->setAttribute( PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC ); 
    $db->setAttribute( PDO::ATTR_PERSISTENT, true );
    break;
  }
    catch (Exception $e) {
        $db = null;
        $counter++;
        if ($counter == $limit)
            throw $e;
    }
  }
$stmt = $db->prepare("SELECT ACTION, PARTNO, ACTIONTXT, REV_WAS, REV_NOW, QTY_FROM, QTY_TO FROM dpuchanges JOIN opendpu ON opendpu.ECRNUM = dpuchanges.PARENT WHERE opendpu.ECRNUM = 82095");
$stmt->execute();
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
            $action=$row["ACTION"];
            $partno=$row["PARTNO"];
            $actiontxt=$row["ACTIONTXT"];
            $rev_was=$row["REV_WAS"];
            $rev_now=$row["REV_NOW"];
            $qty_from=$row["QTY_FROM"];
            $qty_to= $row["QTY_TO"];
?>
<tr>
  <td>Action = <?php echo $action; ?></td>
  <td>PartNo = <?php echo $partno; ?></td>
  <td>Description = <?php echo $actiontxt; ?></td>
  <td>REV WAS = <?php echo $rev_was; ?></td>
  <td>REV NOW = <?php echo $rev_now; ?></td>
  <td>QTY FROM = <?php echo $qty_from; ?></td>
  <td>QTY TO = <?php echo $qty_to; ?></td>
</tr>
<?php } ?>
</table>
</body>
</html>

Upvotes: 1

Views: 814

Answers (2)

Leonardo
Leonardo

Reputation: 736

Your datatypes and SIZES of the related columns "dpuchanges.parent" and "openpdu.ecrnum" MUST be the same.

Source: http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html

Upvotes: 1

Mihai
Mihai

Reputation: 26784

I think you are missing ;

  <td>Action = <?php echo $action; ?></td>
  <td>PartNo = <?php echo $partno; ?></td>
  <td>Description = <?php echo $actiontxt; ?></td>
  <td>REV WAS = <?php echo $rev_was; ?></td>
  <td>REV NOW = <?php echo $rev_now; ?></td>
  <td>QTY FROM = <?php echo $qty_from; ?></td>
  <td>QTY TO = <?php echo $qty_t; ?></td>

Upvotes: 1

Related Questions