DaniWeb IT Discussion Community - submiting info into a database for retrieval!

Intro

Before we start need to create a databse to work in (i will assume you are using phpadmin to do this) just go to (databases)... and in the blank textbar create a new database, and call it phpforms

then open up your command prompt and enter your mysql shell
(for me it was C:\mysql\bin\mysql -u michael -p)

and enter these lines

mysql> CREATE TABLE information (
> id INT NOT NULL AUTO_INCREMENT,
> name VARCHAR (50),
> email VARCHAR (50),
> opinion VARCHAR (30),
> PRIMARY KEY (id)
);

Getting started

OK now we're ready to work. First you need a way to access your database so you can see what people think of your site/forums or whatever you plan on using this for.

We use this script:

[php]<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title> How to Grab Data from a MySQL database!</title>
</head>
<body>
<table border="1" cellspacing="2" cellpadding="1" height="800"><caption>How people felt about my site!</caption>
<tr>
<td valign="top" bgcolor="#CCCCCC">
<?php

// prints out at top of page, so that poeple know what they are looking at//
echo "poeple that found site to be horrible:<hr /> ";

/* declar some relevant variables */

$Host = "locationofmysql"; //location of mySQL on server
$User = "yourlogin"; //my username
$Pass = "yourpass"; //my password
$Name = "phpforms"; //name of the database to be used
$Table = "nameoftable"; //name of the table within the database

mysql_connect ($Host, $User, $Pass, $Name, $Table) or die ("unable to connect to database");
@mysql_select_db("$Name") or die ("unable to select DB");
$sqlquery = "SELECT * FROM $Table WHERE opinion = 'is horrible'";
$result = mysql_query($sqlquery);
$number = mysql_num_rows($result);

$i = 0;

if ($number < 1)
{
echo "<center>No results to display!</center>";
}
else
{
while ($number > $i)
{
$thename = mysql_result ($result, $i, "name");
$theemail = mysql_result ($result, $i, "email");
echo "Name: $thename E-Mail:$theemail <hr />";
$i++;
}
}
?>
</td>
<td valign="top">
<?php

// prints out at top of page, so that poeple know what they are looking at//
echo "People that found your site ok:<hr /> ";

/* declar some relevant variables */

$Host = ""; //location of mySQL on server
$User = ""; //my username
$Pass = ""; //my password
$Name = "phpforms"; //name of the database to be used
$Table = "information"; //name of the table within the database

mysql_connect ($Host, $User, $Pass, $Name, $Table) or die ("unable to connect to database");
@mysql_select_db("$Name") or die ("unable to select DB");
$sqlquery = "SELECT * FROM $Table WHERE opinion = 'is OK'";
$result = mysql_query($sqlquery);
$number = mysql_num_rows($result);

$i = 0;

if ($number < 1)
{
echo "<center>No results to display!</center>";
}
else
{
while ($number > $i)
{
$thename = mysql_result ($result, $i, "name");
$theemail = mysql_result ($result, $i, "email");
echo "Name: $thename E-Mail:$theemail <hr />";
$i++;
}
}
?>
</td>
<td valign="top" bgcolor="#CCCCCC">
<?php

// prints out at top of page, so that poeple know what they are looking at//
echo "People that liked your site:<hr /> ";

/* declar some relevant variables */

$Host = ""; //location of mySQL on server
$User = ""; //my username
$Pass = ""; //my password
$Name = "phpforms"; //name of the database to be used
$Table = "information"; //name of the table within the database

mysql_connect ($Host, $User, $Pass, $Name, $Table) or die ("unable to connect to database");
@mysql_select_db("$Name") or die ("unable to select DB");
$sqlquery = "SELECT * FROM $Table WHERE opinion = 'is great'";
$result = mysql_query($sqlquery);
$number = mysql_num_rows($result);

$i = 0;

if ($number < 1)
{
echo "<center>No results to display!</center>";
}
else
{
while ($number > $i)
{
$thename = mysql_result ($result, $i, "name");
$theemail = mysql_result ($result, $i, "email");
echo "Name: $thename E-Mail:$theemail <hr />";
$i++;
}
}
?>
</td>
</tr>
</table>

</body>
</html>
[/php]

This script will connect to the phpforms databse and look for any entries and check to see if they match with the terms listed in

SELECT * FROM $Table WHERE opinion = 'is great'

Now we need a form that will submit info into another form which will then submit it's info into the database!

<html>

<HEAD>

<title> form Handling with PHP </title>

</head>

<body bgcolor="#FFFFFF">

<center>

<form method=post action="submittedinfo.php"> <!-- Submit this info to the file called submittedinfo.php -->

<input type="hidden" name="id" value="NULL">

<table>

        <tr>

         <td colspan="2"><font SIZE="+0" face="verdana">

         Lets see if we cant get this form to work!

         </td>

        </tr>

        <tr>

         <td>

         </td>

        </tr>

        <tr>

         <td align="left"><font SIZE="+0" face="verdana">

         <b>Your name <br \>Your E-Mail Address</b>

         </td>

         <td>

         <input type="text" name="name" id="name"> <!-- creating the name/id that will be associated with $name -->

         <br />

         <input type="text" name="email" id="email"> <!-- creating the name/id that will be associated with $email -->

         </td>

        </tr>

        <tr>

         <td colspan="2"><center>

         <SELECT name="opinion" id="opinion"> <!-- creating the name/id that will be associated with $opinion -->

         <!-- The values set on these next lines will be used

                 to access the databse for querying, so remember 

                 remember these values, 'is great' 'is OK' 

                 'is horrible' -->

         <option value="is great">I like your site</option>

         <option value="is OK">Your Site is OK</option>

         <option value="is horrible">Your Site is horrible</option>

         </SELECT><p><input type="submit" value="Tell us!">

         </td>

        </tr>

</table>

</form>

</body>

</html>

All this is, is a basic HTML form that will submit info in a file called submitted.php, so now let's create that file.

[php]<?
$DBhost = "locationofmysql";//location of mySQL on server/site
$DBuser = "username";//User name for logging onto mySQL
$DBpass = "yourpass";//Password for logging onto mySQL
$DBName = "phpforms";//Name of the databse for logging into
$Table = "information";//Name of the Table to be used steps to create are included
$name = "$_POST[name]";//Name that the person gave on the form
$email = "$_POST[email]";//Email that person gave on the form
$opinion = "$_POST[opinion]";//Their opinion

mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database"); //connecting to the database using the variable set
@mysql_select_db("$DBName") or die("Unable to select database $DBName"); //at connection to the databse select DBNAME (phpforms) or tell that it couldnt connect
$sqlquery = "INSERT INTO $Table VALUES('$id','$name','$email','$opinion')"; //Telling the mySQL to insert the values from the form into the databse coresponding with the form
$results = mysql_query($sqlquery); //Query the results
mysql_close();
echo "
<html>
<head>
<title> PHP and MySQL </title>
</head>
<body>
<center>
<table border='0' width='500'>
<tr>";
echo " <td>

<center>
You Just Entered This Information Into the Database
</center>";

//display the information that user submitted in the previous form
echo " <blockquote>
<center>

Name : $name E-Mail : $email Opinion : $opinion

</center>
</blockquote>
</td>
</tr>
</table>
</center>
</body>
</html>";
?>
[/php]

This form will take the data from the previous form and add it to the database, and now with the databaseconnect file (listed first) you should be able to see what the users entered about your site!

I have included a zip file with all of the scripts that I used, and they have better formatting than the ones listed above.