SitePoint Sponsor

User Tag List

Results 1 to 21 of 21

Thread: Family Tree

  1. #1
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Family Tree

    just saw the SQL forum page sorry for posting in the wrong forum...

    Hi everyone I am very new to the whole database deal. I always learn best by just jumping in and figuring it out on my own. I was thinking a good way to learn would be to make a family history style website. I really have no clue if it is possible or not but I want to make database with all the members of my family that would include stuff like
    Name, DOB, where they were born, Father. Mother, Spouse, Children, where they lived, and maiden name, etc.
    and then make a page that would retrieve all this info and and

    I have no clue and im sure it will piss me of more often then not but this is what i am wanting to do to try and learn to use php, mysql and html all in one. I have no idea how to retrieve info. from a database.
    I HAVE NO EXPERIENCE WITH SQL I'M OK WITH PHP AND HTML... ANY HELP WOULD BE GREATLY APPRECIATED. THANKS

  2. #2
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Best to think of tables as models of real-world entities. In the case of lineage, think of a table as a person.
    Code sql:
    CREATE TABLE person (
        -- Start with an identity column.  I used a bigint because there are lots of people
        -- out there... auto-increment means it will automatically increase 1, 2, 3, etc..
        -- as you add records to the table.  Primary key means it's unique, indexed, and
        -- the identity column of this table.  
        id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     
        -- First, Middle, Last is a western convention for names, but suits most table
        -- designs.  You could go with name/surname if you want to get technical
        first_name VARCHAR(255) NOT NULL,
        middle_name VARCHAR(255) NULL,
        last_name VARCHAR(255) NOT NULL,
     
        -- Gender can simply be a CHAR, but can also reference
        -- A gender table (which can also be indexed by a char)
        gender_id CHAR(1) NOT NULL DEFAULT 'M',
     
        -- DOB can just be a date field
        dob DATE NULL,
     
        -- Father and Mother reference other person records.
        -- This allows a recursive tree whereby we can search infinite
        -- chains of relationships.  Children of a record will always have
        -- a father id or mother id referencing the parent.
        father_id BIGINT NULL REFERENCES person (id),
        mother_id BIGINT NULL REFERENCES person (id),
     
        created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) TYPE=InnoDB;

    A point of note.... you may prefer instead of the single-table approach to go with a multi-table approach, e.g.:

    Code sql:
    -- Note no father_id or mother_id columns
    CREATE TABLE person (
        id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        first_name VARCHAR(255) NOT NULL,
        middle_name VARCHAR(255) NULL,
        last_name VARCHAR(255) NOT NULL,
        gender_id CHAR(1) NOT NULL DEFAULT 'M',
        dob DATE NULL,
        created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) TYPE=InnoDB;
     
    -- This table models parent-child relationships
    -- One person can have multiple parents, one parent can have multiple children
    -- This is a traditional many-to-many composite-key relationship
    CREATE TABLE person_parent (
        person_id BIGINT NOT NULL REFERENCES person (id),
        parent_id BIGINT NOT NULL REFERENCES person (id)
    ) TYPE=InnoDB;

    The advantage of this type of approach is multi-faceted. First, it would allow a simple join to find all children of a parent, and wouldn't complicate matters of gender implication in the "Mother" and "Father" roles defined in the single-table approach. E.g., what if a person has 2 mothers (non-traditional household). And why should role even be implied in the relationship? "Parent" seems to be enough (no need to store "father" / "mother" role information... parent's gender should be enough for most databases, without getting too personal).

    So there you have it! That's a start... I'm sure you're gonna have a lot more questions... it's a complicated DB structure to use.

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your fast reply! I'm pretty sure I have the database set up right. However when I was creating the tables I got this 3 warnings deal?
    Code SQL:
    CREATE TABLE person (
        ->     id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        ->     first_name VARCHAR(255) NOT NULL,
        ->     middle_name VARCHAR(255) NULL,
        ->     last_name VARCHAR(255) NOT NULL,
        ->     gender_id CHAR(1) NOT NULL DEFAULT 'M',
        ->     dob DATE NULL,
        ->     created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        -> ) TYPE=InnoDB;
    Query OK, 0 ROWS affected, 3 warnings (0.03 sec)
    CREATE TABLE person_parent (
        ->     person_id BIGINT NOT NULL REFERENCES person (id),
        ->     parent_id BIGINT NOT NULL REFERENCES person (id)
        -> ) TYPE=InnoDB;
    Query OK, 0 ROWS affected, 3 warnings (0.02 sec)

    I have been able to add and delete people from the database so it seem to work fine. Thank you!

    This what I have so far for the php page to add people to the database, and its not working
    Code PHP:
    <?php
     // Connects to Database family 
     
     mysql_connect("localhost", "root", "***********") or die(mysql_error()); 
     
     mysql_select_db("family") or die(mysql_error()); 
     
      // checks if the person is already in database
     
     	if (!get_magic_quotes_gpc()) {
     
     		$_POST['first_name'] = addslashes($_POST['first_name']);
    		$_POST['middle_name'] = addslashes($_POST['middele_name']);
    		$_POST['last_name'] = addslashes($_POST['last_name']);
     
     	}
     
     $fist_namecheck = $_POST['first_name'];
     $middle_namecheck = $_POST['middle_name'];
     $last_namecheck = $_POST['last_name'];
     
     $check = mysql_query("SELECT first_name FROM first_name WHERE first_name = '$first_namecheck'")
     $check = mysql_query("SELECT middle_name FROM middle_name WHERE middle_name = '$middle_namecheck'")
     $check = mysql_query("SELECT last_name FROM last_name WHERE last_name = '$last_namecheck'")
     
    or die(mysql_error());
     
     $check2 = mysql_num_rows($check);
     
     
     
     //if the name exists it gives an error
     
     if ($check2 != 0) {
     
     		die('Good news, '.$_POST['first_name'].' '.$_POST['middle_name'].' '.$_POST['last_name'].' is already in the database.');
     
     				}
     
     // insert it into the database
     
     	$insert = "INSERT INTO persom (first_name, middle_name, last_name, gender_id, dob,)
     
     			VALUES ('".$_POST['first_name']."', '".$_POST['middle_name']."', '".$_POST['last_name']."', '".$_POST['gender_id']."', '".$_POST['dob']."')";
     
     	$add_member = mysql_query($insert);	
     
    <h1>Congrats</h1>
     
     <p>Thank you, you have been added to the database</a>.</p>
     
     <?php 
     } 
     
     else 
     {	
     ?>	
     
     <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
     
     <table border="0">
     
     <tr><td>First Name:</td><td>
     
     <input type="text" name="first_name" maxlength="60">
     
     </td></tr>
     
     <tr><td>Middle Name</td><td>
     
     <input type="text" name="middle_name" maxlength="60">
     
     </td></tr>
     
     <tr><td>Last Name:</td><td>
     
     <input type="text" name="last_name" maxlength="60">
     
     <tr><td>Gender:</td><td>
     
    <input type="radio" name="gender_id" value="M">Male
    <input type="radio" name="gender_id" value="F">Female
     
    <tr><td>Date of Birth:</td><td>
     
     <input type="text" name="dob" maxlength="60">
     
     </td></tr>
     
     <tr><th colspan=2><input type="submit" name="submit" 
    value="Register"></th></tr> </table>
     
     </form>
     
     
     <?php
     
     }
     ?>

    I get Parse error: parse error in C:\wamp\www\FamilySQL\includes\add.php on line 23

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    PHP Code:
    if (!get_magic_quotes_gpc()) {
      
    $_POST['first_name'] = addslashes($_POST['first_name']);
      
    $_POST['middle_name'] = addslashes($_POST['middele_name']);
      
    $_POST['last_name'] = addslashes($_POST['last_name']);

    Don't use addslashes to sanitize the user input for mysql. Use mysql_real_escape_string() instead:
    PHP Code:
    // if for some obscure reason magic quotes is enabled, get rid of those extra slashes
    if (get_magic_quotes_gpc()) {
      
    $_POST['first_name'] = stripslashes($_POST['first_name']);
      
    $_POST['middle_name'] = stripslashes($_POST['middele_name']);
      
    $_POST['last_name'] = stripslashes($_POST['last_name']);

    // then, sanitize the user input with mysql_real_escape_string
    $fist_namecheck  mysql_real_escape_string($_POST['first_name']);
    $middle_namecheck mysql_real_escape_string($_POST['middele_name']);
    $last_namecheck mysql_real_escape_string($_POST['last_name']); 
    Then let's take a look at the part that checks if the person already exists (although you might want to add a unique index on the person table for that):

    PHP Code:
    $check mysql_query("SELECT first_name FROM first_name WHERE first_name = '$first_namecheck'")
    $check mysql_query("SELECT middle_name FROM middle_name WHERE middle_name = '$middle_namecheck'")
    $check mysql_query("SELECT last_name FROM last_name WHERE last_name = '$last_namecheck'")
     
    or die(
    mysql_error());
     
    $check2 mysql_num_rows($check); 
    You're missing some ; there. And anyway, this is not the way to check if there's a row in the table with those values for those three columns. You have to use one query like this:
    PHP Code:
    $query "
      SELECT 
          first_name
        , middle_name
        , last_name
      FROM person
      WHERE first_name = '
    $first_namecheck'
      AND   middle_name = '
    $middle_namecheck'
      AND   last_name = '
    $last_namecheck'
    "
    ;
    $check mysql_query($query) or die(mysql_error() . " in query $query");
     
    $check2 mysql_num_rows($check); 
    And in the insert query, use $first_namecheck etc instead of the $_POST variables.

    Now, I'm sure there are other problems in that script (for example, you don't check if the form has been submitted), but first try these changes.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by transio View Post
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    i like how you waste 4 bytes per row for the first two billion people in the table...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i like how you waste 4 bytes per row for the first two billion people in the table... :d
    It actually should have been UNSIGNED... then would have been wasting for the first 4 billion!

  7. #7
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PS - bigint for user tables is a habit formed from working with FB connect... good habit to form since that's quickly becoming the login system of choice for FB users.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by transio View Post
    good habit to form since that's quickly becoming the login system of choice for FB users.
    i don't see the connection between facebook's login system and what a conscientious database developer will choose to do for a separate database

    and speaking as a facebook user, i completely despise the concept of a ubiquitous facebook awareness in other web sites, and purposefully always log out of facebook to avoid it

    i mean, come on, it's one thing to have google know all the web sites you visit, but facebook? they're evil, and i don't trust them as far as i can spit
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you guido2004. That fixed the error's I was having and yes I've got more. this is my code now
    Code PHP:
    // checks if the person is already in database
     
    if (get_magic_quotes_gpc()) { 
      $_POST['first_name'] = stripslashes($_POST['first_name']); 
      $_POST['middle_name'] = stripslashes($_POST['middele_name']); 
      $_POST['last_name'] = stripslashes($_POST['last_name']); 
    }  
    // sanitize the user input with mysql_real_escape_string 
    $fist_namecheck  = mysql_real_escape_string($_POST['first_name']); 
    $middle_namecheck = mysql_real_escape_string($_POST['middle_name']); 
    $last_namecheck = mysql_real_escape_string($_POST['last_name']);  
     
    $query = " 
      SELECT  
          first_name 
        , middle_name 
        , last_name 
      FROM person 
      WHERE first_name = '$first_namecheck' 
      AND   middle_name = '$middle_namecheck' 
      AND   last_name = '$last_namecheck' 
    "; 
    $check = mysql_query($query) or die(mysql_error() . " in query $query"); 
     
    $check2 = mysql_num_rows($check);  
     
     
     
     //if the name exists it gives an error
     
     if ($check2 != 0) {
     
     		die('Good news, '.$_POST['first_name'].' '.$_POST['middle_name'].' '.$_POST['last_name'].' is already in the database.');
     
     				}
     
     // insert it into the database
     
     	$insert = "INSERT INTO person (first_name, middle_name, last_name, gender_id, dob,)
     
     			VALUES ('".$first_namecheck."', '".$middle_namecheck."', '".$last_namecheck."', '".$gender_id."', '".$dob."')";
     
     	$add_member = mysql_query($insert);	
    	?>
     
    <h1>Congrats</h1>
     
     <p>You have been added to the database</a>.</p>
     
     <?php
    if ($_REQUEST['submitted']==1) {
    // form was submitted
    } else {
    // form not submitted
    }
    ?>	
     
     <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
     
     <table border="0">
     
     <tr><td>First Name:</td><td>
     
     <input type="text" name="first_name" maxlength="60">
     
     </td></tr>
     
     <tr><td>Middle Name</td><td>
     
     <input type="text" name="middle_name" maxlength="60">
     
     </td></tr>
     
     <tr><td>Last Name:</td><td>
     
     <input type="text" name="last_name" maxlength="60">
     
     <tr><td>Gender:</td><td>
     
    <input type="radio" name="gender_id" value="M">Male
    <input type="radio" name="gender_id" value="F">Female
     
    <tr><td>Date of Birth:</td><td>
     
     <input type="text" name="dob" maxlength="60"> (MM-DD-YYYY)
     
     </td></tr>
     
     <tr><th colspan=2><input type="submit" name="submitted" 
    value="Submit"></th></tr> </table>
     
     </form>

    I just learning all of this so thank you for your help! Why would I be getting all this when i try to load the page


    Notice: Undefined index: first_name in C:\wamp\www\familySQL\includes\add.php on line 16

    Notice: Undefined index: middle_name in C:\wamp\www\familySQL\includes\add.php on line 17

    Notice: Undefined index: last_name in C:\wamp\www\flamilySQL\includes\add.php on line 18

    Notice: Undefined variable: first_namecheck in C:\wamp\www\familySQL\includes\add.php on line 27

    Notice: Undefined variable: first_namecheck in C:\wamp\www\familySQL\includes\add.php on line 48

    Notice: Undefined variable: gender_id in C:\wamp\www\familySQL\includes\add.php on line 48

    Notice: Undefined variable: dob in C:\wamp\www\familySQL\includes\add.php on line 48
    Congrats

    You have been added to the database.


    Notice: Undefined index: submitted in C:\wamp\www\familySQL\includes\add.php on line 58
    First Name:
    Middle Name
    Last Name:
    Gender: Male Female
    Date of Birth: (MM-DD-YYYY)

  10. #10
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    You get that because you're using the handle-post-information logic even when post information isnt present.

    First line of your script should be
    PHP Code:
    <?php
    if(isset($_POST['first_name'])) { 
    //If this is true, someone's submitted the form. If not, we dont want to do anything regarding handling the data!

    // checks if the person is already in database
     
    if (get_magic_quotes_gpc()) { 
      
    $_POST['first_name'] = stripslashes($_POST['first_name']); 
      
    $_POST['middle_name'] = stripslashes($_POST['middele_name']); 
      
    $_POST['last_name'] = stripslashes($_POST['last_name']); 

    //etc etc etc....

    if ($_REQUEST['submitted']==1) {
    // form was submitted
    } else {
    // form not submitted
    }

    //Why is this block here if you're not using it? anyway. Lets close our logic here...
    //And now we're done, output the form again.
    ?>
    HTML here....

  11. #11
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i don't see the connection between facebook's login system and what a conscientious database developer will choose to do for a separate database
    If you're using facebook connect as your only login method, and you want to persist user information, the easiest way to do that is to mirror facebook's schema... e.g. to use a bigint for the id. Sure, you could use a regular int, but then you're wasting 8 bytes per row, cause you'll need an indexed bigint fbid anyway (for reference). so why store id INT, fbid BIGINT when you can just store id BIGINT?

    and speaking as a facebook user, i completely despise the concept
    That you subjectively don't like it doesn't mean much from an objective business perspective. It works, it's easy to implement, it adds a dimension of virality to your website, it helps people makes money, so people do it.

  12. #12
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am still getting undefined variables and undefined index. If any of you have time will you please show me what a proper page should look like. I have page's that i think should work but they aren't.
    I want a form that will add people to the 'person' table of the db called 'family' and to the 'person_parent'. Then I want a page that shows all the people in the family to be sort-able by first_name, middle_name and so on. This is the page i have to add people to the database.
    Code PHP:
    <?php
     // Connects to Database family 
     
     mysql_connect("localhost", "root", "********") or die(mysql_error()); 
     
     mysql_select_db("family") or die(mysql_error()); 
     
      // checks if the person is already in database
     
    if (get_magic_quotes_gpc()) { 
      $_POST['first_name'] = stripslashes($_POST['first_name']); 
      $_POST['middle_name'] = stripslashes($_POST['middele_name']); 
      $_POST['last_name'] = stripslashes($_POST['last_name']); 
    }  
    // sanitize the user input with mysql_real_escape_string 
    $fist_namecheck  = mysql_real_escape_string($_POST['first_name']); 
    $middle_namecheck = mysql_real_escape_string($_POST['middle_name']); 
    $last_namecheck = mysql_real_escape_string($_POST['last_name']);  
     
    $query = " 
      SELECT  
          first_name 
        , middle_name 
        , last_name 
      FROM person 
      WHERE first_name = '$first_namecheck' 
      AND   middle_name = '$middle_namecheck' 
      AND   last_name = '$last_namecheck' 
    "; 
    $check = mysql_query($query) or die(mysql_error() . " in query $query"); 
     
    $check2 = mysql_num_rows($check);  
     
     
     
     //if the name exists it gives an error
     
     if ($check2 != 0) {
     
     		die('Good news, '.$_POST['first_name'].' '.$_POST['middle_name'].' '.$_POST['last_name'].' is already in the database.');
     
     				}
     
     // insert it into the database
     
     	$insert = "INSERT INTO 'person' ('first_name', 'middle_name', 'last_name', 'gender_id', 'dob',)
     
     			VALUES ('".$first_namecheck."', '".$middle_namecheck."', '".$last_namecheck."', '".$gender_id."', '".$dob."')";
     
     	$add_member = mysql_query($insert);	
    	?>
     
    <h1>Congrats</h1>
     
     <p>You have been added to the database</a>.</p>
     
     <?php
    if ($_REQUEST['submitted']==1) {
    // form was submitted
    } else {
    // form not submitted
    }
    ?>	
     
     <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
     
     <table border="0">
     
     <tr><td>First Name:</td><td>
     
     <input type="text" name="first_name" maxlength="60">
     
     </td></tr>
     
     <tr><td>Middle Name</td><td>
     
     <input type="text" name="middle_name" maxlength="60">
     
     </td></tr>
     
     <tr><td>Last Name:</td><td>
     
     <input type="text" name="last_name" maxlength="60">
     
      <tr><td>Maiden Name:</td><td>
     
     <input type="text" name="maiden_name" maxlength="60">
     
    <tr><td>Parents:</td><td>
     <input type="text" name="Not sure" maxlength="65"> //HAVE NO CLUE WHAT TO DO HERE
     
     <tr><td>Gender:</td><td>
     
    <input type="radio" name="gender_id" value="M">Male
    <input type="radio" name="gender_id" value="F">Female
     
    <tr><td>Date of Birth:</td><td>
     
     <input type="text" name="dob" maxlength="60"> (MM-DD-YYYY)
     
     </td></tr>
     
     <tr><th colspan=2><input type="submit" name="submitted" 
    value="Submit"></th></tr> </table>
     
     </form>

    I'm not sure where the 'person_id' and 'parent_id' come in to play in the form. These are the table's I made in the db.
    Code SQL:
    -- Note no father_id or mother_id columns
    CREATE TABLE person (
        id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        first_name VARCHAR(255) NOT NULL,
        middle_name VARCHAR(255) NULL,
        last_name VARCHAR(255) NOT NULL,
        maiden_name VARCHAR(255) NULL,
        gender_id CHAR(1) NOT NULL DEFAULT 'M',
        dob DATE NULL,
        created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) TYPE=InnoDB;
     
    -- This table models parent-child relationships
    -- One person can have multiple parents, one parent can have multiple children
    -- This is a traditional many-to-many composite-key relationship
    CREATE TABLE person_parent (
        person_id BIGINT NOT NULL REFERENCES person (id),
        parent_id BIGINT NOT NULL REFERENCES person (id)
    ) TYPE=InnoDB;
    Again I have no clue how to sort what data goes to what form and have never actually had any data sent to the db from php. How does the joining of two tables work?

  13. #13
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is a addition to the last post i made
    The advantage of this type of approach is multi-faceted. First, it would allow a simple join to find all children of a parent, and wouldn't complicate matters of gender implication in the "Mother" and "Father" roles defined in the single-table approach. E.g., what if a person has 2 mothers (non-traditional household). And why should role even be implied in the relationship? "Parent" seems to be enough (no need to store "father" / "mother" role information... parent's gender should be enough for most databases, without getting too personal).
    does ether the 'person_id' or 'parent_id' need to be set as primary key in the table?

  14. #14
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    primary key (person_id, parent_id)

    it's a composite key, consisting of both.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dxm31 View Post
    $insert = "INSERT INTO 'person' ('first_name', 'middle_name', 'last_name', 'gender_id', 'dob',) ...
    that's not going to work at all

    you've got single quotes around both the table and column names, making them strings

    remove them and try again

    in fact, you should test all your sql -- inserts and selects -- outside of php first, to make sure it's working correctly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you guys for your continued help. I was able to fix most of the "undefined variables" yesterday except this on..Notice: Undefined variable: first_namecheck in C:\wamp\www\familySQL\includes\add.php on line 29
    Congrats

    Zachariah has been added to the database. Add another person

    PHP Code:
    <?php
     
    // Connects to Database family 

     
    mysql_connect("localhost""root""********") or die(mysql_error()); 

     
    mysql_select_db("family") or die(mysql_error()); 
     
      if (isset(
    $_POST['submit'])) {
     
      
    // checks if the person is already in database
     
    if (get_magic_quotes_gpc()) { 
      
    $_POST['first_name'] = stripslashes($_POST['first_name']); 
      
    $_POST['middle_name'] = stripslashes($_POST['middele_name']); 
      
    $_POST['last_name'] = stripslashes($_POST['last_name']); 
    }  
    // sanitize the user input with mysql_real_escape_string 
    $fist_namecheck  mysql_real_escape_string($_POST['first_name']); 
    $middle_namecheck mysql_real_escape_string($_POST['middle_name']); 
    $last_namecheck mysql_real_escape_string($_POST['last_name']);  

    $query 
      SELECT  
          first_name 
        , middle_name 
        , last_name 
      FROM person 
      WHERE first_name = '
    $first_namecheck
      AND   middle_name = '
    $middle_namecheck'  //this is line 29(not in real code)
      AND   last_name = '
    $last_namecheck
    "

    $check mysql_query($query) or die(mysql_error() . " in query $query"); 
      
    $check2 mysql_num_rows($check);  



     
    //if the name exists it gives an error

     
    if ($check2 != 0) {

             die(
    'Good news, '.$_POST['first_name'].' '.$_POST['middle_name'].' '.$_POST['last_name'].' is already in the database.');

                     }
                    
     
    // insert it into the database

         
    $insert "INSERT INTO person (first_name, middle_name, last_name, maiden_name, gender_id, dob,)

                 VALUES ('"
    .$_POST['first_name']."', '".$_POST['middle_name']."', '".$_POST['last_name']."', '".$_POST['maiden_name']."', '".$_POST['gender_id']."', '".$_POST['dob']."')";

         
    $add_member mysql_query($insert);    
        
    ?>

    <h1>Congrats</h1>

     <p><?php echo $_POST['first_name'?> has been added to the database</a>. <a href="add.php">Add another person</a></p>
     
     <?php

    // form was submitted
    } else 
    // form not submitted
    {
    ?>    
     
     <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">

     <table border="1">

     <tr><td>First Name:</td><td>

     <input type="text" name="first_name" maxlength="60">

     </td></tr>

     <tr><td>Middle Name</td><td>

     <input type="text" name="middle_name" maxlength="60">

     </td></tr>

     <tr><td>Last Name:</td><td>

     <input type="text" name="last_name" maxlength="60">
     
      <tr><td>Maiden Name:</td><td>

     <input type="text" name="maiden_name" maxlength="60">
     
     <tr><td>Parents:</td><td>
     <input type="text" name="Not sure" maxlength="65">
     
     <tr><td>Gender:</td><td>

    <input type="radio" name="gender_id" value="M">Male
    <input type="radio" name="gender_id" value="F">Female

    <tr><td>Date of Birth:</td><td>

     <input type="text" name="dob" maxlength="60"> (MM-DD-YYYY)

     </td></tr>

     <tr><th colspan=2><input type="submit" name="submit" 
    value="Submit"></th></tr> </table>

     </form>
     <?php
     
    }
     
    ?>
    I took out the single quotes I'm not sure why it was like that in the first place. It says it is successful in adding the person to the database but it is not. I have other pages that work for inserting users and passwords but this one is not working the $insert on both of these pages look the same I'm not sure what is happening to this one. I am able to add to this table using mysql workbench, and the cmd MySQL console

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you've got a dangling comma after dob in the INSERT statement

    that's a syntax error, the query definitely fails, and your php code should have caught it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you've got a dangling comma after dob in the INSERT statement

    that's a syntax error, the query definitely fails, and your php code should have caught it
    what is a dangling comma. Sorry could you please show me what your talking about? The only error i am getting when i submit the form is..Notice: Undefined variable: first_namecheck in C:\wamp\www\familySQL\includes\add.php on line 29

    It give the statement that every thing else was fine and that it has been added, but it not in the db. I have tried changing the connection by putting a db that does not exist and it catches that so its connecting to the database fine i believe its just not inserting the data.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    it's not inserting the data because the INSERT statement has a syntax error
    Code:
    $insert = "INSERT INTO person (first_name, middle_name, last_name, maiden_name, gender_id, dob,)  //there's your dangling comma
                  VALUES ...
    you need to turn on your php error detection
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank for pointing out the comma i cant spot stuff like that yet.. I searched how to turn on error reporting and found this

    ini_set('display_errors',1);
    error_reporting(E_ALL);

    its says to place it anywhere inside the <?php ?> brackets and i did it does not show me any errors and am only getting the undefined variable in line 29 notice. And even after removing the , after dob it is still not adding to the table.

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry, this is the mysql forum, and i don't do php

    you could either ask a moderator to move this thread to the php forum, or else start a new thread over there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •