Select from three tables SQL and PHP

Question

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);
}
?>

SO is note a code factory. What have you tried ? What does not work ? Can you post your existing code ? — Emrys Myrooin, Dec 28 '15 at 16:36
http://stackoverflow.com/questions/29204934/how-to-populate-dependable-drop-down-using-ajax-and-php — Gouda Elalfy, Dec 28 '15 at 16:37
http://stackoverflow.com/questions/19039740/populate-one-dropdown-list-based-on-the-selection-of-other-dropdown-list — Gouda Elalfy, Dec 28 '15 at 16:38
http://stackoverflow.com/questions/18755872/jquery-show-hide-drop-down-options-based-on-another-drop-down-option — Gouda Elalfy, Dec 28 '15 at 16:38
i couldn't find out how to do the relationship between the 3 tables? — Sara Ben Chamkha, Dec 28 '15 at 16:40
you should read Sara and learn, this website for advanced question. like if you you write your code and faced advanced bug or error so this website can help. but you didn't find here who write your code. — Gouda Elalfy, Dec 28 '15 at 16:44
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. — Barmar, Dec 28 '15 at 16:46
@GoudaElalfy I doubt those links will really be of any help, since the main question seems to be about querying multiple tables. I could be wrong though ;-) — Funk Forty Niner, Dec 28 '15 at 16:46
Ya but querying is the problem I found alot of them on the internet to fetch data from 3 tables but none of them seem to work can you guys provide me with a general script that can help me to implement mine :) @Barmar — Sara Ben Chamkha, Dec 30 '15 at 08:11
This old question seems to have tailed off without a solution, and is now abandoned. Since the material we are left with is not very useful, I think it is appropriate to close as "needs focus". — halfer, Jan 10 '20 at 15:34

score 2 · Answer 1 · edited Jan 10 '16 at 21:58

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);

edited Jan 10 '16 at 21:58

halfer

19,824
17
99
186

answered Dec 30 '15 at 08:19

Barmar

741,623
53
500
612

Thank you so much for your help and do you have any idea how to convert it to sql ? @Barmar – Sara Ben Chamkha Dec 30 '15 at 21:24
I don't understand, this is SQL. – Barmar Dec 30 '15 at 21:25
ya but i'm not familiar with PDO i just need a simple php mysql queries :) – Sara Ben Chamkha Dec 30 '15 at 21:30
I mean conversion to mysql :) @Barmar – Sara Ben Chamkha Dec 30 '15 at 21:36
I'm not going to waste my time showing you how to convert to an obsolete exdtension. Learn PDO. – Barmar Dec 30 '15 at 21:37
thank you for your help is there any online conversion of softawre that I can use ? plz I need it :) @Barmar – Sara Ben Chamkha Dec 30 '15 at 21:44
Not that I know of. There are tools for going the other way, because many people are trying to upgrade their software. No one should be downgrading, so there's no need for tools that go from PDO->mysql. – Barmar Dec 30 '15 at 21:45
So what to do buddy @Barmar :( – Sara Ben Chamkha Dec 30 '15 at 21:48
Just replace the placeholders with variables: `WHERE id_category = {$_POST['category']} AND id_flavor = {$_POST['flavor']}". How have you been writing queries before this, that you don't know how to do that? – Barmar Dec 30 '15 at 21:51
OK, I've shown how to do it using mysql. But if you're used to mysql, it should be obvious how to do it. – Barmar Dec 30 '15 at 21:54
i just posted my code check it plz i still have errors :( – Sara Ben Chamkha Dec 30 '15 at 23:37

halfer · Answer 2 · 2020-01-10T15:32:09.653

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.

edited Jan 10 '20 at 15:32

answered Dec 30 '15 at 23:49

halfer

19,824
17
99
186

Error Code: 1054 Unknown column 'category.cupcake_id' in 'where clause' – Sara Ben Chamkha Dec 31 '15 at 00:33
hey i repost the changes in code but i get error is it about foreign key or something? – Sara Ben Chamkha Dec 31 '15 at 00:55
@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? – halfer Dec 31 '15 at 01:00
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. – halfer Dec 31 '15 at 01:01

Select from three tables SQL and PHP

2 Answers2