SitePoint Sponsor

User Tag List

Results 1 to 25 of 25
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    One to Many Relationship

    Say for example you have a table for recipes that utilize multiple measurements and those measurements are stored in a separate table.

    measure table: id, measure
    recipe table: ing1, measure1_id, ing2, measure2_id, etc.

    This only lists one measurement for each ingredient. How do you query out the data in this instance? Making sure each ingredient has its original measurement?

    Code:
    $query = "SELECT *
    	FROM measurement, recipe
    	WHERE recipe='recipe'
    	AND measurement.id=measure1_id OR
    	measurement.id=measure4_id";

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i would begin by fixing this --
    recipe table: ing1, measure1_id, ing2, measure2_id, etc
    these are repeating groups, and violate first normal form

    you need a RecipeIngredients table with PK (recipeid,ingid)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's what I now realize...
    what's 'PK'?
    Last edited by ecjughead; Sep 3, 2008 at 06:05.

  4. #4
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

  5. #5
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know what a primary key is, I just never have seen it abbreviated

  6. #6
    SitePoint Zealot Aimhigh's Avatar
    Join Date
    Jul 2008
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sometimes abbreviation makes me crazy.

  7. #7
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I made a new table to fix the violation of first normal form. Now instead of building a query to retrieve the data, I'm unsure on how to store it in the database. I've been reading posts about passing arrays through forms and utilizing those techniques but it hasn't been successful.

    Here's one of my lists in my form that would require an array:

    Code:
    "<select name=\"ing[]\" size=\"1\">";
    	$ing_set = @mysql_query('SELECT id, ingredient FROM ingredient');
    	if (!$ing_set) {
    	exit('<p>Error performing query: ' . mysql_error() . '</p>');
    	}
    	while ($row = mysql_fetch_array($ing_set)) {
    	echo "<option name='ing' value=\"{$row['id']}\">{$row['ingredient']}</option>";
    I am then using the following to process:

    Code:
    $data=base64_encode(serialize($ing));?>
    <input type="hidden" name="ing" value="<? echo $data ?>">
    And on the next page:

    Code:
    $ing=unserialize(base64_decode($_POST['data']));
    Receiving undefined index?

  8. #8
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still unable to get this right, anyone have some insight?

  9. #9
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I now have the following:

    Code:
     $ing = array();
     while ($row = mysql_fetch_array($ing_set)) {
       echo "<option name='ing' value=\"{$row['id']}\">{$row['ingredient']}</option>";
        $ing[] = $row;
    }
      
    $data = base64_encode(serialize($ing));
    echo "<input type=\"hidden\" name=\"data\" value=\"$data\" />";
    And this on the process page:

    Code:
    $ing=unserialize(base64_decode($_POST['data']));
       
        foreach ($ing as $ings){
        print_r($ings);
        }
    Receiving this error: unserialize :Error at offset 0 of 6 bytes. Can anyone help?

  10. #10
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    It's probably because you aren't using htmlentities() on $ing.

    But why are you putting an array through $_POST? it makes no sense at all!

    Just requery on the next page.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  11. #11
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't recomend using unserialize() on user supplied data. Users could modify the string and get your script to create objects you didn't intend to.

    While you can pass arrays in forms...
    http://www.php.net/manual/en/faq.htm...aq.html.arrays

    Now you need to revalidate the data to make sure the user hasn't tampered with it. You might as well just query the database again.

    If you really wanted to use unserialize(), you could pass a salted checksum along with it that you can use to validate the string before feeding it back to unserialize().

    Or you can just use sessions and live with the fact that it will not work right if the user has multiple windows open and is doing the same form submission process at the same time.

  12. #12
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm passing an array because the part of the form (below) is asking for multiple pieces of data...ingredient name, amount and measurement. Right now it's only set to 3 for testing. If this is the incorrect approach, how do you capture multiple entries for one data point?

    Code:
    <?php
           $count = 1;
           while ($count <=3) {
    	echo "<tr><td>";
    	echo $count . ":" . 
    	"<select name=\"ing\" size=\"1\">";
    	$ing_set = @mysql_query('SELECT id, ingredient FROM ingredient');
    		if (!$ing_set) {
    		exit('<p>Error performing query: ' . mysql_error() . '</p>');
    		}
    		$ing=array();
    		while ($row = mysql_fetch_array($ing_set)) {
    		echo "<option name='ing' value=\"{$row['id']}\">{$row['ingredient']}</option>";
    		$ing[] = $row;
    		} 
    	       echo "</select></td>";
    	       echo "<td><input type=\"text\" name=\"amount\" value=\"\" id=\"amount1\" size=\"10\" />";
    		echo "</td>";
    	        echo "<td><select name=\"measure[]\" size=\"1\">"; 
    		$measure_set = @mysql_query('SELECT id, measure FROM measurement');
    		if (!$measure_set) {
    		exit('<p>Error performing query: ' . mysql_error() . '</p>');
    		}
    		while ($row = mysql_fetch_array($measure_set)) {
    		echo "<option name='measure' value=\"{$row['id']}\">{$row['measure']}</option>";
    		}
    		echo "</select></td></tr>";
    		$count++;
    		} ?>

  13. #13
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Can you explain what you're trying to do with more description?

    I really don't get what you mean.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  14. #14
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Trying to build a recipe database. One recipe obviously needs multiple entries for ingredients...an array would cover this no?

  15. #15
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Yes, but why are you putting an array in a hidden field?
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  16. #16
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As I was researching this, using hidden fields was used to pass the array. By your question, I would have to think this is wrong.

  17. #17
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Please, just explain what you're trying to achieve by passing the array. You've yet to do so - which means no one can help you with it.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  18. #18
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm simply passing the array to a process page that will save it to my database. I'm using 'print_r' to see if it passed through...not sure what else I can say.

    user enters recipe in form and it saves to the database...the tricky part I found out was the multiple entries for ingredients.

  19. #19
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    So you're passing the array from one form to a page which puts it inside another form?
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  20. #20
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, passing it from one form to a page that inserts all passed data into the database. Are you suggesting another form? Would that be the best solution?

  21. #21
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    My question is, why are you putting it inside a hidden form field?

    If the data comes from the database, you should requery on the second page, not put the fetched data inside a form field.

    If the data comes from the user, you must be using a double form if you're collecting it and putting it inside another form.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  22. #22
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My question is, why are you putting it inside a hidden form field?
    As I stated before, I thought that's how you performed the task in php. If this is wrong, that's ok.

    The code I supplied is part of the form where it reads data from the database which generates a drop-down list to easily pick ingredients. Since this needs to repeat, I need an array. Am I going about this the wrong way?

  23. #23
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Just don't include the hidden field.

    It doesn't have any purpose - you've already got the ingredients in the select box.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  24. #24
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, do I still need to use serialize?

  25. #25
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    No, the array doesn't need to go to the other page.

    You regenerate the array via a query on the other page.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •