Web Interface for Modifying Database Tables

[MilchstrabeStern]

I need to create a very simple web Interface that will just insert data into certain tables. So the person just has a content box for each field that is in the table (the web interface doesnt need to find out what the fields of the table are because ill define thaqt in the script or whatever). Do you see what im getting at?

Say I have 3 Fields one for Date, one for Content, one for Link and for each of these fields i have a content box, the person types in what they want to add, and then presses insert an dit inserts it into the table in the defined database. Make sense?

[sonjay]

What exactly are you needing help with?
*Creating the database itself?
*Creating the table in the db?
*Creating the fields in the table?
*Creating the form?
*Connecting to the database?
*Writing the mysql query to insert the data?

Tell us what you have so far, and what you still need.

[MilchstrabeStern]

*Creating the database itself?
*Creating the table in the db?
*Creating the fields in the table?
*Creating the form?
*Connecting to the database?
*Writing the mysql query to insert the data? <----sorta this one is what i need

I know how to do it, i have a database set up, mySQL tables, i can connect to it everything I use a lot of the content on my webpage from my databases using php code to retrieve it. BUt I need to create a simple simple webpage for someone whos not good with computers to be able to insert data into the specified table so:



Date

*Text field to type in*

Content

*Text field to type in*

Link

*Text field to type in*

::Insert Button::

[Lats]

So, just a couple of inputs...

<input type="text" name="content" size="40" value="">

etc...

A basic insert query...

$sql = "
INSERT INTO 
table_name 
SET
content_field='".$_POST['content']."',
link_field = '".$_POST['link']."',
date_field = '".$_POST['date_input']."'";

$result = mysql_query($sql) or die(mysql_error()); 

[sonjay]

Lats beat me to it. The form fields and the insert query are what you need.

For the date field, if you want to insert the current date, you can simply use now() for the value to insert, instead of having the user fill out the date. Or you can use any of several date formatting options to format the date a particular way.

For the content and link fields, you'll probably want to do some vetting of the input before inserting it into the db. Before your query, you would do this:

$Content = addslashes($_POST['Content']);

Then insert $Content into the db.

This would "escape" any quotation characters the user entered in the Content form field, so that they get inserted into your db properly.

There are additional functions you can use also, to translate html character entities, trim leading or trailing spaces, etc. You'll have to decide which of them you need to use, depending on your userbase, what they're entering, and how tightly you need to control what goes into the db.

[MilchstrabeStern]

ah very cool, and the content_field, is that just the name of each field? Of course id have to connect to the database and select the table before the $sql= . Also would this create a button the user can press? Or how would I do that?

Thanks!

[sonjay]

Your form page would contain something like this:

<form method="post" action="addform.php">
Content: <input type="text" name="content" size="40" value=""><br>
Link: <input type="text" name="url" size="40" value=""><br>
<input type="submit" value="Submit">
</form>

The form posts to a file named addform.php The file addform.php would contain something like this:

// connection variables:
$db = "dbname"; // name of your database
$user = "user"; // username for your database
$password = "password"; // password for your database

// make the connection
$link = mysql_connect("localhost", $user, $password);
if (!$link) die ("cant connect to mysql");

//select the database once connected.
mysql_select_db($db, $link) or die ("cannot connect to $db");

// Now you're connected to the db

// handle the posted variables
$content = $_POST['content'];
$content = addslashes($content);

$url = $_POST['url'];
$url = addslashes($url);

// Now insert the data into the Database

// tablename is the name of your table
// (content,url,date) are the names of the fields in your table
// $content, $url, now() specify the values to insert into the table
// $content and $url are the variable names from above
// now() inserts the current date

$query=" INSERT INTO tablename (content,url,date) VALUES ( '$content', '$url', now() ) " or die("Bad query: " . mysql_error()) ;

$result=mysql_query($query);

if ($result) {
echo "Your information has been added to the database";
} else {
echo "Sorry, your information has not been added";
}

[MilchstrabeStern]

am I allowed to use a <textarea> tag instead of the <input> tags?

Im encountering an error, if you view the script in action: http://www.mplionhearts.com/phptesti...ouncements.php you will se ewhat i am talking about.

$query=" INSERT INTO weekly (content,date) VALUES ( '$content', '$date' ) " or die("Bad query: " . mysql_error()) ;

$result=mysql_query($query);

if ($result) {
echo "Your information has been added to the database";
} else {
echo "Sorry, your information has not been added";
}

?> 

im assuming its in that area, but i cant add data, the submit button doesnt have any action. Instead whenever you view the page, it adds a blank row of data, so dont refresh it too many times .


And how can I password protect this interface, cause i dont exactly want the whole world enterting data that will appear on my webpage lol.

[Lats]

You're going to need an action for your submit button, otherwise nothing happens - as you've already found out

All you need is something like this...

$submit = $_POST['submit'];
if($submit)
{
   // do database additons
}
else
{
  // do your input form
} 

I suggest your form tag should look something like this...

<form name='theform' action='script_name.php' method='POST'>

[MilchstrabeStern]

Ah with that second part, the idea is that i dont need the php script on the same page as the <form> tag.

[sonjay]

Well, the problem is, you have the form's action set to addform.php, just as I had in my example -- but you apparently put the form processing page on the same page as the form, rather than putting it into a separate file. You can certainly do this, but it adds a slight layer of complexity: You have to address what to display before the form is submitted, and what to display after the form is submitted. The code Lats posted would take care of that. What that's doing is, that will display the form if the person hasn't submitted the form, and it displays the success (or failure) message after they submit the form.

If you're going to do that, though, you would submit the form (set the form action) to the same page the form is on, though, rather than submitting it to a separate form processing file:

<form name="theform" action="<? echo $PHP_SELF;?>" method="POST">

[MilchstrabeStern]

Oh i totally missed that in your post lol sorry about that. Yeah its tons cleaner and more simple to put it in a seperate file so I'll try that when I get home today, thanks!

[sonjay]

Oh i totally missed that in your post lol sorry about that. Yeah its tons cleaner and more simple to put it in a seperate file

It's somewhat simpler, yes, but not necessarily better. Let's say, for example, that you have a user who will be submitting information into the database several times in a row. You could set the form action to submit to itself, and do it like this:

$submit = $_POST['submit'];

// Put your form here; it will display on the page regardless of whether the form has been submitted

if($submit)
{
// put the form processing stuff here
}

If you do that, then the user would be able to hit "Submit" to submit the form, then after processing is done, he would still have the form in front of him to enter another record. No redirection, no back button, just ready for another record entry, immediately.

OTOTH, if the user generally won't be submitting data several times in a row, you'd probably not want to do it that way, because that would increase the likelihood of the user mistakenly entering the same data multiple time. In that case, you'd either want to put the code to display the form inside the "else", as Lats showed, or just submit the form to a separate page for processing.

So the best way to do it really depends on how the form will be used.

[MilchstrabeStern]

<?php

 $db = "mplionhe_announ"; // name of your database
$user = "mplionhe"; // username for your database
$password = "pass"; // password for your database 

 // make the connection
$link = mysql_connect("localhost", $user, $password);
if (!$link) die ("cant connect to mysql");

 //select the database once connected.
mysql_select_db($db, $link) or die ("cannot connect to $db");

 // handle the posted variables
$content = $_POST['content'];
$content = addslashes($content);
 // handle the posted variables
$date = $_POST['date'];
$date = addslashes($date);

 // Now insert the data into the Database

// tablename is the name of your table
// (content,url,date) are the names of the fields in your table
// $content, $url, now() specify the values to insert into the table
// $content and $url are the variable names from above
// now() inserts the current date
 $submit = $_POST['submit'];
if($submit)
{
  $query=" INSERT INTO weekly (content,date) VALUES ( '$content', '$date' ) " or die("Bad query: " . mysql_error()) ; // do database additons
}
else
{
  // do your input form
} 


$result=mysql_query($query);

if ($result) {
echo "Your information has been added to the database";
} else {
echo "Sorry, your information has not been added";
}

?>

Heres the full code, however, now that my form is working, at http://www.mplionhearts.com/phptesti...ouncements.php it wont succesfully insert the information. Go ahead and try it, see what happens.

[Lats]

Add the error checking to the mysql_query line, not the string building line

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

[MilchstrabeStern]

Is it? Cause now I'm getting "The Query was empty" messages?

[Lats]

As you are calling this script from a different form, you can delete the 'if(submit)' bit. That's failing because your input form doesn't have the 'name' submit, and as it's failing, $query doesn't get loaded.

[MilchstrabeStern]

OH yup, forgot about that, works fine now thanks !

[Lats]

Good to hear.