double query problems

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

double query problems

Post 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.
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

What does it fail with, whats the mysql_error() message ?
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

Post by irealms »

nothing, just says query failed, thats why i'm confused :)
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

Can you post the table definition of the database you are trying to write to. It could be a "types" thing.
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

Post 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 :)
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post 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()); 
?>
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

Post 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.
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post 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.
Post Reply