SQL baze podataka - Sponzor: Baze-Podataka.net MySQL, MSSQL, Oracle, Access, ODBC. Ako imate problem brže i preciznije ćete dobiti odgovor ako priložite strukturu tabela ili skript koji kreira tabele i puni ih test podacima umesto što to problem opisujete samo rečima. Sponzor: Baze-Podataka.net - Blog o bazama podataka |
|
Alati teme | Način prikaza |
28. 12. 2011. | #1 |
član
Certified
Datum učlanjenja: 25.07.2008
Poruke: 76
Hvala: 15
296 "Hvala" u 10 poruka
|
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...'); 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 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.... Poslednja izmena od sinisake : 28. 12. 2011. u 14:03. |
|
|