[SOLVED] MYSQL Record Adding Issues

[Shadeaux]

Let me start by saying, I'm used to working with ASP and Visual Basic...not PHP.  My personal website is at www.hypermart.net and they give me five free MYSQL databases, so I'm trying to make use of them in any way possible.  Hypermart is all Unix based and doesn't support ASP.  SO....

 

I created a MYSQL Database.

I created a login and tested the PHP connection to this database.

 

I created an HTML form, then a php script to take the form data and insert it into my table.

 

The script echoes that the connection is made, and it echos that a record was added....but no record was added.  I'm not sure what could cause this.  I can post the code here, if that's the best method?  Any help would be greatly appreciated.

 

David

[teng84]

ya post your code!!!!!!!!!

[Shadeaux]

Okay, here's my script.  I just took out the password and database user name ... they are correct in the actual script.  Thanks~!

 

 

 

<?php

$link = mysql_connect('shadeaux.hypermartmysql.com','<username>','<password>');

if (!$link) {

    die('Could not connect: ' . mysql_error());

}

echo 'Connected successfully';

@mysql_select_db('hertford') or die( "Unable to select database");

 

$built = $_POST['built'];

$style = $_POST['style'];

$destroyed = $_POST['destroyed'];

$address = $_POST['address'];

$FirstOwnerSurname = $_POST['FirstOwnerSurname'];

$FirstOwnerMname = $_POST['FirstOwnerMname'];

$FirstOwnerFname = $_POST['FirstOwnerFname'];

$SecOwnerSurname = $_POST['SecOwnerSurname'];

$SecOwnerMname = $_POST['SecOwnerMname'];

$SecOwnerFname = $_POST['SecOwnerFname'];

$ThirOwnerSurname = $_POST['ThirOwnerSurname'];

$ThirOwnerMname = $_POST['ThirOwnerMname'];

$ThirOwnerFname = $_POST['ThirOwnerFname'];

$FourOwnerSurname = $_POST['FourOwnerSurname'];

$FourOwnerMname = $_POST['FourOwnerMname'];

$FourOwnerFname = $_POST['FourOwnerFname'];

 

$query = "INSERT INTO houses (','$built' , '$style' , '$destroyed' , '$address' , '$FirstOwnerSurname' , '$FirstOwnerMname' , '$FirstOwnerFname' , '$SecOwnerSurname' , '$SecOwnerMname' , '$SecOwnerFname' , '$ThirOwnerSurname' , '$ThirOwnerMname' , '$ThirOwnerFname' , '$FourOwnerSurname' , '$FourOwnerMname' , '$FourOwnerFname' ) ";

mysql_query($query);

echo("Record Added");

 

mysql_close();

?>

[AndyB]

Your posted querystring should have generated an error! Change

 

mysql_query($query);

 

to

 

mysql_query($query) or die("Error: ". mysql_error(). " with query ". $query);

[teng84]

what is this

$query = "INSERT INTO houses (','$built' , <-------','$built' ,

should be

$query = "INSERT INTO houses ($built' ,

or

$query = "INSERT INTO houses (' ','$built' ,

 

 

[Shadeaux]

Okay, I corrected the query line and got the following error:

 

Connected successfullyError: 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 '1928' , 'Gothic' , '1999' , '1313 Mockingbird Lane' , 'Munster' , 'NMN' , '' , '' at line 1 with query INSERT INTO houses (1928' , 'Gothic' , '1999' , '1313 Mockingbird Lane' , 'Munster' , 'NMN' , '' , 'Smith' , 'L' , '' , 'Truman' , 'S' , '' , 'Nixon' , 'M' , '' )

 

I also corrected the (',' at the beginning of the Insert

 

I thought I needed it there because I have a field named Housekey that is the primary key and is auto-incrementing - just a number.  I thought i needed to make sure the INSERT knew to put $built in the second field.  Is that not true?

 

David

[Jessica]

You have to do it like this:

INSERT INTO tables(fields here) VALUES(values here) and leave the first one out.

 

So: INSERT INTO houses('built') VALUES(1234) will insert a row with the auto-id and the built column filled in.

 

Make sense?

[phpSensei]

whats with everyone and not using the code tags?

[Jessica]

Well most of us are probably using the quick-reply and don't bother for ONE LINE OF CODE.

[Shadeaux]

Yes, I understand what you're saying.

 

So - if i were to try an example with two columns inserted, it would be:

 

INSERT INTO houses('built','style') VALUES('1234','1234')  ?

 

Alsok since I've created a variable for each bit of data to be inserted - drawing it from a web form, should I use the variable like:

 

INSERT INTO houses('built') VALUES($built)

 

 

 

I'm sorry to seem so idiotic in all this.  I'm currently working with ASP and VB at work - and this has my head about to explode...

 

David

[Jessica]

Yeah you need to use the variable like $built - as long as you are cleaning it properly by using things like mysql_real_escape_string()

If you have an auto-incrementing column you just leave it out of both parts, the columns list and the values list.

[Shadeaux]

SIgh (lol)

 

Um....what is: Yeah you need to use the variable like $built - as long as you are cleaning it properly by using things like mysql_real_escape_string()

 

where do I use it, and if it's not too much trouble, what is it for?

[Jessica]

Google "SQL Injection".

Also, in the future, when you don't know what a function does go to

php.net/function

 

so http://php.net/mysql_real_escape_string will tell you what it does and how to use it.

Googling SQL injection will give you more info on why to use it and other functions.

[Shadeaux]

Thanks for the resource.  I'm still a little unclear how to implement it, though.  I have a big group of variables defined with the $ and the Formdata...if I understood what I just read, I would use the

 

mysql_real_escape_string()  after the query...but in the examples at php.net they seem to be relating each time to a $username or $password variable (which I'm currently not using).  I'll look into it further and see if i can figure it out.

 

Is there a way to just insert the Form data into the VALUES()  bit without creating the variables?

 

and I do appreciate your time.  I'm always amazed how helpful people can be with code, and time...

 

David

[Jessica]

You need to make variables for each one so that you can "sanitize" the user input on each variable. If it's something that is not POSTed but is in your code like a constant, you don't have to clean it. It looks like all of yours are user input, so you need to make sure they are what you expect, to prevent errors and "hacking".

 

So if $built or $style can ONLY ever be a number, you can skip the mysql_real_escape_string and just use intval(). If it's a string, you need to use escape_string to prevent errors and injection. (If you have magic_quotes_GPC turned off which you really should IMHO.)

[Shadeaux]

Okay, thank you for your help.  I was able (finally) to get the silly thing to add a record to the database.  There was dancing and cheering ...

 

You folks rock.