insert into mysql with conditions using php

Question

I am trying to insert some data to the database in a for loop with list of people who will attend the meeting but I need to check their availability first and if the room that is holding the meeting is available too here is my code

<html>
<body>
<?php
//Try using Error Reporting On
error_reporting(E_ALL);
ini_set('diplay_errors', 'on');

$dbhost = "127.0.0.1";
$dbuser = "root";
$dbpass = "";
$dbname = "mss";
$connection = mysql_connect($dbhost, $dbuser, $dbpass, $dbname);
if (mysql_errno()) {
    die("Database Connection failed:" . mysql_error() . "(" . mysql_errno() . ")");
}
mysql_select_db('mss');
?>
<?php echo $id = (isset($_POST['id']) ? $_POST['id'] : "hello"); ?> <br>
<?php echo $title = (isset($_POST['title']) ? $_POST['title'] : "hello"); ?> <br>
<?php echo $employee = (isset($_POST['employee']) ? $_POST['employee'] : "hello"); ?> <br>
<?php
$participant = (isset($_POST['participant']) ? $_POST['participant'] : "hello");
$starttime = (isset($_POST['starttime']) ? $_POST['starttime'] : "hello");
$endtime = (isset($_POST['endtime']) ? $_POST['endtime'] : "hello");
$day = (isset($_POST['day']) ? $_POST['day'] : "hello");
$room = (isset($_POST['room']) ? $_POST['room'] : "hello");
$Lines = explode("\n", $participant);
foreach ($Lines as $line) 
{
    echo $line;
    $q1 = "select Availability from E_schedule where Employee_name='$line' and     StartTime='$starttime' and Day='$day'";
    $q2 = "SELECT Availability from room_schedule WHERE r_name = '$room' AND StartTime='$starttime' and Day='$day'";
    $result = mysql_query($q1, $connection);
    $result1 = mysql_query($q2, $connection);
    if ($result == FALSE)
    {
        die(mysql_error());
    }
    if ($result1 == FALSE)
    {
        die(mysql_error());
    }
        $info = mysql_fetch_array($result);
        if ($info['Availability'] == 1) {
            echo ("You can't make a meeting at that time, Please Select another Day or time");
            break;
    }
        $info1 = mysql_fetch_array($result1);
        if ($info1['Availability'] == 1) {
            echo ("You can't make a meeting at that Room, Please Select another Room");
            break;
    }
    else
    {
        $insert_meeting="insert into E_schedule (Employee_name, StartTime,     EndTime, Day, Availability, Activity_Name) values     ('$line', '$starttime ', '$endtime', '$day', '1', '$title')";
        $insert_result = mysql_query($insert_meeting, $connection );
        if($insert_result == FALSE) 
        {
            die(mysql_error()); 
        }
        $meeting="insert into meeting (Title, StartTime, EndTime, Day, Participant, Room) values ('$title', '$starttime ', '$endtime', '$day', '$participant','$room')";
        $meeting_result = mysql_query($meeting, $connection );
        if($meeting_result == FALSE) 
        {
            die(mysql_error()); 
        }
        $insert_room="insert into room_schedule (r_name,M_Title, StartTime, EndTime, Day, Availability) values ('$room','$title','$starttime ', '$endtime', '$day', '1')";
        $insert_result1 = mysql_query($insert_room, $connection );
        if($insert_result1 == FALSE) 
        {
            die(mysql_error()); 
        }
        echo ("The Meeting has been Created Successfully"); 
    }
}
?>
<br><?php echo $day = (isset($_POST['day']) ? $_POST['day'] : "hello"); ?> <br>
<?php echo $starttime = (isset($_POST['starttime']) ? $_POST['starttime'] : "hello"); ?><br>
<?php echo $endtime = (isset($_POST['endtime']) ? $_POST['endtime'] : "hello"); ?><br>
<?php echo $room = (isset($_POST['room']) ? $_POST['room'] : "hello"); ?> <br>
</body>
</html>

The problem is that when createing the meeting it will not insert all the participants it w enter only one participants thanks in advance

it's obvious you're just learning PHP, and writing code yourself is great at first to get a handle on the language, but also look into Frameworks, such as Zend, CodeIgniter, Laravel, etc as you get more comfortable with PHP. — Andrew Brown, Mar 20 '14 at 05:18

score 1 · Answer 1 · edited May 23 '17 at 12:28

I'll start with the PSA: You need to sanitize your inputs. The code that you've provided is terribly vulnerable to SQL injection.

As to your specific question: You are looping through the participants, but you change the availability of the room during each iteration of the loop with this line:

$insert_room="insert into room_schedule (r_name,M_Title, StartTime, EndTime, Day, Availability) values ('$room','$title','$starttime ', '$endtime', '$day', '1')";

So, after the first participant, the room will be considered "booked" and you won't be able to add more participants.

The simple fix? Move the chunks of code that check availability of the room ($q2 = "SELECT Availability ... and company) to outside of the for loop.

The better fix? I'd consider breaking up all the different components in that for loop into specific functions. One function to check if the participant is available. One to check if the room is available. One to add all the participants to the meeting. Etc. So your script will flow in this fashion:

Check if the room is available
  |
  |- Room is not available. Exit and tell user.
  `- Room is available. Now check if all participants are available.
       |
       |- One or more participants aren't available. Exit and tell user.
       `- All participants are available. Create the meeting
           |
           |- Failed to create meeting. Exit with error.
           `- Meeting created successfully with room.
               |
               `- Now you Loop through participants and add them to the meeting.

Breaking it up into functions will make it easier to debug, read and organize. If you want to really go down the rabbit hole, you should write a class to organize each meeting and the related properties and methods.

any help from any body still can't get the result – user3385409 Mar 20 '14 at 05:42 — user3385409, Mar 20 '14 at 05:42

insert into mysql with conditions using php

1 Answers1