Problem on How to insert this to database

geekisthenewsexy · September 27, 2010

hi,

i was having difficulty and i'm quite confused how to insert these values to the database before it will become as it is, based on this article:

http://roshanbh.com.np/2008/01/populate-triple-drop-down-list-change-options-value-from-database-using-ajax-and-php.html

what i did was putting/saving the values directly in the database (mysql). but now i want those values to be coming from the user's input:the country,state and city so the system will just get those values, store it to the database with the fields of each table having the same content like that from article above..i really need some help here.. :-\

chintansshah · September 27, 2010

I don't understand your requirement 100% but I tried.

You should create three different forms, one for Country, State and City.

in Country form, Name field is there

in State form, select country first and then add state name

in City form, first selct country, respected state as per country selection and then enter city details.

geekisthenewsexy · September 29, 2010

thank you, i'll try it out and hopefully get over it.

my main concern is actually the 'id's. i'm having difficulty how it was being inserted into another field on another table,in a column..right now, i'm using mysql_insert_id() function to retrieve the id's previously inserted on one table and insert it in the next one. but i'm still having problems.. here's my code to get data from user's input, this is actually what iv'e come up with only i have course,year,block and room and using dynamic textboxes:

html

<html>
<head>
<script language="JavaScript">	

function AddTextBox()
{
document.getElementById('container').innerHTML+='<input type="text" size="15" maxlength="15" name=block[]><br>';	
}
function AddTextBox2()
{
document.getElementById('container2').innerHTML+='<input type="text" size="15" maxlength="15" name=room[]><br>';	
}

</script>
</head>
<body>
<form name="form1" method="post" action="adnew.php">
<input type="hidden" name="cid">
Course:<input type="text" name="course">
<input type="hidden" name="yid">
Year: <select name="year">
            <option value="1">1</option>
            <option value="2">2</option>
          <option value="3">3</option>
<option value="4">4</option>
</select>
  <table width="23%" border="0">
    <tr> 
      <td width="37%" height="1" colspan="2"> <input type="hidden" name="block_id">
        Block: 
        <input name="button" type="button" onClick="AddTextBox();" value="Add textbox"></td>
    </tr>
    <tr> 
      <td height="21"><div id="container"></div></td>
    </tr>
  </table>
  <table width="23%" border="0">
    <tr>
      <td width="64%">Room: 
        <input name="button2" type="button" onClick="AddTextBox2();" value="Add textbox"></td>
    </tr>
    <tr>
      <td>   <div id="container2"></div></td>
    </tr>
  </table>
  
  <br><br><input type="Submit" name="submit" value=" Add ">
</form> 
</body>
</html>

php

<?php
include("dbcon.php");
?>
<?php
$id_c=$_POST['cid'];
$block=$_POST['block'];
$block_id=$_POST['block_id'];
$room=$_POST['room'];
$intblock=0;
$introom=0;

$sql=mysql_query("INSERT INTO course VALUES ('$id_c','$_POST[course]')") or die (mysql_error());
$id_c = mysql_insert_id(); 

$sql=mysql_query("INSERT INTO year VALUES ('$_POST[yid]','$id_c','$_POST[year]')") or die (mysql_error());
$_POST['yid'] = mysql_insert_id(); 

while(count($block)>$intblock) {
if (($block[$intblock]<>"")){
$sql=mysql_query("INSERT INTO block VALUES ('$block_id', '$_POST[yid]', '".$block[$intblock]."')") or die (mysql_error());
mysql_query($sql);
}
else{
echo "Block ".($intblock+1)." is missing values and cannot be inserted.";
}
$intblock=($intblock+1);
}
$block_id = mysql_insert_id(); ///this is what i'm having problems with..
while (count($room)>$introom) {
if (($room[$introom]<>"")){
$sql=mysql_query("INSERT INTO room VALUES ('$block_id', '".$room[$introom]."')") or die (mysql_error()); // this is the 4th table..
mysql_query($sql);
}
else{
echo "Room ".($introom+1)." is missing values and cannot be inserted.";
}
$introom=($introom + 1);
}

echo "Successfully added.";
echo "<br><a href='index.php'>Add another</a>";
?>

i have 4 tables and using the function to get the unique id,it no longer gets the id on the third table which is supposed to be doing so that this id will be inserted to the field of the 4th table..here:

$block_id = mysql_insert_id(); // i'm having problems with this one.

$sql=mysql_query("INSERT INTO room VALUES ('$block_id', '".$room[$introom]."')") or die (mysql_error()); // this is the 4th table..

i have found out that you have to put the function after an INSERT command,but in my case, i have a switch statement and after i tried putting it inside the switch, i get a message that says "duplicate entry 1 for b_id...etc.." any suggestions? :-\

ignace · September 29, 2010

i get a message that says "duplicate entry 1

You need to add an AUTO_INCREMENT to the PRIMARY KEY column.

geekisthenewsexy · September 30, 2010

yes, i already added that one..it seems that,i have found out after several modifications, my problem is within my INSERT command.

in my html form i put a hidden field for the all the ids and inserted it using $_POST method when it should have been ok not to do so having added auto-increment on them. so i deleted all of them and replaced the variable inside INSERT command with NULL. and all this time i thought my server's screwing with me.

thanks for your help though.

geekisthenewsexy · September 30, 2010

ok, so now the id's being collected from the 3rd table is now on the 4th table. fine.

but here's the sitch:

in the field for "block" and "room", i'm using dynamic textbox since it's difficult to judge how many blocks there are in each year level and how many rooms in each block. now in the table "room", i have two fields namely "b_id" and "room". where b_id here stores the ids from the table "block" and where room stores the value from the user's input. note that room can have many inputs as well as block for each block can have many rooms.

now i entered two inputs for field block (BLOCK AC1-A and BLOCK AC1-B) and four inputs for field room (CLA,LAB,CSM, and GYM). note that CLA and LAB here are the rooms of BLOCK AC1-A and CSM,GYM are rooms of BLOCK AC1-B. so that's it.

but i checked the database, here's what it looks like:

table block:

__________________________

b_id | y_id | block

1 1 BLOCK AC1-A

2 1 BLOCK AC1-B

___________________________

table room:

_________________________

b_id | room

2 CLA

2 LAB

2 CSM

2 GYM

_________________________

as you can see, the last two rows there is correct with b_id 2,2. the first two rows are the problem. it should have been 1,1 because :'(on table block, the first row has an id of 1. i'm guessing because of "mysql_insert_function" which i'm using that im having problems beacause it only gets the last inserted id. do you have any suggestions,advises or solutions?i've surfed the web possible solutions but to no avail..i'm currently trying out chintansshah's advise creating different forms,but i wanted was one form only so the user doesn't have to bother going to another page to fill up.. :'(

Sign In

Problem on How to insert this to database

Recommended Posts

geekisthenewsexy

Link to comment

Share on other sites

chintansshah

Link to comment

Share on other sites

geekisthenewsexy

Link to comment

Share on other sites

ignace

Link to comment

Share on other sites

geekisthenewsexy

Link to comment

Share on other sites

geekisthenewsexy

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information