Behind The Sofa

#!/usr/bin/perl # # Original Author: Michael Gray mick@webage.co.uk # SQL upgrades and additions by: Vanessa Bornmann # # Addition to Extropia Webstore # Admin tool, with which you can change, # add, delete items in the database # # Usual Extropia open policy on using this code. # ############################################################ # use DBI tells Perl that it needs to pull # in the DBI module. use DBI; # # Configure the location of your setup file $setup_file_path = "../Library/web_store.setup.frames.javascript"; $this_script = "edit_database.pl"; require "$setup_file_path"; ############################################################ # connect to database $dbh = DBI->connect ($sc_mysql_dsn, $sc_mysql_user_name, $sc_mysql_password, { RaiseError => 0, PrintError => 0}) or $mysql_error_message = "MySQL Error: Could not connect to $sc_mysql_dsn"; if ($mysql_error_message ne "") { &update_error_log($mysql_error_message, __FILE__, __LINE__); } #################################################### # Define the page headers and footers $page_header = qq~ Behind The Sofa

~; $page_footer = qq! !; #################################################### # This application uses two tables, # Product and Category # with specific columns each # see the arrays below for table and row # information used throughout this prog # #################################################### # table array # Products = 0 # Category = 1 @tables = (products, categories); # below are the columns of the Products table # for reference # product_id = 0; # product = 1; # price = 2; # pname = 3; # image_url = 4; # description = 5; # options = 6; @table_one_rows = (product_id, product, price, pname, image_url, description, options); # table Category # categoryID # category @table_two_rows = (cat_id, category); require ".$sc_cgi_lib_path"; &ReadParse(*form_data); print "content-type: text/html\n\n"; &PrintHeader; if ($form_data{'list_products'} || $form_data{'list_products.x'} ne "") { &list_products; exit; } elsif ($form_data{'display_product'} || $form_data{'display_product.x'} ne "") { &display_product; exit; } elsif ($form_data{'edit_product'} || $form_data{'edit_product.x'} ne "") { &edit_product; exit; } elsif ($form_data{'delete_product'} || $form_data{'delete_product.x'} ne "") { &delete_product; exit; } elsif ($form_data{'add_product'} || $form_data{'add_product.x'} ne "") { &add_product; exit; } elsif ($form_data{'submit_add_product'} || $form_data{'submit_add_product.x'} ne "") { &submit_add_product; exit; } elsif ($form_data{'new_product'} || $form_data{'new_product.x'} ne "") { &new_product; exit; } else { &list_categories; exit; } ################################################################# sub list_categories { local (@listed_categories); local ($category); local ($last_category); local ($line); # create a select query to get all categories # * means all fields in table Category $get_categories = "SELECT * FROM @tables[1] ORDER BY @table_two_rows[0]"; # execute the select query after logging into database successfully $sth = $dbh->prepare ("$get_categories"); $sth->execute (); # html bla print $page_header; print "\ ~; $sth->finish (); print $page_footer; # So far we have printed a drop down menu of all # product categories, ready to post back to the # script. } # end of the list_categories sub routine ###################################################################### # sub list_products # list all products by drop down menu # use the product_id code as the option value # and the product_id and product name to be viewed # to the user. sub list_products { local (@database_row); local ($product_category, $product_id, $product_name); local (@product_ids); local (@product_names); local ($line_chosen); $chosen_category = $form_data{'product_category'}; # select product-id from chosen category $get_categories = "SELECT * FROM @tables[0] WHERE @tables[0].@table_one_rows[1] = '$chosen_category' ORDER BY @table_one_rows[0]"; # execute the select query $sth = $dbh->prepare ("$get_categories"); $sth->execute (); # Now we have two arrays containing the chosen products and their # product names # Lets print another drop down menu to choose # a product from # # Set product_id and name back to nothing $product_id = 0; $product_name = 0; print $page_header; print "\ ~; $sth->finish (); print $page_footer; } # end of sub routine ########################################################################## # sub display product # # open the database with read permission # display the information in the product # row, and include text boxes to edit any field # of the row. sub display_product { local ($product_id); local ($product_category); local ($product_line); local ($chosen_product); $chosen_product = $form_data{'product_id'}; # select product info from chosen product $get_product = "SELECT * FROM @tables[0] WHERE @table_one_rows[0] = '$chosen_product'"; # execute the select query $sth = $dbh->prepare ("$get_product"); $sth->execute (); @fields = "@table_two_rows"; print "$page_header"; print "\

"; print qq~ ~; } else { if ($i == 0) { print "\\@table_one_rows[0] is a non-editable database field<\/TD\>\\ @fields[$i]\<\/B\> \<\/TD\>\<\/TR\>\n"; } else { print "\\@table_one_rows[$i]\<\/TD\>\\\<\/TD\>\<\/TR\>\n"; } } $i++; } } print qq~ ~; print qq~

~; print "\ \Editable Product Information for Product ID $chosen_product \\\ \<\/TD\>\<\/TR\>"; print "\n"; # @table_one_rows[x] - use this array (see top) and use the # column names to save the data to be transmitted # we basically create these variables out of the array while ( @fields = $sth->fetchrow_array () ) { $i = 0; while (@table_one_rows[$i]) { if ($i == 1) { # get the categories $get_categories = "SELECT * FROM @tables[1]"; $sthi = $dbh->prepare ("$get_categories"); $sthi->execute (); print qq~
~; print "@table_one_rows[$i]"; print qq~

~; $sthi->finish (); $sth->finish (); print"$page_footer"; } # end of sub routine ################################################################################# # Edit Product Subroutine # This time we can write to the datafile # inot the appropriate row. sub edit_product { local ($product_to_edit); local ($line); local ($field); print "$page_header"; # get the form data out of the column-name-variables which are # referenced in the @table_one_rows array @p_info[0] = $form_data{ $table_one_rows[0] }; $table_set = $table_one_rows[0] . " = '" . @p_info[0] . "'"; $i = 1; while (@table_one_rows[$i]) { @p_info[$i] = $form_data{ $table_one_rows[$i] }; $table_set = $table_set . ", " . $table_one_rows[$i] . " = '" . @p_info[$i] . "'"; # print "$i "; # print "@table_one_rows[$i] "; # print "@p_info[$i] "; # print $table_set; $i++; } $set_product = "UPDATE @tables[0] SET $table_set WHERE @table_one_rows[0] = '@p_info[0]'"; # execute the select query $sth = $dbh->prepare ("$set_product"); if ($sth->execute ()) { print qq~

Your database has been updated!

~; $select_edited_product = "SELECT * FROM @tables[0] WHERE $table_one_rows[0] = '@p_info[0]'"; $sth = $dbh->prepare ("$select_edited_product"); if ($sth->execute ()) { $select_product_cat = "SELECT $table_two_rows[1] FROM @tables[1] WHERE $table_two_rows[0] = '@p_info[1]'"; $sthi = $dbh->prepare ("$select_product_cat"); $sthi->execute (); while ( @catl = $sthi->fetchrow_array () ) { $cate = @catl[0]; } print qq~ ~; while ( @result = $sth->fetchrow_array () ) { print qq~~; } print qq~

ID	Category	Price	Name	Image	Description	Options
~; print "@result[0]"; print qq~	~; print "$cate"; print qq~	~; print "@result[2]"; print qq~	~; print "@result[3]"; print qq~	~; print "@result[4]"; print qq~	~; print "@result[5]"; print qq~	~; print "@result[6]"; print qq~

~; } } else { print qq~ An error has occured. The product could not be updated.

~; } print qq~ All Categories | All Products with this Category | Previous Page ~; $sth->finish (); print "$page_footer"; } # end of sub routine ################################################################################# # Delete Product Subroutine # This time we can write to the datafile # inot the appropriate row. sub delete_product { local ($product_id); local ($product_to_delete); local ($line); local ($field); local (@new_database_row); print "$page_header"; $product_id = $form_data{ $table_one_rows[0] }; $del_product = "DELETE FROM @tables[0] WHERE @table_one_rows[0] = '$product_id'"; # execute the select query $sth = $dbh->prepare ("$del_product"); if ($sth->execute ()) { print qq~~; print "The product ($product_id) has been successfully deleted."; print qq~

~; } else { print qq~ An error has occured. The product could not be deleted.

~; } $sth->finish (); print qq~ All Categories ~; print "$page_footer"; } # end of sub routine ########################################################################## # sub add product # # open the database with read permission # display the information in the product # row, and include text boxes to edit any field # of the row. sub add_product { print "$page_header"; print "\ ~; $sth->finish (); print"$page_footer"; } # end of sub routine ################################################################################# ################################################################################# # Submit Add Product Subroutine # This time we can write to the datafile # inot the appropriate row. sub submit_add_product { local ($product_id); local ($product_to_add); local ($line); local ($field); local (@new_database_row); local ($database_row); print "$page_header"; $product_to_add = $form_data{ $table_one_rows[0] }; $price_to_add = $form_data{ $table_one_rows[2] }; if ($product_to_add ne "" && $price_to_add ne "") { # check if the id already exists $check_id = "SELECT * FROM @tables[0] WHERE @table_one_rows[0] = '$product_to_add'"; $sth = $dbh->prepare ("$check_id"); $sth->execute (); $h = 0; while (@test = $sth->fetchrow_array ()) { $h++; } if ($h > 0) { print "This product_id code already exists.
Please go \ back \<\/a\> and try again, or use Edit Product.

"; } else { # if a new category was entered, insert into Category table $product_one = $form_data{'add_new_category'}; if ($product_one ne "") { $select_cat = "SELECT * FROM @tables[1] WHERE @table_two_rows[1] = '$product_one'"; $sth = $dbh->prepare ("$select_cat"); $sth->execute (); $i = 0; while ( @cats = $sth->fetchrow_array () ) { $i++; } if ($i > 0) { $product_one = ""; } else { $insert_cat = "INSERT INTO @tables[1] (@table_two_rows[1]) values('$product_one')"; $sth = $dbh->prepare ("$insert_cat"); $sth->execute (); $select_cat = "SELECT * FROM @tables[1] WHERE @table_two_rows[1] = '$product_one'"; $sth = $dbh->prepare ("$select_cat"); $sth->execute (); while ( @cats = $sth->fetchrow_array () ) { $product_one = @cats[0]; } } } else { $product_one = $form_data{ $table_one_rows[1] }; } if ($product_one eq "") { print qq~ This Category is already in the database!
The process has been aborted.

Go back to the form.

~; } else { # make the rows and results ready for the INSERT string # product ID and price must be entered and Category is either new # or out of drop down menu so we can start off with that $rows = $table_one_rows[0] . "," . $table_one_rows[1] . "," . $table_one_rows[2]; $results = "'" . $product_to_add . "'," . $product_one . "," . $price_to_add; $i = 3; while (@table_one_rows[$i]) { @product_nr[$i] = $form_data{ $table_one_rows[$i] }; if (@product_nr[$i] ne "") { $rows = $rows . "," . $table_one_rows[$i]; $results = $results . ",'" . @product_nr[$i] . "'"; } $i++; } # end while (@product_nr[$j]) $insert_data = "INSERT INTO @tables[0] ($rows) values($results)"; $sth = $dbh->prepare ("$insert_data"); if ($sth->execute ()) { print qq~

Your database has been updated!

~; $select_new_product = "SELECT * FROM @tables[0] WHERE $table_one_rows[0] = '$product_to_add'"; $sth = $dbh->prepare ("$select_new_product"); if ($sth->execute ()) { $select_product_cat = "SELECT $table_two_rows[1] FROM @tables[1] WHERE $table_two_rows[0] = '$product_one'"; $sthi = $dbh->prepare ("$select_product_cat"); $sthi->execute (); while ( @catl = $sthi->fetchrow_array () ) { $cate = @catl[0]; } print qq~ ~; while ( @result = $sth->fetchrow_array () ) { print qq~~; } print qq~

ID	Category	Price	Name	Image	Description	Options
~; print "@result[0]"; print qq~	~; print "$cate"; print qq~	~; print "@result[2]"; print qq~	~; print "@result[3]"; print qq~	~; print "@result[4]"; print qq~	~; print "@result[5]"; print qq~	~; print "@result[6]"; print qq~

~; } } # end if ($sth->execute ()) else { print qq~ Data could not be inserted. Error!

Check all fields: back to the order form

~; } # end else } #end else } # end else } # end if ($product_to_add != "") else { print qq~ You MUST enter a valid ID, category and price for the product!
The process has been aborted.

Go back

~; } print qq~ All Categories ~; print "$page_footer"; $sth->finish (); } # end of sub routine ################################################################################# $dbh->disconnect (); exit;

~; print qq~ New Product Information
~; print qq~ @table_one_rows[$i]

Select Product Category:

Select a Product to Edit: