PHP - Duplicating Rows

Hi,

Was just wondering if I could get help with this programming problem, I am programming a diary system that people can use to book appointments within time slots on a current day. I have the below code reading from three database tables (one for appointment details, one for the profile of whom booked the appointment and lastly a LEFT JOIN table of times from 09:00:00 to 17:30:00). The functionality of this diary of time slots is where there is a booked slot, slip the appointment attendee into that slot so the avaible link is not seen so no one can book over the appointment.

This is where the problem comes into play, if there is an appointment expanding lets say 45 minutes which means other time slots will be taken, it just echos out the same appointment details so then I am getting it duplicating. for example down below;

09:00:00 - Booked Appointment (name) - Duration: 30 Minutes
09:15:00 - Booked Appointment (name) - Duration: 30 Minutes
09:30:00 - Booked Appointment (name) - Duration: 30 Minutes

Now what I would like the solution to is if there is a way to maybe stop the duplication by showing the first row of the start time of the appointment and then change the the next rows to unavailable? Or even collapsing the time rows so if an appointment is booked at 09:00:00 for 30 mnutes, then the next available time slot would be 09:45:00 so the record would not duplicate.

Here is a screenshot to help you a bit better - http://img185.imageshack.us/img185/7981/72314500.png

Here is my source code, would appreciate any suggestions on how I would do this!


<?php
include('connection.php'); 

//Query
$comment = "SELECT *
FROM timeslots
LEFT OUTER JOIN
  (SELECT time,endtime,status,firstname,secondname,duration
    FROM appointments
    INNER JOIN profile
    ON profile.id=appointments.id
    WHERE appointments.DATE = '" .$_GET['date']."') AS a
ON a.time <= timeslots.timeslot
AND a.endtime >= timeslots.timeslot";

$commentresult = mysql_query($comment);


// If commentresult = false then query return fail
if ($commentresult == false)
{
die("Your Query isn't working correctly! :-( ");

}

// If commentresult = true then echo below information with results
if (mysql_num_rows($commentresult) >= 1)
{ // Start Rows

 while($cr = mysql_fetch_array($commentresult)) 
 { // Start While Loop
{ // Process While Loop

$_GET['time'] = $cr['timeslot'];
$status = $cr['status'];  

if($status=="") { // Start IF Status

// echo Available
echo "<table border='0' width='800'>
<tr><td width='35'><div class='content'>".$cr['timeslot']."</div></td>
<td width='100'><div class='content'><a href='book_slot.php?date=".$_GET['date']."&id=".$_GET['id']."&time=".$_GET['time']."'>Available</a></div></td>
<td width='100'><div class='content'></div></td>
<td width='120'><div class='content'></div></td>
</tr></table>";

} // End IF Status

else { // Start ELSE status

// echo Appointment within Time Slot
echo "<table border='0' width='800'>
<tr><td width='35'><div class='content'>".$cr['timeslot']."</div></td>
<td width='100'><div class='content'>".$cr['status']."</div></td>
<td width='120'><div class='content'>".$cr['firstname']." ".$cr['secondname']."</div></td>
<td width='120'><div class='content'>".$cr['duration']." Minutes</div></td>
</tr></table>";
} // End ELSE Status


} // End Process While Loop
} // End Loop
} // End Row

// Else statement
else 
{ 
    echo "<img src='info.png'> There are no appointments for this day!"; 
}
// Close database connection
mysql_close($dbconnection);
/////////////////////////////

?>

Eventually I will have around 5 or 6 queries that will be replicated in several different pages, albeit with a slight difference in each (generally just a change in the WHERE part).
I have been thinking about placing these common queries in a separate file and calling that file with an 'include' on the pages the query is needed.

My two questions are....

Would this be more beneficial than place the query in the page itself and would there be any downside (as in loading times) if I placed all of these queries in one external file (ie. would this cause all the queries to load each time.

Thanks in advance for any advice.
Steve

I need to stop things from duplicating the insert of products into a table on refresh.  Im stumped!

This is the insert page that forwards to the display page.

Code: [Select]
<?php

session_start();

$UserID = session_id();

$SKU = $_POST['SKU'];
$QTY = $_POST['QTY'];

include("database.php");

mysql_select_db("brandysbeanies", $con);

$result = mysql_query("SELECT * FROM Products WHERE SKU='$SKU'");

while($row = mysql_fetch_array($result))
{

$Name = $row[1];
$Image1 = $row[4];
$Image2 = $row[17];
$Image3 = $row[18];
$Image4 = $row[19];
$Description = $row[3];
$Cost = $row[5];
$Price = $row[6];
$Ship = $row[9];
$Option1 = $row[10];
$Option2 = $row[11];
$Option3 = $row[12];
$Option4 = $row[13];
$Option5 = $row[14];

}

$time_1 = strtotime("now");
                
$today_date = date("ymd",$time_1);
$today_time = date("his",$time_1);

$Total = $Price * $QTY;
$Shipping = $Ship * $QTY;
$ID = "$today_date$today_time";

include("database.php");

mysql_select_db("brandysbeanies", $con);

$sql="INSERT INTO productorders (ClientID,OrderID,Productname,Productdescription,Cost,Price,Quantity,Shipping,Total,Photo)
              VALUES ('$UserID','$ID','$Name','$Description','$Cost','$Price','$QTY','$Shipping','$Total','$Image1')";
  
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }

include("cart.php");

?>

This is the display page.

Code: [Select]
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
  <title></title>
  <meta http-equiv="generator" content="CoffeeCup HTML Editor (www.coffeecup.com)" />
  <meta name="created" content="Mon, 06 Sep 2010 07:35:27 GMT" />
  <meta http-equiv="content-type" content="text/html;charset=utf-8" />
  <meta name="description" content="" />
  <meta name="keywords" content="" />
  <meta http-equiv="Page-Enter" content="revealtrans(duration=2,transition=22)">
 
  <style type="text/css">
    a:link {text-decoration: none;}
    a:visited {text-decoration: none;}
  </style>
 
<style type="text/css">
BODY {
SCROLLBAR-FACE-COLOR: #0E98E0;
SCROLLBAR-HIGHLIGHT-COLOR: #000000;
SCROLLBAR-SHADOW-COLOR: #000000;
SCROLLBAR-3DLIGHT-COLOR: #000000;
SCROLLBAR-ARROW-COLOR: #000000;
SCROLLBAR-TRACK-COLOR: #000000;
SCROLLBAR-DARKSHADOW-COLOR: #000000;
background-image:url('Images/background.gif');
}
</style>
 
<style type="text/css">
div.Object1 { position:absolute; top:0%; left:0%; z-index:2; width:100%; height:20%;}
div.Object2 { position:absolute; top:20%; left:0%; z-index:2; width:100%; height:80%;}
</style>

</head>
<body>

<div class="Object1">
<table border="0"  width="100%" cellpadding="5" cellspacing="5"><tr><td>
<font size="7" face="AR CHRISTY"><b><u>YOUR SHOPPING CART</u></b></font>
</td></tr></table>
</div>

<div class="Object2">
 
<table border='1'  width='100%'>
<tr><!-- Row 1 -->
   <td colspan="2" width='70%' align='left' valign='top'><font size="4" face="AR CHRISTY"><b>PRODUCT</b></font></td><!-- Col 2 -->
   <td width='15%' align='center' valign='top'><font size="4" face="AR CHRISTY"><b>QUANTITY</b></font></td><!-- Col 3 -->
   <td width='15%' align='center' valign='top'><font size="4" face="AR CHRISTY"><b>PRICE</b></font></td><!-- Col 4 -->
</tr>
 
<?PHP

$UserID = session_id();

include("database.php");

mysql_select_db("brandysbeanies", $con);

$result = mysql_query("SELECT * FROM productorders WHERE ClientID='$UserID'");

while($row = mysql_fetch_array($result))
{

echo"
  <tr><!-- Row 2 -->
     <td width='20%' align='center' valign='top'><a href='Products3.php?SKU=$row[0]' style='color:#000000;'><img src='$row[10]' width=60% height=60% alt='' border='1'></a></td><!-- Col 2 -->
     <td width='50%' align='left' valign='top'><font size='3' face='Comic Sans MS'><b>$row[3]</b><br>$row[4]</font></td><!-- Col 2 -->
     <td width='15%' align='center' valign='top'><font size='3' face='Comic Sans MS'>$row[7]</font></td><!-- Col 3 -->
     <td width='15%' align='center' valign='top'><font size='3' face='Comic Sans MS'>$row[9]</font></td><!-- Col 4 -->
  </tr>";
 
}
 
echo"
  <tr><!-- Row 2 -->
     <td width='85%' colspan=3 align='right' valign='top'><font size='4' face='AR CHRISTY'><b>SHIPPING</b></font></td><!-- Col 3 -->
     <td width='15%' valign='top'></td><!-- Col 4 -->
  </tr>";
 
echo"
  <tr><!-- Row 2 -->
     <td width='85%' colspan=3 align='right' valign='top'><font size='4' face='AR CHRISTY'><b>TOTAL</b></font></td><!-- Col 3 -->
     <td width='15%' valign='top'></td><!-- Col 4 -->
  </tr>";
?>

</table>
</div>

</body>
</html>

Hi All,

I have an insert/update that i am using in several places across my site, it works fine everywhere apart from on one page.

I am clearly missing something but cant for the life of me work it out.

My php:

if ($_SERVER['REQUEST_METHOD']=='POST') {
    $jobId = $_SESSION['current_job_id'];
    $qty = $_POST['drinkItemQty'];
    // prepare insert query
    $stmt = $conn->prepare("INSERT INTO ssm_drink_order (drink_qty, job_id, drink_id) 
                            VALUES (?,?,?) 
                            ON DUPLICATE KEY UPDATE 
                            drink_qty = VALUES(drink_qty)"
                        ); 
    
    
    foreach ($_POST['drinkItemId'] as $k => $diid) {

        if ($qty[$k] > 0) {

            $stmt->bind_param("iii", $qty[$k], $jobId, $diid);

            $stmt->execute();
        }

        if ($qty[$k] < 1) {

            $stmt1 =$conn->prepare("DELETE FROM ssm_drink_order WHERE job_id = ?");
            $stmt1->bind_param('i', $jobId);
            $stmt1->execute();
        }
    }   
}

the page html

<tbody>
    <tr>
        <th style="width:70%;" class="text-center">Drink Item</th>
        <th class="text-center">Quantity</th>
    </tr>

    <tr>
        <td>
            House Gin
            <input name="drinkItemId[]" type="hidden" value="2">
        </td>
        <td class="text-center">
            <input name="drinkItemQty[]" type="number" value="999" class="text-center">
        </td>
    </tr>
    <tr>
        <td>
            House Brandy
            <input name="drinkItemId[]" type="hidden" value="4">
        </td>
        <td class="text-center">
            <input name="drinkItemQty[]" type="number" value="" class="text-center">
        </td>
    </tr>
    <tr>
        <td>
            House Vodka
            <input name="drinkItemId[]" type="hidden" value="1">
        </td>
        <td class="text-center">
            <input name="drinkItemQty[]" type="number" value="" class="text-center">
        </td>
    </tr>
    <tr>
        <td>
            House Whiskey
            <input name="drinkItemId[]" type="hidden" value="3">
        </td>
        <td class="text-center">
            <input name="drinkItemQty[]" type="number" value="" class="text-center">
        </td>
    </tr>
</tbody>

Any help is as ever greatly appreciated

I have a hopefully small issue on a form submitting data to the mysql database table and email. The email side works fine as does the adding the data to the database table but if I upload two files, it stores the support ticket twice in the database table where as I want to store just once and the files be stored as a array in the database table. I got the code from the link https://www.codexworld.com/upload-multiple-images-store-in-database-php-mysql/

The code I have is below

 

<?php

require_once "registerconfig.php";

if (isset($_POST['submit'])) {
		
		// File upload configuration 
    $targetDir = "support-ticket-images/"; 
    $allowTypes = array('pdf','doc','docx','jpg','png','jpeg','gif');
	
	$statusMsg = $errorMsg = $insertValuesSQL = $errorUpload = $errorUploadType = '';
	
	// Escape user inputs for security
$ticket_subject = htmlentities($_POST['ticket_subject'], ENT_QUOTES);
$ticket_message = strip_tags($_POST['ticket_message'], ENT_QUOTES);
$ticket_status ='PENDING SUPPORT'; 
$username = htmlentities($_SESSION["user_name"], ENT_QUOTES);
$user_id = htmlentities($_SESSION["user_id"], ENT_QUOTES);
	
	$fileNames = array_filter($_FILES['files']['name']);
	if(!empty($fileNames)){ 
        foreach($_FILES['files']['name'] as $key=>$val){ 
            // File upload path 
            $fileName = basename($_FILES['files']['name'][$key]); 
            $targetFilePath = $targetDir . $fileName; 
             
            // Check whether file type is valid 
            $fileType = pathinfo($targetFilePath, PATHINFO_EXTENSION); 
            if(in_array($fileType, $allowTypes)){ 
                // Upload file to server 
                if(move_uploaded_file($_FILES["files"]["tmp_name"][$key], $targetFilePath)){ 
                    // Image db insert sql 
                    $insertValuesSQL .= "('".$ticket_subject."','".$ticket_message."','".$fileName."','".$ticket_status."','".$username."', '".$user_id."'),";
                }else{ 
                    $errorUpload .= $_FILES['files']['name'][$key].' | '; 
                } 
            }else{ 
                $errorUploadType .= $_FILES['files']['name'][$key].' | '; 
            } 
        }

if(!empty($insertValuesSQL)){ 
            $insertValuesSQL = trim($insertValuesSQL, ','); 
            // Insert image file name into database 
            $insert = $link->query("INSERT INTO DB TABLE NAME (ticket_subject, ticket_message, file_name, ticket_status, user_name, user_id) VALUES $insertValuesSQL");
			
            if($insert){
				
				$to = "emailaddress";

    $subject = "A new support ticket has been submitted";
    $message = " <strong>$username</strong> has just created a support ticket, below is the support ticket
    <br /><br />
    <u>Support Ticket Details</u>
    <br /><br>
    <strong>Support Ticket Subject</strong>: $ticket_subject
    <br/><br><strong>Support Ticket Message</strong>: $ticket_message
    <p><strong><u>Support Ticket Files</u></strong>
    <br>
    <img src='$fileName'>
    ";
    // Always set content-type when sending HTML email
    $headers = "MIME-Version: 1.0" . "\r\n";
    $headers .= "Content-type:text/html;charset=iso-8859-1" . "\r\n";
    // More headers
    $headers .= 'From: <noreply@emailaddress>' . "\r\n";
    $mail=mail($to,$subject,$message,$headers);
				
                $errorUpload = !empty($errorUpload)?'Upload Error: '.trim($errorUpload, ' | '):''; 
                $errorUploadType = !empty($errorUploadType)?'File Type Error: '.trim($errorUploadType, ' | '):''; 
                $errorMsg = !empty($errorUpload)?'<br/>'.$errorUpload.'<br/>'.$errorUploadType:'<br/>'.$errorUploadType;
header("location: support-ticket-confirmation?user=$username");
            }else{ 
                $statusMsg = "Sorry, there was an error uploading your file."; 
            } 
        } 
    }else{ 
        $statusMsg = 'Please select files to upload.';
    } 
     
    // Display status message 
    echo $statusMsg; 
}
?>

The structure of the db table column is file_name, VARCHAR(255), latin1_swedish_ci, NOT NULL