SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Checking fields for values and adding dates

    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 Code:
    <?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. #2
    Non-Member
    Join Date
    Apr 2011
    Location
    no fixed address
    Posts
    851
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by trufflepig View Post
    ......So I guess there need to be 4 different INSERT statements in the code depending on the condition of the Date / Daynight fields.
    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).

    Code PHP:
    <?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. #3
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I'll give that a go...

  4. #4
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    PHP 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>"
    }

    Quote Originally Posted by webdev1958 View Post
    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).

    Code PHP:
    <?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.

  5. #5
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

    Quote Originally Posted by trufflepig View Post
    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:

    PHP 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. #6
    Non-Member
    Join Date
    Apr 2011
    Location
    no fixed address
    Posts
    851
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by trufflepig View Post
    ....... but the INSERT doesn't seem to be working. Not sure why though?
    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.

  7. #7
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Personally I'd set the tables up something like this;
    Code:
    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)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •