0

When I select a category it shows me a new error. I just need to echo the cupcake name and price of the selected category and if possible I want to add another dropdown button with taste. For example, if someone is looking for a birthday cupcakes will vanilla taste all birthday cupcakes with vanilla taste will be shown with their prices.

<html>
<body>
<?php
$server="localhost";
$username="root";
$password="";
$connect_mysql=mysql_connect($server,$username,$password) or die ("Connection Failed!");
$mysql_db=mysql_select_db("wordpress",$connect_mysql) or die ("Could not Connect to Database");
$query = "SELECT * FROM category ";
$result=mysql_query($query) or die("Query Failed : ".mysql_error());
$i=0;
while($rows=mysql_fetch_array($result))
{
$roll[$i]=$rows['name_category'];
$i++;
}
$total_elmt=count($roll);
?>
<form method="POST" action="">
Select cupcake_category : <select name="sel">
<option>Select</option>
<?php 
for($j=0;$j<$total_elmt;$j++)
{
?><option><?php 
echo $roll[$j];
?></option><?php
}
?>
</select>

<input name="submit" type="submit" value="Search"/><br />

</form>

<?php

if(isset($_POST['submit']))
{
$value=$_POST['sel'];

$query2 = "SELECT * FROM cupcakes, category, taste WHERE
  cupcakes.cupcake_id = category.id_category AND
  category.id_category = taste.id_taste AND
  category.cupcake_id = taste.cupcake_id";
$result2=mysql_query($query2) or die("Query Failed : ".mysql_error());
while($row=mysql_fetch_array($result2))
{
	echo "cupcake name: ".$row['cupcake_name']."<br/>";
	echo "price: ".$row['cupcake_price']."<br/>";

}
mysql_close($connect_mysql);
}
?>

Improve this question
11
  • SO is note a code factory. What have you tried ? What does not work ? Can you post your existing code ? Commented Dec 28, 2015 at 16:36
  • 1
    it is easy when you use Ajax: Commented Dec 28, 2015 at 16:37
  • stackoverflow.com/questions/29204934/… Commented Dec 28, 2015 at 16:37
  • stackoverflow.com/questions/19039740/… Commented Dec 28, 2015 at 16:38
  • 1
    You don't need a relationship between the 3 tables. When the user selects from the two dropdowns, you get a category ID and a flavor ID from the input. Then you just select the rows in the first table with those IDs. Commented Dec 28, 2015 at 16:46

2 Answers 2

Reset to default
2

Here's how you do it using PDO

$stmt = $pdo->prepare("SELECT * FROM cupcakes 
                       WHERE id_category = :category AND id_flavor = :flavor");
$stmt->bindParam(':category', $_POST['category']);
$stmt->bindParam(':flavor', $_POST['flavor']);
$stmt->execute();

The query doesn't need to relate the 3 tables. It just gets the category ID and flavor ID from the form inputs, and uses them to select the appropriate rows from the first table.

You only use the other two tables to populate the category and flavor dropdowns.

Here's how you do the above query using the obsolete mysql extension instead of PDO:

// Protect against SQL injection
$cat = mysql_real_escape_string($_POST['category']);
$flavor = mysql_real_escape_string($_POST['flavor']);

// Build and run query
$sql = "SELECT * FROM cupcakes
        WHERE id_category = {$cat}
        AND id_flavor = {$flavor}";
$result = mysql_query($sql);
Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

Thank you so much for your help and do you have any idea how to convert it to sql ? @Barmar
I don't understand, this is SQL.
ya but i'm not familiar with PDO i just need a simple php mysql queries :)
I mean conversion to mysql :) @Barmar
I'm not going to waste my time showing you how to convert to an obsolete exdtension. Learn PDO.
|
0

I can see one problem immediately, in this SQL query:

SELECT * FROM cupcakes, cupcake category, cupcake flavor WHERE
  cupcakes.cupcake_id = cupcake category.id_category AND
  cupcake category.id_category = cupcake flavor.id_flavor AND
  cupcake category.cupcake_id = cupcake flavor.cupcake_id

It looks like you have two tables (cupcake category and cupcake flavor) that have spaces in them. This causes an ambiguity for the query parser: did you mean a table and then another keyword, or did you mean a table name with a space in it? The engine therefore outputs an error, since it does not know how to proceed.

You can still use spaces, but because of this problem doing so is generally thought not to be good practice, and the space would ideally be replaced with an underscore. However, if you must use spaces, you need to delimit the names with backticks:

SELECT * FROM cupcakes, `cupcake category` category, `cupcake flavor` flavor WHERE
  cupcakes.cupcake_id = category.id_category AND
  category.id_category = flavor.id_flavor AND
  category.cupcake_id = flavor.cupcake_id

Rather than delimit all of them, I've used table aliases of category and flavor, which I think makes it easier to read.

Improve this answer

4 Comments

Error Code: 1054 Unknown column 'category.cupcake_id' in 'where clause'
hey i repost the changes in code but i get error is it about foreign key or something?
@Sara, are you asking me what error you are getting? :-). If you have an error on your screen, why not copy and paste it here?
The meaning of the error "Unknown column X" is I think pretty clear - it means that the column you are trying to access does not exist. Either create that column or change the name of the thing you are trying to access.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.