[Resolved] mysql_query problems...

MereCatfish

Hi,
I have a simple HTML form (http://blamdink.co.uk/update.html). It's action is update_roster.php, the code for that is below.

<HTML><HEAD>
<TITLE>Roster</TITLE>
<BODY>
<?php

$Array["Name"] = trim ($_POST["Name"]);
$Array["Username"] = trim ($_POST["Username"]);
$Array["Gamertag"] = trim ($_POST["Gamertag"]);
$Array["Tagline"] = trim ($_POST["Tagline"]);
$Array["Maps"] = trim ($_POST["Maps"]);
$Array["Gametypes"] = trim ($_POST["Gametypes"]);
$Array["Location"] = trim ($_POST["Location"]);
$Array["Connection"] = trim ($_POST["Connection"]);
$Array["XBLGames"] = trim ($_POST["XBLGames"]);
$Array["Role"] = trim ($_POST["Role"]);
$Array["Rss"] = trim ($_POST["Rss"]);
$Array["Emblem"] = trim ($_POST["Emblem"]);

// Set the variables for the database access:
$Host = "no";
$User = "no";
$Password = "no";
$DBName = "no";

$Link = mysql_connect ($Host, $User, $Password);

mysql_select_db($DBname, $Link);

$Query = "INSERT into roster values ('', '$Array[Name]', '$Array[Username]', '$Array[Gamertag]', '$Array[Tagline]', '$Array

[Maps], '$Array[Gametypes]', '$Array[Location]', '$Array[Connection]', '$Array[XBLGames]', '$Array[Role]', '$Array[Rss]', 

'$Array[Emblem]')";
print ("<font face=\"Verdana\">You asked me to add this to the roster:<BR>$Query<P>\n");
$query = mysql_query($Query);
if ($query) {
	print ("Everything went well, the data should now be in the roster. If it isn't, please let MereCatfish know on the 

Helljumpers forum.<BR></font>\n");
} else {
	print ("Omg, something went wrong. The data is not in the roster. Please let MereCatfish know about this. In the 

Helljumpers forum, post the data you were trying to add so the problem can be found. Thanks.<BR></font>\n");
	echo mysql_error();
}
mysql_close ($Link);
?>
</BODY>
</HTML>

Now for some reason, I get the following error.

You asked me to add this to the roster:
INSERT into roster values ('', 'Test', 'Test', 'Test', 'Test', 'Test, 'Test', 'Test', 'Test', 'Test', 'Test', 'test', 'test')

Omg, something went wrong. The data is not in the roster. Please let MereCatfish know about this. In the Helljumpers forum, post the data you were trying to add so the problem can be found. Thanks.

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Test', 'Test', 'Test', 'Test', 'Test', 'test', 'test')' at line

Just wondering if anyone knows how to fix this, since I have been trying for a while and can't see the problem. Thanks. 🙂

kkobashi

Since I can't see your database table schema, I am guessing it is one of the following:

1) Wrong number of columns to values
Count the number of columns to see if what you are entering as values match.

2) Auto increment value not being set on the insert

If you are using a auto increment, then set it to an integer 0 on the insertion, not a blank character.

MereCatfish

1) Counted them...nothing wrong there.

2) I replaced the blank char with a 0, still same problem.

Thanks for your help though :bemused:

kkobashi

I entered a, b, c .... j, k, l on each of the edit boxes in the form. I received the following back:

You asked me to add this to the roster:
INSERT into roster values ('0', 'a', 'b', 'c', 'd', 'e, 'f', 'g', 'h', 'i', 'j', 'k', 'l')

Omg, something went wrong. The data is not in the roster. Please let MereCatfish know about this. In the Helljumpers forum, post the data you were trying to add so the problem can be found. Thanks.
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'f', 'g', 'h', 'i', 'j', 'k', 'l')' at line 1

Notice above, that '0' is the first column. You are representing that as a character, not an integer. If the first column is an integer, this is incorrect. Take away the single quotes.

Make sure you got a connection:

$Link = mysql_connect ($Host, $User, $Password);
if ($Link == FALSE)
echo "Could not connect ";

Make sure your database is getting selected:

if (mysql_select_db($DBname, $Link) == FALSE)
echo "Could not select database";

Need to see your database table schema. Please post it.

kkobashi

Here's the problem:

'$Array[Maps]

Notice that there is a missing single quote at the end...

INSERT into roster values ('0', 'a', 'b', 'c', 'd', 'e, 'f', 'g', 'h', 'i', 'j', 'k', 'l')

Notice the e above. It is missing an ending single quote as well...

MereCatfish

Interesting. I added the single quote and the code to determine whether it was the database and got...

Could not select databaseYou asked me to add this to the roster:
INSERT into roster values (0, 'Test', 'Test', 'Test', 'Test', 'Test', 'Test', 'Test', 'Test', 'Test', 'Test', 'test', 'test')

Omg, something went wrong. The data is not in the roster. Please let MereCatfish know about this. In the Helljumpers forum, post the data you were trying to add so the problem can be found. Thanks.
No Database Selected

So...the database isn't being selected. All the variables for the database are correct, so I am really confused. 🙂

MereCatfish

#

Table structure for table roster

#

CREATE TABLE roster (
id int(10) unsigned NOT NULL auto_increment,
Name text,
Username text,
Gamertag text,
Tagline text,
Maps text,
Gametypes text,
Location text,
Connection text,
XBLGames text,
Role text,
Rss text,
Emblem text,
PRIMARY KEY (id)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

kkobashi

Since there was no message after mysql_connect that you could not connect, I assume the $Link is correct (and therefore, $host, $username, and $password) was correct as well.

Check your $DBName. Make sure you have it correct.

Try removing all the single quotes and get rid of the auto increment at the end. Use this:

CREATE TABLE roster (
id int(10) unsigned NOT NULL auto_increment,
Name text,
Username text,
Gamertag text,
Tagline text,
Maps text,
Gametypes text,
Location text,
Connection text,
XBLGames text,
Role text,
Rss text,
Emblem text,
PRIMARY KEY (id)
) TYPE=MyISAM;

MereCatfish

The DBName is fine. I dropped the table and ran that sql and it still gave me the no database selected error. I removed the single quotes in the Query and got the same error. :bemused:

kkobashi

I want to make sure you are even getting a connection. Please verify that you put in these lines:

$Link = mysql_connect ($Host, $User, $Password);
if ($Link == FALSE)
echo "Could not connect ";

I just ran your form again. Don't remove the single quotes in the query. Put them back.

Again I typed in a, b, .... l.
Notice that there is no single quotes around the characters:

Could not select databaseYou asked me to add this to the roster:
INSERT into roster values (0, a, b, c, d, e, f, g, h, i, j, k, l)

Also, did you remove the AUTOINCREMENT=2?

MereCatfish

Yeah, put that in when you suggested it.

<HTML>
<HEAD>
<TITLE>Update Roster</TITLE>
<BODY>
<?php

$Array["Name"] = trim ($POST["Name"]);
$Array["Username"] = trim ($POST["Username"]);
$Array["Gamertag"] = trim ($POST["Gamertag"]);
$Array["Tagline"] = trim ($POST["Tagline"]);
$Array["Maps"] = trim ($POST["Maps"]);
$Array["Gametypes"] = trim ($POST["Gametypes"]);
$Array["Location"] = trim ($POST["Location"]);
$Array["Connection"] = trim ($POST["Connection"]);
$Array["XBLGames"] = trim ($POST["XBLGames"]);
$Array["Role"] = trim ($POST["Role"]);
$Array["Rss"] = trim ($POST["Rss"]);
$Array["Emblem"] = trim ($POST["Emblem"]);

// Set the variables for the database access:
$Host = "db1.netdojo.com";
$User = "xs";
$Password = "shutdoor";
$DBName = "xs2";

$Link = mysql_connect ($Host, $User, $Password);
if ($Link == FALSE)
echo "Could not connect ";

mysql_select_db($DBname, $Link);
if (mysql_select_db($DBname, $Link) == FALSE)
echo "Could not select database";

$Query = "INSERT into roster values (0, $Array[Name], $Array[Username], $Array[Gamertag], $Array[Tagline], $Array[Maps], $Array[Gametypes], $Array[Location], $Array[Connection], $Array[XBLGames], $Array[Role], $Array[Rss], $Array[Emblem])";
print ("You asked me to add this to the roster: $Query\n");
$query = mysql_query($Query);
if ($query) {
print ("Everything went well, the data should now be in the roster. If it isn't, please let MereCatfish know on the

Helljumpers forum. \n");
} else {
print ("Omg, something went wrong. The data is not in the roster. Please let MereCatfish know about this. In the

Helljumpers forum, post the data you were trying to add so the problem can be found. Thanks. \n");
echo mysql_error();
}
mysql_close ($Link);
?>
</BODY>
</HTML>

MereCatfish

Development:

I noticed that in the code to find whether the database could be selected, the second time DBName was mentioned, the N was lowercase. I fixed that and got a new error.

You asked me to add this to the roster:
INSERT into roster values (0, Test, Test, Test, Test, Test, Test, Test, Test, Test, Test, test, test)

Omg, something went wrong. The data is not in the roster. Please let MereCatfish know about this. In the Helljumpers forum, post the data you were trying to add so the problem can be found. Thanks.
Unknown column 'Test' in 'field list'

kkobashi

Ok, now you know the following:

1) You can connect
2) You can select a database

Try putting single quotes around the values in the query statement and double quotes around the keys in the array indices.

$Query = "INSERT into roster values (0, '$Array["Name"]', '$Array["Username"]', '$Array["Gamertag"]', '$Array["Tagline"]', '$Array["Maps"]', '$Array["Gametypes"]', '$Array["Location"]', '$Array["Connection"]', '$Array["XBLGames"]', '$Array["Role"]', '$Array["Rss"]', '$Array["Emblem"]')";

echo the query out to see if it looks ok:

echo $Query;

Do not put it around the id. It is an integer and not a character value.

'0' is not the same as 0
0 = integer 0
'0' = character 0

Try the above.

kkobashi

I just ran it again. You have an error. Look at the k below. 'k,' should be 'k'

You asked me to add this to the roster:
INSERT into roster values (0, 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k,' 'l')

MereCatfish

I put single quotes around everything in the query except the 0.

I got:

You asked me to add this to the roster:
INSERT into roster values (0, 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k,' 'l')

Omg, something went wrong. The data is not in the roster. Please let MereCatfish know about this. In the Helljumpers forum, post the data you were trying to add so the problem can be found. Thanks.
Column count doesn't match value count at row 1

MereCatfish

Wow! Thanks loads, it works!

http://blamdink.co.uk/view_roster.php

Thanks for all the help, I definitely wouldn't have done it otherwise 😃 .

kkobashi

Now it works!

Click the "mark as resolved" link to close this issue.