Displaying a price based on drop down selections`

[craigtolputt]

Hi Guys,

 

I have successfully managed to get my select drop down boxes to populate from a DB which i am very proud of as I'm  still learning PHP but I really need some help on the next part please...

 

I want to display a price based on the drop down selections so in my DB i have this...

 

id colours size quantity           sides price stock             variations

 

1     1         A6   0-99         SINGLE 200         100g GLOSS         1

2     1         A6   100-199 SINGLE 300         100g GLOSS         1

 

 

What would i need to add to my PHP so that it checks the selections made by the user and displays the relevant price in the price filed in my page?????

 

Hopefully someone can help.

 

thanks

 

Craig

[craigtolputt]

**** BUMP ****

 

Please if someone knows how to do this i would really appreciate the help please

 

**** BUMP ****

[BlueSkyIS]

Use the selected id to perform a SELECT on your database, then list the returned record(s), if any.

[craigtolputt]

Sorry i dont fully understand,  what i want to do is basically return the value in the price field if the other fields match up

 

you can see the form here http://bit.ly/9yDO8T and in my db i have all the different combinations listed so what ever the user selects from the drop down lists there will be a price value

[craigtolputt]

**** BUMP ******** BUMP ******** BUMP ******** BUMP ****

 

Is this really hard to accomplish?  I thought that it would be easy but i have searched the internet all day now and still i cant find an answer.

Please if someone knows how to do this i need to learn please please

 

**** BUMP ******** BUMP ******** BUMP ******** BUMP ****

 

[KevinM1]

If different items are going to have different properties (as in, Item1 could come in a different color, or could be double-sided, etc), you'll need to normalize your database to account for these variations.  Take a look at this and see if you can apply it to your system before you design yourself into a corner: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

 

In short, a database isn't a spreadsheet.  Don't treat it like one.

[craigtolputt]

I have setup the DB so that every different option is accounted for so i have the following and i just need to return the price that matches the row that the user selects so

 

colours+size+quantity+sides+stock+variations  ==  Return Price in that row

 

 

id colours size quantity         sides price     stock                         variations

902   1         A5 0-99                 Single 103     250gsm Silk Artpaper 1

903   1         A5 100-199         Single 104     250gsm Silk Artpaper 1

904   1         A5 200-299         Single 105     250gsm Silk Artpaper 1

905   1         A5 300-399         Single 106     250gsm Silk Artpaper 1

906   1         A5 400-499         Single 107     250gsm Silk Artpaper 1

907   1         A5 500-599         Single 108     250gsm Silk Artpaper 1

908   1         A5 600-699         Single 109     250gsm Silk Artpaper 1

909   1         A5 700-799         Single 110     250gsm Silk Artpaper 1

910   1         A5 800-899         Single 111     250gsm Silk Artpaper 1

911   1         A5 900-999         Single 112     250gsm Silk Artpaper 1

912   1         A5 1000-1099      Single 113     250gsm Silk Artpaper 1

913   1         A5 1100-1199      Single 114     250gsm Silk Artpaper 1

 

[KevinM1]

You're going to have to do a SELECT query that matches everything (pseudo-code) :

 

SELECT price FROM tablename WHERE colours = selected color AND size = selected size AND quantity = selected quantity....

 

Keep in mind that the price won't magically pop up regardless.  PHP can't respond to browser events.  So, even if your code is correct, it won't run unless the values are sent to the server and the results are sent back.  This is done either by manually submitting the form, or by using JavaScript to write some ajax code.

[craigtolputt]

Thanks, so somthing like this do you think?

 

<?php

include "connect.php";

 

$tableName = "pricelist";

 

//Post all of the users information (md5 Encrypt the dna_no)

 

 

$sql1 = mysql_query("SELECT price FROM $tableName WHERE quantity = '$quantity' AND size = '$size' AND stock = '$stock' AND colours = '$colours' AND sides = '$sides' AND variations = '$variations'");

 

$row1 = mysql_num_rows($sql1);

//if there is a result it will be either 1 or higher

if($row1 > 1) {

$price = $_GET['price'];

}

 

if(isset($_POST['contactus'])) {

 

$quantity = $_POST['quantity'];

$size = $_POST['size'];

$stock = $_POST['stock'];

$colours = $_POST['colours'];

$sides = $_POST['sides'];

$variations = $_POST['variations'];

 

}

 

 

 

 

?>

[DavidAM]

Almost ...

$row1 = mysql_num_rows($sql1);
//if there is a result it will be either 1 or higher
if($row1 > 1) {
$price      = $_GET['price'];
}

should probably be

$row1 = mysql_num_rows($sql1);
//if there is a result it will be either 1 or higher
if($row1 >= 1) { // ONE OR MORE ROWS
  $dbData = mysql_fetch_assoc($sql1);  // GET THE DATA FROM THE FIRST ROW
  // IF $row1 IS GREATER THAN 1, THIS MAY NOT BE THE CORRECT PRICE
  $price = $dbData['price'];
}

 

Also, you are executing the query with the user's data BEFORE you retrieve that data into your variables.  The query is going to fail.  Move the assignment block above the query.

[craigtolputt]

Sorry still no luck,

 

This is what i have altogether now...

<?php
include "connect.php";

$tableName  = "pricelist";
$quantity 	= $_POST['quantity'];
$size 		= $_POST['size'];
$stock 		= $_POST['stock'];
$colours 	= $_POST['colours'];
$sides 		= $_POST['sides'];
$variations = $_POST['variations'];

if(isset($_POST['getprice'])) {

$sql1 = mysql_query("SELECT price FROM $tableName WHERE quantity = '$quantity' AND size = '$size' AND stock = '$stock' AND colours = '$colours' AND sides = '$sides' AND variations = '$variations'");

$row1 = mysql_num_rows($sql1);
//if there is a result it will be either 1 or higher
if($row1 >= 1) { // ONE OR MORE ROWS
  $dbData = mysql_fetch_assoc($sql1);  // GET THE DATA FROM THE FIRST ROW
  // IF $row1 IS GREATER THAN 1, THIS MAY NOT BE THE CORRECT PRICE
  $price = $dbData['price'];
}
}

?>