My SQL help

[turnej]

I am trying to create a page where my members can upload information to a mysql table using an on page form - I would want my form to have two inputs one for first name and one for last name and then a submit button which when pressed will send the info to mysql database.

 

I also have two other fields in my mysql database (date uploaded and person who uploaded) With these I need to find a way that will instruct the database to date/time stamp the info and as for the person uploading I am going to password protect the page for individual users so I will know who is on the page and need to somehow send this to the database. I have made a start on some of the functions and pasted the form I am using and my php file below (these are snippets from the web which I am struggling to make work) as you may have guessed I am a beginner with php and mysql so any help needs to be dumbed down so I can understand it ha.

 

<form action="contact_insert.php" method="POST" id="insert">

<table>

<tr>

<td >First Name</td>

<td ><input type="text" size=40 name="fname"></td>

</tr>

<tr>

<td >Last Name</td>

<td ><input type="text" size=40 name="lname"></td>

</tr>

<tr>

<td colspan=2 id="sub"><input type="submit" name="submit" value="Add to Guestlist" ></td>

</tr>

</Table>

</form>

 

and the contact_insert.php file is:

 

<?php

 

// contact to database

$connect = mysql_connect("localhost", "user", "pass") or die ("Error , check your server connection.");

mysql_select_db("database");

 

//Get data in local variable

$v_fname=$_POST['fname'];

$v_lname=$_POST['lname'];

 

 

// check for null values

if ($v_fname=="" or $v_lname=="")

echo "All fields must be entered, hit back button and re-enter information";

else{

$query=insert into "database","table"("fname","lname") values("$v_fname","$v_lname",);

mysql_query($query) or die(mysql_error());

echo "Your guest has been added";

}

 

?>

 

Thanks in advance for any help

[kickstart]

Hi

 

Not sure what you are asking.

 

You can add a timestamp field to the table to automatically record the insert date / time. Or you can specify a value in a date / time field (NOW() will give you an appropriate value).

 

You should also use mysql_real_escape_string on the values you are inserting into the database to prevent SQL injection attacks.

 

Further your table should have a unique key. Name is unlikely to be unique long term. Probably best to have an Id field which is just an autonumber integer field (ie, each row that is inserted gets the next value).

 

All the best

 

Keith

[turnej]

Thanks Keith that's great I am sure this is a really stupid question but I am not familiar with mysql_real_escape_string could you explain a bit about these?

 

With regard to the unique identifier could it be both the first and last name? I am keen to make sure nobody is added to the table twice.

 

Can you spot any problems with my coding so far as when I try to run it I get errors relating to syntax etc?

 

Thanks again

Jon

[kickstart]

Hi

 

Mysql_real_escape_string is used to prevent some potentially dangerous things being added.

 

For example if you allow a single quote then that could signal the end of the string you are inserting. Most of the time this would just result in an error, but someone could be devious and put in a single quote followed by enough other info to complete the INSERT followed by a semi colon and then some rather naughty piece of SQL (such as DROP TABLE) which would wreck your site.

 

Name is highly unlikely to be unique for long. Even full name. There are loads of John Smiths around.

 

Only error that jumps out on me is the INSERT. You have:-

 

$query=insert into "database","table"("fname","lname") values("$v_fname","$v_lname",);

 

which is missing various quotes and has a load of extra ones, along with an extra comma. Try

 

$query= "insert into table('fname','lname') values('$v_fname','$v_lname')";

 

All the best

 

Keith

[turnej]

that's great Keith thanks very much you have been extremely helpful

[turnej]

I have used the fixes you gave me but when I tried to test I have got the following error, any ideas?

 

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 ''fname','lname') values('john','smith')' at line 1

[Pikachu2000]

Either remove the single quotes around the field names, or replace them with `backticks`. Single quotes are used to enclose string values in a query string.

[turnej]

This is how it appears at the moment?

 

<?php

 

// contact to database

$connect = mysql_connect('host', 'user', 'pass') or die ("Error , check your server connection.");

mysql_select_db('database');

 

//Get data in local variable

$v_fname=$_POST['fname'];

$v_lname=$_POST['lname'];

 

 

// check for null values

if ($v_fname==""  or $v_lname=="")

echo "All fields must be entered, hit back button and re-enter information";

else{

$query= "insert into table name('fname','lname') values('$v_fname','$v_lname')";

mysql_query($query)  or die(mysql_error());

echo "Your guest has been added";

}

 

?>

 

I have backticks around the fields I think?, not sure what I am missing?

[kickstart]

Hi

 

Sorry, that was my fault. I spotted the error and instead made one of my own.

 

Your code is currently

 

$query= "insert into table name('fname','lname') values('$v_fname','$v_lname')";

 

Either change that to

 

$query= "insert into table name(`fname`,`lname`) values('$v_fname','$v_lname')";

 

or to

 

$query= "insert into table name(fname,lname) values('$v_fname','$v_lname')";

 

Back tics (normally at the top left of the keyboard below the escape key - NOT single quotes) are used if a field name is a reserved word. So if you had a field called from you can access it without confusing the SQL. Personally I try to avoid any fields that are reserved words so virtually never need to use them.

 

In your code fname and lname are not reserved words so do not require back ticks, but no technical reason you can't use them.

 

All the best

 

Keith

[turnej]

ah I see I was using single quotes instead of backticks but I have dropped both now and it works so thanks for all your help I am very grateful