Trouble getting mysql results to php variables

Question

I have this PHP code of:

$count = "select 
              count(fruit) as total,
              sum(fruit like '%apple%') as apple,
              sum(fruit like '%orange%') as orange
          FROM my_wonderful_table_of_fruits";

$count = mysql_query($count);
$count = mysql_fetch_row($count);

I'm trying to store these in a variable and can't seem to catch them :/

My code is:

while ($row = mysql_fetch_array($count)) {
    $count_total = $row['total'];
    $count_apple = $row['apple'];
    $count_orange = $row['orange'];
}

And I was expecting to be able to echo them like so:

echo "$count_total[0] is the total of $count_apple apples and $count_orange oranges

When I run this query in MySQL Admin, I get a nice row that looks like:

total    apple    orange
  5        3         2

Anyone know how what I'm doing wrong? (besides the fact that I'm using the 'evil' version of mysql_fetch_row)

Much appreciated!

"Anyone know what's wrong?" --- why do you think it is wrong? Provide your code in a single piece, not splitted. Do you perform mysql_fetch_array twice? — zerkms
– zerkms, Commented Sep 4, 2012 at 0:40
Please try to move to mysqli or pdo. mysql_* are deprecated. If you are learning from a book teaching mysql_* functions, it is old and outdated. — Ivo
– Ivo, Commented Sep 4, 2012 at 0:44

Niet the Dark Absol · Accepted Answer · 2012-09-04 00:41:58Z

1

Since your query only produces one row, you can simplify it to the following:

list($total,$apple,$orange) = mysql_fetch_row(mysql_query("
    SELECT COUNT(`fruit`) AS `total`,
      SUM(`fruit` LIKE '%apple%') AS `apple`,
      SUM(`fruit` LIKE '%orange%') AS `orange`,
    FROM `my_wonderful_table_of_fruits`"));
echo "$total is the total of $apple apples and $orange oranges.";

answered Sep 4, 2012 at 0:41

Niet the Dark Absol

326k86 gold badges480 silver badges604 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Waleed Khan Over a year ago

You say simplify, but I say mangle into a difficult-to-read command.

d-_-b Over a year ago

It's not pretty, but it works! Thanks Kolink! ...Just waiting to see if anyone has a neater way.

Ivo · Accepted Answer · 2012-09-04 00:47:58Z

0

You need to reanalyse what you're doing. Your SQL will only return one row with some aggregate results, but it seems you expect to be able to iterate over several rows of results (due to your while()).

What your sql does and how you're trying to use it are two different things.

answered Sep 4, 2012 at 0:47

Ivo

5,4302 gold badges21 silver badges19 bronze badges

1 Comment

d-_-b Over a year ago

Very true, I see what you mean. Thanks!

Collectives™ on Stack Overflow

Trouble getting mysql results to php variables

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related