sinisake |
28. 12. 2011. 14:01 |
FB lajkovanje...
Pokusavam ovih dana da napravim neki social networking sajt, uz pomoc Codeignitera, neku obogaljenu i ogoljenu varijantu gejsbuka, sa osnovnim funkcijama (zarad vezbe, nije nikakav posao). Postojeca baza izgleda ovako:
Kôd:
-- phpMyAdmin SQL Dump
-- version 3.3.9
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 28, 2011 at 12:40 PM
-- Server version: 5.5.8
-- PHP Version: 5.3.5
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `socialize`
--
-- --------------------------------------------------------
--
-- Table structure for table `ci_sessions`
--
CREATE TABLE IF NOT EXISTS `ci_sessions` (
`session_id` varchar(40) NOT NULL DEFAULT '0',
`ip_address` varchar(16) NOT NULL DEFAULT '0',
`user_agent` varchar(120) NOT NULL,
`last_activity` int(10) unsigned NOT NULL DEFAULT '0',
`username` varchar(30) NOT NULL,
`logged_in` int(11) NOT NULL,
PRIMARY KEY (`session_id`),
KEY `last_activity_idx` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `ci_sessions`
--
INSERT INTO `ci_sessions` (`session_id`, `ip_address`, `user_agent`, `last_activity`, `username`, `logged_in`) VALUES
('49903a602dc97815b09afa668c5633aa', '127.0.0.1', 'Mozilla/5.0 (Windows NT 5.1) AppleWebKit/535.7 (KHTML, like Gecko) Chrome/16.0.912.63 Safari/535.7', 1324982478, 'zozo', 1),
('b9c91a6c0295e735eb7924fc7e6d6e8d', '127.0.0.1', 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; InfoPath.2; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30)', 1325010798, 'testo', 1),
('ccd724a8c9c85a7da0392c131acfa1c1', '127.0.0.1', 'Mozilla/5.0 (Windows NT 5.1; rv:8.0) Gecko/20100101 Firefox/8.0', 1324982287, 'sinisake', 1);
-- --------------------------------------------------------
--
-- Table structure for table `likes`
--
CREATE TABLE IF NOT EXISTS `likes` (
`lid` int(11) NOT NULL AUTO_INCREMENT,
`post_id` int(11) NOT NULL,
`like_user` varchar(30) NOT NULL,
PRIMARY KEY (`lid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- Dumping data for table `likes`
--
INSERT INTO `likes` (`lid`, `post_id`, `like_user`) VALUES
(1, 25, 'sinisake'),
(2, 25, 'zozo'),
(3, 12, 'sinisake'),
(4, 12, 'zozo');
-- --------------------------------------------------------
--
-- Table structure for table `posts`
--
CREATE TABLE IF NOT EXISTS `posts` (
`pid` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(200) NOT NULL,
`content` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`user_id` int(11) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;
--
-- Dumping data for table `posts`
--
INSERT INTO `posts` (`pid`, `title`, `content`, `user_id`, `time`) VALUES
(1, 'Great song!', '<iframe width=''200'' height=''150'' src=''http://www.youtube.com/embed/y0LgDlvtgYs'' frameborder=''0'' allowfullscreen></iframe>', 1, '2011-12-27 18:37:24'),
(2, '35553', 'Write something here...', 1, '2011-12-27 18:51:18'),
(4, 'Novi post', 'A jbg... valda radi...', 2, '2011-12-27 19:21:43'),
(5, 'Sonic ljut!', '<iframe width=''200'' height=''150'' src=''http://www.youtube.com/embed/RtWU_X_61A8'' frameborder=''0'' allowfullscreen></iframe>', 1, '2011-12-27 19:41:34'),
(11, '', '<iframe width=''200'' height=''150'' src=''http://www.youtube.com/embed/y0LgDlvtgYs'' frameborder=''0'' allowfullscreen></iframe>', 1, '2011-12-27 23:34:49'),
(12, 'Master of Stratocaster', '<iframe width=''200'' height=''150'' src=''http://www.youtube.com/embed/1x_IVpr1oso'' frameborder=''0'' allowfullscreen></iframe>', 1, '2011-12-27 23:36:27'),
(25, '', 'Jos jedan...', 1, '2011-12-28 00:08:52'),
(26, 'Ovo je test post', 'Mislim da bi ovo trebalo da funkcionise...no, vidicemo vec...', 3, '2011-12-28 09:37:20');
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`password` varchar(32) NOT NULL,
`first_name` varchar(20) NOT NULL,
`last_name` varchar(30) NOT NULL,
`email` varchar(30) NOT NULL,
`active` tinyint(1) NOT NULL,
`show_name` tinyint(1) NOT NULL,
`image` varchar(100) NOT NULL,
`about` text NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
--
-- Dumping data for table `users`
--
INSERT INTO `users` (`id`, `username`, `password`, `first_name`, `last_name`, `email`, `active`, `show_name`, `image`, `about`) VALUES
(1, 'sinisake', '8aa87050051efe26091a13dbfdf901c6', 'Sinisa', 'Test', 'trutru@yahoo.com', 1, 1, 'sinisake1324983446.png', 'Something against you...'),
(2, 'testo', '69e153e4d7add22f245e24de590eec21', 'testo', 'testic', 'test@test.com', 1, 1, 'no_image.gif', ''),
(3, 'zozo', 'bdc7f4fae58fa4d5b4b48226896aeea9', 'zozo', 'zozoic', 'zozo@zozo.net', 1, 1, 'zozo1324982557.jpg', 'Ja sam Zozo...');
E, sad... imam problem sa prikazom... hteo bih da na strani na kojoj se nalaze postovi (statusi, sta god) imam i one standardne podatke - ko je lajkovao, koliko lajkova ima post, da se na klik ispod posta otvaraju komentari (dakle, najmanje 4 tabele bi trebalo spojiti jednim upitom...
Ovaj upit delimicno resava stvar:
Kôd:
SELECT *
FROM posts
JOIN users ON users.id = posts.user_id
LEFT JOIN likes ON likes.post_id = posts.pid
LIMIT 0 , 30
ali, onda u prikazu imam problem, jerbo dobijam onoliko postova koliko ima lajkova (postovi se dupliraju).
Da li je ovo moguce resiti jednim sveobuhvatnim upitom (ukljucujuci i komentare), ili cu morati da idem na vise upita (napravim posebne funkcije get_likes() i get_comments(), pa ih pozivam tokom ispisa (sto bi verovatno bila smrt za server?) Da li je redizajn baze resenje?
Hvala!
P.S. Naravno da ne bih odradio SELECT *, izvukao bih samo ono sto treba, ali ovo je zbog testa i preglednosti....
|