Populate Form to many Tables in a Database

aeafisme23 · August 3, 2006

To better understand my issue, I will include all code and small screenshots of precisely what needs to be done. Hence, I am only stuck one part of the code, which is populating add.php's form to populate my database. Only problem is the mysql insert part that screws me up because usually when you insert, it asks you to insert into what table and not individually assign text boxes to populate certain tables in created database.

First here is the database code:
[url=http://www.kindervision.org/anthonydwayne/sql.txt]http://www.kindervision.org/anthonydwayne/sql.txt[/url]

Here is a relational view of my database:
[url=http://www.kindervision.org/anthonydwayne/relationship.jpg]http://www.kindervision.org/anthonydwayne/relationship.jpg[/url]

Now that you understand the database structure we can move on.

--- Dropdown multi-chain select, pulls from database IF manually entered in to mySQL
(dd3.php) below

[code]<?php

$dbservertype='mysql';
$servername='localhost';
// username and password to log onto db server
$dbusername='THISWOULDBEUSER';
$dbpassword='THISWOULDBEPASS';
// name of database
$dbname='THISWOULDBEDBName';

////// DONOT EDIT BELOW /////////
connecttodb($servername,$dbname,$dbusername,$dbpassword);
function connecttodb($servername,$dbname,$dbuser,$dbpassword)
{
global $link;
$link=mysql_connect ("$servername","$dbuser","$dbpassword");
if(!$link){die("Could not connect to MySQL");}
mysql_select_db("$dbname",$link) or die ("could not open db".mysql_error());
}
?>

<html>
<head>
<title>Year/Make/Model Multiple DD - Populated from Form</title>
<SCRIPT language=JavaScript>
function reload(form)
{
var val=form.make.options[form.make.options.selectedIndex].value;
self.location='dd3.php?make=' + val ;
}
function reload3(form)
{
var val=form.make.options[form.make.options.selectedIndex].value;
var val2=form.model.options[form.model.options.selectedIndex].value;

self.location='dd3.php?make=' + val + '&submodel=' + val2 ;
}

</script>
</head>

<body>
<?
///////// Getting the data from Mysql table for first list box//////////
$quer2=mysql_query("SELECT DISTINCT make,make_id FROM make order by make");
///////////// End of query for first list box////////////

/////// for second drop down list we will check if category is selected else we will display all the subcategory/////
$cat=$HTTP_GET_VARS['make']; // This line is added to take care if your global variable is off
if(isset($make) and strlen($make) > 0){
$quer=mysql_query("SELECT DISTINCT model,model_id FROM model where make_id=$make order by model");
}else{$quer=mysql_query("SELECT DISTINCT model,model_id FROM model order by model"); }
////////// end of query for second subcategory drop down list box ///////////////////////////

/////// for Third drop down list we will check if sub category is selected else we will display all the subcategory3/////
$cat3=$HTTP_GET_VARS['submodel']; // This line is added to take care if your global variable is off
if(isset($submodel) and strlen($submodel) > 0){
$quer3=mysql_query("SELECT DISTINCT submodel FROM submodel where model_id=$submodel order by submodel");
}else{$quer3=mysql_query("SELECT DISTINCT submodel FROM submodel order by submodel"); }
////////// end of query for third subcategory drop down list box ///////////////////////////

echo "<form method=post name=f1 action='retrieve.php'>";
////////// Starting of first drop downlist /////////
echo "<select name='make' onchange=\"reload(this.form)\"><option value=''>Select Make</option>";
while($noticia2 = mysql_fetch_array($quer2)) {
if($noticia2['make_id']==@$make){echo "<option selected value='$noticia2[make_id]'>$noticia2[make]</option>"."<br>";}
else{echo "<option value='$noticia2[make_id]'>$noticia2[make]</option>";}
}
echo "</select><br><br>";
////////////////// This will end the first drop down list ///////////

////////// Starting of second drop downlist /////////
echo "<select name='model' onchange=\"reload3(this.form)\"><option value=''>Select Model</option>";
while($noticia = mysql_fetch_array($quer)) {
if($noticia['model_id']==@$submodel){echo "<option selected value='$noticia[model_id]'>$noticia[model]</option>"."<br>";}
else{echo "<option value='$noticia[model_id]'>$noticia[model]</option>";}
}
echo "</select><br><br>";
////////////////// This will end the second drop down list ///////////

////////// Starting of third drop downlist /////////
echo "<select name='submodel' ><option value=''>Select Submodel</option>";
while($noticia = mysql_fetch_array($quer3)) {
echo "<option value='$noticia[submodel]'>$noticia[submodel]</option>";
}
echo "</select><br><br>";
////////////////// This will end the third drop down list ///////////

echo "<input type=submit value=Submit>";
echo "</form>";
?>

</body>

</html>[/code]

This works beautifully, no problems at all, i can populate via mySQL and retrieve all information to retrieve.php. But who in their right mind would enter EVERY single car make, model, and submodel (along with products not in this thread)? No one, so I want to create a form so he can update the make, model, and submodel when he has a product for that exact match.

--- Form that needs to populate Database --- ****this part is the only bad part***
(add.php) below
In this code i took the OO approach with no prevail, by no means is this the only way I want to do it, I just need to figure out how to populate the dd3.php from add.php.

[code]<?php
//test.php
if (isset($_POST['submit'])) {
echo "submitting <br>";
include_once("conn.php");

// new Car MAKE:; replaced title with submodel here
$newmake = new make;
$newmake->submodel = ($_POST['submodel']);
$newmake->PersistNew();

// new Car MODEL replaced fname and firstname with model might have to put in a newmodel to put in make as link
$newmodel = new model;
$newmodel ->model = ($_POST['model']);
// $newmodel ->make = ($_POST['make']);
$newmodel->PersistNew();

// new Car SUBMODEL
$newsubmodel = new submodel;
$newsubmodel ->make = $newmake->id;
$newsubmodel ->model = $newmodel->id;
$newsubmodel->PersistNew();

echo "done, check out the database";
} else {
?>
<html>
<form method="POST" action="add.php">

<table width="720" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#000000"> <tr>
<td>Car Make:</td>
<td><input name="make" /></td>
</tr>
<tr>
<td>Car Model:</td>
<td><input name="model" /></td>
</tr>
<tr>
<td>Car Submodel:</td>
<td><input name="submodel" /></td>
</tr>
<tr>
<td><input type="submit" name="submit" value="submit" /></td>
<td><input type="reset" /></td>
</tr>
</table>
</form>
</html>
<?php
}
?>
</body>
</html>
[/code]

This is the next part that makes it tick:
[code]<?php
// conn.php

// A database persitable object, pretty useless unless you extend it.
class Persistable {

// Unique database identifier.
var $id;
// Table name that is used in the database
var $table;
// Map of column labels in the database to object variables
var $columns = array();

// Persist a new object in the database.
function PersistNew() {
mysql_connect('localhost', 'username', 'password')
or die("Could not connect: " . mysql_error());
mysql_select_db('databasename')
or die ("Unable to select database");

$sql = "INSERT INTO `".$this->table."` ";
$labels = "(`id` ";
$values = ") VALUES ('' ";

foreach ($this->columns as $column) {
$labels .= ", `".$column[0]."` ";
$temp = '$this->'.$column[1];
$values .= ", '"."$temp"."' ";
unset($temp);
}

$sql .= $labels.$values.")";
eval ("\$sql = \"$sql\";");
$result = mysql_query($sql);
$this->id = mysql_insert_id();
}
}// End Class Persistable

class make extends Persistable {

var $make;//String of submodel title.

function make() { //Constructor for make.
$this->table = 'make';
$this->columns = array( array('make','make')
);
}
} // End Class make

class model extends Persistable {

var $model; // String of Cars model.
// var $model; String of Cars Model.

function model() { //Constructor for model.
$this->table = 'model';
$this->columns = array(
array('model','model')
//, array('lastname','lname')
);
}
} // End Class model

class submodel extends Persistable {

var $submodel; // integer model unique id.
// NOTHING should be here? var $author; // integer author unique id.

function submodel() { //Constructor for submodel.
$this->table = 'submodel';
$this->columns = array( array('submodel','submodel')
//,array('author','author')
);
}
} // End Class submodel
?> [/code]

I am SO lost when it comes to getting this to work, although i see no error message (peachy) I am sure i have one of my classes messed up, but I would perfer to do this a non OO way and just hardcode in php.

So my final thoughts are, is there a way to :
$sql = "INSERT INTO make(make_id, make) VALUES ('$make_id','$make')"; and
$sql = "INSERT INTO model (model_id, model) VALUES ('$model_id','$model')"; and
$sql = "INSERT INTO submodel(model_id, submodel) VALUES ('$model_id','$submodell')";
in the same page without having to use Object Oriented programming using something called Persisting data?

I am not asking for code on how to do this, however it would be very much appreciated, i am however looking for LOGIC, i am getting caught up on one small thing and that is just populating a form to many tables. Thank you all.

Extra info: To see a demo of what i want here are some images of the process it would look like.

1)submit user/pass > [url=http://www.kindervision.org/anthonydwayne/login.jpg]http://www.kindervision.org/anthonydwayne/login.jpg[/url]
2)go to add form > [url=http://www.kindervision.org/anthonydwayne/userselect.jpg]http://www.kindervision.org/anthonydwayne/userselect.jpg[/url]
3)submit the make/model/submodel[url=http://www.kindervision.org/anthonydwayne/add.jpg]http://www.kindervision.org/anthonydwayne/add.jpg[/url]
4)see results in the dropdown at [url=http://www.kindervision.org/anthonydwayne/dd3.jpg]http://www.kindervision.org/anthonydwayne/dd3.jpg[/url]

aeafisme23 · August 4, 2006

I suppose too much code below deters people so heres my short question. Is there a way to populate a database from one php page using a form with many textboxes to "many tables" in the "same database" such as
$sql = "INSERT INTO make(make_id, make) VALUES ('$make_id','$make')"; and
$sql = "INSERT INTO model (model_id, model) VALUES ('$model_id','$model')"; and
$sql = "INSERT INTO submodel(model_id, submodel) VALUES ('$model_id','$submodell')";

all on the same page? Is there a logic behind this? Thanks all!!!

HeyRay2 · August 4, 2006

It sounds like you already have the queries written and ready to go. As long as the variables you mention in them exist, you can run a unlimited amount of queries. So, why not just do this:

[code=php:0]
// Write the queries
$sql = "INSERT INTO make(make_id, make) VALUES ('$make_id','$make')";
$sql2 = "INSERT INTO model (model_id, model) VALUES ('$model_id','$model')";
$sql3 = "INSERT INTO submodel(model_id, submodel) VALUES ('$model_id','$submodell')";

// Execute the queries
mysql_query($sql) or die(mysql_error());
mysql_query($sql2) or die(mysql_error());
mysql_query($sql3) or die(mysql_error());
[/code]

aeafisme23 · August 4, 2006

How do I assign the certain textboxes such as Make to be assigned to $SQL and Model to be assigned to query $SQL2 etc...? This has to be an elementary procedure but for the life of me I can not find in my reference book PHP and MySQL -Web development. This would be very helpful if someone responds again, thanks again for the reply, it was very appreciated and a great answer to my question!!!!

Sign In

Populate Form to many Tables in a Database

Recommended Posts

aeafisme23

Link to comment

Share on other sites

aeafisme23

Link to comment

Share on other sites

HeyRay2

Link to comment

Share on other sites

aeafisme23

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information