MySQL data fetching problem

[pdedecker]

This is my MySQL table:

$getrating_result = mysql_query("SELECT pdedecker FROM votes WHERE id='0'");$getrating_listing = mysql_fetch_object($getrating_result);$getrating_rating = $getrating_listing->pdedecker;

When I try to echo $getrating_rating, it returns '-1'. Why? Am I using the wrong code/syntax?

[reportingsjr]

Why not just do an "echo $generating_listing->pdedecker;"? The syntax is all right, or you would be getting an error. Try this:

$getrating_result = mysql_query("SELECT pdedecker FROM votes WHERE id='0'") or die(mysql_error());$getrating_listing = mysql_fetch_object($getrating_result) or die(mysql_error());echo $getrating_listing->pdedecker;

If that shows an error say what it says.

[pdedecker]

It doesn't show any kind of error, and if I echo $getrating_listing->pdedecker, I get "-1" as result (instead of +1).Screenshot: http://img101.imageshack.us/img101/5862/minusonelm2.jpg

[jhecht]

ok i dont know why you are getting a negative one, but ur code just kinda bugs me.

$mysql = mysql_connect("localhost","usernmae","password") or die(mysql_error());@mysql_select_db("database");$getrating_result = mysql_query("SELECT pdedecker FROM votes WHERE id=0",$mysql);//You dont need quotes around numbers;$getrating_listing = mysql_fetch_array($getrating_result);$getrating_rating = $getrating_listing['pdecker'];//Mainly because i have no idea what the ###### mysql_fetch_object does, use fetch_array or fetch_assoc;

What type of column is pdecker? that might be your problem. It should be either an integer, a float, or a double. if its an integer you dont need the + sign before it, its just assumed to be a +, unless you put a - sign infront, where it makes it negative.

[pdedecker]

When I use your code, I get these two messages:

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in C:\xampplite\htdocs\showdigger\include\listings.class on line 41Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xampplite\htdocs\showdigger\include\listings.class on line 42

What I'm creating is a digg.com-like thing for podcasts. The '+1', '0', '-1' tell my site whether the user has dug the listed podcast (+1), undug the listed podcast (-1) or none of both (0).'pdedecker' is a username (every user gets its own column in the rows table) and number in the first column is the ID of the podcast.

[www.mihalism.com]

Heres a nice clean data fetching code try it

<?php//connect$con = mysql_connect("localhost","usernmae","password");if(!$con){ echo mysql_error(); }//pick databasemysql_select_db("database_name", $con);//database query$result = mysql_query("SELECT `pdedecker` FROM `votes` WHERE `id` = '0'", $con) or die(mysql_error());//fetchwhile($getrating_listing = mysql_fetch_array($result)){$getrating_rating = $getrating_listing['pdecker'];}//close connectionmysql_close($con);?>

[jhecht]

Heres a nice clean data fetching code try it

<?php//connect$con = mysql_connect("localhost","usernmae","password");if(!$con){ echo mysql_error(); }//pick databasemysql_select_db("database_name", $con);//database query$result = mysql_query("SELECT `pdedecker` FROM `votes` WHERE `id` = '0'", $con) or die(mysql_error());//fetchwhile($getrating_listing = mysql_fetch_array($result)){$getrating_rating = $getrating_listing['pdecker'];}//close connectionmysql_close($con);?>

Your code is the exact same thing as mine. Just different variables, and with DIE statements(he didnt put em, so neither did i)and pdecker, did you change the values for the mysql_connect and mysql_select_db()?And dude, how you are doing the plus or minus thing is going to cause you ###### later on.make one table for the podcasts, to keep them there and whatever, name etc etc, next create a seperate table with a few fields:ID(for each deletion purposes later on)author, the interger ID of the usertype, i would assume an int, 0 for subtract, 1 for addamount (int type)podcast_id (the ID from the podcasts table)how you are doing it is just going to murder on your server.

[pdedecker]

Thanks for the advice. However, I think it's better not to merge the tables 'podcasts' and 'votes'.Why, you ask? Well, the 'votes' table will have a seperate column for each user. If my site has 100 users, the 'votes' table will have 101 columns.If I merge the tables with one another, the merged table will have 115 columns (or something like that) for 100 users...Second reason: I only need to access the 'votes' table if the user decides to vote. However, I need to access the 'podcasts' table to generate the podcast listings that appear on every page. Merging both tables would make the site slower instead of faster.That's my opinion, if you disagree, please tell me why.

[jhecht]

I'm sorry man but what you are doing is just over complicating your script so much its killing me.ok so you have one table for users, right?lets say its a simple one with these fields:

user_id (int unique not null auto_increment([saves you time later on]username(a varchar(255) column)password( varchar(32) because md5 is always 32 chars long)

This is for users to log in, and whatever.Next is a table for the podcasts

podcast_id( int unique not null auto_increment)podcast_name(varchar(255)podcast_poster(int not null)podcast_url(text)[text just in case some podcasts have incredibly large and complex url's]

That is the podcast table, Next comes the votes

vote_id (int unique not null auto_increment)vote_podcast_id(int, this is the id of the podcast from the podcast table)vote_from_user(int, this is from the USER table)vote_type(int, 0 for "not digged", 1 for "digged", or however you want to do it)vote_amount(int not null, for the +1-+ to -1 or -3 or whatever many numbers you want to be able to do it as.

This would save you time, instead of everytime a user registers having to add a new column, you just insert a new row and its done. Same with the podcasts, you aren't creating a new column for every user, you just wait for the user to submit a podcast, which also goes dito for the voting. Instead of having to do it by the ID and then adding a column for the user who votes on it, you just add another row.Altering tables just takes too much time, its too ugly, and honestly its just something(no offense meant to you) a newbie would do. Im not saying you literally MERGE the tables, im saying when you select something from one table you can MERGE it(not literally) with values. Lets say im selecting 3 podcasts from my system, but i also want the user who posted it's name to be on there.

SELECT p.*,u.username FROM `podcasts` p,`users` u WHERE u.user_id=p.podcast_poster ORDER BY p.podcast_id DESC LIMIT 3

If you want me to show you what im talking about i could whip up a quick mock version of a podcast system to prove it is faster.

[justsomeguy]

I will agree with Jhecht, you are setting up your database server for a huge crash, followed by several hours of frantic rewriting. I'm assuming you want your site to become popular, if you wake up one day and find that your link got posted on several high-traffic sites and that now you have 10,000 users, after you clean up the plastic puddle where your server used to be, you will realize that a table with 10,000 columns in it is terrible design. 10,000 rows in a table with 5 columns is much quicker and easier for the server to manage then 5 rows in a table with 10,000 columns. You are forcing the server to keep indexing, data type, length, name etc information for way more columns then is necessary.Also, the point he made about using joins to select data from other tables is good as well. But I've never created an application anywhere that alters the tables, I design the tables and then use them. The only time I would be programatically altering my database schema is if I'm programming a database management system.

[pdedecker]

I followed your advice, the 'votes' table now looks like this:I've also been able to solve the problem described in the first post of this topic. Thanks!