Storing Data into PHP Array

Question

total_starsHere is the code, it is the best explanation of what I am trying to do:

$total_stars = array();
$query = "SELECT items_id FROM items";
$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {
    $item_id = $row['items_id'];
    $sql = "Select Count(item_id) FROM ratings WHERE item_id = '{$item_id}'";
    $result = mysql_query($sql);
    $total_stars[] = mysql_fetch_array($result);
}

//  To see the output by entering the URL I want to print_r  
print_r($total_stars);

// In the end, I am trying to JSON encode this and will later output in Java for Android
print(json_encode($total_stars));

(So as not to confuse, the items_id is in items table, item_id (without an 's') is in ratings table)

Now in plain English:

I am looping through each row in the items table. As I do that, in each loop I am referring to another table called "Ratings", and getting a count of how many ratings for that item.

For example I want an array to look like (65, 43, 55, 43, etc). Sample numbers of course. But each represent the total amount of ratings for each item in a table. How can I get print__r to display the results of the SQL statement inside the loop?

UPDATED CODE TRYING TO USE JOIN:

$query = "SELECT items_id FROM items";

$q = 'SELECT 
    items.items_id, 
    COUNT(ratings.item_id) AS rate 
    FROM `items`
    LEFT JOIN ratings ON (ratings.item_id = items.items_id)
    GROUP BY items_id';
$result = mysql_query($q);

while ($row = mysql_fetch_array($result)) {

    $total_stars = mysql_fetch_array($result);

}

print_r($total_stars);

print_r outputs this: Array ( [0] => 783 [items_id] => 783 [1] => 0 [rate] => 0 )

You should consider using a join query instead.

Jonathan
– Jonathan

2012-07-20 15:20:31 +00:00
Commented Jul 20, 2012 at 15:20 — Jonathan
– Jonathan, Commented Jul 20, 2012 at 15:20

Jonathan · Accepted Answer · 2012-07-20 16:17:59Z

3

$total_stars = array();
while ($row = mysql_fetch_array($result))
{
    $item_id = $row['items_id'];
    $sql = "Select Count(item_id) FROM ratings WHERE item_id = '{$item_id}'";
    $result = mysql_query($sql);
    $row = mysql_fetch_row($result);
    $total_stars[] = row[0];
}

This should do it.

Don't forget that mysql_fetch_array returns an array with index for the columns. So mysql_fetch_array($result) would be an array that you want the first element in (result from MySQL function COUNT()), not the count itself.

But as I said in my comment, use a join query instead. You will reduce the amount of queries to the mysql server from the number of rows in your first query+1 (which could be alot!) to just one, which is a huge improvement.

Update: Here's how to do it with a join query (query by Sven):

$q = 'SELECT 
    items.items_id, 
    COUNT(ratings.item_id) AS rate 
    FROM `items`
    LEFT JOIN ratings ON (ratings.item_id = items.items_id)
    GROUP BY items_id';
$result = mysql_query($q);

$total_stars = array();
while ($row = mysql_fetch_array($result))
{
    $total_stars[] = $row['rate']; // Stars count is stored in $row['rate']
    echo "Item id ".$row['items_id']." has rating ".$row['rate'];

}

edited Jul 20, 2012 at 16:17

answered Jul 20, 2012 at 15:23

Jonathan

3,0093 gold badges28 silver badges36 bronze badges

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

8 Comments

TheLettuceMaster Over a year ago

hey thanks... however, I just realized Icrossed some code up. What I described below is different than what I was trying to do above. In the code, I am getting the rating for a single items_id. What I WANT to do is go through each items row and refer to the ratings table and count the corresponding ratings that matchs the items_id of first tables row.. I adjusted the first query slightly to maker it more in line with what I am saying. SO I want it go to items_id 1 and count the total ratings with that id, then to items_id 2, etc etc - and store the results in the array

Jonathan Over a year ago

I'm pretty sure thats what this does, have you tried it? It gets the rating for a single item_id in each iteration of the loop yes and stores it in the array, so in the end its gonna be for every item in your first query.

TheLettuceMaster Over a year ago

I'm checking it out.. will let you know... right now I am getting this with the print_r 'Array ( [0] => 29 )' not sure where that comes from. I can tell you for a fact I have 30 plus items, and was expecting about 30 numbers in the array (each being the total ratings of the item).

TheLettuceMaster Over a year ago

I just confirmed; item 1 has 29 ratings. So its not looping through each of the 30 plus rows and putting total in Array.

Jonathan Over a year ago

Did you try my update or the old post? Otherwise I'm not sure. Check how many times the loop iterates by putting an echo inside the loop for debugging. If it only echoes once then there cant be 30 items.

|

Sven van Zoelen · Accepted Answer · 2012-07-20 21:01:51Z

1

Why not use a JOIN as suggested in the comments? If you want an array with all the ratings of the items, then this is a way (didn't test the PHP db code, but i did test the query).

$total_stars = array();

$q = 'SELECT 
        items.item_id,
        COUNT(ratings.item_id) AS rate 
      FROM `items`
      LEFT JOIN ratings ON (ratings.item_id = items.item_id)
      GROUP BY item_id';
$result = mysql_query($q);

$total_stars = mysql_fetch_array($result);

You should use MYSQLi or PDO_MYSQL instead. more info MYSQLi, more info PDO

And here you can find more info about JOINS: Info about MYSQL joins

edited Jul 20, 2012 at 21:01

answered Jul 20, 2012 at 15:49

Sven van Zoelen

7,2595 gold badges40 silver badges49 bronze badges

5 Comments

Jonathan Over a year ago

Do you really need the GROUP BY statement though? As COUNT() only gives you one row per item anyways. Or am I wrong?

TheLettuceMaster Over a year ago

I tried this method, and this is what I get Array ( [0] => 1 [items_id] => 1 [1] => 29 [rate] => 29 ) -- still not managing to loop through each row. Only grabbing the rating total of row 1. Will update code.

Jonathan Over a year ago

You shouldn't just copy code like that, you should understand what it does and it wouldn't have been a problem. This query should be at the top, and there shouldn't be any query at all in the loop.

TheLettuceMaster Over a year ago

Edited code once more with output. Now it shows 783 items. I have actually never used a JOIN statement before. I am trying to understand it as I go and by the results I get as I am copying. Yes, ideally, i'd study it then apply it. I don't have that luxury at the moment. But I really do thank you for your help.

Sven van Zoelen Over a year ago

@MrAzulay Yes you do need the GROUP BY. The query will also return rows found in ratings because of the JOIN LEFT. We want 1 row per item, so by using the GROUP BY we remove all the doubles. @KickingLettuce I tested the query with a replica of your given data. The query will return the right results. This snippet of code sets the $total_stars variable. If you want to loop through all the total stars of each item, then loop through the $total_stars after the code i.e. foreach($total_stars as $stars) { .. }

Collectives™ on Stack Overflow

Storing Data into PHP Array

2 Answers 2

8 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related