Click here to Skip to main content
14,422,889 members
Rate this:
Please Sign up or sign in to vote.
Hello
i am currently trying to make a relational database in PHPmyadmin so i can have a user save there information in 1 table and then in a 2nd table i am trying to have a user be able to save their phone number.
I will connect the 2 tables with a foreign key of userID and/or phoneID.
I made a button on my form right now its standard 1 textbox and label for a phone number. i used javascript so i can dynamically add another textbox and label to the form to have more then one phone number for a user.
My problem is creating a database table that will automatically create another column in the table for a 2nd phone number. Also inserting data into both of the tables because i am not sure how many phone numbers a user may want to have.
You can click the button as many times as you want to keep adding textboxes for more numbers.
I just want to be able to save as many phone numbers as the user wants and to connect both tables.
Here is my code.
<form method="post" id="userProfileForm">
				<fieldset>
					<legend>Information</legend>
					<div class="section">
						<label for="firstName">First name:</label>
						<input type="text" name="firstName" id="firstName" placeholder="Enter your name" />
						<br><br>
						<label for="lastName">Last name:</label>
						<input type="text" name="lastName" id="lastName" placeholder="Enter last name" />
						<br><br>
						<label for="address">Address:</label>
						<input type="text" name="address" id="address" placeholder="Enter your address" />
						<br><br>
						<label for="email">Email:</label>
						<input type="text" name="email" id="email" placeholder="Enter your email" />
						<br>
					</div>
					
					<!-- all of the phone numbers -->
					<h5>Phone number(s)</h5>
					<label for="areaHome">Home Phone:</label>
					<input type="text" name="areaHome" id="areaHome" placeholder="Area code" />
					<input type="text" name="homePhone" id="homePhone" placeholder="Phone Number" />
					<br>
					<div id="addPhone">
						<input type="button" name="addnumber" id="addnumber" value="Add Number" onclick="NewPhoneData()" />    <!-- ; -->
						<br>
					</div> with a submit button at the bottom


function NewPhoneData()
           {
               var lblPhone, txtPhone;
               lblPhone = document.createElement('label');
               lblPhone.appendChild(document.createTextNode("Phone Number "+txtboxcounter));
               txtPhone = document.createElement('input');
               txtPhone.type = 'text';
               lblPhone.appendChild(txtPhone);
               document.getElementById('addPhone').appendChild(lblPhone);
               txtboxcounter++;
           }


//phone number field
               if (empty($_POST['homePhone']))
                 {
                   echo 'ERROR: Please enter a valid employee name';
                   $inputError = true;
               }
               else
                 {
             // make sure the string is properly escaped
                     $homePhone = $mysqli->escape_string($_POST['areaHome'] . $_POST['homePhone']);
                     //echo "It Worked!!";
               }

               if ($inputError != true)
                 {
                   // build our insert query -- we can put php variables directly in the string
                     $sql = "INSERT INTO people (address, email, firstName, lastName, mainPhone)
                      VALUES ('$address', '$email', '$fName', '$lName', '$homePhone')";
                   if ($mysqli->query($sql) === true)
                   {
                     echo 'New employee record added with ID: '  . $mysqli->insert_id;
                   }
                   else
                   {
                     echo "ERROR: Could not execute query: $sql. " . $mysqli->error;
                   }
                 }
Posted

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

You're going about it all wrong. You don't add a column for an additional phone number: You add another phone number record referenced by the same name.

Example:
Table 1: user='bozo' id=7

Table 2:
Phone=5165551212 id=7
Phone=7185551212 id=7
add as many as you want for each user


You can keep a column if in table 2 if you wish to designate a primary phone number (true or false, or number then), You can keep a column for the type of phone (cell, home, office).

Do the same for email, etc.
That's why we call these "relational databases" - look online for an article or description about normalization


It's almost inconceivable that, except in an application designed to create a database as it's primary function, you'd ever want to add columns based upon user needs.
   
Comments
TheBigBearNow 6-Oct-14 14:39pm
   
Ok yes i understand exactly what you are saying. That is what i was wanting to do.
So i just create a bunch of rows in my 2nd table and can fill them up as i need them?

Your example is exactly what i am trying to do with my dynamically created fields.
I will create multiple rows in my 2nd table and find a way to loop through my dynamic fields to insert the data in the table.
How to i make the id reference to be the same in the 2nd table.

right now my table 1 automatically creates a id with auto increment.
in my 2nd table i make a row column for id but i am not sure how to set it.
W Balboos 6-Oct-14 15:03pm
   
For an existing user entry, retrieve their ID when you look them up.

For a new user entry you need to retreive the newly created ID. This can be done (with MS SQLServer) by using the following query right after you insert the new user entry:

SELECT TOP 1 @@IDENTITY FROM yourTableWithNewEntry.

It is important that you do not close your connection between the insert query and the one above. It's return value is the newly created identity for the user and is connection-dependent. What this means is that if two people are using the program at the same time it will not get them mixed up. That is why I did not suggest simply requesting the MAX(id): it can be for the wrong entry.

In either case, you have the ID for the user for tagging all the different types of data you wish.


This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100