Page 1 of 1

double query problems

Posted: Thu Jun 12, 2003 3:57 am
by irealms

Code: Select all

<?php
$query = "INSERT INTO users (username, passwd, email) VALUES ('$_POST[username]', '$_POST[password]', '$_POST[email]')"; 
   $result = mysql_query($query, $db_conn) or die("query [$query] failed: ".mysql_error()); 
   $query2 = "INSERT INTO characters (username, character, level, class, race, main) VALUES ('$_POST[username]', '$_POST[char]', '$_POST[lvl]', '$_POST[class]', '$_POST[race]','1')";
   $result2 = mysql_query($query2, $db_conn) or die("query [$query2] failed: ".mysql_error()); 
?>
the 2nd query fails but i can't see any reason for the failure.

Posted: Thu Jun 12, 2003 3:58 am
by cactus
What does it fail with, whats the mysql_error() message ?

Posted: Thu Jun 12, 2003 4:04 am
by irealms
nothing, just says query failed, thats why i'm confused :)

Posted: Thu Jun 12, 2003 4:09 am
by cactus
Can you post the table definition of the database you are trying to write to. It could be a "types" thing.

Posted: Thu Jun 12, 2003 4:15 am
by irealms
ok this is table users:

TABLE users (
id int(10) unsigned NOT NULL auto_increment,
username varchar(16) NOT NULL default '',
passwd varchar(16) NOT NULL default '',
email varchar(50) NOT NULL default '',
approved int(1) unsigned NOT NULL default '0',
rank int(1) unsigned NOT NULL default '0',
admin int(1) unsigned NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;


and this is table characters:

TABLE characters (
username varchar(16) NOT NULL default '',
character varchar(50) NOT NULL default '',
level int(3) unsigned NOT NULL default '1',
class varchar(30) NOT NULL default 'not set',
race varchar(30) NOT NULL default 'not set',
main int(1) unsigned NOT NULL default '0'
) TYPE=MyISAM;


thanks for helping btw :)

Posted: Thu Jun 12, 2003 4:29 am
by []InTeR[]
Maybe 'escape' the table and colum names?

Code: Select all

<?php 
$query = "INSERT INTO `users` (`username`, `passwd`, `email`) VALUES ('$_POST&#1111;username]', '$_POST&#1111;password]', '$_POST&#1111;email]')"; 
   $result = mysql_query($query, $db_conn) or die("query &#1111;$query] failed: ".mysql_error()); 
   $query2 = "INSERT INTO `characters` (`username`, `character`, `level`, `class`, `race`, `main`) VALUES ('$_POST&#1111;username]', '$_POST&#1111;char]', '$_POST&#1111;lvl]', '$_POST&#1111;class]', '$_POST&#1111;race]','1')"; 
   $result2 = mysql_query($query2, $db_conn) or die("query &#1111;$query2] failed: ".mysql_error()); 
?>

Posted: Thu Jun 12, 2003 4:35 am
by twigletmac
Always a good idea to check the list of reserved names when creating a table:
http://www.mysql.com/doc/en/Reserved_words.html

Mac

Posted: Thu Jun 12, 2003 4:45 am
by irealms
thanks i renamed the characters field to charactername. Looking at the tables i showed you shuld i use username as a primary key in both fields? Not sure if i need id in the users table as this will have gaps in when users are removed.

Posted: Thu Jun 12, 2003 4:55 am
by cactus
It's always best (IMHO) to equate usernames with a unique ID and then create an index on that column. Having gaps in ID feilds shouldn't be too much to worry about.