get table data from php/mysql and save as part of a form to another table

Hi,
I hope some one can help with this frustrating but probably simple issue.
I have 2 mysql tables, one called 'country' the other called 'businessdetails'.
On my page I have a form, people will register thier details and post the form back to 'businessdetails' table This works perfectly well so far. On this page I also pull in the country list from the 'country' table as a drop down list, again no problem at all, it works o.k.
Now the problem, ...as part of the users regististration I want them to select thier country from the 'country' dropdown list, register the rest of their details and save everything to 'businessdetails' where there is another 'country' column name which should be populated from the 'country'dropdown box, I just cannot get it to do this, it just posts the array created from the form details but not the country dropdown box..................HELP!

Thanks

re: get table data from php/mysql and save as part of a form to another table

Okay..

1. Try assigning numerical values to each country on that list and that numerical value will thus represent the country they selected. Make this a separate form or combine the tables.

2. Make the 'country' and 'businessdetails' on two separate pages and have them submitted separately.

3. Try not to rely on reading so much data from the tables. Write up an include or something to contain these boxes.

re: get table data from php/mysql and save as part of a form to another table

Welcome to The Scripts!

I absolutely do not agree with the former poster. Having you data in tables makes it a lot easier to maintain. Also having 2 different forms also makes it a lot more difficult to maintain

Further I cannot guess what you are trying to do. In order to get things going post your code here.

And don't forget to enclose the posted code within php or code tags!!. See the Posting Guidelines at the top of this forum for the forum rules.

Ronald :cool:

re: get table data from php/mysql and save as part of a form to another table

Hi.
Thanks for the very prompt responses, I will post up the scripts this evening.
Just to clarify what I'm doing, I know it was a long winded post.:
On the registration page is a form (name, email address, url, etc)
Also an include(d) php script, this display the country list from the 'country' table.
This works.
When I submit the registration form to the 'businessdetails' table, i want it to include the country they would have selected. I just cannot get the 'country' field in 'businessdetails' populated, I'm sure I've got to get the selected country variable into the form, but I don't know how. The rest of the details (name, email address, url, etc) are inserted ok.

Thanks again.

re: get table data from php/mysql and save as part of a form to another table

Hi again, the code as requested, hope this helps. (If it does at least i'll have learnt something)
Thanks
FF

[PHP]
<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">

<?php
include "dbconn.php";
?>

//<?php
//include "countrylist.php";
//?>

<?php
//NORMALLY THE INCLUDE ABOVE IS USED, Below is the script for the countrylist.php
///////////////////////////////////////////////////////////////////////////////
// Doing my Country table query
$query = 'SELECT c.`printable_name` FROM country c';
$selection = mysql_query($query) or die('Query failed: ' . mysql_error());

$query="SELECT c.`printable_name` FROM country c";

$result = mysql_query ($query);
echo "<select printable_name=country value=''>Country</options>";

while($nt=mysql_fetch_array($result)){//Array or records stored in $nt
echo "<option value=$nt[id]>$nt[printable_name]</option>";

}
echo "</select>";

// Output the resulting info

echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "\t<tr>\n";
foreach ($line as $col_value) {
echo "\t\t<td>$col_value</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>\n";


// Free resultset
mysql_free_result($result);

// Closing connection
//mysql_close($link);

//END OF THE NORMALLY INCLUDED SCRIPT
/////////////////////////////////////////////////////////////////////////////////
?>

[/PHP]
[HTML]
<p>
Business Name:<br />
<input type="text" name= "BusinessName" size="25" maxlength="40" value="" />
</p>

<p>
Web site address(URL):<br />
<input type="text" name= "URL" size="25" maxlength="40" value="" />
</p>

<p>
Email Address:<br />
<input type="text" name= "EmailAddr" size="25" maxlength="40" value="" />
</p>

<p>
Password Min 8 chars:<br />
<input type="text" name= "User_Password" size="12" maxlength="32" value="" />
</p>

<input type="Submit" name="Submit" value="Submit">
</p>
</form>
[/HTML]
[PHP]
<?php

//If Submit pressed

if (isset($_POST['Submit']))
{

//Get the data input from the form above

$BusinessName = $_POST['BusinessName'];
$URL = $_POST['URL'];
$EmailAddr = $_POST['EmailAddr'];
$printable_name = $_POST['Country'];

//Now put the information into the Business details table

$query = "INSERT INTO businessdetails SET BusinessName='$BusinessName', URL='$URL', EmailAddr='$EmailAddr', Country='$printable_name'";

$result = mysql_query($query);

//***********Successfull or Not?******************
if ($result) echo "<p>Details successfully added, Thankyou. </p>";
else echo "<P> Failed to register details<?p>";

//Below is a small test to see what's in the array to be submitted

echo '<pre>';
print_r($_POST);
echo '</pre>';

mysql_close();
}


?>
[/PHP]

re: get table data from php/mysql and save as part of a form to another table

What is this statement doing there?
[php]echo "<select printable_name=country value=''>Country</options>";[/php]

The proper format of the select statement is and the text display option is:
[php]echo "<select name='country'>";
echo "<option value=''>Select Country</option>";[/php]

Ronald :cool:

re: get table data from php/mysql and save as part of a form to another table

Hi thanks for the response again.
Sorry, the first line that you mention wasn't supposed to be there, in my frustration I've been trying anything and everything.
As you pointed out, the correct line that you instruct to use has now allowed the country option to be part of the array.
Unless I'm missing something, on the second line you say to insert, if I use that piece of script, the dropdown box just repeats the words 'select country' a couple of hundred times.
So you have certainly moved me forward, thanks, I just now need to get a selected country from the dropdown list attached to the array so that it will insert into the business details table.

FF

re: get table data from php/mysql and save as part of a form to another table

You should only construct the 'Select country' option once, at the start of the drop down box. I reworked tat particular part of your code a bit, hope that makes it clearer:

[php]
// perform the query
$result = mysql_query ($query)
or die("Query error: ".mysql_errror());;

// start the drop down box
echo "<select name='country'>";

// echo the top content of the drop down box
echo "<option value=''>Select Country</option>";

// echo every country in the array
while ($nt=mysql_fetch_array($result)){ //Array or records stored in $nt
echo "<option value='".$nt['id']."'>".$nt['printable_name']."</option>";
}

// close the drop down box
echo "</select>";
[/php]

Ronald :cool:

re: get table data from php/mysql and save as part of a form to another table

Yes that is clearer now and the dropdown is good.
Maybe the final furlong?....
I still don't get how when i make a selection from the dropdown, e.g. Canada, to save as part of the form into my business details table. I just cannot see how to get it posted with the other array items.
I can't see for the life of me how I make that connection work
At the bottom of my script I use the following to see what in the array will get submitted to the Business details table:

[PHP]echo '<pre>';
print_r($_POST);
echo '</pre>';
[/PHP]

This always shows the following:--- (note country is not populated even though a selection was made).


Details successfully added, Thankyou.

Array
(
[BusinessName] => xyz ltd
[url] => www.xyz.com
[EmailAddr] => xyz@xyz.com
[User_Password] => 12345678
[country] =>
[Submit] => Submit
)


I notice that even though I REM out the 'country item in the post section of the form, it is not used anyway as it was declared earlier e.g.

[PHP]$country = $_POST['country'];[/PHP]
is not required, so where do i get the data from to get it to be inserted, the query insert looks like this and it works ok for all the other bits of the form:

[PHP]$query = "INSERT INTO businessdetails SET BusinessName='$BusinessName', URL='$URL', EmailAddr='$EmailAddr', country='$country'";[/PHP]
Thanks again for your support