Mysql / php query question.

[acctman]

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]`

        $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?

[KickAssJesse]

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

[BestXXXPorn]

Also:

http://www.php.net/nl2br

[acctman]

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)

$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>';
}

[ewsmedia]

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

	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

[acctman]

Originally Posted by ewsmedia

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

	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

[EddyTheDog]

Originally Posted by acctman

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]`

        $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...

[acctman]

Originally Posted by EddyTheDog

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...

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

[Operator]

Join Database platforms not tables.

[acctman]

Originally Posted by Operator

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.

$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++;        
    }