how to write this query in PDO?

Question

I had used a query using mysql_query() and mysql_connect(), bun now i am using PDO in php

please help me, what is the best and fastest way for this query:

my old query was:

$sql = "SELECT * FROM products WHERE publish = '1' 
    AND id IN 
        (SELECT product_id FROM category_controller WHERE category_id IN 
                (SELECT id FROM categories WHERE publish = '1'))";
$result = mysql_query($sql);

my new query is:

$PDO = new PDO("mysql:host=localhost;dbname=mydb", 'root', '');
$sql = "SELECT * FROM products WHERE publish = '1' 
    AND id IN 
        (SELECT product_id FROM category_controller WHERE category_id IN 
                (SELECT id FROM categories WHERE publish = '1'))";

$result = $PDO->query($sql);

but the both ways that i had used was very slowly beacuse there are more than 5000 records in products table.

please help me to find a new and fastest way to run this query.

You can probably replace those subqueries with JOINs. Also, make sure `product_id`, `category_id`, `publish` have indexes. — gen_Eric, Sep 30 '15 at 16:13
This [link](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php/60496#60496) maybe be useful — Thiago Augustus Oliveira, Sep 30 '15 at 16:14
Did the `mysql_` version run faster than the `PDO` version????? — RiggsFolly, Sep 30 '15 at 16:14
@RiggsFolly `but the both ways that i had used was very slowly` — MonkeyZeus, Sep 30 '15 at 16:15
This question is unclear, what do you mean when you say fastest? Fast to run? Fast to encode? PDO is an abstraction layer that represents a connection between PHP and a database server. `mysql_query()`is a function also deprecated in PHP 5.5.0. — Thiago Augustus Oliveira, Sep 30 '15 at 16:23
excuse me, beacuse I am not english and I can not speak english good. sorry friends — یدالله رحیمی, Sep 30 '15 at 16:39

mcklayin · Answer 1 · 2015-09-30T16:16:59.147

0

PDO doesn't improve your speed, as I can see in this hard query. You need to explode queries by parts, if you need to increase speed. For expample: 1st query will get all id from your categories, and make array of ids. 2 query will get all product_id from cateogry_controller USING your array of ids from previous query ... WHERE category_id IN (implode(',',$arrayOfcats))

Then do the same for products

edited Sep 30 '15 at 16:16

answered Sep 30 '15 at 16:13

mcklayin

1,260
10
16

2

this is a comment, not an answer – CodeGodie Sep 30 '15 at 16:14
@CodeGodie, you r right, but i have edited my answer. – mcklayin Sep 30 '15 at 16:19
1

Not the best answer, but it is not a comment anymore, thanks for the edit. – CodeGodie Sep 30 '15 at 16:20
are you sure is it faster than that query? – یدالله رحیمی Sep 30 '15 at 17:43

score 0 · Accepted Answer · answered Sep 30 '15 at 16:24

0

You may speed this up by using the JOIN concept rather than all the sub queries like this.

SELECT p.* 
FROM products p
   JOIN category_controller cc ON cc.category_id = c.id
   JOIN categories c ON c.publish = 1
WHERE p.publish = 1

answered Sep 30 '15 at 16:24

RiggsFolly

89,708
20
100
143

how to write this query in PDO?

2 Answers2