Writing to database via a web form

Hi Guys

I am a newbie so...but I can not find a decent tutorial on how to use a form to submit data to a MySQL table using PHP5. I tried several ways and got either duplicate records or records when the page was loaded or no data at all. Does the PHP go before or after the form data...I am using Olaf's access class as a basis and have connection to the DB but for some reason I can not get it to work.

For each user that has access to the webpages, do they need to have an account setup within MySQL? I know there are alot of questions here and have been reading quite alot, but none of the tutorials I found, seemed to work for me. Any suggestions on where I can find something that will work?

Thanks
gman

[Join us @ facebook]

As I am not sure what Olaf's class do exactly, for start I will try to answer your general  questions :

For each user that has access to the webpages, do they need to have an account setup within MySQL?

The user that connects to the database is php. So no matter how many users your site has, your users connect to the site, and the site connects to mysql with one account.

In general to react with the database you need a connection, and then everything is done with queries (SELECT, INSERT, UPDATE, etc.)

[Free WordPress Themes]

as addition to Nick's his post, maybe you should describe what you try to do...

[Join us @ facebook]

BTW you inspired me to write a small tutorial here :

http://www.webdigity.com/index.php?action=tutorial;code=31

[Free WordPress Themes]

BTW you inspired me to write a small tutorial here :

http://www.webdigity.com/index.php?action=tutorial;code=31

Nicely done

but I think that gman has some problems with database table design and keys

[Join us @ facebook]

Yeah propably...

Thanks guys...have not looked at any of your responses but there are no issues with the database structure..that seems to be fine. What I would like is to just have a user form and add to the table what has been entered when the submit button is clicked. But I am writing to fields 2, 3 and 7 and not the others as they are auto incremented, so I am not sure if that has to be accounted for when doing the INSERT statement.

So let me read what you fine fellows have written and I am glad that I inspired you all 

gman

[Free WordPress Themes]

try a statement like this:

INSERT INTO table SET col1 = 'value 1', col7 = 'value7',....

this way you don't need to add values for all columns

Nikolas

I tried your example and assigned the variables for the $db_server..etc, but I am getting an error with the $query on the line starting with $res...also, O do not understand the $fields within your INSERT statement...what is this reading? The form names? Do they need to be defined outside the form? ie. $fields = 'field1', 'field2', 'field3'?

Thanks again
gman

[Join us @ facebook]

To get this snippet work, you must make a post form that will send the data there.

For each <input> you must declare as name the name of the field in the table that you are about to insert the data.

Thanks Nikolas...I am still getting an error with the $query. Here is my code

Code:

<form id="form1" name="form1"  method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
  <label></label>
  <p>
    <label>Study_name
    <input type="text" name="study_name" />
    </label>
  </p>
  <p>
    <label>Study_Description
    <input type="text" name="study_description" />
    </label>
  </p>
  <p>
    <label>Study_Status
    <input type="text" name="study_status" />
    </label>
  </p>
  <p>
    <label>Create Study
<input type="submit" name="Submit" value="Submit" />
    </label>
  </p>
  </form>
<?php
 //Replace the values with the correct :
 $db_server='localhost';
 $db_user='user';
 $db_password='password';
 $db_name='clinical';
 $db_connection = @mysql_connect($db_server, $db_user, $db_passwd);
 @mysql_select_db($db_name, $db_connection);
 //This is the table that we are about to insert data in the database :
 $dbTable = 'global_studies';
 //Some security tasks(SQL injection)
 foreach ( $_POST as $f => $v )
  $_POST[$f] = addslashes ( $v );
 $fields = array_keys($_POST);
 //Prepare the query
 $sql = 'INSERT INTO '. $dbTable . '(`' . implode ('`, `', $fields) . '`) VALUES (\'' . implode ('\', \'', $_POST) . '\');';
 //So let's execute the query
 $res = mysql_db_query($db_name,$query,$db_connection);
?>

[Join us @ facebook]

in the end of the file add this, and let us know the result :

Code:

<?php
if (!$res) {
   echo "DB Error, could not query the database\n";
   echo 'MySQL Error: ' . mysql_error();
   exit;
}

It states that "query" is undefined and

mysql_db_query(): supplied argument is not a valid MySQL-Link resource...in file name
DB Error, could not query the database MySQL Error:

[Join us @ facebook]

Then the error is in the connection.

Something is wrong there.

Thanks Nikolas

The connection now seems to be fine but I am getting an error stating that the submit button is unknown

Could you look at my form to see if that is setup correctly? I hate to be a bother with this

Thanks
gman

[Join us @ facebook]

Does this works ?

<input type="submit" value="Submit" />

Same results...this is what I was getting

MySQL Error: Unknown column '' in 'field list'

Any idea? Don't you love challenges?

Thanks

[Join us @ facebook]

ok can you do this?

Code:

<?php
echo '<pre>';
var_dump( $_POST );

and post the result here (after you hit the submit)

I am now getting the first field within my table showing up even though I do not specifically tell it to be there

That code produced this
array(0) {
}

[Join us @ facebook]

Wait a minute. You get that error when you first load the page right?

If you press the submit button everything will be fine.

Here is how you should do the above code you posted :

Code:

<form id="form1" name="form1"  method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
  <label></label>
  <p>
    <label>Study_name
    <input type="text" name="study_name" />
    </label>
  </p>
  <p>
    <label>Study_Description
    <input type="text" name="study_description" />
    </label>
  </p>
  <p>
    <label>Study_Status
    <input type="text" name="study_status" />
    </label>
  </p>
  <p>
    <label>Create Study
<input type="submit" name="Submit" value="Submit" />
    </label>
  </p>
  </form>
<?php
if ( isset ( $_POST['study_status'] ) )
{
//Replace the values with the correct :
 $db_server='localhost';
 $db_user='user';
 $db_password='password';
 $db_name='clinical';
 $db_connection = @mysql_connect($db_server, $db_user, $db_passwd);
 @mysql_select_db($db_name, $db_connection);
 //This is the table that we are about to insert data in the database :
 $dbTable = 'global_studies';
 //Some security tasks(SQL injection)
 foreach ( $_POST as $f => $v )
  $_POST[$f] = addslashes ( $v );
 $fields = array_keys($_POST);
 //Prepare the query
 $sql = 'INSERT INTO '. $dbTable . '(`' . implode ('`, `', $fields) . '`) VALUES (\'' . implode ('\', \'', $_POST) . '\');';
 //So let's execute the query
 $res = mysql_db_query($db_name,$query,$db_connection);
}
?>

[Guest]

[37.733]

Googlebot, Yahoo crawler, Msnbot, Gigabot