Checking fields for values and adding dates

PHP
1

I’m setting up a system using PHP/mySQL where the end user picks a date from a calendar (just a typical Javascript date picker) and also selects “Day” or “Night” using a radio button input, then submits the form with those two values.

Up to five dates and day/night values can be stored per user in total, i.e I have set up these fields in the mySQL users table: Date1, DayNight1, Date2, DayNight2, Date3, DayNight3, Date4, DayNight4, Date5, DayNight5.

When the form is submitted, I want it to populate Date1 and DayNight1, but ONLY if there’s nothing already in those fields. If there is, then it should check Date2 and DayNight2, and so on until all fields have been checked. If all fields are full, it will echo a message to say so, and direct the user to a separate page / form where they can delete values as appropriate.

If Date1 and DayNight1 are empty, it should just run the INSERT statement as normal. If they contain values, but Date2 and DayNight2 don’t, then run the INSERT statement but with values being entered into the fields for Date2 and DayNight2 instead. So I guess there need to be 4 different INSERT statements in the code depending on the condition of the Date / Daynight fields.

Getting a bit confused with how to put all this into the code though. I’ve got as far as this but I know it won’t work as is (I need to find a way of adding in the other INSERT statements but can’t see how)


<?php
include ('inc/dbconnect.php');
$user =@$_POST['_user'];
$dateavailable =@$_POST['_dateavailable'];
$dayornight =@$_POST['_dayornight'];

if($_POST['addavailabledates'])
{
	
if (!empty($row['DateAvailable1']))
{
	if (!empty($row['DateAvailable2']))
	{
		if (!empty($row['DateAvailable3']))
		{
			if (!empty($row['DateAvailable4']))
			{
				if (!empty($row['DateAvailable5']))
				{
					echo '<p>All your date slots are currently full. <a href="deleteavailabledates.php">Click here</a> to remove some of your date slots.</p>';
				}
				else {
					// Build SQL Query  
					$query = "INSERT INTO users (DateAvailable5, DayOrNight5) VALUES ('$dateavailable', '$dayornight') WHERE Username LIKE '$user'"; // specify the table and field names for the SQL query
					if($result = mysql_query($query)) 
					{ 
					header('location: addconfirmed.php');
					exit;
					} 
					else 
					{ 
					echo "ERROR: ".mysql_error(); 
					}				
				}

			}
		}
	}
}



}
?>

I’m also aware that I need to run an initial query somehow to output the DateAvailable fields for that specific user, to check them- something along the lines of a “SELECT DateAvailable1 (etc) FROM users WHERE Username LIKE ‘$user1’” though I’m not sure what exact format that should take.

Any ideas?.. as this seems quite complex at the moment but probably doesn’t need to be.

By the way, the calendar / date picker doesn’t allow multiple dates to be added at the same time (and even if it did, obviously this could make it messy when inputting values from the input in the form, into the db table date fields)

2

Not necessarily.

You can use a single update statement but in a loop looping through the user’s DateAvailable1…DateAvailable5.

Something like this (which doesn’t include any error checking/handling).


<?php

$dates = $_POST['txtDateAvail'];   //array containing user entered dates available
$dayNight = $_POST['txtDayNight']; //array containing user entered day/night values

$query = 'select * from users where Username = "'.$user.'"';
$rs = mysql_query($query, $conn) or die('Unable to connect to database');
$row = mysql_fetch_assoc($rs);
mysql_free_result($rs);

$dateAvailable = Array();
$dateAvailable[] = $row['DateAvailable1'];
$dateAvailable[] = $row['DateAvailable2'];
$dateAvailable[] = $row['DateAvailable3'];
$dateAvailable[] = $row['DateAvailable4'];
$dateAvailable[] = $row['DateAvailable5'];

//now loop through the user's available dates in the db and update where required
for($i=0; $i < count($dateAvailable); $i++){
    if(empty($dateAvailable[$i]) || $dateAvailable[$i] == ''){
        $query = 'update users set DateAvailable'.($i+1).' = "'.$dates[$i].'", DayOrNight'.($i+1).' = "'.$dayNight[$i].'" where Username = "'.$user.'"';
        mysql_query($query, $conn);
    }
}

?>

I haven’t tested the above code, but you should be able to see the overall logic I am using…

Another approach could be to build the update query as you loop through $dateAvailable to update the required columns in the row and then just run the update query once after the loop has finished.

3

Personally I’d set the tables up something like this;


users
====

id | 23
name | "Mickey love"


bookings
======

id_ref | 23
night | 1
booking | "2011-11-05"


id_ref | 23
night | 0
booking | "2011-11-15"

// add a unique index on all 3 fields that way 
// mickey is not able to make a duplicate booking
// and you get a clear warning from the database

How many bookings has Mickey made?

select count(id_ref) from bookings where id_ref=23;

If that is less than five, then show the form to add another.

You can also simply go on and find answers to questions like this far simpler:

Which is your busiest day?
Which is most booked, nights or days?
Which is your busiest week (or month or year or any time period)

4

Thanks, I’ll give that a go…

5

Hi webdev, well I tried that, although it claims to be ok (no error flagged up and it forwards to the confirmation page) the values aren’t being added into the db. I’ve tested the flow from the frontend form with some echo statements and the variables all look ok (contain values) but the INSERT doesn’t seem to be working. Not sure why though?

This is the code:


include ('inc/dbconnect.php');

if($_POST['localsaddavailabledates'])
{
	
$user =@$_POST['_user'];
$dates =@$_POST['_dateavailable'];
$dayornight =@$_POST['_dayornight'];

echo $user . '<br>';
echo $dates . '<br>';
echo $dayornight . '<br>';

$query = 'select * from users where Username = "'.$user.'"';
//echo $query;
$rs = mysql_query($query) or die('Unable to connect to database');
$row = mysql_fetch_assoc($rs);
mysql_free_result($rs);
 
$dateAvailable = Array();
$dateAvailable[] = $row['DateAvailable1'];
$dateAvailable[] = $row['DateAvailable2'];
$dateAvailable[] = $row['DateAvailable3'];
$dateAvailable[] = $row['DateAvailable4'];
$dateAvailable[] = $row['DateAvailable5'];
 
//now loop through the user's available dates in the db and update where required
for($i=0; $i < count($dateAvailable); $i++){
    if(empty($dateAvailable[$i]) || $dateAvailable[$i] == ''){
        $query = 'UPDATE users SET DateAvailable'.($i+1).' = "'.$dates[$i].'", DayOrNight'.($i+1).' = "'.$dayornight[$i].'" WHERE Username = "'.$user.'"';
        mysql_query($query, $conn);
    }
}

if($result = mysql_query($query)) 
{ 
		header('location: admin_confirmed.php');
		exit;
		}
else 
{ 
echo "<p>Sorry, there was a problem adding your data. Make sure that you entered valid values.</p>"; 
}
}
6

Also, in the db table, the DATE fields autoset themselves as 0000-00-00, is that causing a problem? Just wondering if the fact they already have values (albeit zero values) might be causing a problem?

7

The “demo” code I posted has an UPDATE not INSERT statement.

Are you updating existing rows in the database or inserting new rows into the database?

With the demo code, if $user doesn’t exist in the table users, then the update query will not update anything (obviously) and so you will need to use INSERT instead. If $user does exist in users (and assuming it is unique in the table) then you need to use UPDATE instead of INSERT.