GoFuckYourself.com - Adult Webmaster Forum - Mysql / php query question.

- Fucking Around & Business Discussion (https://gfy.com/forumdisplay.php?f=26)

- - Mysql / php query question. (https://gfy.com/showthread.php?t=1054619)

Mysql / php query question.

I'm collecting data from 3 tables, of which one of the tables (social_meminfo) has multiple rows for results. How can I isolate the results of social_meminfo identifying each rows results? Or should I use two separate queries? At there should be at least two rows that would be found. Also instead of using `$en['m'.$key]` I want to use `$en['b'.$key]`

Code:

        $res = mysql_query("SELECT *, DATE_FORMAT(sm.m_lld,'%m/%d/%y') 

                            AS m_lld_formatted 

                            FROM social_members sm

                            JOIN social_meminfo smi ON (sm.m_id = smi.m_id)

                            LEFT OUTER JOIN social_memtext smt ON (sm.m_id = smt.m_id)

                            WHERE sm.m_user = '".mysql_real_escape_string($en['user'])."'");            

        if (mysql_num_rows($res) == 0) call404();

    #while ($line = mysql_fetch_assoc($res)) {

        $line = mysql_fetch_assoc($res);

        foreach ($line as $key => $value) {

                $en['m'.$key] = str_replace("\n",'<br/>',stripslashes($value));

                }

or should I just use two queries and a while statement?

I believe you want to use the SELECT DISTINCT() and the GROUP BY clause.

I decided to split the query... using the code below how can I get each row plus field result? (i.e. $line['m_pos'][0] or something like that. which would be fieldname and row result 1)

Code:

$bio = mysql_query("SELECT * FROM social_meminfo 

            WHERE m_id = '".mysql_real_escape_string($en['mm_id'])."'");

                             

if (mysql_num_rows($bio) == 0) call404();

while ($line = mysql_fetch_assoc($bio)) {

foreach ($line as $key => $value) {

        $en['b'.$key] = str_replace("\n",'<br/>',stripslashes($value));

        }

echo '<pre>'; 

    print_r($line);

echo '</pre>';

}

Best for rapid development is to use custom query functions like this

Code:

        function fetchAll($query = false, $key = false) {

                $fetch = false;



                if($query) {

                        if($result = mQuery($query)) {

                            while($res = mysql_fetch_assoc($result)) {



                                    if($key && $res[$key]) {

                                            $fetch[$res[$key]] = $res;

                                    } else {

                                            $fetch[] = $res;

                                    }

                            }

                    }

            }



            return $fetch;

        }

and than you can tune it all, you will not have your code doubled :)

Quote:

Originally Posted by ewsmedia (Post 18707634)

Best for rapid development is to use custom query functions like this

Code:

        function fetchAll($query = false, $key = false) {

                $fetch = false;



                if($query) {

                        if($result = mQuery($query)) {

                            while($res = mysql_fetch_assoc($result)) {



                                    if($key && $res[$key]) {

                                            $fetch[$res[$key]] = $res;

                                    } else {

                                            $fetch[] = $res;

                                    }

                            }

                    }

            }



            return $fetch;

        }

and than you can tune it all, you will not have your code doubled :)

still running into the problem of not being able to separate the data by the outputting rows. everytime it does a loop it grabs another row or data. but I can't figure out how to designate a specific id or var for each row+field. if I have 5 rows of data I want to be able to say echo row:2 field: m_pos ... show data

Quote:

Originally Posted by acctman (Post 18706649)

Code:

        $res = mysql_query("SELECT *, DATE_FORMAT(sm.m_lld,'%m/%d/%y') 

                            AS m_lld_formatted 

                            FROM social_members sm

                            JOIN social_meminfo smi ON (sm.m_id = smi.m_id)

                            LEFT OUTER JOIN social_memtext smt ON (sm.m_id = smt.m_id)

                            WHERE sm.m_user = '".mysql_real_escape_string($en['user'])."'");            

        if (mysql_num_rows($res) == 0) call404();

    #while ($line = mysql_fetch_assoc($res)) {

        $line = mysql_fetch_assoc($res);

        foreach ($line as $key => $value) {

                $en['m'.$key] = str_replace("\n",'<br/>',stripslashes($value));

                }

or should I just use two queries and a while statement?

All that code gives me a headache, I am a relative novice at this stuff.

I know you are trying to refine and use best coding practice and all that stuff, but unless this is a job for someone else or for a site with MASSIVE traffic why worry so much?

Use 2 or more queries - any modern server could handle it... :2 cents:

Quote:

Originally Posted by EddyTheDog (Post 18708260)

I decided to just go with two queries in post #4 but I'm running into the problem of not knowing how to separate the outputting data by rows. I need to do something like $line['m_pos'][0] where 0 means row one. That way I can properly place the data where it needs be. As of right now $line['FIELD'] doesn't let me know when row it came from

Join Database platforms not tables.

Quote:

Originally Posted by Operator (Post 18708561)

Join Database platforms not tables.

explain...? I personally am not a big fan of joins cause they can slow a system down if used to much.

decided to go with two queries and solved my row issue with an increment. so everything is working.

Code:

$bio = mysql_query("SELECT * FROM social_meminfo 

            WHERE m_id = '".mysql_real_escape_string($en['mm_id'])."'");

$i = 1;

if (mysql_num_rows($bio) == 0) call404();

    while ($line = mysql_fetch_assoc($bio)) { 

        foreach ($line as $key => $value) {

        $en['b'.$key . $i] = str_replace("\n",'<br/>',stripslashes($value));

        }

    $i++;        

    }