Jump to content


Help getting data from normalized tables

  • Please log in to reply
5 replies to this topic

#1 ryan.od

  • Members
  • PipPipPip
  • Advanced Member
  • 75 posts

Posted 25 September 2006 - 04:16 AM

I'm new to MySQL / PHP and am trying to set up a system where a site visitor can post a recommendation with the following:

  - title
  - description
  - URL link
  - category

I set up the tables using normalization 1, 2, and 3 techniques. This resulted in the title and description being in one table while the links and the categories were in two different tables that used foreign keys to 'connect' to the main table.

My issue is this. I am using Dreamweaver to set up the dynamic side of the site. I expected to be able to create recordsets and  capture the info from the tables and use the 'repeat region' feature to get it to cycle through the tables and get all the info. Unfortunately, the information is nested (I need it to go 'title', 'description', 'link' in that order. DW wouldn't allow that.

What technique do I use to do this? I am wondering about the JOIN feature in MySQL. Is it possible to use an INNER JOIN and then work with that table?

I'm going to post a chunk of code that represents the area I am talking about. I know it doesn't work right and I understand why. I just don't know what approach will get it to work. Thanks for any and all help.

<div id="content_main">
    <div class="art">
    <?php do { ?>
          <?php do { ?>
            <a href="<?php echo $row_Recordset2['url_link']; ?>">
              <?php } while ($row_Recordset2 = mysql_fetch_assoc($Recordset2)); ?>
              <?php echo $row_Recordset3['title_post']; ?>
<?php echo $row_Recordset3['intro_post']; ?>
            <?php } while ($row_Recordset3 = mysql_fetch_assoc($Recordset3)); ?>

        <?php do { ?>
<?php echo $row_Recordset4['cat_category']; ?>
        <?php } while ($row_Recordset4 = mysql_fetch_assoc($Recordset4)); ?>

#2 sasa

  • Staff Alumni
  • Advanced Member
  • 2,804 posts
  • LocationHrvatska

Posted 25 September 2006 - 07:37 PM

can you post structure of your db

#3 ryan.od

  • Members
  • PipPipPip
  • Advanced Member
  • 75 posts

Posted 26 September 2006 - 12:04 AM

Yes, the db structure is attached to this post. Thanks.

recordset2 refers to the reviews_link table
recordset3 refers to the reviews_post table
recordset4 refers to the reviews_category table

[attachment deleted by admin]

#4 redarrow

  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 26 September 2006 - 12:13 AM

Why not change the database to 1 table then insert the information.
Wish i new all about php DAM i will have to learn

#5 ryan.od

  • Members
  • PipPipPip
  • Advanced Member
  • 75 posts

Posted 26 September 2006 - 02:08 AM

I thought that was the entire idea behind database normalization. Whenever there is a one-to-many or many-to-many relationship, the designer should break the table up into smaller tables (each with their own primary key) and link them with foreign keys.

Initially, I used one table (and everything worked fine). Then, after doing some research about db design, I went back and redesigned my dbs so they satisfied db normalization up to normal level 3.

Perhaps I am mistaken, but I was under the impression I was understanding db design pretty well.

#6 HuggieBear

  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 26 September 2006 - 08:41 AM

Ryan, you're spot on!

I can't look at this now as I'm really busy, but I'll check this evening when I get in from work and I'll see what I can do.

Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users