Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result res...

[Sprout]

Alright, well the last couple of days I've been attempting to learn how to create a user point system for my site (which is well beyond my understanding of PHP) and I've been making lots of progress. However, the page that's supposed to be displaying the points is now giving me this error.

 

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/public_html/test/logger/count.php on line 22

 

Now, I never created a mysql database for the point system, I had just been linking it to my user database under the half-baked assumption that it would just draw the username and record the points. But based on this error I'm starting to believe that's not the case. Anyway, here's my code for count.php

 

<?php
require_once('connect.php'); //require a database connection, to include the selection of the database.
$user = 1; //There must be a way to determine who's profile you are looking at. This will be the user.
if(isset($user)) { //if we have a user.
$user = mysql_real_escape_string($user); //escape them for mysql interaction.
if(isset($_GET['points']) && ($_GET['points'] == 'increase' || $_GET['points'] == 'decrease')) { //if the url has 'points' set, and the = one of two things.
}
if($_GET['points'] == 'increase') { //increase the count.
$update = 'count + 1';
}
elseif($_GET['points'] == 'decrease') { //decrease the count.
$update = 'count - 1';
}
$sql = "UPDATE counter SET count = $update WHERE user_id = '$user'"; //finalize query.
mysql_query($sql); //execute query.
}
$increasePoints = '<a href="?points=increase">Increase</a>'; //increase points link set to variable.
$decreasePoints = '<a href="?points=decrease">Decrease</a>'; //decrease points link set to variable.
//Call the data from the db.
$sql = "SELECT count FROM counter WHERE user_id = '$user'";
$re = mysql_query($sql);
$r = mysql_fetch_row($re);
$count = $r[0];
//echo the count and the variables. You can do this on the current page, or any page this code is included in.
echo 'User has ' . $count . ' points!
Do you want to ' . $increasePoints . ' or ' . $decreasePoints . '?
';
?>

 

Can anybody explain to me what it is I have to do to get this up and running?  :-\

 

[kickstart]

Hi

 

Do you have a column called count? If so then I suspect it is getting confused with it being a function. If not then you need something to count in brackets after it.

 

All the best

 

Keith

[Chidori Soul]

Well, I'm not sure if this will help, but to what I normally do, your update query for setting the count is missing something. I'm not sure if your way works or not, but try this.

 

$query = mysql_query("SELECT * FROM `users` WHERE `user_id`='$user'");
$result = mysql_fetch_array($query);
$count = $result['count'] + 1; //If its decreasing, replace it with minus, etc
$update = mysql_query("UPDATE `users` SET `count`='$count' WHERE `user_id`='$user'");

 

Same with you checking count from the users table, you could do it a different way.

 

$query1 = mysql_query("SELECT * FROM `users` WHERE `user_id`='$user'");
$result1 = mysql_fetch_array($query1);
$points = $result1['count'];
echo "This user has ".$count." points.";
//etc

 

I'm not exactly sure if this will help, but its the best I can think of.

[Sprout]

Whoa, thanks for the quick responses guys. I'm about to head to the store to buy some headphones then I'll check into your suggestions and let you know how it turns out. Very much appreciated.

[Mchl]

COUNT is not a reserved word. It's a function identifier, so in function context it's always followed by ().

Use mysql_error to display actual error message from MySQL.

[funkathustra]

$query = mysql_query("SELECT * FROM `users` WHERE `user_id`='$user'");
$result = mysql_fetch_array($query);
$count = $result['count'] + 1; //If its decreasing, replace it with minus, etc
$update = mysql_query("UPDATE `users` SET `count`='$count' WHERE `user_id`='$user'");

 

I would recommend reducing your double-query + PHP processing to a single query:

$update = mysql_query("UPDATE users SET count = count+1 WHERE user_id='$user'");

 

And, for God's sake, never use a SELECT * if you're not going to work with ALL the columns. It's inefficient.

 

[Chidori Soul]

$query = mysql_query("SELECT * FROM `users` WHERE `user_id`='$user'");
$result = mysql_fetch_array($query);
$count = $result['count'] + 1; //If its decreasing, replace it with minus, etc
$update = mysql_query("UPDATE `users` SET `count`='$count' WHERE `user_id`='$user'");

 

I would recommend reducing your double-query + PHP processing to a single query:

$update = mysql_query("UPDATE users SET count = count+1 WHERE user_id='$user'");

 

And, for God's sake, never use a SELECT * if you're not going to work with ALL the columns. It's inefficient.

 

 

 

I know, I was just trying to provide an alternate solution to his problem, sometimes the longer ways function a bit better.

[Sprout]

Well, I'm not sure if this will help, but to what I normally do, your update query for setting the count is missing something. I'm not sure if your way works or not, but try this.

 

$query = mysql_query("SELECT * FROM `users` WHERE `user_id`='$user'");
$result = mysql_fetch_array($query);
$count = $result['count'] + 1; //If its decreasing, replace it with minus, etc
$update = mysql_query("UPDATE `users` SET `count`='$count' WHERE `user_id`='$user'");

 

Same with you checking count from the users table, you could do it a different way.

 

$query1 = mysql_query("SELECT * FROM `users` WHERE `user_id`='$user'");
$result1 = mysql_fetch_array($query1);
$points = $result1['count'];
echo "This user has ".$count." points.";
//etc

 

I'm not exactly sure if this will help, but its the best I can think of.

 

I've been trying to figure out how to properly implement this into the code I have and all I've done is successfully manage to make it worse a dozen times.

 

Do I replace the

 

$sql = "UPDATE counter SET count = $update WHERE user_id = '$user'"; //finalize query.
mysql_query($sql); //execute query

 

with

 

$query = mysql_query("SELECT * FROM `users` WHERE `user_id`='$user'");
$result = mysql_fetch_array($query);
$count = $result['count'] + 1; //If its decreasing, replace it with minus, etc
$update = mysql_query("UPDATE `users` SET `count`='$count' WHERE `user_id`='$user'");

?

 

I'm sorry, I just started learning PHP about two weeks ago so if I come across like I have no idea what I'm doing it's because I pretty much don't.

[Chidori Soul]

I've been trying to figure out how to properly implement this into the code I have and all I've done is successfully manage to make it worse a dozen times.

 

Do I replace the

 

$sql = "UPDATE counter SET count = $update WHERE user_id = '$user'"; //finalize query.
mysql_query($sql); //execute query

 

with

 

$query = mysql_query("SELECT * FROM `users` WHERE `user_id`='$user'");
$result = mysql_fetch_array($query);
$count = $result['count'] + 1; //If its decreasing, replace it with minus, etc
$update = mysql_query("UPDATE `users` SET `count`='$count' WHERE `user_id`='$user'");

?

 

I'm sorry, I just started learning PHP about two weeks ago so if I come across like I have no idea what I'm doing it's because I pretty much don't.

 

Well, in my way, you do this.

 

$query = mysql_query("SELECT * FROM `users` WHERE `user_id`='$user'");
$result = mysql_fetch_array($query);
if($_GET['points'] == 'increase') { //increase the count.
$update = $result['count'] + 1;
}
elseif($_GET['points'] == 'decrease') { //decrease the count.
$update = $result['count'] - 1;
}
$update = mysql_query("UPDATE `users` SET `count`='$update' WHERE `user_id`='$user'");

[PFMaBiSmAd]

Read Mchl's post above and use mysql_error() to find out why your query is failing.

 

The extraneous posting by Chidori Soul that did not have anything to do with troubleshooting why your code is not working just derailed the thread.

[Sprout]

I don't know how to use the mysql_error() and google isn't being of much help.

[PFMaBiSmAd]

Mchl's post contains a link to the php.net manual section that shows a code example of how to use it after a query.

[Sprout]

I think there's a high probability that this code isn't working because I didn't create any sort of MySQL database for it. All I did was point it in the direction of my user MySQL database I created for users but I created nothing at all to do with storing numbers or counting or anything. But then again I hardly understand wtf I'm doing.

 

I can't figure out how to add the mysql_error messages in my code, all it does is start bitching at me about syntax errors. I appreciate all your help guys, and if anyone wants to continue to try to help me figure out how to do this then I would really greatly appreciate it, but I think maybe I just need to sit down with some more tutorials and get a better understanding of what I'm doing.

[PFMaBiSmAd]

$re = mysql_query($sql);
echo mysql_errno() . ": " . mysql_error() . "\n";

 

Edit: updated to match the code in your first post.

 

Edit2: Yes, if you don't have a table named counter with a column named count, there's no point in executing queries against it.

[Sprout]

Alright, Thanks PFMaBiSmAd it ended up returning this message.

 

1146: Table 'webvilla_users.counter' doesn't exist

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/webvilla/public_html/test/logger/count.php on line 23

 

I assume I set up my "connect.php" file incorrectly as well as have no created the counter table. Should I be creating a whole new database or should I put it with the users table I have? I barely understand MySQL databases I've only two up and one was the trial run the other way following a tutorial to create my user sections for login.

[Mchl]

If both tables are used by one application, it's usually wise to put them in one database.

[Sprout]

Alright, well I've made progress (I think) I set up a table named counter with a column named count but now the error message is saying.

 

1054: Unknown column 'user_id' in 'where clause'

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/webvilla/public_html/test/logger/count.php on line 23

 

My database is set up as so

 

 

webvilla_users (5) <-- this is my database

 

active_guests <-- these are my tables

active_users

banned_users

counter

users