SQL Select x-row from table2 if y.table1=y.table2 multiple tables

0
votes

I have 2 different tables: categories and products. Each table has category_id column. For outputting the arrays when SELECT-ing, I am using echo htmlentities.

What is the SQL syntax for selecting fields like product name and price where products category_id=categories category_id? (the row headers are the same -> name (on prod.) and name (on cat).

Bear in mind that I first want to select the products based on criterias from the product table and afterwards if category_id from cat. table equals category_id from product table then it will show the category name (WHICH IS IN category table)

Here is the original Code:

<?php
include('includes/global.php');

$sql = $db->query("SELECT name FROM ".DB_PREFIX."products WHERE active=1 AND closed=0 AND deleted=0 AND approved = 1 AND name LIKE '%".htmlentities($_GET["search"],  ENT_QUOTES)."%' LIMIT 20");

while($product_array = $db->fetch_array($sql)) {
    echo htmlentities($product_array["name"], ENT_QUOTES, $lang[codepage])."\n";
}

This is what I tried: 

$sql = $db->query("SELECT a.name,a.buy_price,c.name FROM ".DB_PREFIX."products a WHERE active=1 AND closed=0 AND deleted=0 AND approved = 1 AND name LIKE '% ".htmlentities($_GET["search"],  ENT_QUOTES)."%' INNER JOIN ".DB_PREFIX."categories c ON a.category_id = c.category_id LIMIT 20");

while($product_array = $db->fetch_array($sql)) {
    echo htmlentities($product_array["name"], ENT_QUOTES, $lang[codepage])." for ";
    echo htmlentities($product_array["buy_price"], ENT_QUOTES, $lang[codepage])." from category ";
    echo htmlentities($product_array[" * categoryname"], ENT_QUOTES, $lang[codepage])."\n";
}

Any suggestions?

2
phpmysqlsqlselect
Which DBMS are you using? Postgres? Oracle? – a_horse_with_no_name
it's MySQL @a_horse_with_no_name – Alex Daniel M

2 Answers

0
votes

I think this is a matter of ordering, the entire table needs to be constructed before anything else.

SELECT a.name,a.buy_price,c.name FROM ".DB_PREFIX."products a 
INNER JOIN ".DB_PREFIX."categories c ON a.category_id = c.category_id 
WHERE active=1 AND closed=0 AND deleted=0 AND approved = 1 AND name LIKE '% ".htmlentities($_GET["search"],  ENT_QUOTES)."%' 
LIMIT 20
0
votes

This is the final code i got in which i get no output when searching (in the respective searchbox). I personally think there is a problem merging the data from the two tables. Or maybe i this is not the proper way to echo the htmlentities.

$sql = $db->query("SELECT a.name,c.name,a.buyout_price FROM ".DB_PREFIX."auctions a INNER JOIN ".DB_PREFIX."categories c ON a.category_id = c.category_id WHERE active=1 AND closed=0 AND deleted=0 AND approved = 1 AND a.name LIKE '% ".htmlentities($_GET["search"],  ENT_QUOTES)."%' LIMIT 20");

while($product_array = $db->fetch_array($sql)) {
    echo htmlentities($product_array["a.name"], ENT_QUOTES, $lang[codepage])." from category ";
    echo htmlentities($product_array["c.name"], ENT_QUOTES, $lang[codepage])." for ";
    echo htmlentities($product_array["a.buyout_price"], ENT_QUOTES, $lang[codepage])." EUR \n";}