MySQL Forums
Forum List  »  Italian

introdurre join della tabella relationship
Posted by: stefano searedone
Date: December 05, 2011 03:03PM

Sera a tutti, spero che possiate risolvermi questo immenso problema

ho 4 tabelle

users
messages
comments
relationship

e una functions ..

la functions originale è questa e tutto funziona


<?php

class Wall_Updates {

public $perpage = 10; // Uploads perpage



// Updates
public function Updates($uid,$lastid)
{
// More Button
$morequery="";
if($lastid)
$morequery=" and M.msg_id<'".$lastid."' ";
// More Button End

$query = mysql_query("SELECT M.msg_id, M.uid_fk, M.message, M.created, U.username,M.uploads FROM messages M, users U WHERE M.uid_fk=U.uid and M.uid_fk='$uid' $morequery order by M.msg_id desc limit " .$this->perpage) or die(mysql_error());

while($row=mysql_fetch_array($query))
$data[]=$row;
return $data;

}
// Updates
public function Total_Updates($uid)
{

$query = mysql_query("SELECT M.msg_id, M.uid_fk, M.message, M.created, U.username,M.uploads FROM messages M, users U WHERE M.uid_fk=U.uid and M.uid_fk='$uid' $morequery order by M.msg_id ") or die(mysql_error());
$data=mysql_num_rows($query);
return $data;

}

//Comments
public function Comments($msg_id,$second_count)
{
$query='';
if($second_count)
$query="limit $second_count,2";
$query = mysql_query("SELECT C.com_id, C.uid_fk, C.comment, C.created, U.username FROM comments C, users U WHERE C.uid_fk=U.uid and C.msg_id_fk='$msg_id' order by C.com_id asc $query") or die(mysql_error());
while($row=mysql_fetch_array($query))
$data[]=$row;
if(!empty($data))
{
return $data;
}
}


//Avatar Image
//From database
public function Profile_Pic($uid)
{
$query = mysql_query("SELECT profile_pic FROM `users` WHERE uid='$uid'") or die(mysql_error());
$row=mysql_fetch_array($query);
if(!empty($row['profile_pic']))
{
$profile_pic_path=$base_url.'profile_pic/';
$data= $profile_pic_path.$row['profile_pic'];
return $data;
}
else
{
$data="icons/default.jpg";
return $data;
}
}
// Gravatar Image
public function Gravatar($uid)
{
$query = mysql_query("SELECT email FROM `users` WHERE uid='$uid'") or die(mysql_error());
$row=mysql_fetch_array($query);
if(!empty($row))
{
$email=$row['email'];
$lowercase = strtolower($email);
$imagecode = md5( $lowercase );
$data="http://www.gravatar.com/avatar.php?gravatar_id=$imagecode";;
return $data;
}
else
{
$data="default.jpg";
return $data;
}
}

//Insert Update
public function Insert_Update($uid, $update,$uploads)
{
$update=mysql_real_escape_string($update);
$time=time();
$ip=$_SERVER['REMOTE_ADDR'];
$query = mysql_query("SELECT msg_id,message FROM `messages` WHERE uid_fk='$uid' order by msg_id desc limit 1") or die(mysql_error());
$result = mysql_fetch_array($query);

if ($update!=$result['message']) {
$uploads_array=explode(',',$uploads);
$uploads=implode(',',array_unique($uploads_array));
$query = mysql_query("INSERT INTO `messages` (message, uid_fk, ip,created,uploads) VALUES (N'$update', '$uid', '$ip','$time','$uploads')") or die(mysql_error());
$newquery = mysql_query("SELECT M.msg_id, M.uid_fk, M.message, M.created, U.username FROM messages M, users U where M.uid_fk=U.uid and M.uid_fk='$uid' order by M.msg_id desc limit 1 ");
$result = mysql_fetch_array($newquery);

return $result;
}
else
{
return false;
}


}

//Delete update
public function Delete_Update($uid, $msg_id)
{
$query = mysql_query("DELETE FROM `comments` WHERE msg_id_fk = '$msg_id' and uid_fk='$uid' ") or die(mysql_error());
$query = mysql_query("DELETE FROM `messages` WHERE msg_id = '$msg_id' and uid_fk='$uid'") or die(mysql_error());
return true;

}

//Image Upload
public function Image_Upload($uid, $image)
{
//Base64 encoding
$path="uploads/";
$img_src = $path.$image;
$imgbinary = fread(fopen($img_src, "r"), filesize($img_src));
$img_base = base64_encode($imgbinary);
$ids = 0;
$query = mysql_query("insert into user_uploads (image_path,uid_fk)values('$image' ,'$uid')") or die(mysql_error());
$ids = mysql_insert_id();
return $ids;
}

//get Image Upload
public function Get_Upload_Image($uid,$image)
{
if($image)
{
$query = mysql_query("select id,image_path from user_uploads where image_path='$image'") or die(mysql_error());
}
else
{
$query = mysql_query("select id,image_path from user_uploads where uid_fk='$uid' order by id desc ") or die(mysql_error());
}

$result = mysql_fetch_array($query);

return $result;
}

//Id Image Upload
public function Get_Upload_Image_Id($id)
{
$query = mysql_query("select image_path from user_uploads where id='$id'") or die(mysql_error());
$result = mysql_fetch_array($query);

return $result;
}

//Insert Comments
public function Insert_Comment($uid,$msg_id,$comment)
{
$comment=mysql_real_escape_string($comment);

$time=time();
$ip=$_SERVER['REMOTE_ADDR'];
$query = mysql_query("SELECT com_id,comment FROM `comments` WHERE uid_fk='$uid' and msg_id_fk='$msg_id' order by com_id desc limit 1 ") or die(mysql_error());
$result = mysql_fetch_array($query);

if ($comment!=$result['comment']) {
$query = mysql_query("INSERT INTO `comments` (comment, uid_fk,msg_id_fk,ip,created) VALUES (N'$comment', '$uid','$msg_id', '$ip','$time')") or die(mysql_error());
$newquery = mysql_query("SELECT C.com_id, C.uid_fk, C.comment, C.msg_id_fk, C.created, U.username FROM comments C, users U where C.uid_fk=U.uid and C.uid_fk='$uid' and C.msg_id_fk='$msg_id' order by C.com_id desc limit 1 ");
$result = mysql_fetch_array($newquery);

return $result;
}
else
{
return false;
}

}

//Delete Comments
public function Delete_Comment($uid, $com_id)
{
$query = mysql_query("DELETE FROM `comments` WHERE uid_fk='$uid' and com_id='$com_id'") or die(mysql_error());
return true;

}



}

?>


se aggiungo la relazione con la tabella relationship che serve a far si che i post, postati si vedano solo dagli amici che sono amici di chi posta, un po' il meccanismo di facebook ..

CREATE TABLE IF NOT EXISTS `relationship` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`user_1` bigint(20) NOT NULL,
`user_2` bigint(20) NOT NULL,
`amico` enum('S','N') NOT NULL DEFAULT 'N',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=67 ;

--
-- Dump dei dati per la tabella `relationship`
--

INSERT INTO `relationship` (`id`, `user_1`, `user_2`, `amico`) VALUES
(12, 54, 57, 'S'),
(9, 54, 57, 'S'),
(8, 54, 55, 'S'),
(7, 54, 58, 'N'),
(65, 55, 54, 'S'),
(66, 54, 61, 'S');


in questo modo tutto funziona ma come mi logo con un'altro utente vedo il post postato dal mio amico ripetuto per tutti gli utenti registrati come se fosse un refuso ...

<?php


class Wall_Updates {

public $perpage = 100; // Uploads perpage



// Updates
public function Updates($uid,$lastid)
{
// More Button
$morequery="";
if($lastid)
$morequery=" and M.msg_id<'".$lastid."' ";
// More Button End

$query = mysql_query("SELECT M.msg_id, M.uid_fk, M.message, M.created, U.username, M.uploads FROM messages M, users U WHERE M.uid_fk='$uid' AND M.uid_fk = U.uid OR M.uid_fk IN ( SELECT user_1 FROM relationship AS R WHERE R.user_2 = '$uid' AND R.amico = 'S' ) OR M.uid_fk IN ( SELECT user_2 FROM relationship AS R WHERE R.user_1 = '$uid' AND R.amico = 'S') $morequery order by M.msg_id desc limit " .$this->perpage) or die(mysql_error());

while($row=mysql_fetch_array($query))
$data[]=$row;

return $data;


}


// Updates
public function Total_Updates($uid)
{

$query = mysql_query("SELECT M.msg_id, M.uid_fk, M.message, M.created, U.username, M.uploads FROM messages M, users U WHERE M.uid_fk = U.uid AND M.uid_fk='$uid' AND M.uid_fk = U.uid OR M.uid_fk IN ( SELECT user_1 FROM relationship AS R WHERE R.user_2 = '$uid' AND R.amico = 'S' ) OR M.uid_fk IN ( SELECT user_2 FROM relationship AS R WHERE R.user_1 = '$uid' AND R.amico = 'S') $morequery order by M.msg_id ") or die(mysql_error());

$data=mysql_num_rows($query);
return $data;

}


Allora mi sono detto faccio una join, ma non riesco a capire come mai mi da' sempre un errore , ho provato così ma l'errore è questo:

Unknown column 'messages.usersuid' in 'on clause'

$query = mysql_query( "SELECT * FROM
messages INNER JOIN users ON (messages.usersuid=user.uid )
INNER JOIN relationship ON ( messages.relationshipid = relationship.id AND user_2 = '$uid' )
OR messages.uid_fk IN
( messages.relationshipid = relatioship.id AND user_1 = '$uid' )
WHERE
messages.uid_fk='$uid' AND relationship.user_1 = 'S' AND relationship.user_2 = 'S' $morequery order by M.msg_id desc limit " .$this->perpage) or die(mysql_error());

Help spero che qualcuno mi aiuto è un mese che provo e riprovo

Options: ReplyQuote


Subject
Views
Written By
Posted
introdurre join della tabella relationship
3643
December 05, 2011 03:03PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.