[MySQL][PHP] problem z zapytaniem Opcje

[jjarkus]

Dręczy mnie pewien problem z moim kodem
Mianowicie tworzę sobie taką małą grę i problem jest następujący:
Podczas rejestracji nowego użytkownika wyskakuje taki błąd:

Kod

         Warning:  mysql_fetch_row(): supplied argument is not a valid MySQL result resource in c:\usr\krasnal\www\gra\stats.php on line 43
         Warning:  mysql_fetch_row(): supplied argument is not a valid MySQL result resource in c:\usr\krasnal\www\gra\stats.php on line 43
         Warning:  mysql_fetch_row(): supplied argument is not a valid MySQL result resource in c:\usr\krasnal\www\gra\stats.php on line 43

Skorzystałem też z

[PHP] pobierz, plaintext 
 echo mysql_error();
[PHP] pobierz, plaintext 

i pokazał taki komunikat:

Kod

         Something is wrong in your syntax obok 'SELECT id FROM stats WHERE display_name = 'mag' OR short_name = ' w linii 1

Wychodzi na to, że chyba wykonanie zapytania się nie powiodło.
W czym może tkwić błąd?

Dodatkowo kod stats.php

[PHP] pobierz, plaintext 
<?php
 
function getStat($statName,$userID) {
    include 'config.php';
    $conn = mysql_connect($dbhost,$dbuser,$dbpass)
        or die ('Error connecting to mysql:');
    mysql_select_db($dbname);
    createIfNotExists($statName,$userID);
    $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($userID));
    $result = mysql_query($query);
    list($value) = mysql_fetch_row($result);
    return $value;
      $userID = mysql_insert_id($conn);
 
}
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,$userID);
    $query = sprintf("UPDATE user_stats SET value = '%s' WHERE stat_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($userID));
    $result = mysql_query($query);
}
 
function createIfNotExists($statName,$userID) {
    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 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($userID));
    $result = mysql_query($query);
    list($count) = mysql_fetch_row($result);
    if($count == 0) {
        $query = sprintf("INSERT INTO user_stats(stat_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($userID),
        '0');
        mysql_query($query);
    }    
}
 
?>
[PHP] pobierz, plaintext 

Oraz register.php

[PHP] pobierz, plaintext 
<?php
 
include 'smarty.php';
 
if($_POST) {
    $password = $_POST['password'];
    $confirm = $_POST['confirm'];
    if($password != $confirm) {
        $error = 'Passwords do not match!';    
    } else {
        require_once 'config.php';       
        $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 'stats.php';
            setStat('atk',$userID,'5');
            setStat('def',$userID,'5');            
            setStat('mag',$userID,'5');            
            $message = 'Congratulations, you registered successfully!';
        }
    }    
}
$smarty->assign('error',$error);
$smarty->assign('message',$message);
$smarty->display('register.tpl');
 echo mysql_error();
?>
[PHP] pobierz, plaintext 

[Pawel_W]

najprościej by było jakbyś sobie zrobił echo $query, wtedy zobaczyłbyś co dostaje mysql.

[deniol13]

weź po każdej operacji mysql_fetch_row(); daje or die(mysql_error();

czyli

$zmienna = mysql_fetch_row($zapytanie) or die(mysql_error()); i pokaż kod błędu wtedy

[jjarkus]

Kod błędu pod doadniu echo $query wygląda tak:

Kod

         Something is wrong in your syntax obok 'SELECT id FROM stats WHERE display_name = 'mag' OR short_name = ' w linii 1INSERT INTO users(username,password) VALUES ('66','3295c76acbf4caaed33c36b1b5fc2cb1')

A tak po tym co napisał daniol13 taki komunikat:

Kod

Something is wrong in your syntax obok 'SELECT id FROM stats WHERE display_name = 'atk' OR short_name = ' w linii 1

[Pawel_W]

Kod błędu pod doadniu echo $query wygląda tak:

Kod

         Something is wrong in your syntax obok 'SELECT id FROM stats WHERE display_name = 'mag' OR short_name = ' w linii 1INSERT INTO users(username,password) VALUES ('66','3295c76acbf4caaed33c36b1b5fc2cb1')

A tak po tym co napisał daniol13 taki komunikat:

Kod

Something is wrong in your syntax obok 'SELECT id FROM stats WHERE display_name = 'atk' OR short_name = ' w linii 1

przecież to są 2 różne zapytania, musiałes w złym miejscu echo wstawić...

[jjarkus]

Tutaj wstawiam echo $query:

[PHP] pobierz, plaintext 
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,$userID);
    $query = sprintf("UPDATE user_stats SET value = '%s' WHERE stat_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($userID));
    $result = mysql_query($query);
 echo mysql_error(); \\teraz dałem tutaj
                   echo $query; \\ i tu
 
}
 
function createIfNotExists($statName,$userID) {
    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 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($userID));
    $result = mysql_query($query);
    list($count) = mysql_fetch_row($result);
    if($count == 0) {
        $query = sprintf("INSERT INTO user_stats(stat_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($userID),
        '0');
        mysql_query($query);
              echo mysql_error(); \\teraz dałem tutaj
                   echo $query; \\ i tu
    }    
}
[PHP] pobierz, plaintext 

Wyskakuje coś takiego:

Kod

     Warning:  mysql_fetch_row(): supplied argument is not a valid MySQL result resource in c:\usr\krasnal\www\gra\stats.php on line 45
       Something is wrong in your syntax obok 'SELECT id FROM stats WHERE  display_name = 'atk' OR short_name = ' w linii 1INSERT INTO  user_stats(stat_id,user_id,value) VALUES ((SELECT id FROM stats WHERE  display_name = 'atk' OR short_name = 'atk'),'30','0')Something is wrong  in your syntax obok 'SELECT id FROM stats WHERE display_name = 'atk' OR  short_name = ' w linii 1UPDATE user_stats SET value = '5' WHERE stat_id  = (SELECT id FROM stats WHERE display_name = 'atk' OR short_name =  'atk') AND user_id = '30'

       Warning: mysql_fetch_row(): supplied argument is not a valid MySQL  result resource in c:\usr\krasnal\www\gra\stats.php on line 45
       Something is wrong in your syntax obok 'SELECT id FROM stats WHERE  display_name = 'def' OR short_name = ' w linii 1INSERT INTO  user_stats(stat_id,user_id,value) VALUES ((SELECT id FROM stats WHERE  display_name = 'def' OR short_name = 'def'),'30','0')Something is wrong  in your syntax obok 'SELECT id FROM stats WHERE display_name = 'def' OR  short_name = ' w linii 1UPDATE user_stats SET value = '5' WHERE stat_id  = (SELECT id FROM stats WHERE display_name = 'def' OR short_name =  'def') AND user_id = '30'

       Warning: mysql_fetch_row(): supplied argument is not a valid MySQL  result resource in c:\usr\krasnal\www\gra\stats.php on line 45
       Something is wrong in your syntax obok 'SELECT id FROM stats WHERE  display_name = 'mag' OR short_name = ' w linii 1INSERT INTO  user_stats(stat_id,user_id,value) VALUES ((SELECT id FROM stats WHERE  display_name = 'mag' OR short_name = 'mag'),'30','0')Something is wrong  in your syntax obok 'SELECT id FROM stats WHERE display_name = 'mag' OR  short_name = ' w linii 1UPDATE user_stats SET value = '5' WHERE stat_id  = (SELECT id FROM stats WHERE display_name = 'mag' OR short_name =  'mag') AND user_id = '30'

[Pawel_W]

hmm, ciężko powiedzieć co jest źle, jak masz phpMyAdmin to spróbuj wykonać w nim te zapytania, wtedy dowiesz się co jest źle.

[jjarkus]

Dobra napisałem to jeszcze raz od nowa i poszło Dzięki za pomoc. Ale natrafiłem na jakiś dziwny błąd
Teraz próbuję dodać avatary.

W tabeli users dodałem pole avatar, w którym będzie przetrzymywany adres http:// do avatara. Po rejestracji elegancko nowe dane są wpisywane do bazy.
A teraz nie za bardzo wiem jak wyświetlić avatar
Dodatkowo korzystam ze smartów.

Próbowałem to zrobić tak ( na razie żeby wyświetlał się po prostu adres http:// avatara):

W pliku stats.php tworzę taki kod:

[PHP] pobierz, plaintext 
function getAvatar($avatar,$userID) {
        $query = sprintf("SELECT avatar FROM users WHERE id = (SELECT id FROM users WHERE UPPER(username) = UPPER('%s'))",
                mysql_real_escape_string($query));
        $result = mysql_query($query);
        echo $query;
        list($value) = mysql_fetch_row($result);
        return $value;
}
[PHP] pobierz, plaintext 

W index.php

[PHP] pobierz, plaintext 
$smarty->assign('avatar',getAvatar($avatar,$userID));
[PHP] pobierz, plaintext 

W index.tpl

[HTML] pobierz, plaintext 
Avatar: <strong>{$avatar}</strong>
[HTML] pobierz, plaintext 

I coś to nie działa ...
W index.php wyskakuje takie komunikat:

Kod

SELECT avatar FROM users WHERE id = (SELECT id FROM users WHERE  UPPER(username) = UPPER(''))SELECT avatar FROM users WHERE id = (SELECT  id FROM users WHERE UPPER(username) = UPPER(''))

Na pewno coś źle zrobiłem

[Pawel_W]

spróbuj tak:

[PHP] pobierz, plaintext 
$query = sprintf("SELECT `avatar` FROM `users` WHERE `users`.`id` = (SELECT u.`id` FROM `users` u WHERE UPPER(u.`username`) = UPPER('%s'))",
[PHP] pobierz, plaintext 

[jjarkus]

Dobra zrobiłem tak i wszystko działa teraz pięknie:
stats.php

[PHP] pobierz, plaintext 
function getAvatar($avatar,$userID) {
        $query = sprintf("SELECT avatar FROM users WHERE UPPER(username) = UPPER('%s')",
        mysql_real_escape_string($_SESSION['username']),
        mysql_real_escape_string($query));
        $result = mysql_query($query);
        list($avatar) = mysql_fetch_row($result);
        return $avatar;
}
[PHP] pobierz, plaintext 

index.php

[PHP] pobierz, plaintext 
$smarty->assign('avatar',getAvatar($avatar,$userID));
[PHP] pobierz, plaintext 

index.tpl

[XML] pobierz, plaintext 
<img src="{$avatar}"/>
[XML] pobierz, plaintext 

I awatary się wyświetlają
A jak zrobić żeby przy rejestracji, jak się nic nie wpisze w pole awatar, aby w tablicy został podany adres domyślny awatara dla nowego usera?

Ten post edytował jjarkus 3.01.2010, 16:46:20

[Pawel_W]

poczytaj o IFNULL dla sql