php mysql saving query results

Question

i have a query that goes like this:

            $get_feed_amount = mysql_query("

                SELECT COUNT(*) as num
                FROM `migo_feeds`
                WHERE
                (
                    (
                        feed_type='10' ||
                        feed_type='11' ||
                        feed_type='28' ||
                        feed_type='27' ||
                        feed_type='30' ||
                        feed_type='31'

                    )
                    AND
                    (
                        from_id IN (

                            SELECT DISTINCT from_id
                            FROM `migo_relations`
                            WHERE (
                                (to_id='".$_SESSION['user_id']."')

                                AND

                                (rel_processed='1' AND rel_accepted='1')
                            )                       

                        )

                        OR

                        to_id IN (

                            SELECT DISTINCT from_id
                            FROM `migo_relations`
                            WHERE (
                                (to_id='".$_SESSION['user_id']."')

                                AND

                                (rel_processed='1' AND rel_accepted='1')
                            )                               

                        )

                        OR

                        from_id=".$_SESSION['user_id']."

                        OR

                        to_id=".$_SESSION['user_id']."

                    )
                    AND
                    feed_deleted='0'
                );

            ");

as you can see im allready selecting friends twice in this query

and also later i do queries like this:

            $migo_groups = mysql_query("

                SELECT 
                    group_id,
                    group_title,
                    group_desc,
                    group_cat_name,
                    group_area,
                    group_city,
                    group_quarter,
                    group_pic,
                    group_tmb,
                    group_members_amount
                FROM
                    `migo_groups`
                WHERE (

                    group_type=1

                    AND

                    group_id IN (

                        SELECT group_id
                        FROM `migo_group_membership`
                        WHERE (

                            membership_type IN (1,2,3)

                            AND

                            to_id IN (

                                SELECT DISTINCT from_id
                                FROM `migo_relations`
                                WHERE (
                                    (to_id='".$_SESSION['user_id']."')

                                    AND

                                    (rel_processed='1' AND rel_accepted='1')
                                )                                   
                            )
                        )
                    )
                )
                ORDER BY RAND() LIMIT 4;
            ");

selecting friends once again.. this seems a little bit of waste of resources. Especially if the tables are very large, things will probably take forever to load, or not even load at all.

My question is, can i just do a friend query in the beginning and use the result everywhere? Is that any good at all?

I was thinking perhaps save the resutls in an array or something, is that bad? What would be the best way to run things?

Regards, Alexander

dynamic · Accepted Answer · 2011-06-07 14:01:45Z

1

You can save the results of the first query in an array and then using only the array for your second need.

Example:

$result=array();
$query=mysql_query("SELECT complex sql [...]");
while($r = mysql_fetch_assoc($query)) {
    $result[] = $r;
    /*your first operation here*/
}

Now in your result you have all the results of the first query, and you can use that array to do your second operation without the need of other queries.

foreach($result as $row) {
      // code
}

Also note you should use PDO.

answered Jun 7, 2011 at 14:01

dynamic

48.2k56 gold badges163 silver badges236 bronze badges

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

2 Comments

Alexander Over a year ago

but is this recommended? i mean i dont know which one is faster, for the php to write out an arra or the database to do a simple friend request query with indexing

dynamic Over a year ago

using an array instead to do a query it's up to 10x faster

Collectives™ on Stack Overflow

php mysql saving query results

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related