Today's Posts Follow Us On Twitter! TFL Members on Twitter  
Forum search: Advanced Search  
Navigation
Marketplace
  Members Login:
Lost password?
  Forum Statistics:
Forum Members: 24,254
Total Threads: 80,792
Total Posts: 566,472
There are 2113 users currently browsing (tf).
 
  Our Partners:
 
  TalkFreelance     Design and Development     Programming     PHP and MySQL :

relations database

Thread title: relations database
Reply    
    Thread tools Search this thread Display Modes  
04-20-2011, 06:44 PM
#1
pinzdesign.com is offline pinzdesign.com
Status: I'm new around here
Join date: Feb 2011
Location: Copenhagen/DK
Expertise: php
Software: Paint
 
Posts: 15
iTrader: 0 / 0%
 

pinzdesign.com is on a distinguished road

  Old  relations database

i have made a small system where registered users may add places through the city's downtown and actually give them thumbs up or down, like video rating on youtube.
The conditions:
- a registered user may add as many places as they want
- a registered user may vote for each/any place only once - by giving a thumb up or down...
I have performed it with help of explode()
1.I created a cell in users table called "voted_for"
2.when users votes for some place he:
a.explode() the "voted_for" string,using | as separator
b.compare each chunk with the id of the place - if it exists within the results of exploded "voted_for" - forbid user to vote again
if it's not -do the voting and adds that place's id to his "voted_for" with a |

I did get everything to work properly, thus question is - how to make this work with relations db?

Here is the code:
Code:
if(isset($_REQUEST['sted'])) {
	$sted = $_REQUEST['sted'];
	$kanlide = $_REQUEST['kanlide'];
	$get_places_que = mysql_query("SELECT * FROM users WHERE id='$sid'");
	$places_array = mysql_fetch_array($get_places_que);
	$all_places = $places_array['votedfor'];
	$place = explode("|", $all_places);
	$bedom = "yes";
	for($i = 0; $i <= count($place); $i++) {
		if($place["$i"] == $sted) { 
			echo "<span>Du har bedømt den valgte forslag.</span>";
			$bedom = "no";
			}
		}
	if($bedom == "yes" && $kanlide == "yes") {
			$get_yes_votes = mysql_query("SELECT * FROM steder WHERE id='$sted'");
			$yes_votes_array = mysql_fetch_array($get_yes_votes);
			$newrate = $yes_votes_array['sted_good'] + 1;
			$addvote = $all_places."|$sted";
			$place_que = mysql_query("UPDATE steder SET sted_good='$newrate' WHERE id='$sted'");
			$user_que = mysql_query("UPDATE users SET votedfor='$addvote' WHERE id='$sid'");
			}
	elseif($bedom == "yes" && $kanlide == "no") { 
			$get_no_votes = mysql_query("SELECT * FROM steder WHERE id='$sted'");
			$no_votes_array = mysql_fetch_array($get_no_votes);
			$newrate = $no_votes_array['sted_bad'] + 1;
			$addvote = $all_places."|$sted";
			$place_que = mysql_query("UPDATE steder SET sted_bad='$newrate' WHERE id='$sted'");
			$user_que = mysql_query("UPDATE users SET votedfor='$addvote' WHERE id='$sid'");
			}
}

04-20-2011, 08:07 PM
#2
Village Genius is offline Village Genius
Village Genius's Avatar
Status: Geek
Join date: Apr 2006
Location: Denver, CO
Expertise: Software
Software: Chrome, Notepad++
 
Posts: 6,894
iTrader: 18 / 100%
 

Village Genius will become famous soon enough

  Old

Relational databases are a fairly large concept. There are numerous tutorials online that cover a lot more than I'd be able to type up here. But in essence, relational databases are databases that have relations to other ones. So instead of storing the data in a CSV format they each have their own row in a separate table and they are linked together. This is faster, less error prone, more flexible and overall a much better practice. Again, there are extensive tutorials that you can find on Google for this subject.

Once you understand relational databases queries get more efficient (although a little harder to write). The script you have there could be done in one single query with a properly designed database. To give you an example here is how I would do the above:

table users:
user_id int(9) primary_key, auto_incriment
user_name varchar(255)
ect...

table places:
place_id int(9) primary_key, auto_incriment
place_name

table ratings
rating_id int(9) primary_key, auto_incriment
rating_place_id int(9)
rating_user_id int (9)
rating_score int(1) //0=thumbs down, 1=thumbs up

This would get ratings by place:
SELECT COUNT(rating_id), place_name FROM places LEFT JOIN ratings ON rating_place_id=place_id GROUP BY place_name
Example output:
10 | Chicago
8 | New York
15 | Paris

This would get ratings for a particular user:
SELECT rating_score, place_name FROM ratings LEFT JOIN places ON rating_place_id=place_id WHERE ratings_user_id=[USER_ID]
Example output:
1 | Chicago
0 | Paris
1 | New York

Thanked by 2 users:
Artashes (04-25-2011), Tomos (04-22-2011)
04-24-2011, 08:32 AM
#3
pinzdesign.com is offline pinzdesign.com
Status: I'm new around here
Join date: Feb 2011
Location: Copenhagen/DK
Expertise: php
Software: Paint
 
Posts: 15
iTrader: 0 / 0%
 

pinzdesign.com is on a distinguished road

  Old

yaaay thx this third table ratings did give me the whole picture actually

Reply With Quote
Reply    


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 

  Posting Rules  
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump:
 
  Contains New Posts Forum Contains New Posts   Contains No New Posts Forum Contains No New Posts   A Closed Forum Forum is Closed