Help with form submission page [Archive]

Click to See Complete Forum and Search --> : Help with form submission page

Dopple

03-21-2005, 06:23 AM

I recently asked on the HTML forum about a way in which reviewers for a site might be able to log onto a site and enter the name of a band and type up a review for them and for it to appear. Last thread. (http://www.webdeveloper.com/forum/showthread.php?s=&postid=337167#post337167) Could anyone please point me in the right direction here? My server supports PHP and SQL. I'm just unsure as of where to start.

scragar

03-21-2005, 06:45 AM

start at the begining.
consider what you need to store and make you DB for it first.
then you want you HTML page(if theres a problem later with the php you can try various test to find it, if you did php first then you would have problems testing it)
then write your PHP part by part, testing as you go along(validity should come first, then security checking before finishing up whatever else you need.

Dopple

03-21-2005, 08:30 AM

Well I've nearly got the DB up. There's just a couple of points I'd like cleared up
I have the following fields in a table:
-artist
-name of song
-venue
-date of gig
-review
-reviewer
-date added
I have made the 2 date fields' types "date" and the others text but I'm not sure what to put for "Length/values", "Attributes", "null", "default" or "extra". I'm also not sure about primary or index fields. I'm assuming "artist" would be the primary field to search by.

scragar

03-21-2005, 08:35 AM

none of them should be your primary key, the artist can have more than 1 gig, and he/she could also do the same song twice.

I recomend adding an autoincriment bigInt as your primary key.

Length/values are for varchars(none new line characters), that's what you should have for artist, name of song, venue and reviewer as these will not conatain new lines, the legnth specifies how much you can enter. for a general rule names should permit a full 255, while songs around 50.

Dopple

03-21-2005, 08:49 AM

That's the DB set up now. I added a field called "review number" which is the primary key but I've also labled it as 'UNIQUE'. Is this correct? Is my next step to set up the HTML form which will send data to my DB? Thanks for the help so far! :D

scragar

03-21-2005, 08:55 AM

that's right.

I've got to go now, but I should be online tommorow and wednesday.

ffurnai

03-21-2005, 09:01 AM

depending on how you set up your search page you could do multiple search attributes that would build your search query to be as broad or specific as you want. For example, you could do pull downs that are populated by the db (everything in the artist column, everything in the album, etc) then do multiple pull down selections to refine your search (artist, album, date or just artist) then when you submit your query it could dynamically build your results page.

I'd do your date of entry as a `timestamp` that isn't entered specifically by the reviewer, but rather when the submission is made.

just ideas, I'm interested in seeing this when its done :)

Dopple

03-21-2005, 09:53 AM

i like the idea about a dynamic search. I was wondering if I could use Xforms (which I just discovered) to send the data to the SQL database. Is this possible? What would be the way in which I got the form and the SQL to work together?

ffurnai

03-21-2005, 10:06 AM

Hey Dopple,
Much like Scragar I've got to get to some other work. But, I'll check back to see how you're doing. I might be able to work up an example to show you, but it might be a while before I can get to it.

Ian

Dopple

03-21-2005, 10:32 AM

Thanks. Hopefully I will have worked it out for myself but I'll check back tomorrow if not. I'm just working on the Xform just now so hopefully I can get it fully operational by tonight. If anyone else has any tips, I would be eager to hear them.

ffurnai

03-21-2005, 01:12 PM

OK,
I actually went ahead and worked something up for the search part.

I made a db that has these columns

rev_num - int - auto-increment - unique
artist - varchar(50)
album - varchar(50)
review - varchar(250)
reviewer - varchar(50)
dateadded - timestamp(14)

Here is a simple search form that builds dynamically from the artist and album columns:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.wx.org/TR/2000/REC-xhtml1-20000126/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">

<head>

<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<title>Search Review Archive</title>
<LINK REL=STYLESHEET TYPE="text/css" HREF="style.css">

</head>

<body bgcolor=#b6b6b6 leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">

<?php
$db = @mysql_connect("localhost" , "user" , "password")
or die("Retard, no connection");
@mysql_select_db("dbname")
or die("Retard, no database");
?>



<form action="results.php" method="post">


<table cellpadding=5 bgcolor=#cccccc width="192">


<tr>
<td colspan=2>
<p><b>Artist</b>
<select name="artist">
<option value=""> </option>



<?php
$sql = "SELECT DISTINCT `artist` FROM `musicreview`";
$result = mysql_query(" $sql ");

while ($row = mysql_fetch_array($result)) {
echo('<option value="' . $row['artist'] . '">' . $row['artist'] . '</option>');
}
?>


</select></p>



<p><b>Album</b>
<select name="album">
<option value=""> </option>



<?php
$sql = "SELECT DISTINCT `album` FROM `musicreview`";
$result = mysql_query(" $sql ");

while ($row = mysql_fetch_array($result)) {
echo('<option value="' . $row['album'] . '">' . $row['album'] . '</option>');
}
?>


</select></p>

</td>
</tr>


<tr><td width=92> </td>
<td>
<input type="submit" name="submit" value="Search" />
</td>
</tr>

</table>

</form>

</body>
</html>

and the results page named "results.php"
<html>
<head>
<title>Review Results</title>
<LINK REL=STYLESHEET TYPE="text/css" HREF="style.css">
</head>

<body bgcolor=#ffffff>

<?php
$db = @mysql_connect("localhost" , "user" , "password")
or die("Retard, no connection");
@mysql_select_db("dbname")
or die("Retard, no database");

$where = array();
$whereclause = "";

if (!empty($_POST['artist']) ) {
$artist = $_POST['artist'];
$where[] = "(artist = '$artist')";
}

if (!empty($_POST['album']) ) {
$album = $_POST['album'];
$where[] = "(album = '$album')";
}

if (count($where) > 0) $whereclause = " WHERE " . join (' AND ', $where);

$sql = "SELECT * from musicreview " . $whereclause . "ORDER BY `rev_num`";

$result = mysql_query(" $sql ");

echo ('<table border="1">');

while ($row = mysql_fetch_array($result)) {
echo('
<tr>
<td>' . $row['artist'] . '</td>
<td>' . $row['album'] . '</td>
<td>' . $row['reviewer'] . '</td>
<td>' . $row['review'] . '</td>

</tr>

');
}

echo ("</table>");
?>

</body>
</html>

this is quick and not very pretty looking, but might get you going in a direction. :)

Ian

ffurnai

03-21-2005, 01:15 PM

Check it out here (http://ten28design.com/PHPtest/musicreview/reviewsearch.php).

I think that this works pretty well. The above code should get you going.

Ian

ffurnai

03-21-2005, 03:55 PM

OK and now for the form to add the review

It can be seen in action here (http://ten28design.com/PHPtest/musicreview/addto.php)

I used an include on this one for the db connection its called "db.php"
<?
// Create database connection and select database
mysql_select_db('dbname', mysql_pconnect('localhost','user','password')) or die (mysql_error());
?>

and here is the form called addto.php
<html>

<head>
<title>Add Review</title>
</head>

<body bgcolor="#cccccc">

<?php
if (isset($_POST['submit']))
{
require_once("db.php");

$artist = $_POST['artist'];
$album = $_POST['album'];
$reviewer = $_POST['reviewer'];
$review = $_POST['review'];

$result = mysql_query("INSERT INTO musicreview SET artist='$artist', album='$album', reviewer='$reviewer', review='$review'");

if ($result) echo "<p>Review Entered.</p>";
else echo "<p>Something's wrong, retard.</p>";

mysql_close();
}
?>

<form name="addfield" action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
Artist:<br />
<input type="text" name="artist" /><br />
Album:<br />
<input type="text" name="album" /><br />
Reviewer:<br />
<input type="text" name="reviewer" /><br />
Review:<br />

<textarea rows="10" cols="30" name="review"> </textarea><br />

<input type="submit" name="submit" value="Add Review" /><br />
</form>

</body>
</html>

Like I said, I'm no guru but this might help.

Dopple

03-22-2005, 03:05 AM

Wow. :D Thanks for your help. I'll use that to start with and then twist and shift it around to see what I can come up with. If I have any more problems I'll surely be back posting here again.

Dopple

03-22-2005, 03:25 AM

Where does the db.php include go in the code? would that be in the head?

Dopple

03-22-2005, 05:53 AM

Scrap that last silly question. I have all of those copied into my server (I'm pretty sure I've changed what's needed changed) I'm just trying to get it all working as one. I'm having trouble getting the form to send data to the database though. It's coming up with page not found. Should I be linking it to a page saying "thanks for your submission" or something? The form page (http://freeownhost.com/members/filemanager/?action=view&file=addto.php)

ffurnai

03-22-2005, 06:03 AM

your link asks for a login. . .:)

Dopple

03-22-2005, 06:21 AM

I've just tried to move the .php files into the main folder so you could get in and they've just dissappeared. I'll have a hunt for them and then if I can't find them I'll just have to make them again. :rolleyes:

Dopple

03-22-2005, 06:42 AM

If the folder is /www/mmcdermid.freeownhost.com/ that I'm keeping the php files in shouldn't the URL be http://www.mmcdermid.freeownhost.com/addto.php? This appears not to work.

ffurnai

03-22-2005, 07:43 AM

Yeah, it shouldn't really matter where the file is as long as its in the same directory as the db.php.

Are you sure that PHP is working on your host? Do the other files (reviewsearch.php and results.php) work?

Dopple

03-22-2005, 08:43 AM

The php files work (in the lesser sense of the word) on the server but I can't seem to figure out how to get it so that I can get to them from outside the server. They are all in the same folder too. I think I'll just paste them here. Although not ideal I can't for the life of me figure out this web host.

ffurnai

03-22-2005, 08:48 AM

well. . . you said it was a free host right?

I guess you get what you pay for ;)

The php files work (in the lesser sense of the word) on the server but I can't seem to figure out how to get it so that I can get to them from outside the server.
I'm a little confused by this. .

Can you tell I'm sitting here waiting to see how it goes? I'm replying in seconds after you post. :D

Dopple

03-22-2005, 09:01 AM

You're not wrong there mate! We're wanting to makesure everything pans out before going to a host that we have to pay for. This is what is in results.php. I'm pretty sure I haven't changed anything that I shouldn't have.
$sql = "SELECT * from musicreview " . $whereclause . "ORDER BY `rev_num`";
Should I have a file or something called musicreview?

<html>
<head>
<title>Review Results</title>
<LINK REL=STYLESHEET TYPE="text/css" HREF="style.css">
</head>

<body bgcolor=#ffffff>

<?php
$db = @mysql_connect("freeownhost.com" , "mmcdermid" , "******")
or die("No connection");
@mysql_select_db("dbname")
or die("No database");

$where = array();
$whereclause = "";

if (!empty($_POST['artist']) ) {
$artist = $_POST['artist'];
$where[] = "(artist = '$artist')";
}

if (!empty($_POST['song']) ) {
$song = $_POST['song'];
$where[] = "(song = '$song')";
}

if (count($where) > 0) $whereclause = " WHERE " . join (' AND ', $where);

$sql = "SELECT * from musicreview " . $whereclause . "ORDER BY `rev_num`";

$result = mysql_query(" $sql ");

echo ('<table border="1">');

while ($row = mysql_fetch_array($result)) {
echo('
<tr>
<td>' . $row['artist'] . '</td>
<td>' . $row['song'] . '</td>
<td>' . $row['reviewer'] . '</td>
<td>' . $row['review'] . '</td>

</tr>

');
}

echo ("</table>");
?>

</body>
</html>

Dopple

03-22-2005, 09:07 AM

I see what I've done! I've changed "localhost" when I shouldn't have. I'm picking it up slowley. Thanks for your patience.:)

ffurnai

03-22-2005, 09:10 AM

OHHHH, yes a slight oversight on my part. . .

'musicreview' is the name of the table in my db.

sorry, I didn't make that clear guess I should have put "yourtablename" in every instance like the "user" and "password" items.

ffurnai

03-22-2005, 09:13 AM

Yeah, localhost means that the SQL server is running on the same machine that is serving up your pages. You would only change that if you were accessing a SQL db that was hosted somewhere different from your host server.

Thanks for your patience.

no problem, I'm "paying it forward" you might say. These forums have helped me out on numerous occasions.

Dopple

03-22-2005, 09:22 AM

Hmm. It's still seems not to want to work. Would the name of the database be 'mmcdermid_db@freeownhost.com' or just 'mmcdermid_db'?

ffurnai

03-22-2005, 09:40 AM

Hmmmm, not really sure what your db name would be. Should tell you in your admin. I've only used PHPmyadmin for managing db's. Like I said, I'm no guru and only have experience using my current host/provider (ipowerweb.com). With them the db name is "username_dbname" typically. But I'm not sure if that's an across the board standard way to name a db.

Maybe someone else with a little more experience can chime in on that. . . (Moderator?)

I will put in a plug for my webhost here because you said you were looking for something cheap. Ipowerweb goes for $7.95/mo for a year commitment that includes 2GB of space a bazillion e-mail accounts and all the extra goodies you can think of. (if you go with them tell them I recommended it to you!:) )

Dopple

03-22-2005, 10:52 AM

I think it is just host_db. Oh well, it's time to shoot off now anyway. Thanks for your help. I'll probably post back here tomorrow if I make any headway.
Cheers.
Graham

Dopple

03-23-2005, 09:01 AM

Hallelujah (http://mmcdermid.freeownhost.com/addto.php)
That's the addto.php
And the rest are here.

db.php (http://mmcdermid.freeownhost.com/db.php)
results.php (http://mmcdermid.freeownhost.com/results.php)
search.php (http://mmcdermid.freeownhost.com/search.php)
I'm putting not being able to work out the domain yesterday down to over working! (poorest excuse ever!)

ffurnai

03-23-2005, 09:18 AM

Hi Graham,

Hate to be the bearer of bad news. . .
I tested out your links. The form loads right but I think that the db.php is still not configured right. I'm getting no connection errors on all your pages.

Ian

Dopple

03-23-2005, 09:37 AM

Bah!!!
I noticed a couple of mistakes but changing them made not one iota of difference. I'll email the server help and see if they can shed any light on what I'm doing wrong. I'll post back when I hear something.