PHP not updating my MYSQL table

Den_M

Hi, I'm trying to insert a new row in a MYSQL table called savedetails.
The php code i have is as follows, the table is defined at the end of this post.
I have obviously done something wrong as I keep getting the error in Flash that tells me no rows were affected by the query. Flash is finding the file ok, I'm not very good at mysql and have probably just written the insert query wrong. Can anyone please point out what the problem is?

I'm using included.php to save having to write out the name of the table, password etc each time.
The code in that is :

---
<?
// my variables

$DBhost = "localhost";
$DBuser = "root";
$DBpass = "mypassword";
$DBName = "savedetails";
$table = "savedetails";

?>

---

The code in register.php is :

---

require 'include.php';
// Connect to the Database.
$Connect = mysql_connect($DBhost, $DBuser, $DBpass);
mysql_select_db("$DBName");

// Preforms the SQL query
$query = "INSERT INTO $table (userName, chapter, frame, box, lantern, chainkey, battery, crowbar, shotgun, toolbox) VALUES ('$RegName' , '1', '1', 'no', 'no', 'no', 'no', 'no', 'no', 'no')";
$result = mysql_query($query);

// Gets the number of rows affected by the query as a check.
$numR = mysql_affected_rows($Connect);

if ($numR == 0) {
print "Status=Failure Please Fill out all Fields - Register Again";
}
else if ($numR == 1) {
print "Status=Success You can Now Login - Login";
}

else { print "Status=General Error - UserName already in Use";
}

?>

---

I keep getting that 'Failure Please Fill out all Fields - Register Again' error printed to the status label in flash. I created my table in the following manner :

create table savedetails (
userID int not null auto_increment,
userName varchar(60) not null unique,
chapter int,
frame int,
box varchar(60),
lantern varchar(60),
chainkey varchar(60),
battery varchar(60),
crowbar varchar(60),
shotgun varchar(60),
toolbox varchar(60),
primary key (userID));

Or would i be better off looking for help on a mysql board?

ron_darrell

My experience is almost entirely with ASP so I may be shooting the breeze here but it seems to me that if a variable is defined as type int then it shouldn't be passed in quotes

Try:
$query = "INSERT INTO $table (userName, chapter, frame, box, lantern, chainkey, battery, crowbar, shotgun, toolbox) VALUES ('$RegName' , 1, 1, 'no', 'no', 'no', 'no', 'no', 'no', 'no')";
$result = mysql_query($query);

Instead of:
$query = "INSERT INTO $table (userName, chapter, frame, box, lantern, chainkey, battery, crowbar, shotgun, toolbox) VALUES ('$RegName' , '1', '1', 'no', 'no', 'no', 'no', 'no', 'no', 'no')";
$result = mysql_query($query);


-RD

Den_M

Thanks for replying - I tried that. Still getting the same error as i think something that happens before that is going wrong. Does anyone know what the mysql $DBhost value should be in include.php. i have localhost in there but thats surely not right...anyone know the location of the mysql host is? PHP needs to be able to jnow where the table is located so it can enter info in...

leeroybrown

The best thing to do in this situation (if at all possible) is to get your PHP code to print out the SQL query and manually run it via the mysql command line. You may even realise why it's failing when you see the actual query the code is running.

Adam

With regards to leeroybrowns post, change the line [php]$result = mysql_query($query);[/php] to [php]$result = mysql_query($query) or die(mysql_error());[/php]

This means that if there is an error in your query and it doesn't run, it will give you the mysql error relating to your problem. Helps with debugging.

As for the actual problem, if I'm not mistaken you have written this part wrong:

[php]$numR = mysql_affected_rows($Connect);[/php]

You're running mysql_affected_rows on the $Connect, which is simply your database connection. What you want to do is check the affected rows from the result of your query, so that line should read:

[php]$numR = mysql_affected_rows($result);[/php]

Try both of these and let us know how it goes. I'm assuming there is another problem somewhere as I imagine you've checked the database manually to see if the rows are being inserted.

Den_M

Thanks for those suggestions fellas, I made those changes Mirror, still getting no update in my table though. This has me tearing my hair out, its for a college project and has to be in by tomorrow afternoon.

Den_M

Thing is, I dont get the 'fill in fields again' error or the 'username already taken' error, I must be doing the query wrong? How does the php file know where my table is, from where I specified in include.php?

When installing mysql i wasnt asked to give a username, only password

Adam

How did you go about installing php/mysql? Have you done database work before? Are you sure they are communicating correctly?

I've tested the above script and it works fine for me...

Den_M

I installed both via the usual wizard style installs. i downloaded the zip files from their websites. I have php version 5.2.1 and mysql server 4.1. I'm also running apache 2.0.59. I'm getting worried now that i've set up something incorrectly and thats the reason nothings working.

When mysql starts up it says : your mysql connection id is 456 to server version 4.1.22-community-nt. is this of any relevance?

thanks for the help Mirror

Cake Fiend

Den_M wrote:

How does the php file know where my table is, from where I specified in include.php?

Yes (specifically, the values you pass to mysql_select_db() and the insert statement).

Using 'localhost' as the mysql server will only work if mysql is running on the same host as the web server - is this the case?

If so, the php code looks OK at a cursory glance, are the values actually getting entered into the table?

Den_M

yes, this isnt something that i want to even host, its all on the one machine. nope the values arent going into the table, im checking with select * from...and its telling me that its empty

Den_M

When i type http://localhost/register.php into my browser, I'm getting 'Access denied for user 'ODBC'@'localhost' (using password: NO)'
That seem familiar? first time ive seen that, usually get some text that it prints out if the query was unsuccessful. I have been changing things in include.php a bit though

I'm not sure what to put in for my mysql username, i never specified one while installing mysql, just a password

Adam

With the default mysql installation, your username is root.

Edit register.php (or create a new file called test.php or something) and put in this code:

[php]
<?
// my variables

$DBhost = "localhost";
$DBuser = "root";
$DBpass = "mypassword";
$DBName = "savedetails";
$table = "savedetails";


// Connect to the Database.
$Connect = mysql_connect($DBhost, $DBuser, $DBpass);
mysql_select_db("$DBName");

$RegName = "bob";

// Preforms the SQL query
$query = "INSERT INTO $table (userName, chapter, frame, box, lantern, chainkey, battery, crowbar, shotgun, toolbox) VALUES ('$RegName' , '1', '1', 'no', 'no', 'no', 'no', 'no', 'no', 'no')";
$result = mysql_query($query) or die(mysql_error());

echo $query;
[/php]

and tell me what the result is. That code there definitely works, I've tested it. If you get an error, copy and paste here.

Also, rather than checking your database entrys with SELECT statements, click this link to download SQLYog Community Edition, its a very user friendly database interface: http://www.webyog.com/downloads/SQLyog527.exe

Den_M

Sorry, did you mean me to put that file in my localhost folder and trpe the address in the browser?

if so i just got this : <? // my variables $DBhost = "localhost"; $DBuser = "root"; $DBpass = "korn666"; $DBName = "savedetails"; $table = "savedetails"; // Connect to the Database. $Connect = mysql_connect($DBhost, $DBuser, $DBpass); mysql_select_db("$DBName"); $RegName = "bob"; // Preforms the SQL query $query = "INSERT INTO $table (userName, chapter, frame, box, lantern, chainkey, battery, crowbar, shotgun, toolbox) VALUES ('$RegName' , '1', '1', 'no', 'no', 'no', 'no', 'no', 'no', 'no')"; $result = mysql_query($query) or die(mysql_error()); echo $query;

pretty much whats in the file i guess.

i also tried linking to the file from behind my register button in flash, same result as before im afraid

the code behind my register button BTW is :

---

on (release) {
//add_user()
if (RegName ne "") {
Status = "Beginning registration Process... Please Hold";
loadVariablesNum ("http://localhost/pleasework.php", 0);
}
else {
Status = "Please enter a User Name to register";

}

}

---

as you can tell by the name i gave the file i was hoping against hope

the tutorial i was going by had this extra bit to the line behind the register button : //loadVariablesNum ("http://localhost/register.php?userName="+RegName+"&"+random(999), 0);

not sure what it wanted to adda a random number after it for

Den_M

I installed SQLyog, set up a new connection, put in my password and databse name and it connected fine.the table is showing up ok

Adam

Ok so you saved the code I gave you into a file and ran it with http://localhost/filename.php and basically got the content of the file printed out?

That could mean only two things I believe...

1) Apache isn't running properly/at all and therefore wasn't parsed by the browser...
2) You forgot your open and closing php tags (<?php ...... ?>)?

EDIT: Actually, I see that you had your tags in the resulting print (well actually you missed the closing tag...) but I think another possible problem is that you have short-tags disabled in php.ini, meaning you can't just use <? you have to use <?php when opening a block of php code.

Den_M

I changed the <? to <?php and refreshed
this time i got the query printed out :

INSERT INTO savedetails (userName, chapter, frame, box, lantern, chainkey, battery, crowbar, toolbox, shotgun) VALUES ('bob' , '1', '1', 'no', 'no', 'no', 'no', 'no', 'no', 'no')

that any closer to what should be happening?

edit : checked the table, a row got added!! 'bob' and everything! a good sign surely?

Den_M

Still getting that 'Access denied for user 'ODBC'@'localhost' (using password: NO)' error when I try register.php, the one I was using earlier

If register.php got working and instead of hard coding in the regName in the php file, set it to the value of my text box so i could add rows by changing the name in the text box and clicking register, it'd be flippin fanastic.

thanks for the help so far Mirror, youre a star

Adam

Ok, the ODBC thing simply means your mysql username and password are wrong. You said you weren't asked for a username, so that has to be "root". I presume if you put in a password you've edited the file to include your password?

Den_M

I'm an idiot, i had a lowercase r instead of a capital one. I'm getting a mysql query problem now, the password ect are obviously ok, im prolly trying to put in too many things or doinf it the wrong way. Still, its better that it was for sure!

Adam

Ah it's always the little things

Let me know if I can help you further, though I'm not one for flash so I won't be much use in that respect!

Den_M

The problem query is :

$query = "INSERT INTO $table (userName, chapter, frame, box, lantern, chainkey, battery, crowbar, toolbox, shotgun) VALUES ('$RegName', 1, 1, 'no', 'no', 'no', 'no', 'no', 'no', 'no')";

do the names of whats in the table have to mentioned before the values you want to out in? thats the way it was done in the table from the tutorial i was working along with. i thought itd be just be :

insert into table values (blah....)

Adam

Nope, that's the correct syntax, and as I mentioned, it works.

Be sure to use the $result=mysql_query($query) or die(mysql_error()); to get a helpful error message should the query fail.

If that query isn't working it's probably because you've still got $RegName hard coded as bob but you've set the field userName to be unique so bob wont go in to the table twice therefore the whole query fails! Just a guess

Den_M

I've got a bit more to get done yet but i think the confusing parts out of the way, many, many thanks Mirror!!

Adam

Not a problem at all, I was there once, deadlines are a bitch!

If you need me I'll still be around on gmail adam.p.gibbons[at]gmail, as boards will be down now shortly! Best of luck!