PHP MySQL JOIN tables, while loop

Question

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

<!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>

Leonardo · Accepted Answer · 2013-11-08 15:42:03Z

1

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

answered Nov 8, 2013 at 15:42

Leonardo

7364 silver badges11 bronze badges

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

6 Comments

bagofmilk Over a year ago

I changed PARENT to int(11), so now ECRNUM and PARENT are both int(11), I still get no readings. I think my issue is the SQL statement. Thank you for pointing this out though, I did not know this.

Leonardo Over a year ago

Mmmm... Is it pulling any data if you execute the sql in phpMyAdmin?

bagofmilk Over a year ago

I revised it to only select one category (ACTION) and I was actually able to pull data. So now I am certain its the SQL statement

Leonardo Over a year ago

Try ending the sql sentece like this: opendpu.ECRNUM = '82095';"

bagofmilk Over a year ago

I semi-solved it. I made a single SELECT statement for each category, then looped and posted. So I have 7 different while loops. It works, but is probably inefficient.

|

Mihai · Accepted Answer · 2013-11-08 15:34:53Z

1

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>

edited Nov 8, 2013 at 15:34

answered Nov 8, 2013 at 15:22

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

3 Comments

bagofmilk Over a year ago

haha. I was, but I'm still getting no data. Thank you for pointing that out though as that would have caused errors.

bagofmilk Over a year ago

But I revised my question. I included a picture of the data

bagofmilk Over a year ago

I use this "while loop" technique to display ALL of my data in the tables and it works great. I think my SQL statement is the issue but I cannot figure out where it went wrong. I followed this: sitepoint.com/mysql-foreign-keys-quicker-database-development

Collectives™ on Stack Overflow

PHP MySQL JOIN tables, while loop

2 Answers 2

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related