SQL Query Anomole

WillUK · #1 (**permalink**) 12-01-09, 04:18 AM

Hi,

I am a reasonably experienced developer now but with a very basic query:

I built a CMS for a client, that allows him to add products to his store. Each product is assigned a type (type_id) depending on what type of product is is. Each type_id is accompanied by a type name (type_name).

The CMS originally gave the client the functionality to add their own types, when they saw fit. However, I decided that this approach was not practical due to the way that the front end is presented.

For this reason, I reformatted the form accordingly, removing the option for the client to add new product types.
Instead, I added the types directly to the database.

Using a drop down menu in the form within the CMS, the client could then choose from the product type list that I created.

However, when it came to subsequently posting to the database, the form would not process the data correctly. The relevant tables would simply not populate due to a system error.

This was a headache, and I couldn't understand why.

After trying a number of things to get around the problem I found a solution, although I have no idea why it worked:

I reverted to the form in its original format i.e. with the functionality enabling the client to add his own product type.

I then proceeded to add 6 dummy products via the form, with each one being of a different product type. (6 product types is what I wanted anyway).

I then looked at the database, and noticed that the tables were all correctly populated.

After doing this, I went back to the form and removed the option for the client to be able to add new product types.

Job done!

Could someone offer a reason as to why my direct approach at populating the database was incompatible with the accompanying form?

The languages I am using are PHP and MySQL.

Thanks

End User · #2 (**permalink**) 12-01-09, 04:56 AM

Quote:

Originally Posted by WillUK

Could someone offer a reason as to why my direct approach at populating the database was incompatible with the accompanying form?

No, not without seeing the code. Without the code all we can do is guess as to why it wouldn't work.

WillUK · #3 (**permalink**) 12-01-09, 05:15 AM

PHP Code:


			

session_start();
$page_title = 'Add Product';
include_once ('../inc/admin_header.php');
require_once ('../inc/mysql_connect.php'); // Connect to the database.

if (!isset($_SESSION['admin_id'])) {
    echo "<br /><br /><div class='column_main'><i>You must login to access this section</i></div>";
    include_once ('../inc/admin_footer.php');
    break;
    }
    else
    if (isset($_POST['submit'])) { // Handle the form.
    
    // Validate the print_name, image, artist (existing or first_name, last_name, middle_name), size, price, and description.

    // Check for a print name.
    if (!empty($_POST['product_name'])) {
        $pn = escape_data($_POST['product_name']);
    } else {
        $pn = FALSE;
        echo '<p><font color="red"><div class="column_main"><i>Please enter a name for the product </i></font></p></div>';
    }
    
    // Check for an image (not required). *//THIS FILE TRANSFERS, BUT NO CORRESPONDING ENTRY IS MADE INTO THE DATABASE.
    if (is_uploaded_file ($_FILES['image']['tmp_name'])) {
        if (move_uploaded_file($_FILES['image']['tmp_name'], "../inc/uploads/{$_FILES['image']['name']}")) { // Move the file over.
            echo '<div class=column_main><br /><br /><i><p>Success!</p></i></div>';
        } else { // Couldn't move the file over.
            echo '<p><font color="red"><div class="column_main"><i>The file could not be moved.</font></p></i></div>';
            $i = '';
        }
        $i = $_FILES['image']['name'];
    } else {
        $i = '';
    }
    
    // Check for a size (not required).
    if (!empty($_POST['size'])) {
        $s = escape_data($_POST['size']);
    } else {
        $s = '<div class="column_main"><i>Size information not available.</i></div>';
    }
    
    // Check for a weight (not required).
    if (!empty($_POST['weight'])) {
        $w = escape_data($_POST['weight']);
    } else {
        $w = '<div class="column_main"><i>Size information not available.</i></div>';
    }
    
    // Check for a price.
    if (is_numeric($_POST['price'])) {
        $p = $_POST['price'];
    } else {
        $p = FALSE;
        echo '<p><font color="red"><div class="column_main"><i>Please enter a sale price for the photo </font></p></i></div>';
    }
    
    // Check for a description (not required).
    if (!empty($_POST['description'])) {
        $d = escape_data($_POST['description']);
    } else {
        $d = '<div class="column_main"><i>No description available.</i></div>';
    }
    
    // Validate the manufacturer.
    if ($_POST['manufacturer'] == 'new') {

        // If it's a new manufacturer, add the manufacturer to the database.*//THIS BIT WORKS.
        $query = 'INSERT INTO manufacturer (manu_id, manu_name) VALUES (NULL, ';        
         
         //Check for a manu_name.
        if (!empty($_POST['manu_name'])) {
            $query .= "'" . escape_data($_POST['manu_name']) . "')";
            $result = @mysql_query ($query); // Run the query.
            $mn = @mysql_insert_id(); // Get the manu_ID.        
            } else {
            $mn = FALSE;
            echo '<p><font color="red"><div class="column_main"><i>Please enter the manufacturer name!</font></p></i></div>';
        }
        
        } elseif ( ($_POST['manufacturer'] == 'existing') && ($_POST['existing'] > 0)) {
        $mn = $_POST['existing'];
        } else {
        $mn = FALSE;
        echo '<p><font color="red"><div class="column_main"><i>Please enter or select the product manufacturer!</font></p></i></div>';
        }
    
    
    // Validate the Product Category.
    if ($_POST['product_type'] == 'new') {

        // If it's a new product type, add to the database. *//THIS BIT WORKS.
        $query = 'INSERT INTO product_type (type_id, type_name) VALUES (NULL, ';        
         
         //Check for a type_name.
        if (!empty($_POST['type_name'])) {
            $query .= "'" . escape_data($_POST['type_name']) . "')";
            $result = @mysql_query ($query); // Prepare the query for posting into the database.
            $type = @mysql_insert_id(); // Create a type_ID and prepare for posting into the database.        
            } else {
            $type = FALSE;
            echo '<p><font color="red"><div class="column_main"><i>Please enter the product category</font></p></i></div>';
        }
        
        } elseif ( ($_POST['product_type'] == 'existing') && ($_POST['existing'] > 0)) { //* THIS BIT ISN'T WORKING
        $type = $_POST['existing'];
        } else {
        $type = FALSE;
        echo '<p><font color="red"><div class="column_main"><i>Please select the product category</font></p></i></div>';
        }
        
    if ($pn && $p && $mn && $type) {
    
        // Add the product to the database.
        $query = "INSERT INTO products (manu_id, type_id, product_name, price, description, size, weight, image_file_name) VALUES ('$mn', '$type', '$pn', '$p', '$d', '$s', '$w', '$i')";
        if ($result = @mysql_query ($query)) { // Worked.
            echo '<p><div class="column_main"><i>The product has been added. Please click on Add Product to add another.</i></p></div>';
        } else { // If the query did not run OK.
            echo '<p><font color="red"><div class="column_main">Your submission could not be processed due to a system error.</font></p></div>'; 
        }
        
    } else { // Failed a test.
            echo '<p><font color="red"><div class="column_main"><i>Please click "back" and try again.</font></p></i><div>';
    }
    
} else { // Display the form.

HTML Code:

<div class="column_main">

<br />
	
<form enctype="multipart/form-data" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">

<input type="hidden" name="MAX_FILE_SIZE" value="10000000000"><!--make sure that the filesize is sufficiently high to cover all types of photograph-->

<fieldset><legend>Fill out the form to add a product to the catalog:</legend>
<br />
<p><b>Product Name:</b> <input type="text" name="product_name" size="30" maxlength="60" /></p>

<p><b>Image:</b> <input type="file" name="image" /></p>

<p><b>Select Manufacturer:</b> 
Existing <input type="radio" name="manufacturer" value="existing" /> 
<select name="existing"><option>Select One</option>

PHP Code:


			
 // Retrieve all the manufacturers and add to the pull-down menu.
$query = "SELECT manu_id, manu_name AS manu_name FROM manufacturer ORDER BY manu_name ASC";        
$result = @mysql_query ($query);
while ($row = mysql_fetch_array ($result, MYSQL_BOTH)) {
    echo "<option value=\"{$row['manu_id']}\">{$row['manu_name']}</option>\n";
}
//mysql_close(); // Close the database connection.

HTML Code:

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

<p><b>Product Category:</b> 
Select <input type="radio" name="product_type" value="existing" /> 
<select name="existing"><option>Select One</option>

PHP Code:


			
 // Retrieve all the product categories and add to the pull-down menu.
$query = "SELECT type_id, type_name AS type_name FROM product_type ORDER BY type_name ASC";        
$result = @mysql_query ($query);
while ($row = mysql_fetch_array ($result, MYSQL_BOTH)) {
    echo "<option value=\"{$row['type_id']}\">{$row['type_name']}</option>\n";
}
//$type({$row['type_id']}{$row['type_name']});
//mysql_close(); // Close the database connection.

HTML Code:

</select><br />
<!--New <input type="radio" name="product_type" value="new" /> 
Name: <input type="text" name="type_name" size="10" maxlength="30" /> -->
</p>
<p><b>Size:</b> <input type="text" name="size" size="30" maxlength="60" /></p>

<p><b>Weight:</b> <input type="text" name="weight" size="30" maxlength="60" /></p>

<p><b>Price:</b> <input type="text" name="price" size="10" maxlength="10" /><br /><small>Do not include the '£' sign or commas. For example: £100.50p should be entered as 100.50 </small></p>

<p><b>Description:</b> <textarea name="description" cols="40" rows="5"></textarea></p>

</fieldset> <!--Need to mirror the add_print form for form fields, because now we are adding artist_id into the equation....Do we need to add artist_id to the form aspect? Maybe edit to not allow user to select "new artist"-->
	
<div align="center"><input type="submit" name="submit" value="Submit" /></div>

</form><!-- End of Form -->

</div>

PHP Code:


			
} // End of main conditional.

include_once ('../inc/admin_footer.php');

WillUK · #4 (**permalink**) 12-01-09, 05:36 AM

So there's the code...
Ignore the comments I've made, they are now not relevant.
The question is: based on the form below, how come when I tried to input data into the database manually, the form menu could not recognise or post to the correct database/tables?

product_type and product_name would only be populated in the database, when the form was completed in entirety i.e. this part was processed:

HTML Code:

New <input type="radio" name="product_type" value="new" /> 
Name: <input type="text" name="type_name" size="10" maxlength="30" /> -->
</p>

In the code below, I have commented it out because I do not want the client to have this functionality.

wirehopper · #5 (**permalink**) 12-01-09, 06:12 AM

My guess would be that when you removed some of the functionality, there was a reference to it that remained and caused it to fail.

The SQL may have referred to a field that wasn't being sent or set.

WillUK · #6 (**permalink**) 12-01-09, 08:48 AM

Yea, I would have guessed the same...it's just difficult to tell.
So, in your experience, there's nothing wrong with manually inputting data into a database, and then querying it with SQL via an html form in the usual way? Output should still be rendered right?

End User · #7 (**permalink**) 12-01-09, 11:33 AM

As long as the data is entered correctly it doesn't matter how it got there. The application can't tell and neither can the browser.

Quote:

Originally Posted by WillUK

Yea, I would have guessed the same...it's just difficult to tell.
So, in your experience, there's nothing wrong with manually inputting data into a database, and then querying it with SQL via an html form in the usual way? Output should still be rendered right?

WillUK · #8 (**permalink**) 12-01-09, 12:50 PM

Thanks for your help! Evidently it was something related to human error i.e. me!