Stuck on Table layout

any thoughts would be appreciated

[mcjohnson]

I am trying to create an online "sign up" page for the annual church pumpkin patch. People should be able to sign up for any date between Oct 1 and Oct 31, and choose between 10-12pm, 12-3pm and 3-6pm.

Then I would like to serve up the results in table format, so that as people sign up, they can see who else has signed up and for what time slot

So, initially, I set up one table called "sign up" with 93 different fields...each representing a different time slot (1-1 was Oct 1, 10-12, and so on). But that seemed a little cumbersome...

At first blush, what would you recommend from a table/field set up? I'd be very grateful for some input. Once I get the tabels set up, I am quite sure I'll be back for more input on the language.

Best Regards,

Pat

[PeteM]

First reaction would be to create a 2 dimensional array where the first dimension is the date and the second dimension is the time of day.

You could then ouput this into a 7 column table representing the days of the week.

[mcjohnson]

PeteM,

because I am enough of a newbie, I am a little confused by the term "two dimensional array". Ultimately, the only information being inputted by the user is a name, which will then be flooded into the date/time field of the database, and that name will then be outputted to the specific sign up times.

I hope this helps to clarify.

Many thanks,

Pat

[mcibor]

I think better option would be a 4 field table:

CREATE TABLE pumpkin (id INT NOT NULL auto_increment, day DATE, time INT, user VARCHAR(50), PRIMARY KEY(id));

form to put in values:

<form action="process.php" method="POST">
<input type="text" name="user"><br>
<select name="day"><option value="1">1 Oct</option>...etc: 2 for 2 Oct, 30 for 30 Oct </select>
<select name="time"><option value="1">10-12pm</option><option value="2">12-3pm</...<... value="3">3-6pm</option></select>
<input type="submit" name="action" value="Submit>

file to process the query: process.php (where the <form points)

<?php
if(($_POST["action"]!= "Submit") &#166;&#166; (empty($_POST["user"])) &#166;&#166; (!(int)$_POST["day") &#166;&#166; (!(int)$_POST["time")) die("Try again. Click <a href=\"index.html\">here</a>");

[edited by: mcibor at 2:28 pm (utc) on Aug. 18, 2005]

[mcibor]

...

$sql = "INSERT INTO pumpkin VALUES('', '2005-10-".(int)$_POST["day"]."', '".(int)$_POST["time"]."', '".mysql_real_escape_string($_POST["user"])."')";
//connect to db and run this query

To get the list sorted by time and date
$sql = "SELECT user, time, day FROM pumpkin ORDER BY day, time";
That should do. If you wish any other questions, just ask!
Michal Cibor

[zulu_dude]

Personally, what I would do is just set up a table with 4 fields... id (auto incrementing), date, time_slot, name.

Say someone signed up for the second slot on October 10th, their entry would look like so:
2 (or whatever the id is), 2005-10-10, 2, Joe Soap.

Then, when you're signing people up, you can just check to see that there isn't already an entry for that time slot on that date before putting their signup into the table.

Hope this helps...

[zulu_dude]

Ok Michal, you were too quick for me! Great minds must think alike :-)

[mcjohnson]

awesome - thank you guys, VERY much. I shall give those recommendations a try.

Pat

[mcibor]

93 fields was a bit too many!
Great mind cannot think the same - they wouldn't be great minds :)
It's just that 4 is the most optimal solution.

The cleanest would be 3 fields and 2 tables: 1: id, datetime, id_user
2: id, user_name, other_info_on_user

However in this case (only 3 times) I prefer to have time separatly (it's easier to compare, order etc)

Best regards
Michal Cibor

PS. If great would think as one, then they wouldn't think of anything new - therefore wouldn't be great minds :)

[mcjohnson]

Mcibor,

I have set up the table and am now trying to run the "insert" query, but I am getting a Parse error: The language I am using in process.php is as follows:

<?php
if(($_POST["action"]!= "Submit") &#166;&#166; (empty($_POST["user"])) &#166;&#166; (!(int)$_POST["day") &#166;&#166; (!(int)$_POST["time"))
die("Try again. Click <a href=\"index.htm\">here</a>");

$sql = "INSERT INTO pumpkin VALUES('', '2005-10-".(int)$_POST["day"]."', '".(int)$_POST "time"]."', '".mysql_real_escape_string($_POST["user"])."')";
//connect to db and run this query
?>

The error message is that there is an uxpected ";" at line 33, which on my page begins with die("try again... and ends with here</a>");

If I remove the ; at the end of the line, it just gives me a new error for line 34 that there is an unexpected T_variable.

Am I missing something obvious? I know that a comma, semi-colon, etc that is not in the right spot can throw everything off. Any thoughts would be greatly appreciated!

Thanks!

Patr

[zulu_dude]

Hi Pat,

Try this:

$user = mysql_real_escape_string($_POST['user']);
$day = $_POST['day'];
$time = $_POST['time'];

if((isset($_POST['submit'])) && (!empty($user)) && (is_numeric($day) && (is_numeric($time)))
{
$date = "2005-10-".$day;
$sql = "INSERT INTO pumpkin VALUES('','$date','$time','$user')";
}
else
{
die("Try again. Click <a href=\"index.htm\">here</a>");
}

This is doing almost exactly the same as Michal's script, just in a slightly different way. I thought this might just be a little simpler for someone who is just learning php. This is a very basic way of doing it, as you don't actually check that the sql result was successful. But it should work (I think!).

[mcjohnson]

Hi Zulu_Dude

Thank you for your reply...I really appreciate it.

I am getting an error message with that script that says "unexpected { on line..." which is the first { in the script. If I remove it, it leads to a T-variable error, which, if I add a ";" to the previous line, causes an "unexpected ;" error.

Do you know what I might be doing to cause the "Unexpected {" error? I copied and pasted the language right between the <?php and?> tags.

Thank you! I appreciate your mentoring!

Pat

[zulu_dude]

Which script is is that you're having a problem with? The one that Michal posted or the one that I posted?

From what I can gather, Michal's script should have { and } around the line that has 'die' in it, as well as an 'else' statement for the rest.

Like so:

<?php
if(($_POST["action"]!= "Submit") ¦¦ (empty($_POST["user"])) ¦¦ (!(int)$_POST["day") ¦¦ (!(int)$_POST["time"))
{
die("Try again. Click <a href=\"index.htm\">here</a>");
}
else
{
$sql = "INSERT INTO pumpkin VALUES('', '2005-10-".(int)$_POST["day"]."', '".(int)$_POST "time"]."', '".mysql_real_escape_string($_POST["user"])."')";
//connect to db and run this query
}
?>

Note that the && (and) have changed to ¦¦ (or). I think that this is the better way to do it, but I stand to be corrected.

If it was my script that you had a problem with, I can see why... I left out a bracket, which would cause the error that you encountered. Fixed version:

$user = mysql_real_escape_string($_POST['user']);
$day = $_POST['day'];
$time = $_POST['time'];
if((isset($_POST['submit'])) && (!empty($user)) && (is_numeric($day)) && (is_numeric($time)))
{
$date = "2005-10-".$day;
$sql = "INSERT INTO pumpkin VALUES('','$date','$time','$user')";
}
else
{
die("Try again. Click <a href=\"index.htm\">here</a>");
}

[zulu_dude]

And I also just noticed that these scripts don't actually insert the data into the database. They merely construct the sql query. I presume that you've already connected to the database?

I've also changed the sql statement slightly, spotted another possible problem with my previous post. Here is a more verbose version (i.e. copy and paste) of what you'd need to use, here it is (I'm at work and bored so have plenty of time to respond at the moment :-)...

$user = mysql_real_escape_string($_POST['user']);
$day = $_POST['day'];
$time = $_POST['time'];
if((isset($_POST['submit'])) && (!empty($user)) && (is_numeric($day)) && (is_numeric($time)))
{
$date = "2005-10-".$day;
$sql = "INSERT INTO pumpkin (`day`,`time`,`user`) VALUES ('$date','$time','$user')";
$result = mysql_query($sql);
if ($result)
{ echo "Operation successful, thank you."; }
else
{ echo "There was an error. Please try again."; }
}
else
{
die("Try again. Click <a href=\"index.htm\">here</a>");
}

[mcjohnson]

We have lift off!

You rock, my friend. Thanks. I suspect I shall be back to ask more questions on the output end.

Have an awesome day!

Pat

[zulu_dude]

No worries mate! I'd be happy to help out if you have any more questions, enjoy your weekend,

The White Zulu