travisco Posted May 13, 2009 Share Posted May 13, 2009 Hello, all. I am new to this kind of stuff and I am hoping that someone with more experience will help me. Please excuse me if I have posted this to the wrong thread. I am developing an ecommerce website. I have created an ordering process, all of my scripts work, and all the data passed to and retrieved from the database works exactly as expected. Each menu option passes a value represented as an integer (a price) and stored in an INT field. When I first created the database, this made perfect sense because I planned to implement some sort of script to give the visitor an order confirmation page with a 'total' retrieved from the pricing stored in the database. when I view the confirmation page it does indeed display everything correctly (including the total), but I now realize that I also need to display a description along with the price. For instance... my (simplified) confirmation page looks like this: Size Condition: $50.00 Glass: $120.00 Clear Shield: $130.00 TOTAL: $300.00 But i need my (simplified) confirmation page to look like this: Size Condition: $50.00 50in x 84in Glass: $120.00 Clear Clear Shield: $130.00 Super TOTAL: $300.00 Since I cannot store anything but integers in an INT table (duh!) and I can't use my PHP script to total data that is not in an INT or, at the very least, is a mixture of characters in VARCHAR- I am at a total loss for a solution. I have researched this a great deal and I don't think that the description can be added after the fact by matching the price to a description (via PHP), because some of the options have the same prices. I thought that, perhaps, I could assign a hidden value to each separate menu selection, but none of my experiments in this vein have worked out as some of the menus are Dependant on other menus and are generated by Javascript. I keep thinking that, in my inexperience, I am not grasping some sort of academic principal regarding the storgae of my data or in the creation of the structure of the database/table that will allow for a simple solution. Below, i have posted examples of the code i am using. Thanks in advance, tco ********************************************************************************************************************* this is a sample of the options page. the 'glasstype' and the 'clearshield' menus are dependent upon the 'sddimensions' menu option selection via javascript ********************************************************************************************************************* <form id="step" name="step2" method="post" action="step2.php" onSubmit="return validateForm(step2);"> <select name="sddimensions" size="1" id="sddimensions" onchange="setOptions(document.step2.sddimensions.options[document.step2.sddimensions.selectedIndex].value,document.step2.glasstype);"> <option value="" selected="selected">Choose your dimensions</option> <option value="0">Door up to 28" wide / Up to 80" high - included in price</option> <option value="50">Door 28-1/8" to 36" wide / Up to 80" high - add $50</option> </select> <select name="glasstype" id="glasstype" onChange="setOptions(document.step2.glasstype.options[document.step2.glasstype.selectedIndex].value,document.step2.clearshield);"> <option value="" selected="selected">Please select a dimension first</option> </select> <select name="clearshield" id="clearshield"> <option value="" selected="selected">Please select a dimension first</option> </select> <input type="submit" name="stp2submit" id="stp2submit" value="CONTINUE..." onClick="valbutton(step2)"/> ********************************************************************************************************************* this is a sample of the javascript that processes the menu option selection from the 'sddimensions' menu page ********************************************************************************************************************* function setOptions(chosen,selbox) { selbox.options.length = 0; if (chosen == "") { selbox.options[selbox.options.length] = new Option('Select your glass first',' '); } if (chosen == "0") { selbox.options[selbox.options.length] = new Option('Please choose your Glass',''); selbox.options[selbox.options.length] = new Option('Clear Glass - included in price','00'); selbox.options[selbox.options.length] = new Option('Niagara Glass - add $100','100'); selbox.options[selbox.options.length] = new Option('Super Clear Glass - add $200','200'); setTimeout(setOptions('',document.step2.clearshield),5); setTimeout(setOptions('00',document.step2.clearshield),5); setTimeout(setOptions('100',document.step2.clearshield),5); setTimeout(setOptions('200',document.step2.clearshield),5); } if (chosen == "50") { selbox.options[selbox.options.length] = new Option('Please choose your Glass',''); selbox.options[selbox.options.length] = new Option('Clear Glass - included in price','000'); selbox.options[selbox.options.length] = new Option('Niagara Glass - add $120','120'); selbox.options[selbox.options.length] = new Option('Super Clear Glass - add $240','240'); setTimeout(setOptions('',document.step2.clearshield),5); setTimeout(setOptions('000',document.step2.clearshield),5); setTimeout(setOptions('120',document.step2.clearshield),5); setTimeout(setOptions('240',document.step2.clearshield),5); } if (chosen == "00") { selbox.options[selbox.options.length] = new Option('Would you like Clear Shield?',''); selbox.options[selbox.options.length] = new Option('No Thanks','0'); selbox.options[selbox.options.length] = new Option('Clear Shield - add $110','110'); } if (chosen == "100") { selbox.options[selbox.options.length] = new Option('Would you like Clear Shield?',''); selbox.options[selbox.options.length] = new Option('No Thanks','0'); selbox.options[selbox.options.length] = new Option('Clear Shield - add $110','110'); } if (chosen == "200") { selbox.options[selbox.options.length] = new Option('Would you like Clear Shield?',''); selbox.options[selbox.options.length] = new Option('No Thanks','0'); selbox.options[selbox.options.length] = new Option('Clear Shield - add $110','110'); } if (chosen == "000") { selbox.options[selbox.options.length] = new Option('Would you like Clear Shield?','') selbox.options[selbox.options.length] = new Option('No Thanks','0'); selbox.options[selbox.options.length] = new Option('Clear Shield - add $130','130'); } if (chosen == "120") { selbox.options[selbox.options.length] = new Option('Would you like Clear Shield?',''); selbox.options[selbox.options.length] = new Option('No Thanks','0'); selbox.options[selbox.options.length] = new Option('Clear Shield - add $130','130'); } if (chosen == "240") { selbox.options[selbox.options.length] = new Option('Would you like Clear Shield?',''); selbox.options[selbox.options.length] = new Option('No Thanks','0'); selbox.options[selbox.options.length] = new Option('Clear Shield - add $130','130'); } } ********************************************************************************************************************* this is a sample of the PHP page that collects the form data and passes it to the database ********************************************************************************************************************* <?php $sddimensions = $_POST['sddimensions']; $glasstype = $_POST['glasstype']; $clearshield = $_POST['clearshield']; $dbc = mysqli_connect('mydomain.myhost.com', 'customer', 'xxxxxxxx', 'mydb') or die('MySQL server connection screw-up.'); $query = "INSERT INTO order2 (sddimensions, glasstype, clearshield) " . "VALUES ('$sddimensions', '$clearshield', '$glasstype')"; $result = mysqli_query($dbc, $query) or die('screw-up while querying database.'); mysqli_close($dbc); ?> ********************************************************************************************************************* this is a sample of the PHP page that collects the data, displays the data, and totals the data ********************************************************************************************************************* <?php $username="customer"; $password="xxxxxx"; $database="mydatabase"; mysql_connect('mydomain.myhost.com',$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $query="SELECT * FROM order ORDER BY session_id DESC LIMIT 1"; $result=mysql_query($query); mysql_close(); $i=0; while ($i < $num) { $sddimensions=mysql_result($result,$i,"sddimensions"); $glasstype=mysql_result($result,$i,"glasstype"); $clearshield=mysql_result($result,$i,"clearshield"); echo " <b>Size Condition:</b>$sddimensions<br> <b>Glass:</b>$glasstype<br> <b>Clear Shield:</b>$clearshield<br> "; $total= $sddimensions + $glasstype + $clearshield; echo " <b><br>TOTAL:</b>$total<br> "; $i++; } ?> ********************************************************************************************************************* this is a sample of the 'order2' table from 'mydatabase' ********************************************************************************************************************* +--------------+-----------------+------+-----+---------+----------------+ | Field Type | Null | Key | Default | Extra | +--------------+-----------------+------+-----+---------+----------------+ | session_id | int ( 8 ) unsigned | NO | PRI | NULL | auto_increment | | sddimensions | int (4) | NO | | 0 | | | glasstype | int (4) | NO | | 0 | | | clearshield | int(4) | NO | | 0 | | +--------------+-----------------+------+-----+---------+----------------+ Quote Link to comment Share on other sites More sharing options...
travisco Posted May 13, 2009 Author Share Posted May 13, 2009 sorry- forgot to add this: Current MySQL Version: 5.0.45 Quote Link to comment Share on other sites More sharing options...
fenway Posted May 13, 2009 Share Posted May 13, 2009 TLDR... you're new here, so maybe you haven't taken the time to read the rules/TOS -- posting scripts is frowned upon. What exactly is the specific issue here? Quote Link to comment Share on other sites More sharing options...
travisco Posted May 13, 2009 Author Share Posted May 13, 2009 My apologies, fenway. I was preoccupied with trying to understand my problem as I was trying to describe it. Your time is much appreciated. I will be sure to do that properly next time. Basically I am storing values (prices) in a database and then, using PHP, totaling those prices in a confirmation page. I need to store a price description along with the price, so that a visitor can see what item they chose and the dollar value of that item. in my original comment i used this example- For instance... my (simplified) confirmation page looks like this: Size Condition: $50.00 Glass: $120.00 Clear Shield: $130.00 TOTAL: $300.00 But i need my (simplified) confirmation page to look like this: Size Condition: $50.00 50in x 84in Glass: $120.00 Clear Clear Shield: $130.00 Super TOTAL: $300.00 i posted samples of how i am currently storing data and retrieving the data- i guess i need to know how to store VARCHAR data and then extract only the pricing from that field and then total it. could i do this by building an array from a VARCHAR field using the explode() function then save appropriate strings back to INT fields? does that make any sense? sorry if i am a bit lax here. as i said earlier i am a pitiful noob but i am trying to wrap my head around this. help or advice of any nature i appreciated. thanks, tco Quote Link to comment Share on other sites More sharing options...
fenway Posted May 13, 2009 Share Posted May 13, 2009 Why are you storing "mixed" data that way? Quote Link to comment Share on other sites More sharing options...
travisco Posted May 13, 2009 Author Share Posted May 13, 2009 currently i am not storing mixed data. it looks like i do need to store "mixed" data, but i know that that is not the correct approach. Quote Link to comment Share on other sites More sharing options...
fenway Posted May 14, 2009 Share Posted May 14, 2009 Then I'm confused... why not simply join the tables? Quote Link to comment Share on other sites More sharing options...
travisco Posted May 18, 2009 Author Share Posted May 18, 2009 because, to my knowledge, i can only transfer a single value per menu selection. for instance- i have product X. lets say that one of the sub-properties of X product is that it can be purchased in a 3/8" thickness and a 1/2" thickness and each thickness is the same cost. for the sake of argument- lets say the cost for both is 70$, so my menu might look like this: <option value="70">3/8inch - add $70 inch</option> <option value="70">1/2inch - add $70</option> Quote Link to comment Share on other sites More sharing options...
travisco Posted May 18, 2009 Author Share Posted May 18, 2009 sorry- i accidentally submitted that before i was done. anyway... when the values are submitted they are both stored in INT fields in the appropriate table. so right now- when a visitor gets to the end of the process they get a confirmation and total page that resembles this: Glass: $70.00 TOTAL: $70.00 but i want it to spit back something like this: Glass: $70.00 1/2 inch TOTAL: $70.00 as far as i can tell i need a way to submit a value to a VARCHAR field and then extract the price data only so i can total it using my current script, or submit two separate values for the same selection. thanks, tco Quote Link to comment Share on other sites More sharing options...
fenway Posted May 19, 2009 Share Posted May 19, 2009 because, to my knowledge, i can only transfer a single value per menu selection. Why? Quote Link to comment Share on other sites More sharing options...
travisco Posted May 19, 2009 Author Share Posted May 19, 2009 because i dont know any better thanks, travis Quote Link to comment Share on other sites More sharing options...
fenway Posted May 20, 2009 Share Posted May 20, 2009 because i dont know any better thanks, travis I don't really understand. Quote Link to comment Share on other sites More sharing options...
travisco Posted May 20, 2009 Author Share Posted May 20, 2009 no problem. thanks anyway. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.