|
PHP - Duplicating Common Queries
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 Similar TutorialsHi ,im face little problem in duplicating rows. it's not easy task wht i need: i got this rows right now id----------position-------------A_Q_ID 1---------------1--------------------5----- 2---------------2--------------------5----- 3---------------3--------------------5----- 4---------------1--------------------6----- 5---------------2--------------------6----- 6---------------3--------------------6----- what i need is to duplicat all the rows and have them inserted after the rows i allready have like that: id------------position-------------A_Q_ID 7-----------------1--------------------7----- 8-----------------2--------------------7----- 9-----------------3--------------------7----- 10---------------1--------------------8----- 11---------------2--------------------8----- 12---------------3--------------------8----- i tryed few ways without success . any idea how to do that please? i cannot get the logic on doing it thanks 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); ///////////////////////////// ?> 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/
<?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 Hi All, I have a table, the column type is an attribute that items in this table share. A simple explanation would be if the field was colour and the items could be one of the colours. I am struggling with a for each loop to get the items out of this table grouped by this value. I am wanting to create a dopdown where the item_type is a header. This is what i have so far which obviously doesnt work:
unction getRentalItems($display){
include 'includes/dbconn.php';
$stmt = $conn -> prepare('
SELECT i.id, item, it.type, price_GBP, price_USD, price_CAD
FROM items i
LEFT JOIN item_type it on i.type = it.id
');
$stmt -> execute();
$stmt -> bind_result($id, $item, $type, $gbp, $usd, $cad);
if($display == 'Options'){
$out = '';
while($stmt -> fetch()){
$out .= '<optgroup label="$type">';
$out .= "<option data-item='$type' data-GBP='$gbp' data-USD='$usd' data-CAD='$cad' value='$id'>$item</option>";
}
}
return $out;
}
I would like the optgroup to be first whenever the type changes. I hope this explanation makes sense - there is an image to show what i mean - the bold text should only appear once per instance. Edited October 10, 2020 by Adamhumbug In my mysql database i have a field that records peoples details one part of the details is their county/region they live in. It records the county in a normal varchar field however i need a simple php script that searching the database and finds the most common county so i can then return to the screen where the most popular region for my members. Thank you for reading. If I have three files such as the following, and put session_start() in each, the session cookie will be domain: "mydomain.com" and path "/". As such, the session will be shared between all three files.
http://mydomain.com/index.php http://mydomain.com/folder1/index.php http://mydomain.com/folder1/folder2/index.phpIf I want the cookie to be unique to each file, then it seems I have several options. I could use session_name() to give the cookie a unique name for each file. I could use session_set_cookie_params() to set a unique path for each (/, /folder1, /folder1/folder2), however, the index files in the children directories will also be sent the root directories session cookie, right? Will this cause problems? Should both approaches be implemented? Or is there another approach? Thanks I have wizard that asks several predefined questions to find-out user needs. at the end it must offer some items based on user answers. all of available items have some properties in common and one or two specific properties. what is the best way or algorithm to do this in JavaScript? e.g: Item 1 Properties: Name Weight Color Size Item 2 Properties: Name Size Weight Item 3 Properties: Name Color Size Thanks. Hello, I have two arrays: $questions, which has 'question_id' as one of its keys and $assignment_questions which is a subset of $questions, pulled off my database. Using php, I'd like to construct a 3rd array which takes my $questions array and removes from it any question that has a question_id which can be found in both $questions and $assignment_questions, in addition to the other values associated with a particular question_id. For example, $questions has: question_id, solution, author (and other keys). Is there any way to check if a particular key matches and then zap out all of the corresponding fields (solution, author, etc.)? Thanks! This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=321735.0 Hi,
I have the following query
SELECT user_details.User_club_ID, user_details.fname, user_details.lname, user_details.email, user_details.club_No club.CLUBCODE, club.club_id FROM user_details, club WHERE club_id = $cid AND user_details.club_No = club.CLUBCODE AND user_status = 'active'";which I converted to a prepared statement as SELECT user_details.User_club_ID, user_details.fname, user_details.lname, user_details.email, user_details.club_No club.CLUBCODE, club.club_id FROM user_details, club WHERE club_id = ? AND user_details.club_No = club.CLUBCODE AND user_status = ?";Please note that user_status is a field in the table user_details. The original query (non -PDO) works correctly. I want to know if this is correct and that the comparison in the WHERE clause i.e. user_details.club_No = club.CLUBCODE is security safe. If not then how should this be modified. Also if there is a better way to write this statement, kindly show that as well. Thanks Thanks all ! Edited by ajoo, 11 December 2014 - 02:35 AM. I'm showcasing user avatars and nicknames on the page, the mistake I did was I used session variables for that which are being set after login, now obviously those will be deleted as soon as the session is over. So I thought of inserting the values into scalar variables instead of session variables. The problem I'm encountering is, how to make those variables available at the necessary spots on other pages? Let's say I fetch data off the database after login inside login.php and I put the data into scalar variables, what would be common practice to make those scalar variables accessible in index.php? Is this solution a good solution at all to make the avatar and nickname of the user permanent visible? What would be common practice? Hello I have a from that updates a few things in the db, and uses "<?php echo $PHP_SELF;?>". The form does update everything fine, however that page does not reflect it. If I refresh the page manually I can see the new values. Is this a common thing, and can anything be done to fix this? Hello all, I've tried several ways to calculate a commission in the multi level marketing script, but it all ended up with nothing, recursive functions, nested sets nothing worked, and i'm running out of time to keep trying I've made multiple queries on the same table, and it's working, but only the first 3 queries, not more, I tried to perform it 4 times (although I need the queries to be performed 13 times) but still only 3 times example: A recruited B and C, and B recruited D, and D recruited E.... what's supposed to happen is A takes a commission on B, C, D, and E but now it only takes commission on the first three, and no commission for anyone comes after that, and the same for B, C, D, and E each takes commission on only 3 members down the line, and no more here is the queries Code: [Select] <? $result = mysql_query("SELECT * FROM users"); echo "<table width='589' border='1'> <tr> <th>ID</th> <th>Name</th> <th>National ID</th> <th>Commission</th> </tr>"; while($row = mysql_fetch_array($result)) { $query = mysql_query("SELECT * from users where recruiteris = '".$row['id']."'"); $num_rows=mysql_num_rows($query); $id1 = $row['id']; $commission = $num_rows; $_SESSION['id1'] = $id1; echo "<tr>"; while ($roww = mysql_fetch_array($query)) { $querry=mysql_query("select * from users where recruiteris = '".$roww['id']."'"); $num_rowss = mysql_num_rows($querry); while ($rowws= mysql_fetch_array($querry)) { $var3= '10'; $querrys=mysql_query("select * from users where recruiteris = '".$rowws['id']."'"); $num_rowsss = mysql_num_rows($querrys); while ($rowwss= mysql_fetch_array($querrys)) { $querryss=mysql_query("select * from users where recruiteris = '".$rowwss['id']."'"); $num_rowssss = mysql_num_rows($querryss); while ($rowwsss= mysql_fetch_array($querryss)) { $querryr=mysql_query("select * from users where recruiteris = '".$rowwsss['id']."'"); $num_rowssr = mysql_num_rows($querryr); } } } } $total= ($commission + $num_rowws + $num_rowwss + $num_rowwsss + $num_rowss + $num_rowssr) * $var3; echo "<td><a href=\"user.php?id=".$row['id']."\">".$row['id']."</a></td>"; echo "<td>" . $row['fname'] .''. $row['lname'] ." </td>"; echo"<td> ". $row['nid'] ." </td>"; echo "<td>".$total."</td> </tr>"; } echo "</table>"; ?> ive had these queries working okay until i checked on it today, it's not working anymore.. these are the errors::: Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\wamp\www\arrastre\add.php on line 105 Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\wamp\www\arrastre\add.php on line 117 if(isset($_POST['add'])){ if($billnmbr=="" or $orno=="" or $payor=="" or $arrastre=="" or $wharfage=="" or $total=="" or $date=="" or $tcl==""){ echo "At least one field was left blank."; } else{ $query = mysql_query("select * from `arrastre` WHERE `billnmbr`='$billnmbr'"); /*****************this is my line 105************/ $count = mysql_num_rows($query); if($count==1){ echo "This billnumber is already in the database."; } else{ $bll = strtoupper($billnmbr); $payr = strtoupper($payor); $query="insert into `arrastre` (`billnmbr`, `orno`, `payor`, `arrastre`, `wharfage`, `total`, `date`, `tcl`) values ('$bll', '$orno', '$payr', '$arrastre', '$wharfage', '$total', '$date', '$tcl')"; $result=mysql_query($query); $query = mysql_query("select * from `arrastre` where `billnmbr`= '$billnmbr'", $link); /************and this is my line 117*************/ $count = mysql_num_rows($query); if($count==1){ echo "last bill number added: ".$billnmbr; } } Thank you very much for your time and have a nice day. Good day everyone. I go by the nickname Sbosh and I am a newbie in PHP. I am currently learning php using video tutorials. I need help with regards to php queries. I have the following code: "SELECT 'food', 'calories' FROM 'diet' ORDER BY 'id'" which is supposed to display content from a table inside phpmyadmin which i created manually using phpmyadmin. But It gives an error saying i must check the MariaDB version on how to right this query, something like that. Please assist on the proper way of writing this code. Again I am a newbie in PHP and just starting to learn, so your help will be highly appreciated. |
|||||||