MySQL Forums
Forum List  »  PHP

Updating a table
Posted by: Benjamin Adlard
Date: June 21, 2005 05:01AM

i have 3 tables:

machines:-
>machine_id
>manufacturer_id
>model_id
>price
>size etc

manufacturer:-
>manufacturer_id
>manufactuer_name

model:-
>model_id
>model_name

When i run the code:-

// This page allows the administrator to add a print (product).

require_once ('../../mysql_connect.php'); // Connect to the database.

if (isset($_POST['submit'])) { // Handle the form.

// Check for an image (not required).
if (is_uploaded_file ($_FILES['image']['tmp_name'])) {
if (move_uploaded_file($_FILES['image']['tmp_name'], "../../uploads/{$_FILES['image']['name']}")) { // Move the file over.

echo '<p>The file has been uploaded!</p>';

} else { // Couldn't move the file over.
echo '<p><font color="red">The file could not be moved.</font></p>';
$i = '';
}
$i = $_FILES['image']['name'];
} else {
$i = '';
}

// Check for a size (not required).
if (!empty($_POST['size'])) {
$s = escape_data($_POST['size']);
} else {
$s = '<i>Size information not available.</i>';
}

// Check for a price.
if (is_numeric($_POST['price'])) {
$p = $_POST['price'];
} else {
$p = FALSE;
echo '<p><font color="red">Please enter the print\'s price!</font></p>';
}

// Check for a description (not required).
if (!empty($_POST['description'])) {
$d = escape_data($_POST['description']);
} else {
$d = '<i>No description available.</i>';
}

// Validate the manufacturer.
if ($_POST['manufacturer'] == 'new') {

// If it's a new manufacturer, add the manufacturer to the database.
$query = 'INSERT INTO manufacturer (manufacturer_id, manufacturer_name) VALUES (NULL, ';

// Check for a manufacturer_name.
if (!empty($_POST['manufacturer_name'])) {
$query .= "'" . escape_data($_POST['manufacturer_name']) . "')";
$result = @mysql_query ($query); // Run the query.
$mn = @mysql_insert_id(); // Get the artist ID.
} else {
$mn = FALSE;
echo '<p><font color="red">Please enter the manufacturers name!</font></p>';
}

} elseif ( ($_POST['manufacturer'] == 'existing') && ($_POST['existing'] > 0)) {
$mn = $_POST['existing'];
} else {
$mn = FALSE;
echo '<p><font color="red">Please enter or select the manufacturer!</font></p>';
}

// Validate the model.
if ($_POST['model'] == 'new') {

// If it's a new model, add the model to the database.
$query = 'INSERT INTO model (model_id, model_name) VALUES (NULL, ';

// Check for a model_name.
if (!empty($_POST['model_name'])) {
$query .= "'" . escape_data($_POST['model_name']) . "')";
$result = @mysql_query ($query); // Run the query.
$x = @mysql_insert_id(); // Get the artist ID.
} else {
$x = FALSE;
echo '<p><font color="red">Please enter the model name!</font></p>';
}

} elseif ( ($_POST['model'] == 'existing') && ($_POST['existing'] > 0)) {
$x = $_POST['existing'];
} else {
$x = FALSE;
echo '<p><font color="red">Please enter or select the model!</font></p>';
}

if ($mn && $x && $p) {

// Add the machine to the database.
$query = "INSERT INTO machines (manufacturer_id, model_id, price, size, description, image_name) VALUES ($mn, $x, $p, '$s', '$d', '$i')";

if ($result = @mysql_query ($query)) { // Worked.
echo '<p>The machine has been added.</p>';
} else { // If the query did not run OK.
echo '<p><font color="red">Your submission could not be processed due to a system error.</font></p>';
}

} else { // Failed a test.
echo '<p><font color="red">Please click "back" and try again.</font></p>';
}

} else { // Display the form.
?>
<form enctype="multipart/form-data" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<input type="hidden" name="MAX_FILE_SIZE" value="524288">
<fieldset>
<legend>Fill out the form to add a machine to the catalog:</legend>
<p><b>Manufacturer:</b> Existing <input type="radio" name="manufacturer" value="existing" />
<select name="existing">
<option>Select One</option>
<?php // Retrieve all the manufacturer and add to the pull-down menu.
$query = "SELECT manufacturer_id, CONCAT(manufacturer_name) AS name FROM manufacturer ORDER BY manufacturer_name ASC";
$result = @mysql_query ($query);
while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {
echo "<option value=\"{$row['manufactuer_id']}\">{$row['name']}</option>\n";
}

?>
</select>
<br />
New <input type="radio" name="manufacturer" value="new" />
Manufacturer Name: <input type="text" name="manufacturer_name" size="10" maxlength="30" />

<!-- Manufacturer Select ends -->

<p><b>Model:</b> Existing <input type="radio" name="model" value="existing" />
<select name="existing">
<option>Select One</option>
<?php // Retrieve all the models and add to the pull-down menu.
$query = "SELECT model_id, CONCAT(model_name) AS name FROM model ORDER BY model_name ASC";
$result = @mysql_query ($query);
while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {
echo "<option value=\"{$row['model_id']}\">{$row['name']}</option>\n";
}
mysql_close(); // Close the database connection.
?>
</select>
<br />
New <input type="radio" name="model" value="new" />
Model Name: <input type="text" name="model_name" size="10" maxlength="30" />
</p>

<!-- Model Select ends -->

<p><b>Image:</b>
<input type="file" name="image" />
</p>
<p><b>Size:</b>
<input type="text" name="size" size="30" maxlength="60" />
</p>
<p><b>Price:</b>
<input type="text" name="price" size="10" maxlength="10" />
<br />
<small>Do not include the dollar sign or commas.</small></p>
<p><b>Description:</b>
<textarea name="description" cols="40" rows="5"></textarea>
</p>
</fieldset>
<div align="center">
<input type="submit" name="submit" value="Submit" />
</div>
</form>


...this code updates a new model/manufacturer in the model, manufacturer and machine tables respectively.

BUT, does not update a new manufacturer in the machine table, although a new value is added.

For Example:-

machine_id | manuf..._id | model_id | price etc etc
1 | 6 | 6
2 | 2 | 2

Looking at the manchine table its clear that "6" does not equal the corresponding value in the manufacturer table. The model_id nevertheless is correct. Its duplicating the value from the model_id column.

Again my english isnt great so i hope you can get the gist.

Suggestions are more than welcome

-Ben

Options: ReplyQuote


Subject
Written By
Posted
Updating a table
June 21, 2005 05:01AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.