Uploading data to database using where clause

PHP
1

Morning,

I have slightly ammended a insert script to upload some form data according to a specific email address in the database.


// Get values from form 
$name=$_POST['name'];
$lastname=$_POST['lastname'];
$email=$_POST['email'];

// Insert data into mysql 
$sql="INSERT INTO $tbl_name(Username, Password)VALUES('$lastname', '$email') where Email='$name'";
$result=mysql_query($sql);

When where Email=‘$name’ is not there the script works, but when I put that back it doesnt.

As you can see, its saying to put a username and password into the database where the email address in the database matches the email address selected by the user ($name’).

Should I be using a select script to to firstly select the email address from (Email) field.

2

OK I’m trying a few things as below, and still cant get it to work.


$sql=mysql_query("INSERT INTO $tbl_name(Username, Password)VALUES('$lastname', '$email') WHERE Email='".$_POST['$name']."'");
3

where Email=‘$email’"

Are you positive you should not be doing the above?

With all things PHP/Mysql it is a case of divide and conquer.

Temp add this line to find out what sql is being assembled by PHP

echo $sql;

Copy the output of that to your database and make sure it is a LEGAL statement, and that you have MATCHING DATA in your database.

If you don’t then deal with those problems first.

Make sure you have a working example of a positive SQL statement, then recreate that in PHP

$sql=“INSERT INTO MyTable (Username, Password) VALUES (‘testname’, ‘testpassword’) where Email=‘test@email.com’”;

Alter MyTable to the ACTUAL TABLE NAME you are using.

Enter the test records into your database.

Paste the query into your database - do they get the result?

Paste the query into PHP, and start to carefully ONE BY ONE replace the actual values with PHP ones - keep testing with the test values you have set in your database.

4

You can’t have a WHERE in an INSERT statement.

If you want to update the values of username and password for a certain user, use UPDATE instead.

5

that’s because INSERT VALUES does not allow a WHERE clause

i have a feeling you really want to be doing an UPDATE, not an INSERT

6

You’re missing a comma between the two fields to be updated.

7

OK, so I change INSERT to UPDATE and keep the code as is, is that right.

$sql=“UPDATE $Register(Username, Password)VALUES(‘$lastname’, ‘$email’) WHERE Email=‘$name’”;
$result=mysql_query($sql);

I tried it, and it still doesnt work…

Be great if I can get this sorted as I have been ages on it, and was on the wrong track by the sounds.

8

$sql=mysql_query(“UPDATE Register(Username, Password)VALUES(‘$lastname’, ‘$email’) WHERE Email=‘$name’”);

9

Trying this now, and still no workio. Sorry guys.

$sql=mysql_query(“UPDATE Register SET Username=‘$lastname’ Password=‘$email’ WHERE Email=‘$name’”);

10

Hi guido2004,

Tried that as below and it didnt work, and the echo values are correct too.

$sql=“UPDATE Register SET Username=‘$lastname’, Password=‘$email’ WHERE Email=‘$name’”;
$result=mysql_query($sql);

echo=$sql

UPDATE Register SET Username=‘5’, Password=‘6’ WHERE Email=‘jmberrio@checksafetyfirst.com’

Thats exactly the details I entered into the form.

That email does exist in the database, and it is the correct Field name.

Strange, and thank you for helping

11

Copy and paste that echoed query and run it in phpMyAdmin

12

Ah yes, pasted it in, and it worked…

It put the correct details into Username and Password for the correct email address, so thats good.

So is the problem the next line, or you think there maybe permission troubles?

$result=mysql_query($sql);

13

Hi Guido2004, we checking with the host now on permissions, hopefully that will show something up.

as the code seems to be good

14

Change that line in

$result=mysql_query($sql) or die('mysql error : ' . mysql_error() . ' in query: ' . $sql);

and see if an error shows up.

15

Yes i got this error;

mysql error : UPDATE command denied to user ‘tourcheck’@‘172.31.16.12’ for table ‘Register’ in query: UPDATE Register SET Username=‘6’, Password=‘7’ WHERE Email=‘jmberrio@checksafetyfirst.com’

16

It means the user doesn’t have the permission to update that table. Use another user, or change the permissions.

17

Thank you guido2004, we are looking into it now.

Thanks for the help, and trouble will let you know.

Thanks again

18

Hi again guido2004,

Do you have a simple script handy wither php or html that will send an email on success.