mysql - combining columns and tables

Posted by Phil Jackson on Stack Overflow See other posts from Stack Overflow or by Phil Jackson
Published on 2010-06-16T04:52:21Z Indexed on 2010/06/16 6:32 UTC
Read the original article Hit count: 227

Filed under:
|
|
|

Hi, I'm not much of a SQL man so I'm seeking help for this one. I have a site where I have a database for all accounts and whatnot, and another for storing actions that the user has done on the site.

Each user has their own table but I want to combine the data of each user group ( all users that are "linked together" ) and order that data in the time the actions took place.

Heres what I have;

<?php
$query = "SELECT `TALKING_TO` FROM `nnn_instant_messaging` WHERE `AUTHOR` = '" . DISPLAY_NAME . "' AND `TALKING_TO` != ''";
$query = mysql_query( $query, $CON ) or die( "_error_ " . mysql_error());
if( mysql_num_rows( $query ) != 0 ) {
    $table_str = "";
    $select_ref_clause = "( ";
    $select_time_stamp_clause = "( ";
    while( $row = mysql_fetch_array( $query ) ) {
        $table_str .= "`actvbiz_networks`.`" . $row['TALKING_TO'] . "`, ";  
        $select_ref_clause .= "`actvbiz_networks`.`" . $row['TALKING_TO'] . ".REF`, ";
        $select_time_stamp_clause .= "`actvbiz_networks`.`" . $row['TALKING_TO'] . ".TIME_STAMP`, ";
    }   
    $table_str = $table_str . "`actvbiz_networks`.`" . DISPLAY_NAME . "`";
    $select_ref_clause = substr($select_ref_clause, 0, -2) . ") AS `REF`, ";
    $select_time_stamp_clause = substr($select_time_stamp_clause, 0, -2) . " ) AS `TIME_STAMP`";    
}else{
    $table_str = "`actvbiz_networks`.`" . DISPLAY_NAME . "`";   
    $select_ref_clause = "`REF`, ";
    $select_time_stamp_clause = "`TIME_STAMP`";
}
$where_clause = $select_ref_clause . $select_time_stamp_clause;

$query = "SELECT " . $where_clause . " FROM " . $table_str . " ORDER BY TIME_STAMP";
die($query);
$query = mysql_query( $query, $CON ) or die( "_error_ " . mysql_error());
if( mysql_num_rows( $query ) != 0 ) {

}else{
?>
<p>Currently no actions have taken place in your network.</p> 
<?php   
}
?>

The code above returns the sql statement:

SELECT ( `actvbiz_networks`.`john_doe.REF`, `actvbiz_networks`.`Emmalene_Jackson.REF`) AS `REF`, ( `actvbiz_networks`.`john_doe.TIME_STAMP`, `actvbiz_networks`.`Emmalene_Jackson.TIME_STAMP` ) AS `TIME_STAMP` FROM `actvbiz_networks`.`john_doe`, `actvbiz_networks`.`Emmalene_Jackson`, `actvbiz_networks`.`act_web_designs` ORDER BY TIME_STAMP

I really am learning on my feet with SQL.

Its not the PHP I have a problem with ( I can quite happly code away with PHP ) I'ts just help with the SQL statement.

Any help much appreciated,

REgards,

Phil

© Stack Overflow or respective owner

Related posts about mysql

Related posts about join