Need some help assigning three prices to an id

PHP
1

I have an id that has several prices and names assigned to it and I have been thinking how can I code to assigned x price depending on user input to the id?

I was thinking of storing the prices and names in a separate table and create a many-to-many relationship table where one Id has several prices and names but I am not clear on that.

To see an illustration of what I am talking about visit this link.

http://www.nyhungry.com/example2.php?subject=4&id=4&register=0&menu=38

The first id coctel de camarones will have three different prices and it will have an add bottom that will take user to a shopping cart. user can also choose the three types of trays as well and it will be add up to the shooping cart.

Thank you.

2

This is a one-to-many relationship, not a many-to-many. One ID to many sets of name&price. You need only two tables – one for the products, and one for the name/price associated with the variations for sale. The second table has the ID of the product in it as a foreign key.

3

thank you Dan.

4

Guys I am kind of confuse here.

I have set the following

This is what i have in cater table

CREATE TABLE IF NOT EXISTS catertray (
id int(1) NOT NULL,
price decimal(9,2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO cater (id, price) VALUES
(1, 13.90),
(1, 19.34),
(1, 28.12);


<?php 
$query3 = "SELECT cater.id, cater.name, cater.price, cater.ap_id
FROM caridadcaterin
WHERE
ap_id= " . (int) $_GET['menu']; 
$result = mysql_query($query3, $connection);
 while ($row4 = mysql_fetch_array($result)) {
echo'<p class="name">Name:</p><p class="p">'. $row4['name'] . '</p>';

$query4 = "SELECT catertray.id, catertray.price
FROM catertray
WHERE
id= " ; 
$result = mysql_query($query3, $connection);
$row5 = mysql_fetch_assoc($result);
echo'
<div class="iteration">
<div class="small">
<p class="size">Small Tray</p>
<img src="images/bandeja2.jpg" width="80" height="90"/>
<p class="price">'. $row5['price'] . '</p>
</div>
<div class="medium">
<p class="size2">Medium Tray</p>
<img src="images/bandeja2.jpg " width="130" height="140"/>
<p class="price">'. $row5['price'] . '</p>
</div>
<div class="large">
<p class="size3">Large Tray</p>
<img src="images/bandeja2.jpg" width="150" height="180"/>
<p class="price">'. $row5['price'] . '</p>
</div>
</div>';
}
?>

Above I have the id as the relational database foreing key because otherwise I will have to reareange the other tables related to this one and they have a lot of information already. It hink I won’t need a id with a primary key at the moment at cater table. With that said I have thought if it is better to instead of assigning a row for each price to put all the price in one row in three different indexes [0], [1],[2] and then assign the index number in the html to display WHERE id=id in cater table. Can you see the cater.id? I want to compare in the WHERE clause at the second query or $query4

$query4 = "SELECT catertray.id, catertray.price
FROM catertray
WHERE
id= cater.id" ;

The above example is just an idea on what I want to compare it with. I know some kind of Global variable could be used in there to extract the cater.id value from the first query. Should I define a variable with the cater.id as a value in between the two queries? then compare it in at the WHERE clause in the second query?

Thank you.

5

I’ve been reading, and you can consider doing it in one table…


ID       Name                                Size         Price
--       -----                                -----        -----
 1       Cot&#233;l de camarones             Large        8.90
 2       Cot&#233;l de camarones             Medium      6.20
 3       Cot&#233;l de camarones             Small         2.90
 4       Almejas Frescas                  Large        9.50
 5       Almejas Frescas                  Medium      7.20
6

I perhaps missing some check boxes to choose one or even two sizes. I will need a little sum for the price field so that when the user click to the addtocart bottom then it is all summed or maybe insert it in the cart individually?

what would be your approach.

Thank you I will consider having everything is in one table.

7

Two tables was the right schema, but you should put the size in the second table, otherwise you don’t know what price goes with what size. I often help you with one thing but have to leave the thread, because you always reply with a pile of code that I can barely follow…

It’d be SO much easier to help you, and you’d probably need SO much less help, if you did things in order only after you completed the previous step:

  1. Create the database tables
  2. Write the queries that select the data you need for the page, and test them to make sure they give you the right data back
  3. Write the PHP code that issues those queries, incorporating query strings or form data
  4. Write the code that retrieves the data from the result set
  5. Add in your HTML to mark up the page around that data

It looks like you start at the end and try to work towards the beginning, since you have bunches of HTML filled with variables that aren’t populated, SQL queries that aren’t syntactically correct so they can’t be working, and a confusing schema. Try flipping it around. Then you’ll know exactly where you’re having a problem, or what part you need help with.

8

Thank you for your suggestion and order.

Some of the varables are defined at the top of the script, I have to say yes that some SQL queries were not syntactically correct, but Next time I will bring it more clear and if any issues as well.

I understood that the sizes and prices would go in the same table right?

9 
CREATE TABLE products (
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name VARCHAR(255)
);

CREATE TABLE product_varieties (
  product_id INT,
  variety VARCHAR(100),
  price DOUBLE,
  description TEXT,
  PRIMARY KEY (product_id, variety)
);

INSERT INTO products (id, name) VALUES (1, 'Cot&#233;l de camarone');
INSERT INTO product_varieties (product_id, variety, price, description) VALUES (1, 'Small Tray', 2.9, 'Serves 6 to 8 people');
INSERT INTO product_varieties (product_id, variety, price, description) VALUES (1, 'Medium Tray', 6.9, 'Serves 12 to 15 people');
INSERT INTO product_varieties (product_id, variety, price, description) VALUES (1, 'Large Tray', 8.9, 'Serves 18 to 21 people');
10

This is step 2 in from post #7

<?php 

$query1= "SELECT id, name
FROM products 
$result = mysql_query($query1, $connection);
 while ($row = mysql_fetch_array($result)) {
echo'<div id="namerating">
<p class="name">Name:</p><p class="p">'. $row['name'] . '</p>
</div>';

$query2="SELECT product_id, variety, price, description,image 
FROM product_varieties
WHERE product_id= "the id in $query1, How can I script it in here?" 
$result2 = mysql_query($query2, $connection);
$row2=mysql_fetch_array($result2) {
echo'<div class="firstiteration">
<div class="small">
<p class="size">'. $row2['variety'] . '</p>
<img src="'. $row2['image'] . '" width="80" height="90"/>
<p class="price">'. $row2['price'] . '</p>
<p class="serve1">'. $row2['description'] . '</p>
</div>';
}
}
?>

I have gotten stuck because,

How can I compare the product_id with the id in $query1?

Help…

Thanks

11

You only need one query to create your page. Join the two tables on the product_id.

12

I have find out about implicit joins and I was wondering if the below implicit join is correct please if correct me if I am mistaken.


<?php 
$query1= "select p.id, p.name, p.image, p.colon_id, v.product_id,v.price,v.description,v.image from products p,product_varieties v where p.colon=" .(int) $_GET['menu'] AND p.id = v.product_id 
$result = mysql_query($query1, $connection);
?>

The menu index in the WHERE clause is compared to p.colon so it can display according to the menu index value coming from the url. Then I also want to do another comparison which is p.id=v.product_id so if those two comparison then {
display html.
}

Please correct the above implicit join if it is mistaken.

13

Dan

I did one mistake. I miss to put in the former post that products table has a field called p.colon_id and it is compare with a variable coming from the url call menu.

the query alone would look like:

$query1= "select p.id, p.name, p.image, p.colon_idfrom products pwhere p.colon=" .(int) $_GET['menu']$result=mysql_query($query1,$connection);$row = mysql_fetch_array($result) {

with the join of the two tables on product_id and p.colon it would look as below, but it is throwing an warning:



$query1= "select p.id, p.name, p.image, p.colon_id, v.product_id,v.price,v.description,v.image from products p,product_varieties v where p.colon=" .(int) $_GET['menu'] ." AND p.id = v.product_id ";
$result = mysql_query($query3, $connection);
while ($row4 = mysql_fetch_array($result)) { // line 258
html....
}

But as I said PHP is throwing the following WARNING and ít’s not letting the data to query and display

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in 
public_html/example2.php on line 258

Help…

14

That means your query was not valid. mysql_query() returned FALSE instead of a result resource. You then pass FALSE to mysql_fetch_array and, as you see, FALSE is not a valid MySQL result resource.

IOW: Go back to step 2. Write the query to get the data you want, outside of PHP, and test it, outside of PHP.

Once that’s done, THEN you can go to step 3 – putting the query in your PHP code and substituting $_GET/$_POST values into it.

15

It is working it’s just, I tested on phpmyadmin and came up that I had put a field that didn’t exist in one of the tables. Now the only thing is that instead of putting the variety, prices and name each one in a separate row, I think I need to assign variety 1, variety 2, and variety 3 fields the same thing for the prices and names so it can print the three prices, and varieties for each id or product. instead of printing one, prices, name or variety per row. So that way i will be able to address each prices, name and variety by its indexes to each product.

Thank you.

16

No, you do not want to do that. That’s denormalizing your tables into a worse design. You can make the page you linked with the design I showed you, easily. Have you considered hiring a programmer to do work for you or mentor you?

17

A mentor would be a good idea right now i have to get some resources ready.

18

It is fortunate that you have Dan helping you out here. He’s an Advisor, which is the next step up from a Mentor.
Be kind to him and feed him plenty of virtual chocolate fish.

Others here will also help out as and when possible.

19

I’m talking about a personal mentor (aka counselor, teacher) to guide him through his programming problems, not a forum moderator. It does not seem like his code is improving over time as he gets help in this forum, it often doesn’t even run, so perhaps finding someone experienced in program willing to guide him through formulating and solving problems with code would help make the lessons stick.

20

@pmw57 thank you

@Dan Thanks I often get confused whenever I get the chance i will get your advice in practice.

Thank all of you for putting a bit on helping.