Error Message

[Jbye]

I am running a script that returns this message:-

Table 'users.user_stats' doesn't exist

Yet no where in my script or the previous script am I calling a table users.user_stats.
The table I'm calling is simply called user_stats.

How do I resolve this?
  Thanks.

[Nox]

It's format
table.column

For example

Code: [Select]

SELECT user_stats FROM usersreally means users.user_stats ... the fact that you don't have to write it is because DB can unambiguously determine that it's from table users

[Jbye]

Ok but the table user_stats does exist?

[Nox]

Ok.... if user_stats should be a table then you might have used a tablename where column should be used
Or maybe I just don't get it

So it would be helpful if you share a part of the script that causes the problem whether tables/columns of respective names used exist

[Jbye]

Here's the code i'm using.  It's from a tutorial.
<?php
 
function getStat($statName,$user_id) {
   include 'config.php';
   $conn = mysql_connect($dbhost,$dbuser,$dbpass)
      or die ('Error connecting to mysql:');
   mysql_select_db($dbname);
   createIfNotExists($statName,$user_id);
   $query = sprintf("SELECT value FROM user_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND user_id = '%s'",
      mysql_real_escape_string($statName),
      mysql_real_escape_string($statName),
      mysql_real_escape_string($user_id));
   $result = mysql_query($query);
   list($value) = mysql_fetch_row($result);
   return $value;      
}
function setStat($statName,$userID,$value) {
   include 'config.php';
   $conn = mysql_connect($dbhost,$dbuser,$dbpass)
      or die ('Error connecting to mysql');
   mysql_select_db($dbname);
   createIfNotExists($statName,$user_id);
   $query = sprintf("UPDATE user_stats SET value = '%s' WHERE stats_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND user_id = '%s'",
      mysql_real_escape_string($value),
      mysql_real_escape_string($statName),
      mysql_real_escape_string($statName),
      mysql_real_escape_string($user_id));
   $result = mysql_query($query);
}
 
function createIfNotExists($statName,$user_id) {
   include 'config.php';
   $conn = mysql_connect($dbhost,$dbuser,$dbpass)
      or die ('Error connecting to mysql:');
   mysql_select_db($dbname);
   $query = sprintf("SELECT count(value) FROM user_stats WHERE stats_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND user_id = '%s'",
      mysql_real_escape_string($statName),
      mysql_real_escape_string($statName),
      mysql_real_escape_string($user_id));
   $result = mysql_query($query);
   $result=mysql_query($query); echo mysql_error ();
   if (mysql_num_rows($result) ==0) {
      // the stat doesn't exist; insert it into the database
      $query = sprintf("INSERT INTO user_stats(stats_id,user_id,value) VALUES ((SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s'),'%s','%s')",
      mysql_real_escape_string($statName),
      mysql_real_escape_string($statName),
      mysql_real_escape_string($user_id),
      '0');
      mysql_query($query);
   }   
}

[Jbye]

Is it because in all three sections i'm calling config.php which sets the database as "users" yet the script is calling for a different database?

[Nox]

1) Insert codes into [ code ][ /code ] (strip whitespace)
2) You can modify your own messages (thus the last one was not necessary)
3) Initiate connection to DB and including config file inside (every) function is something terrible... this should be in the initiating part of whole script
4)
 a) In createIfNotExist you can the query twice
 b) "create WHAT if not exist?"

[Jbye]

Duly noted.  I've deleted one of the queries yet I still get the error message.
If it helps I am following a tutorial and am on this page

http://buildingbrowsergames.com/2008/06/03/building-browsergames-adding-stats-php/

[dbest]

In phpmyadmin, run the "SHOW DATABASES" query in the SQL tab and verify that a database called "users" exists.
Then check your config.php file and verify the value of the "$dbname" variable.

If the values are same, then in phpmyadmin, run the following queries:

Code: [Select]

USE users;
SHOW TABLES;

Verify that the user_stats table is shown in the output.

[Jbye]

Checked that just and they are all there.

The table users gets written too when I run the Registration script.  So a new user gets created yet it doesn't create new stats for the new user as it's supposed to do.

I am also getting a

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\wamp\www\Game\stats2.php on line 41

at the same time which is this line


   if (mysql_num_rows($result) ==0) {

[Nox]

Are you connected to the correct database then, is it all correct? Check all values immediately before being passed to the connecting function

[Sagefire135]

Well, the script you have shown is just 3 functions. you arent actually DOING anything with them here. what script are you using the functions in? what does that one look like?

Its possible that the values you use in the functions arent matching up with the values in the database so nothing happens (I do this all the time because i forget...lol).
function setStat(strength,15,100);
isnt going to work unless strength is listed in both the stats table, and the user_stats table

and...
Return Values for mysql_num_rows from php.net says
"The number of rows in a result set on success or FALSE on failure."

it is returning false because you arent selecting rows in the previous query, you are selecting a number (count(value)) . im thinking that the mysql_num_rows() part isnt even needed?

[dbest]

Do a simple echo of the variables before you call the function  "createIfNotExists($statName,$user_id)". This will help you identify if the correct variables are being passed to the function.

Also, as sagefire mentioned, you do not need a mysql_num_rows. Check your code against the tutorial on the site.

[Jbye]

This is the script im using before the one above ...

Code: [Select]

<?php
 error_reporting(E_ALL ^ E_NOTICE); 
 include "smarty.php";
 
if($_POST) {
$password = $_POST['password'];
$confirm = $_POST['confirm'];
if($password != $confirm) {
$error = 'Passwords do not match!';
} else {
require_once 'config.php'; // our database settings
$conn = mysql_connect($dbhost,$dbuser,$dbpass)
or die('Error connecting to mysql');
mysql_select_db($dbname);
$query = sprintf("SELECT COUNT(id) FROM users WHERE UPPER(username) = UPPER('%s')",
mysql_real_escape_string($_POST['username']));
$result = mysql_query($query);
list($count) = mysql_fetch_row($result);
if($count >= 1) { 
$error = 'that username is taken.';
} else {
$query = sprintf("INSERT INTO users(username,password) VALUES ('%s','%s');",
mysql_real_escape_string($_POST['username']),
mysql_real_escape_string(md5($password)));
mysql_query($query);
$userID = mysql_insert_id($conn);
require_once 'stats2.php';
setStat('atk',$user_id,'5');
setStat('def',$user_id,'5');
setStat('mag',$user_id,'5');
$message = 'Congratulations, you registered successfully!';
}
}
}
$Smarty->assign('error',$error);
$Smarty->assign('message',$message);
$Smarty->display('register.tpl');
 
?>


[Jbye]

OH MY GOOD GOD!!!!!!

After a lot of time going back and forth I have finally sorted it out!!

I had an epiphany after staring at my phpadmin page for a while.  All the Tables I have been calling DIDN'T actually EXIST!!! 
It turns out I was calling a DATABASE.  So I had to re-structure everything and add the TABLES to my users database, change some stuff in my scripts and bobs your uncle and fanny's your aunt (as we say).

So i'd like to say thanks to all your help you did nudge me in the right direction.