13 replies to this topic

[lol33d]

Hi guys,

i have 2 table for categorys and products

i want a query for get category names and products
only one query no more.

this is my tables:

CREATE TABLE IF NOT EXISTS `categorys` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(255) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`)

) ENGINE=MyISAM;


CREATE TABLE IF NOT EXISTS `products` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `parent` int(11) unsigned NOT NULL DEFAULT '0',

  `product` varchar(255) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`)

) ENGINE=MyISAM;

query using mysql PDO
please help


thank you

[Vaielab]

In the table products, the parent columns represent the id of categorys?

[lol33d]

yes
parent for id of category

[Vaielab]

The sql code will be

select * from products p inner join categorys c on p.parent = c.id

And the PDO code can be something that look like this

$dbh = new PDO('mysql:host=;dbname=' . $dbName, $username, $password);

$sth = $dbh->prepare('select * from products p inner join categorys c on p.parent = c.id');

$sth->execute();

while ($result = $sth->fetch())

{


}

Remember, try to never use "select *", you should name the columns insted

[WingedPanther]

SELECT `categorys`.`name`, `products`.`product` FROM `products` LEFT OUTER JOIN `categorys` ON `products`.`parent`=`categorys`.`id`;

[Vaielab]

True, a left join is probably better since it gonna show the categorys that don't have any product

[lol33d]

better is

select * from products p inner join categorys c on p.parent = c.id

OR

SELECT `categorys`.`name`, `products`.`product` FROM `products` LEFT OUTER JOIN `categorys` ON `products`.`parent`=`categorys`.`id`;

???

[Vaielab]

The first one will won't return empty categorys (category that don't have any product)

But the second one will

Depend on what you want.

[lol33d]

ok tnx

i want use 'WHERE' and 'ORDER' in query, how to use?

[Vaielab]

$sth = $dbh->prepare('select * from products p inner join categorys c on p.parent = c.id where c.name = :name order by p.product');

$sth->execute(array(':name' => 'House'));

This code will only show product from the category "House" and order it by product name
Or course you can use WingedPanther sql just add the where and the order
The reason I didn't write the category name "House" directly in the sql is that you will probably receive this information from a post or a get, and putting it in the execute function like so will prevent sql injection

[lol33d]

thank you vaielab :pinguin:

---------- Post added at 02:08 PM ---------- Previous post was at 01:56 PM ----------

how to create a query for 3 table?

this is my tables:

CREATE TABLE IF NOT EXISTS `categorys` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(255) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`)

) ENGINE=MyISAM;


CREATE TABLE IF NOT EXISTS `products` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `parent` int(11) unsigned NOT NULL DEFAULT '0',

  `product` varchar(255) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`)

) ENGINE=MyISAM;


CREATE TABLE IF NOT EXISTS `productsinfo` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `product_parent` int(11) unsigned NOT NULL DEFAULT '0',

  `price` varchar(255) NOT NULL DEFAULT '',

  `details` varchar(255) NOT NULL DEFAULT '',

  `images` varchar(255) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`)

) ENGINE=MyISAM;

parent for id of category and product_parent for id of products table

thank you

[Vaielab]

Do you wish to have show the categorys when their no product in them?