9 Replies - 1342 Views - Last Post: 25 May 2013 - 12:20 AM Rate Topic: -----

#1 adn258  Icon User is offline

  • D.I.C Addict

Reputation: 11
  • View blog
  • Posts: 753
  • Joined: 31-August 11

How Can You Output Multiple Categories In PHP MYSQL With One Query?

Posted 23 May 2013 - 12:19 AM

Ok so I put this in PHP because this might be a trick with php arrays (I will go into details later).

For example say you have a table of food and one of the columns specifies the category of food with 4 categories

American, Asian, Italian, Mexican.

Now lets say on another page you want to display your Menu by Category where there are Boxes That Say American, Mexican Etc. You could just run 4 Separate Queries like so

$query = 'SELECT * FROM food WHERE category=?';



And for the American Menu part of the page you use category=American etc. etc. etc.


It seems ridiculous to have to run a separate query for each category. That leaves the next idea I got where you could simply use a select all query and on output check the category like so

$query = 'SELECT * FROM food';
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($Food as $results)
{
if ($Food['category'] == 'American')
{
  echo '<li>'.$Food['Item'].'</li>;
}
}



but on the page you would have to sort through EVERY item making sure it's only that category and this ALSO SEEMS ridiculous. Is there any easy way of doing this? What's the trick? I've tried using GROUP BY and some other ideas to no avail.

Is This A Good Question/Topic? 0
  • +

Replies To: How Can You Output Multiple Categories In PHP MYSQL With One Query?

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3397
  • View blog
  • Posts: 9,609
  • Joined: 08-June 10

Re: How Can You Output Multiple Categories In PHP MYSQL With One Query?

Posted 23 May 2013 - 12:50 AM

Quote

It seems ridiculous to have to run a separate query for each category.

why? you have one query that provides data for one list. and when you need the next list you run the statement again with another category. its just a matter of the code that produces the list.


PS. you can use a PDOStatement directly in a foreach() without converting it to an array first.
Was This Post Helpful? 1
  • +
  • -

#3 adn258  Icon User is offline

  • D.I.C Addict

Reputation: 11
  • View blog
  • Posts: 753
  • Joined: 31-August 11

Re: How Can You Output Multiple Categories In PHP MYSQL With One Query?

Posted 23 May 2013 - 01:23 AM

Isn't that sort of bad for performance and hard on the server running a query 4 separate times or MORE TIMES depending on the number of categories you have?

This post has been edited by Dormilich: 23 May 2013 - 01:25 AM

Was This Post Helpful? 0
  • +
  • -

#4 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3397
  • View blog
  • Posts: 9,609
  • Joined: 08-June 10

Re: How Can You Output Multiple Categories In PHP MYSQL With One Query?

Posted 23 May 2013 - 01:28 AM

that’s why there are Prepared Statements. the DB execution plan is made, no additional parsing required and DBs are supposed to be requested for data.

in the end you have to choose your poison, either the DB does the work or your PHP script, and I don’t guarantee that PHP sorts faster than the DB.


and yes, a prepared statement executed 4 times is faster than 4 regular queries. (well, it becomes the more effective, the more times you execute it)

not to mention the advantage of built-in SQL Injection prevention ...

This post has been edited by Dormilich: 23 May 2013 - 01:31 AM

Was This Post Helpful? 3
  • +
  • -

#5 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3636
  • View blog
  • Posts: 5,759
  • Joined: 08-June 10

Re: How Can You Output Multiple Categories In PHP MYSQL With One Query?

Posted 23 May 2013 - 04:04 AM

In this context, whatever you do, stay away from GROUP_CONCAT. I've sometimes seen people use that to solve this problem; to get one row for each category with a field containing a comma separated list of sub-categories. - Multiple queries or (preferably) a reused prepared statement should definitely perform better than that. (String manipulation tends to be an expensive thing, in comparison.)
Was This Post Helpful? 1
  • +
  • -

#6 adn258  Icon User is offline

  • D.I.C Addict

Reputation: 11
  • View blog
  • Posts: 753
  • Joined: 31-August 11

Re: How Can You Output Multiple Categories In PHP MYSQL With One Query?

Posted 23 May 2013 - 12:29 PM

View PostAtli, on 23 May 2013 - 04:04 AM, said:

In this context, whatever you do, stay away from GROUP_CONCAT. I've sometimes seen people use that to solve this problem; to get one row for each category with a field containing a comma separated list of sub-categories. - Multiple queries or (preferably) a reused prepared statement should definitely perform better than that. (String manipulation tends to be an expensive thing, in comparison.)


That's probably the poison I will pick for this then. I'll simply reuse a prepared statement and change the bound parameter. That's probably the best way to go. I appreciate your help guys +1
Was This Post Helpful? 0
  • +
  • -

#7 Particule42  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 13
  • Joined: 24-May 13

Re: How Can You Output Multiple Categories In PHP MYSQL With One Query?

Posted 24 May 2013 - 10:42 AM

Even if you found a solution, why not use MySQL ORDER BY clause on category field in your select all query ?
Was This Post Helpful? 0
  • +
  • -

#8 adn258  Icon User is offline

  • D.I.C Addict

Reputation: 11
  • View blog
  • Posts: 753
  • Joined: 31-August 11

Re: How Can You Output Multiple Categories In PHP MYSQL With One Query?

Posted 24 May 2013 - 04:43 PM

View PostParticule42, on 24 May 2013 - 10:42 AM, said:

Even if you found a solution, why not use MySQL ORDER BY clause on category field in your select all query ?


The problem with this idea is it makes no difference. While you would get back a list of categories for example
Mexican food item 1
mexican food item 2
mexican food item 3

italian food item 1
italian food item 2
italian food item 3

etc. etc. etc.

you wouldn't have any idea where one started or ended without checking in which case you're back to using something like if (category == some category)

and you might as well just use a prepared statement then as that's probably more efficient.
Was This Post Helpful? 0
  • +
  • -

#9 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3636
  • View blog
  • Posts: 5,759
  • Joined: 08-June 10

Re: How Can You Output Multiple Categories In PHP MYSQL With One Query?

Posted 24 May 2013 - 04:59 PM

View PostParticule42, on 24 May 2013 - 05:42 PM, said:

Even if you found a solution, why not use MySQL ORDER BY clause on category field in your select all query ?

I can think of two reasons:

  • You'd be getting a lot more data than you need. Each item would be accompanied by it's category, which is unnecessary. Keeping the amount of data you are transferring from MySQL to PHP to a minimum just make sense, no?

  • The ORDER BY clause can impact the performance of the query. Often for it to work, MySQL has to internally create temporary tables, or even resort to other more drastic measures for large sets, which you want to avoid.


The prepared statements Dormilich suggested would be the most efficient way, while keeping the amount of data retrieved to only exactly what you need.
Was This Post Helpful? 0
  • +
  • -

#10 Particule42  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 13
  • Joined: 24-May 13

Re: How Can You Output Multiple Categories In PHP MYSQL With One Query?

Posted 25 May 2013 - 12:20 AM

OK so the problem with ORDER BY clause on select all query is either in PHP with memory or in MySQL with execution time.

Thanks, now I understand.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1