MySQL Beginner... help me please!

[Jeff321]

Hello everybody, I am a beginner with MySQL and am having some problems editing records in my tables using MySQL-Front and the MySQL Control Center.

In MySQL-Front I see the buttons to Edit Record, Delete Record, etc... but I can't seem to get them to work. I edit and then "Post Edit" (which I assume is how you save it) but when I refresh I find it has edited both records, not just one?? Also, I can't figure out how to get the Delete Record button to work. I can delete it, but I guess it's not saving/registering the delete because when I refresh, the deleted record is right back there again.

In the MySQL Control Center, I can't edit or delete... I'm just getting "[jeff321] ERROR 1146: Table 'jeff321.' doesn't exist". I don't have a table named 'jeff321.' nor am I trying to access it. jeff321 is my DB name, does that have anything to do with it?

Anybody know what I'm doing wrong? Any suggestions of different programs I should try?

Thanks a lot. :)

[Metro]

Jeff,in ops/sql do you have the permissions set to Y (yes) under the user section for updates & deleting. It sounds like they are not set to yes.

[netaustin]

how did you get your records there in the first place if you can't edit or delete using your current privledges?

[Jeff321]

John, everything is set to Yes in OPS. I never touched it.

Austin, they were created with a PHP script.

[Jeff321]

I installed PHP-MyAdmin, and got that to work.

I've been learning about PHP and writing a simple script to login to a website based on a password in a MySQL DB. However, it's not working, and I don't know why.

http://jeff321.com/test/php-mysql-login-test.php

You can view the source code at this URL (Try view-source, or just save the file. MySQL info has been edited.):
http://jeff321.com/test/php-mysql-login-test.txt

The user/pass as stored in the DB is "theuser" and "thepass", but even when I put in the correct info, it returns "Invalid password for user theuser."

Can anybody find my problem in the code?

Thanks for your help. :)

[HalfaBee]

$result = @mysql_query("SELECT pass FROM test_login WHERE user = '$user_from_form'");

// Check if there is no result. (This part does not seem to work.)
if (!$result) {
echo( "<p>Invalid user.</p>" );
$displaylogin = "yes";
exit();
}


$result will be FALSE only if the SQL was incorrect.
To check for a result use mysql_num_rows($result).

You can do the user and password checking in one select statement

"SELECT user from test_login WHERE user='$user_from_form' and pass='$pass_from_form' "

This will return 1 row if correct and 0 rows if incorrect.

HalfaBee

[Jeff321]

Thanks a lot HalfaBee.

I assumed that if $result was false, that meant it could not find that user, but I guess that was incorrect. As you suggested, I'll make the changes to the code to check whether or not the user exists first. I did not know how to check the user and pass at the same time (nor did I know it was possible). That should simplify things, and (I hope) correct my problem. Thanks again, I'll be sure to try it out and post how it works.

One question though... what if there are two rows in the DB with the same user? Will it cause any problems with the script? How could I check the number of results returned and, say, show an error if the number of results is greater than 1?

[HalfaBee]

You could/should make the user UNIQUE and avoid the problem.

create table newtable ( user varchar(30) unique,pass varchar(30) )


SQL is able to do the 2 operations in 1 go no problems it is very flexible.


You test for 2 rows the same way
if( mysql_nums_rows($result)>1 )
echo "Two users the same O dear";

HalfaBee

[Jeff321]

Thanks again.

I rewrote the code, and it works great now. :D

http://jeff321.com/test/php-mysql-login-test2.php
http://jeff321.com/test/php-mysql-login-test2.txt

Comments and suggestions are always appreciated.

Next step: Incorporate it with actual content in the DB, and allow the user to edit their password and info. Eventually I'll want to use it as a way for members to edit their profile on a website. :)