Here is some updates: Users can add or update their reviews of stuff. Check it out here: http://chaosplay.com/facebook/Stuff/index.php
It’s still in development, so please excuse any bugs right now.
Thanks
Database:
– phpMyAdmin SQL Dump
– version 2.11.6
– http://www.phpmyadmin.net
–
– Host: localhost
– Generation Time: Aug 02, 2008 at 07:06 PM
– Server version: 4.1.22
– PHP Version: 5.2.6
SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”;
–
– Database: `chaospla_facebook`
–
– ——————————————————–
–
– Table structure for table `t_platforms`
–
CREATE TABLE IF NOT EXISTS `t_platforms` (
`platformid` int(11) NOT NULL auto_increment,
`platform_name` varchar(25) NOT NULL default ”,
PRIMARY KEY (`platformid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
– ——————————————————–
–
– Table structure for table `t_properties`
–
CREATE TABLE IF NOT EXISTS `t_properties` (
`propertyid` int(11) NOT NULL auto_increment,
`property_name` varchar(32) NOT NULL default ” COMMENT ‘name, address, phone, ‘,
PRIMARY KEY (`propertyid`),
UNIQUE KEY `property_name` (`property_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
– ——————————————————–
–
– Table structure for table `t_reviews`
–
CREATE TABLE IF NOT EXISTS `t_reviews` (
`reviewid` int(10) unsigned NOT NULL auto_increment,
`userid` int(10) unsigned NOT NULL default ‘0′ COMMENT ‘FK’,
`thingid` int(10) unsigned NOT NULL default ‘0′ COMMENT ‘FK’,
`review` varchar(128) NOT NULL default ”,
`thumbs` tinyint(1) NOT NULL default ‘0′ COMMENT ‘Up or down’,
`updt_date` datetime NOT NULL default ‘0000-00-00 00:00:00′,
PRIMARY KEY (`reviewid`),
UNIQUE KEY `userid` (`userid`,`thingid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
– ——————————————————–
–
– Table structure for table `t_tags`
–
CREATE TABLE IF NOT EXISTS `t_tags` (
`tagid` int(10) unsigned NOT NULL auto_increment,
`name` varchar(64) NOT NULL default ”,
PRIMARY KEY (`tagid`),
UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
– ——————————————————–
–
– Table structure for table `t_tag_tag`
–
CREATE TABLE IF NOT EXISTS `t_tag_tag` (
`tagid1` int(10) unsigned NOT NULL default ‘0′ COMMENT ‘FK’,
`tagid2` int(10) unsigned NOT NULL default ‘0′ COMMENT ‘FK’,
UNIQUE KEY `tagid1` (`tagid1`,`tagid2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
– ——————————————————–
–
– Table structure for table `t_things`
–
CREATE TABLE IF NOT EXISTS `t_things` (
`thingid` int(10) unsigned NOT NULL auto_increment,
`name` varchar(64) NOT NULL default ”,
PRIMARY KEY (`thingid`),
UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
– ——————————————————–
–
– Table structure for table `t_things_properties`
–
CREATE TABLE IF NOT EXISTS `t_things_properties` (
`thingid` int(10) unsigned NOT NULL default ‘0′ COMMENT ‘FK’,
`propertyid` int(10) unsigned NOT NULL default ‘0′ COMMENT ‘FK’,
`value` varchar(64) NOT NULL default ”,
UNIQUE KEY `thingid` (`thingid`,`propertyid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
– ——————————————————–
–
– Table structure for table `t_things_tags`
–
CREATE TABLE IF NOT EXISTS `t_things_tags` (
`thingid` int(10) unsigned NOT NULL default ‘0′ COMMENT ‘FK’,
`tagid` int(10) unsigned NOT NULL default ‘0′ COMMENT ‘FK’,
UNIQUE KEY `thingid` (`thingid`,`tagid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
– ——————————————————–
–
– Table structure for table `t_users`
–
CREATE TABLE IF NOT EXISTS `t_users` (
`userid` int(10) unsigned NOT NULL auto_increment,
`platformid` int(11) NOT NULL default ‘0′,
`platform_userid` varchar(60) NOT NULL default ”,
`crte_date` datetime NOT NULL default ‘0000-00-00 00:00:00′,
`active` tinyint(1) NOT NULL default ‘0′,
`updt_date` datetime default NULL,
PRIMARY KEY (`userid`),
UNIQUE KEY `platformid` (`platformid`,`platform_userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
– ——————————————————–
–
– Table structure for table `t_user_properties`
–
CREATE TABLE IF NOT EXISTS `t_user_properties` (
`userid` int(10) unsigned NOT NULL default ‘0′ COMMENT ‘FK’,
`propertyid` int(10) unsigned NOT NULL default ‘0′ COMMENT ‘FK’,
`value` varchar(64) NOT NULL default ”,
`active` tinyint(1) NOT NULL default ‘0′,
`updt_date` datetime NOT NULL default ‘0000-00-00 00:00:00′,
UNIQUE KEY `userid_2` (`userid`,`propertyid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
——————
Index.php
<?php
ini_set(”display_errors”, 1);
require_once ‘includes/appinclude.php’;
require_once ‘includes/dbainclude.php’;
require_once ‘includes/user_lib.php’;
require_once ‘includes/constants.php’;
?>
<h1>the Stuff list</h1>
<p>Please go ahead and let the world know what you think of anything.</p>
<?
//check if user already exist in the db, if not then add user
if (!user_check($user)){
$userid = user_add($facebook, $user);
} else {
$userid = user_get_id($user);
}
//if user submitted review, then validate it and then save it
if ($_POST['thing'] <> null && $_POST['tag'] <> null and $_POST['thumbs'] <> null){
user_review($userid, $_POST['thing'], $_POST['tag'], $_POST['review'], $_POST['thumbs']);
}
//output text boxes…
?>
<form action=”index.php” method=”post”>
<p> Thing: <input type=”text” class=”text” name=”thing” value=”chaosplay.com” size=”64″ maxlength=”64″ /></p>
<p> Which is: <input type=”text” class=”text” name=”tag” value=”website” size=”64″ maxlength=”64″ /></p>
<p> My take on it: <input type=”text” class=”text” name=”review” value=”best website ever” size=”128″ maxlength=”128″ /></p>
<p> Thumbs: <input type=”radio” class=”radio” name=”thumbs” value=”1″ /> Up
<input type=”radio” class=”radio” name=”thumbs” value=”0″ /> Down</p>
<p><input type=”submit” class=”submit button” name=”save” value=”save” />
<input type=”reset” class=”reset button” name=”reset” value=”reset” />
</p>
</form>
——————————————-
user_lib.php
<?php
/*
* Created on Jul 30, 2008
*
* To change the template for this generated file go to
* Window - Preferences - PHPeclipse - PHP - Code Templates
*/
function user_check($user) {
$res = query(’SELECT `userid` FROM `t_users` WHERE `platform_userid`=’ . $user);
if ($row = mysql_fetch_assoc($res)) {
return true;
} else {
return false;
}
}
function user_add($facebook, $user) {
$res = query(’INSERT INTO t_users (platformid, platform_userid, crte_date, active) values ‘ .
‘( 1, “‘ . $user . ‘”, now(), true )’);
$info = $facebook->api_client->users_getInfo($user, array (
‘last_name’,
‘first_name’,
’sex’,
‘religion’,
‘relationship_status’,
‘political’,
‘meeting_for’,
‘current_location’
));
$res = query(’SELECT `userid` from `t_users` where `platform_userid` = ‘ . $user);
$userid = mysql_fetch_assoc($res);
$userid = $userid['userid'];
$res = query(’INSERT INTO t_user_properties (`userid`, `propertyid`, `value`, `active`, `updt_date`) ‘ .
‘ values ‘ . ‘ (’ . $userid . ‘, ‘ . FIRST_NAME_ID . ‘, “‘ .
$info[0]['first_name'] . ‘”, true, now())’);
$res = query(’INSERT INTO t_user_properties (`userid`, `propertyid`, `value`, `active`, `updt_date`) ‘ .
‘ values ‘ . ‘ (’ . $userid . ‘, ‘ . LAST_NAME_ID . ‘, “‘ .
$info[0]['last_name'] . ‘”, true, now())’);
$res = query(’INSERT INTO t_user_properties (`userid`, `propertyid`, `value`, `active`, `updt_date`) ‘ .
‘ values ‘ . ‘ (’ . $userid . ‘, ‘ . SEX_ID . ‘, “‘ .
$info[0]['sex'] . ‘”, true, now())’);
$res = query(’INSERT INTO t_user_properties (`userid`, `propertyid`, `value`, `active`, `updt_date`) ‘ .
‘ values ‘ . ‘ (’ . $userid . ‘, ‘ . RELIGION_ID . ‘, “‘ .
$info[0]['religion'] . ‘”, true, now())’);
$res = query(’INSERT INTO t_user_properties (`userid`, `propertyid`, `value`, `active`, `updt_date`) ‘ .
‘ values ‘ . ‘ (’ . $userid . ‘, ‘ . RELATIONSHIP_ID . ‘, “‘ .
$info[0]['relationship_status'] . ‘”, true, now())’);
$res = query(’INSERT INTO t_user_properties (`userid`, `propertyid`, `value`, `active`, `updt_date`) ‘ .
‘ values ‘ . ‘ (’ . $userid . ‘, ‘ . POLITICAL_ID . ‘, “‘ .
$info[0]['political'] . ‘”, true, now())’);
$res = query(’INSERT INTO t_user_properties (`userid`, `propertyid`, `value`, `active`, `updt_date`) ‘ .
‘ values ‘ . ‘ (’ . $userid . ‘, ‘ . LOOKING_FOR_ID . ‘, “‘ .
$info[0]['meeting_for'] . ‘”, true, now())’);
// $res = query(’INSERT INTO t_user_properties (`userid`, `propertyid`, `value`, `active`, `updt_date`) ‘ .
// ‘ values ‘ . ‘ (’ . $userid . ‘, ‘ . LAST_NAME_ID . ‘, “‘ .
// $info[0]['last_name'] . ‘”, true, now())’);
//echo $info[0]['current_location'];
return $userid;
}
function user_get_id($user) {
$res = query(’SELECT `userid` from `t_users` where `platform_userid` = ‘ . $user);
$userid = mysql_fetch_assoc($res);
$userid = $userid['userid'];
return $userid;
}
function user_review($userid, $thing, $tag, $review, $thumbs) {
$thing = strtolower(trim($thing));
$tag = strtolower(trim($tag));
$review = strtolower(trim($review));
//check if thing already is in db
$sql = sprintf(”SELECT `thingid` FROM `t_things` WHERE `name`=’%s’”, mysql_real_escape_string($thing));
$res = query($sql);
if ($row = mysql_fetch_assoc($res)) {
$thingid = $row['thingid'];
} else {
$sql = sprintf(”INSERT INTO `t_things` (`name`) values (’%s’)”, mysql_real_escape_string($thing));
$res = query($sql);
$sql = sprintf(”SELECT `thingid` FROM `t_things` WHERE `name`=’%s’”, mysql_real_escape_string($thing));
$res = query($sql);
$row = mysql_fetch_assoc($res);
$thingid = $row['thingid'];
}
//check if tag already is in db
$sql = sprintf(”SELECT `tagid` FROM `t_tags` WHERE `name`=’%s’”, mysql_real_escape_string($tag));
$res = query($sql);
if ($row = mysql_fetch_assoc($res)) {
$tagid = $row['tagid'];
} else {
$sql = sprintf(”INSERT INTO `t_tags` (`name`) values (’%s’)”, mysql_real_escape_string($tag));
$res = query($sql);
$sql = sprintf(”SELECT `tagid` FROM `t_tags` WHERE `name`=’%s’”, mysql_real_escape_string($tag));
$res = query($sql);
$row = mysql_fetch_assoc($res);
$tagid = $row['tagid'];
}
//check if thing is already tagged with tag
$sql = “SELECT * from `t_things_tags` where `thingid`=” . $thingid . ” and `tagid`=” . $tagid;
$res = query($sql);
if (!$row = mysql_fetch_assoc($res)) {
$sql = “INSERT INTO `t_things_tags` VALUES (” . $thingid . “, ” . $tagid . “)”;
$res = query($sql);
}
//check if user has already reviewed the thing
$sql = “SELECT `reviewid` from `t_reviews` WHERE `thingid`=” . $thingid . ” AND `userid`=” . $userid;
$res = query($sql);
if ($row = mysql_fetch_assoc($res)) {
//if already reviewed, then update existing review
$sql = sprintf(”UPDATE `t_reviews` SET `review`=’%s’, `thumbs`=%s, `updt_date`=now() WHERE `reviewid`=%s”, mysql_real_escape_string($review), $thumbs, $row['reviewid']);
query($sql);
echo “<h3>updated your review</h3>”;
} else {
//add a new review
$sql = sprintf(”INSERT INTO `t_reviews` (`userid`, `thingid`, `review`, `thumbs`, `updt_date`) VALUES (%s, %s, ‘%s’, %s, now())”, $userid, $thingid, mysql_real_escape_string($review), $thumbs);
query($sql);
echo “<h3>inserted new review</h3>”;
}
}
?>
1 response so far ↓
1 funnygirl // Aug 7, 2008 at 1:01 pm
Yay! Interesting.
Leave a Comment