|
PHP - Updating Specific Record In Mysql Field
Hi All,
Please can someone help me with this bit of code. I have a mysql database with a table called 'events'. I have six fields namely 'ID, mxitID, type, message, ts, publish'. I am trying to update the field 'publish' from Yes --> No or if it is already No then back to Yes again. I have two files then main one beacon.php and publish.php Here is the code: beacon.php Code: [Select] <?php // connect to the database include('connect-db.php'); // display data in table // echo "<p><b>View All</b> | <a href='view-paginated.php?page=1'>View by Page</a></p>"; // echo "Select your Carousel: "; // get results from database $result = mysql_query("SELECT * FROM events WHERE type='beacon'") or die(mysql_error()); echo "<table border='1' cellpadding='10'>"; echo "<a href='index.php'>b Back</a><br><br>"; echo "<tr> <th>ID</th> <th>mxitID</th> <th>Type</th> <th>Message</th> <th>Timestamp</th> <th>Published</th> <th>Modify</th></tr>"; // loop through results of database query, displaying them in the table while($row = mysql_fetch_array( $result )) { // echo out the contents of each row into a table echo "<tr>"; echo '<td>' . $row['ID'] . '</td>'; echo '<td>' . $row['mxitID'] . '</td>'; echo '<td>' . $row['type'] . '</td>'; echo '<td>' . $row['message'] . '</td>'; echo '<td>' . $row['ts'] . '</td>'; echo '<td>' . $row['publish'] . '</td>'; echo '<td><a href="publish.php?id=' . $row['ID'] . '"><input name="update" type="submit" id="update" value="Update"></a></td>'; echo "</tr>"; } // close table> echo "</table>"; ?> publish.php <?php // connect to the database include('connect-db.php'); // get id value $id = $_GET['id']; echo "$id"; $sql="SELECT * FROM events WHERE type='beacon'"; $result=mysql_query($sql); $active=($result['publish']=='')?'Yes':'No'; echo "$active"; if ($active='Yes') { $active='No'; } else { $active='Yes'; } $sql = "UPDATE events SET publish='$active' WHERE ID='$id'"; $result = mysql_query($sql); // header("Location: beacon.php"); ?> I cant seem to change the value...any help would be much appreciated. Thank you in advance. MOD EDIT: code tags added. Similar TutorialsHi: I'm going crazy trying to do the following: I'm making a job registration process where the user registers on one php page to the website, must acknowlege and email receipt using an activate php page, then is directed to upload their C.V. (resume) based on the email address they enter in the active page output. I then run an upload page to store the resume in teh MySQL db based on the users email address in the same record. If I isolate the process of the user registering to the db, it works perfectly. If I isolate the file upload process into the db, it works perfect. I simply cannot upload teh file to the existing record based on teh email form field matching the user_email field in the db. With the processes together, teh user is activated, but teh file is not uploaded. Maybe I've simply been at this too long today, but am compeled to get through it by end day. If anyone can help sugest a better way or help me fix this, I will soo greatly appreciate it. My code is as follows for the 2 pages. ---------activate.php------- <?php session_start(); include ('reg_dbc.php'); if (!isset($_GET['usr']) && !isset($_GET['code']) ) { $msg = "ERROR: The code does not match.."; exit(); } $rsCode = mysql_query("SELECT activation_code from subscribers where user_email='$_GET[usr]'") or die(mysql_error()); list($acode) = mysql_fetch_array($rsCode); if ($_GET['code'] == $acode) { mysql_query("update subscribers set user_activated=1 where user_email='$_GET[usr]'") or die(mysql_error()); echo "<h3><center>Thank You! This is step 2 of 3. </h3>Your email is confirmed. Please upload your C.V. now to complete step 3.</center>"; } else { echo "ERROR: Incorrect activation code... not valid"; } ?> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta content="text/html; charset=utf-8" http-equiv="Content-Type" /> <title>Job application activation</title> </head> <body> <center> <br/><br/><br/> <p align="center"> <form name="form1" method="post" action="upload.php" style="padding:5px;"> <p>Re-enter you Email : <input name="email" type="text" id="email"/></p></form> <form enctype="multipart/form-data" action="upload.php" method="POST"> <input type="hidden" name="MAX_FILE_SIZE" value="4000000"> Upload your C.V.: <input name="userfile" type="file" id="userfile"> <input name="upload" type="submit" id="upload" value="Upload your C.V."/></form> </p> </center> </body> </html> --------upload.php---------- <?php session_start(); if (!isset($_GET['usr']) && !isset($_GET['code']) ) { $msg = "ERROR: The code does not match.."; exit(); } if(isset($_POST['upload']) && $_FILES['userfile']['size'] > 0) { $fileName = $_FILES['userfile']['name']; $tmpName = $_FILES['userfile']['tmp_name']; $fileSize = $_FILES['userfile']['size']; $fileType = $_FILES['userfile']['type']; $email = $_POST['email']['user_email']; $fp = fopen($tmpName, 'r'); $content = fread($fp, filesize($tmpName)); $content = addslashes($content); fclose($fp); if(!get_magic_quotes_gpc()) { $fileName = addslashes($fileName); } include 'reg_dbc.php'; $query = "UPDATE subscribers WHERE $email = user_email (name, size, type, content ) ". "VALUES ('$fileName', '$fileSize', '$fileType', '$content')"; mysql_query($query) or die('Error, query failed'); mysql_close($dbname); } ?> <center> <br/> <br/> <br/> <br/> Thank you for uploading your <?php echo "$fileName"; ?> file, completing your registration, and providing us your C.V. for this position. <br/> <br/> <br/> We will contact you if your canditature qualifies. </center> This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=351353.0 Hey Guys, Im slowly updating our website to php/mysql, im getting there slowly but stuck at the minute. Im try to create a form where we can edit one of the pages text and then update the text in the database if that makes sense Basically at the moment i have a form where you can see the pages text like so: <table width="400" border="0" align="center" cellpadding="3" cellspacing="1"> <tr> <td><strong>Edit About Us English </strong></td> </tr> </table> <table width="400" border="0" align="center" cellpadding="0" cellspacing="1"> <tr> <td><form name="form1" method="post" action="send_update_english.php"> <table width="100%" border="0" cellspacing="1" cellpadding="3"> <tr> <td>Detail</td> <td>:</td> <td><textarea name="detail" cols="50" rows="4" id="detail"> [b]<?php mysql_connect("localhost", "web148", "123") or die(mysql_error()); mysql_select_db("web148") or die(mysql_error()); mysql_query("SET names 'utf8'"); $result = mysql_query("SELECT body FROM aboutenglish WHERE ID = '1'"); $row = mysql_fetch_array( $result ); echo nl2br($row['body']); ?>[/b] </textarea></td> </tr> <tr> <td> </td> <td> </td> <td><input type="submit" name="Submit" value="Submit"> <input type="reset" name="Submit2" value="Reset"></td> </tr> </table> </form> </td> </tr> </table> Ive got that php bit in the middle to pull out the text thats on that page but not sure if thats correct or not. But anyways on the 'send form' page i have: <?php // Details [b]$message="$detail";[/b] mysql_connect("localhost", "web148", "123") or die(mysql_error()); mysql_select_db("web148") or die(mysql_error()); mysql_query("SET names 'utf8'"); $result = mysql_query("UPDATE aboutenglish SET body = '$message' where ID = '1'"); ?> the update query works if i put text in there, but when i use '$message' the database is left blank. This is only my second week doing all this stuff, so bare with me I appreciate your answers. I am getting this error when updating a record (through a form) Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/wormste1/public_html/tilburywebdesign/shop/FTPServers/barryottley/updates.php on line 62 This is updates.php <?php mysql_connect("localhost", "wormste1_barry", "barry") or die(mysql_error()); mysql_select_db("wormste1_barry") or die(mysql_error()); $carname = mysql_real_escape_string(trim($_POST['CarName'])); $cartitle = mysql_real_escape_string(trim($_POST['CarTitle'])); $carprice = mysql_real_escape_string(trim($_POST['CarPrice'])); $carmiles = mysql_real_escape_string(trim($_POST['CarMiles'])); $cardesc = mysql_real_escape_string(trim($_POST['CarDescription'])); mysql_query("UPDATE cars WHERE id='.intval ($_GET['id']).' LIMIT 1 (CarName, CarTitle, CarPrice, CarMiles, CarDescription, photo) VALUES('$carname', '$cartitle', '$carprice', '$carmiles', '$cardesc' ) ") or die(mysql_error()); echo "The vehicle data has been updated!"; ?> the updates.php actually processes the update. the form page is edit.php: <CENTER><B>Update a Vehicle</B></CENTER> <BR> <?php $what_id=$_GET['id']; $query="SELECT * FROM cars WHERE id = '$what_id'"; $result=mysql_query($query); while($row=mysql_fetch_array($result)) { $carname = $row['CarName']; $cartitle = $row['CarTitle']; $carprice = $row['CarPrice']; $carmiles = $row['CarMiles']; $cardesc = $row['CarDescription']; } ?> <form action="updates.php" method="post"> <CENTER>Vehicle Name:</CENTER> <br><CENTER><input type="text" name="CarName" value="<?php echo $carname; ?>"></CENTER><br> <CENTER>Vehicle Type:</CENTER><br><CENTER><input type="text" name="CarTitle" value="<?php echo $cartitle; ?>"></CENTER><br> <CENTER>Vehicle Price:</CENTER><br><CENTER><input type="text" name="CarPrice" value="<?php echo $carprice; ?>"></CENTER><br> <CENTER>Vehicle Mileage:</CENTER><br><CENTER><input type="text" name="CarMiles" value="<?php echo $carmiles; ?>"></CENTER><br> <CENTER>Vehicle Description:</CENTER><br><CENTER><input type="text" name="CarDescription" value="<?php echo $cardesc; ?>"></CENTER><br> <CENTER>Vehicle Description:</CENTER><br> <CENTER><textarea name="CarDescription" rows="10" cols="30" value="<?php echo nl2br($cardesc); ?>"></textarea></CENTER><br> <CENTER><input type="Submit"></CENTER> </form> Any idea for the error. I think it could be because the UPDATE tag syntax is wrong. Help would be great!!! I have three pages; edit_records.php (list of records, user picks one ready to edit, works fine) update.php (by in large works fine, heres the code for it anyway) Code: [Select] <?php require_once ('./includes/config.inc.php'); require_once (MYSQL); if($id = isset($_GET['prodID'])) { $query = "SELECT * FROM product WHERE prodID='{$_GET['prodID']}'"; $r = mysqli_query($dbc, $query); while ($row = mysqli_fetch_array($r)) { $id = $row['prodID']; $product = $row ['product']; ?> <form action="update_save.php" method="post"> ID: <input type="text" value="<?php echo $id;?>" name="id" disabled="disabled" /> Product: <input type="text" value="<?php echo $product;?>" name="product" /> <br /> <input type="submit" value="submit changes" /> </form> <?php } } and finally update_save.php (this is where the actual updating takes place) Code: [Select] <?php require_once ('./includes/config.inc.php'); require_once (MYSQL); $product = $_POST['product']; $query = "UPDATE product SET product = $product WHERE prodID = '$id'"; $r = mysqli_query($dbc, $query); echo 'Database Updated!!'; ?> however, on line 19 of update save, i get this error; Quote An error occurred in script 'C:\Users\David Morgan\Desktop\WEBSITES\hairz_&_graces\site\admin\update_save.php' on line 9: Undefined variable: id now I am aware i should really create a variable but since i already stored it in update.php, i had hoped it would work but it hasnt. what should i do? help would be much appreciated please I don't know if I'm wording this right. I have a textarea that is entered into a database. When this is edited, I want to store and display a list of all the changes that have been made to it. Like a conversation in a forum. The reason for this is that I want past changes to be locked from editing. Would a new database table storing only this text fields data need to be created? Maybe have column for the work request number and a second column for the text entered? But then wouldn't I need an third identifier column so the changes can be displayed in the right order? Looking for sage like wisdom Thanks Brad_Strickland I have a snippet which only partly works. The code is intended to update a record, but if it does not exist, then create it. This snippet always creates a new record, and will not update an existing one. What's wrong? $query = mysql_query("SELECT equip FROM topics WHERE equip = '$equip' AND managerId = '$userid'"); if (mysql_num_rows($query) > 0) { mysql_query("UPDATE topics SET egroup1 = '$egroup1', egroup2 = '$egroup2', egroup3 = '$egroup3', egroup4 = '$egroup4', egroup5 = '$egroup5', egroup6 = '$egroup6', WHERE equip = '$equip' AND managerId = '$userid'"); } else { mysql_query("INSERT INTO topics (managerId, equip, title, url_big, url_small, egroup1, egroup2, egroup3, egroup4, egroup5, egroup6) VALUES ('$userid','$wordquip', '$equip', '$bigpic', '$smallpic', '$egroup1', '$egroup2', '$egroup3', '$egroup4', '$egroup5', '$egroup6')"); } I making an web app for teachers to assess their students online. The problems I am currently having are on select only one record is displayed and after update no records are displayed in the form even though it was successfully updated.. The program has two files, index.php and process.php. The problems started when got the class name into the query to search and display the class. $result = $mysqli->query("SELECT * FROM data WHERE classroom = '".$classroom."' ORDER BY '.name';") or die($mysqli->error); The query works but doesn't order by name I would like. I would be grateful if anyone could cast an eye over it and give me some tips. Thanks guys. The beers are on me if anyone can sort it, but you'll have to come to Phnom Penh, Cambodia as that is where I live. Anyway here's the code. process.php
<html>
<?php
session_start();
$mysqli = new mysqli("localhost","ray","password","reports") or die(mysqli_error($mysqli));
$id = 0;
$update = false;
$name = '';
$classroom = '';
if (isset($_GET['edit'])){
$id = $_GET['edit'];
$update = true;
$result = $mysqli->query("SELECT * FROM data WHERE id=$id") or die($mysqli->error());
if(isset($result->num_rows) && $result->num_rows > 0) {
$row = $result->fetch_array();
$name = $row['name'];
$classroom = $row['classroom'];
$pacomment = $row['pacomment'];
}
}
if (isset($_POST['update'])){
$id = $_POST['id'];
$pacomment = $_POST['pacomment'];
$mysqli->query("UPDATE data SET pacomment= '$pacomment' WHERE id=$id") or die($mysqli->error);
$_SESSION['message'] = "Record has been updated!";
$_SESSION['msg_type'] = "warning";
header('location: index.php');
}
index,php
<?php require_once 'process.php'; ?>
<!--************************************** Setup Messages **************************************** -->
<?php if (isset($_SESSION['message'])): ?>
<div class="alert alert-<?=$_SESSION['msg_type']?>">
<?php
echo $_SESSION['message'];
unset($_SESSION['message']);
?>
</div>
<?php endif ?>
<!--**************************************End Setup Messages *********************************************** -->
<?php
?>
<!DOCTYPE html>
<html>
<head>
<title>Home of English Reports</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<style>=
body {
margin: 0;
font-family: Arial, Helvetica, sans-serif;
}
.topnav {
overflow: hidden;
background-color: #008080;
}
.topnav a {
float: left;
color: #f2f2f2;
text-align: center;
padding: 14px 16px;
text-decoration: none;
font-size: 17px;
}
.topnav a:hover {
background-color: #ddd;
color: black;
}
.topnav a.active {
background-color: #4CAF50;
color: white;
}
</style>
</head>
<body style="background-color:linen;">
<!--************************************** End Setup Page Styles ************************************* -->
<div class="container">
<form action="index.php" method = get>
<label for="classroom">Class name:</label><br>
<input type="text" id="classroom" name="classroom" value="107i am"><br>
<input type="submit" value="Submit">
</form>
</div>
<?php $resultcomment = $mysqli->query("SELECT EnglishComment FROM comments"); ?>
<!-- ************************************** Begin Connect DB ************************************************ -->
<div class="container" align-content-center>
<?php
$classroom = (isset($_GET['classroom']) ? $_GET['classroom'] : null);
$result = $mysqli->query("SELECT * FROM data WHERE classroom = '".$classroom."' ORDER BY '.name';") or die($mysqli->error);
?>
<!-- ************************************** End Connect DB **************************************************** -->
<div class="row justify-content-center">
<form action="process.php" method="POST">
<input type="hidden" name="id" value="<?php echo $id; ?>">
<div class="form-group">
<h1><label><?php echo $name?></label></h1>
</div>
<form action="process.php" method="POST">
<input type="hidden" name="id" value="<?php echo $id; ?>">
<div class="form-group">
<h3><label>PA Teacher's Comment</label></h3> <select name = "pacomment">
<?php
while($rows = $resultcomment-> fetch_assoc())
{
$EnglishComment = $rows['EnglishComment'];
echo "<option value='$EnglishComment'>$name.$EnglishComment</option>";
}
?></h2>
</select><br>
<p>
<div class="form-group">
<?php
if ($update == true):
?>
<button type="submit" class="btn btn-info" name="update">Update</button>
<?php else: ?>
<!-- <button type="submit" class="btn btn-primary" name="save">Save</button> -->
<?php endif; ?>
</div>
</form>
<!-- ************************************** Begin Setup Table Headers ****************************************** -->
<div class="row justify-content-center">
<table class="table" width = "20%" border = "5" cellpadding = "1";>
<thead>
<tr>
<th><center>Action</center></th>
<th><center>ID</center></th>
<th>Name and Comment</th>
</tr>
</thead>
<!-- ************************************** End Setup Classlist Table Headers ****************************************** -->
<!-- ****** Loop thru Every Record From $result Query Variable and get variables and echo each variable into the table rows ********** -->
<?php
while ($row = $result->fetch_assoc()): ?>
<tr>
<td>
<center><a href="index.php?edit=<?php echo $row['id']; ?>"
class="btn btn-info">Assess</a></center>
</td>
<!-- ************************************** Put data into Classlist table rows ****************************************** -->
<td><center><?php echo $row['studentid']; ?></center></td>
<td><?php echo $row['name']." ".$row['pacomment'] ?></td>
</tr>
<?php endwhile; ?> <!-- ****************** End While() Loop **************************** -->
</table> <!-- *************** End of Classlist Table ****************************************** -->
</div>
</div>
</div>
</body>
</html>
Edited July 25, 2020 by raymon Pehaps this is kidstuff but i'm a kid at heart. I have only 1 form field on a form User enters a special code in the form. Once submitted the form is queried against a mysql database to see if the code exists. If code does not exist it must be reentered. If code exists then redirected. basically its like "get to the next page if you have the code" thanks Yesterday, I created a topic about how I could update records and I managed to achieve that successfully. Now I have another dilemma. When I have a specific record I want to update, I want to change a category ID of an product (e.g. change it from 1 to 2) but how do I go about doing this? Here is my code thus far: Code: [Select] <?php require_once ('./includes/config.inc.php'); require_once (MYSQL); $id=$_GET['prodID']; $results = mysqli_query($dbc, "SELECT * FROM product WHERE prodID=".$_GET['prodID'].""); $row = mysqli_fetch_assoc($results); ?> <form action="" method='POST'> Product ID: <input type="text" value="<?php echo $row['prodID'];?>" name="prodID" /> <br /> Product: <input type="text" value="<?php echo $row['product'] ;?>" name="product" /> <br /> Product Description: <input type="text" value="<?php echo $row['prod_descr'] ;?>" name="prod_descr" /> <br /> Category: <select name="category"> <option value="<?php echo $row['catID'];?>"></option> </select> Price: <input type="text" value="<?php echo $row['price'] ;?>" name="price" /> <br /> In Stock: <input type="text" value="<?php echo $row['stock'] ;?>" name="stock" /> <br /> <br /><input type="submit" value="save" name="save"> </form> <?php if(isset($_POST['save'])) { $id = $_POST['prodID']; $product = $_POST['product']; $descr = $_POST['prod_descr']; $price = $_POST['price']; $stock = $_POST['stock']; // Update data $update = mysqli_query($dbc, "UPDATE product SET product='$product', prod_descr='$descr', price='$price', stock='$stock' WHERE prodID=".$_GET['prodID'].""); header( 'Location: update_save.php' ) ; } ?> Hey, im trying to fix a line of code that should update a field in the database when filled in. The code i think handles this is below. <input type="text" id="uuser_address" name="uuser_address" value="<?php echo("$slide[address]"); ?>"><br /><input type="text" id="uuser_town" name="uuser_town" value="<?php echo("$slide[town]"); ?>"><br /><input type="text" id="uuser_county" name="uuser_county" value="<?php echo("$slide[county]"); ?>"><br /><input type="text" id="uuser_postcode" name="uuser_postcode" value="<?php echo("$slide[postcode]"); ?>"></div> can anyone tell me why this is updating all the address fields other than the postcode? thanks, gavin Okay, I have a useredit page in where a user can manage their account info. All the text field and SQL connections work fine, and they all update perfectly. But when I go ahead and add my own text field (first name) and hit submit, it says success - but in my SQL it hasn't actually updated ... What the Ef? Here are my snippets of my codes: --useredit.php-- <form action="process.php" method="POST"> <input type"text" name"firstname" value="<? if($form->value("firstname") == ""){ echo $session->userinfo['firstname']; }else{ echo $form->value("firstname"); } ?>"> <input type="hidden" name="subedit" value="1"> <input type="submit" value="Edit Account"> --process.php-- function procEditAccount(){ global $session, $form; /* Account edit attempt */ $retval = $session->editAccount($_POST['curpass'], $_POST['newpass'], $_POST['email'], $_POST['firstname']); /* Account edit successful */ if($retval){ $_SESSION['useredit'] = true; header("Location: ".$session->referrer); } --session.php-- function editAccount($subcurpass, $subnewpass, $subemail, $subfirstname){ global $database, $form; if($subfirstname){ $database->updateUserField($this->username,"firstname",$subfirstname); } --database.php-- function updateUserField($username, $field, $value){ $q = "UPDATE ".TBL_USERS." SET ".$field." = '$value' WHERE username = '$username'"; return mysql_query($q, $this->connection); } If you need to see more code, let me know. Would appreciate the help, thanks! Ollie! I must be using the wrong search terms in google cause I cant find out how to update a field to a blank spot. trying to make name blank. I thought it was just " " hope someone can please help me with this Code: [Select] mysql_query("UPDATE users SET name = '".mysql_real_escape_string "."' WHERE id = '".mysql_real_escape_string($_SESSION['user_id']))."'"; I want to add a note field and a checkbox to my list/grid page and save this information to a json file using uomptrucknumber as the key. The note information will be information for the whole truck not by order. The check box will let them know that the truck is ready to have paper work printed. I have not add the note field or checkbox because I did not want to start off wrong.
echo "<table>";
echo "<tr style='background:#82fbfd'>";
echo "<th>ShipDate</th>";
echo "<th>ShipMeth</th>";
echo "<th>Name</th>";
echo "<th>LoadID</th>";
echo "<th>Drop</th>";
echo "<th>OrderID</th>";
echo "<th>Status</th>";
echo "<th>FGI</th>";
echo "<th>FGM</th>";
echo "<th>TotalBox</th>";
echo "<th>Cubes</th>";
echo "<th>Total</th>";
echo "<th>Notes \n</th>";
//echo "<table>";
echo "<tr style='background:#82fbfd'>";
$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}
function ShowDetail($row){
if($row['ompclosed']==-1){
$theColor="#AAF0D2";
}else{
$theColor="#FFFFFF";
}
if($row['ompclosed']==0 && $row['date'] < date("m/d/Y")) {
$theColor="#FF0000";
}
echo "<tr style='background-color :$theColor'><td>" .$row['date'] ."</td>";
echo "<td>" .$row['ompshippingmethodid'] ."</td>";
echo "<td style='text-align:left'>" .$row['cmoname'] ."</td>";
echo "<td>" .$row['uomptrucknumber'] ."</td>";
echo "<td>" .$row['uompdropsequence'] ."</td>";
echo "<td>" .$row['ompsalesorderid'] ."</td>";
echo "<td>" .$row['ompclosed'] ."</td>";
echo "<td>" .round($row['FGIqty'],0) ."</td>";
echo "<td>" .round($row['FGMqty'],0) ."</td>";
echo "<td>" .round($row['uomptotalboxcount'],0) ."</td>";
echo "<td>" . number_format($row['uompvolumetotal'],2) ."</td>";
echo "<td>" . number_format($row['ompordertotalbase'],2) ."</td>";
echo "<td style='text-align:left'>" .$row['ompordercommentstext'] ."</td></tr>" ;
}
//****************************************************************
while ($row = odbc_fetch_array($result))
{
if($row['ompclosed']==-1){
$theColor="#AAF0D2";
}else{
$theColor="#FFFFFF";
}
if($row['ompclosed']==0 && $row['date'] < $today) {
$theColor="#FF0000";
}
if($row['uomptrucknumber'] != $loadid) {
if ($loadid !== 0) {
echo "<tr style='background:#eee'>
<td colspan='7'>TOTAL</td>
<td>$TotalFGI</td>
<td>$TotalFGM</td>
<td> </td>
<td> </td>
<td>$loadTotal</td>
</tr>";
}
$TotalFGI=0;
$TotalFGM=0;
$loadTotal=0;
}
ShowDetail($row);
$loadid = $row['uomptrucknumber'];
$TotalFGI+=round($row['FGIqty'],0);
$TotalFGM+=round($row['FGMqty'],0);
$loadTotal +=$row['ompordertotalbase'];
}
echo "<tr style='background:#eee'>
<td colspan='7'>TOTAL</td>
<td>$TotalFGI</td>
<td>$TotalFGM</td>
<td> </td>
<td> </td>
<td>$loadTotal</td>
</tr>
</table>";
odbc_close($connect);
?>
Hi there. I'm totally new (about a week!) with php and mysql and am encountering a problem that perhaps someone can help me with?
I've looked through to see if a similar problem has appeared or been solved, but without success, so apologies if I am repeating something.
In php I am trying to update 7 fields from a form from which a user has edited/modified any of the fields in a chosen record (except id).
Here is the code:
$id=$_GET['id']; I need some help with updating the database table with the correct selection from that box. I have a dropdown box. This is actually a list from the images in a curtain directory. here the code I used: <?php $dhandle = opendir('../images/Vessels/'); $files = array(); if ($dhandle) { // loop through all of the files while (false !== ($fname = readdir($dhandle))) { if (($fname != '.') && ($fname != '..') && ($fname != basename($_SERVER['PHP_SELF']))) { $files[] = (is_dir( "./$fname" )) ? "(Dir) {$fname}" : $fname; } } closedir($dhandle); } So when inserting a new record I can use the dropdownbox to populate the correct field. Database: Id, Vesselname, Image, more, andmore There is a section where an authorized user can upload an image to the directory. This image will then be available in dropdown when the need to add a new vessel (in this case) The problem that I have is, when I later what to update the vessel, I want that same dropdownbox to be showing the correct imagename as "selected". How can I do that? This is what I have been expirementing with sofar: <?php require_once('../Connections/bgf_db.php'); ?> <?php $dhandle = opendir('../images/Vessels/'); $files = array(); $selectedFile = "mare.jpg"; if ($dhandle) { // loop through all of the files while (false !== ($fname = readdir($dhandle))) { if (($fname != '.') && ($fname != '..') && ($fname != basename($_SERVER['PHP_SELF']))) { $files[] = (is_dir( "./$fname" )) ? "(Dir) {$fname}" : $fname; } } closedir($dhandle); } if (!function_exists("GetSQLValueString")) { function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { if (PHP_VERSION < 6) { $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; } $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue); switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? doubleval($theValue) : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue; } } $MyFile_Recordset1 = "no-picture.jpg"; if (isset($selectedFile)) { $MyFile_Recordset1 = $selectedFile; } mysql_select_db($database_bgf_db, $bgf_db); $query_Recordset1 = sprintf("SELECT vessels.Name, vessels.Image FROM vessels WHERE vessels.Image = %s", GetSQLValueString($MyFile_Recordset1, "text")); $Recordset1 = mysql_query($query_Recordset1, $bgf_db) or die(mysql_error()); $row_Recordset1 = mysql_fetch_assoc($Recordset1); $totalRows_Recordset1 = mysql_num_rows($Recordset1); $selectedFile = $row_Recordset1['Image']; ?> <select name="NameHere"> <option value="<?php echo $selectedFile?>" <?php if (!(strcmp($selectedFile, $row_Recordset1['Image']))) {echo "selected=\"selected\"";} ?>><?php echo $selectedFile ?> </option> <?php // Now loop through the files, echoing out a new select option for each one foreach( $files as $fname ) { echo "<option value=\"$fname\">{$fname}</option>\n"; } echo "</select>\n"; ?> I have a form that allows my client to update some products. Now the products are simple just basic info and 1 picture. I have set this up so they can edit the products and change the information, having done this many times in the past, but now hit a puzzling block that I am baffled. The client when editing is presented with the form with the information pulled from the database and the form fields loaded with that data ready to edit. The image can either be left alone or they can choose to upload a new image. They are shown the image they currently have stored in the database. The problem I have is EVEN if they decide not to upload an image and change other information, when the submit the form it must be sending a blank value for the image somewhere as it is updating the database and removing the image reference as if it has been removed. I have an if/else statement based on the form to perform 2 different queries for the update in mysql. Here is the code for the form update, as you can see the image should not update?? Please help?? if ($_SERVER['REQUEST_METHOD'] =='POST') { //This stops SQL Injection in POST vars foreach ($_POST as $key => $value) { $_POST[$key] = mysql_real_escape_string($value); } // **************************** THIS IS FOR NO NEW IMAGE ******************************** if ($_SERVER['REQUEST_METHOD'] =='POST' && empty($_FILES['product_image']['name'])) { # setup SQL statement for no new image $SQL = " UPDATE products SET product_title = '{$_POST['product_title']}', product_description = '{$_POST['product_description']}', standard_price = '{$_POST['standard_price']}', deluxe_price = '{$_POST['deluxe_price']}' WHERE product_id = '{$_REQUEST['product_id']}' "; } // **************************** THIS IS FOR A NEW IMAGE ******************************** else { // Check the image type is a jpeg or gif for the image. if (($_FILES['product_image']['type'] != "image/gif") && ($_FILES['product_image']['type'] != "image/jpeg") && ($_FILES['product_image']['type'] != "image/pjpeg")) { echo "<FONT FACE=\"Verdana\"><SPAN CLASS=\"content\">You have chosen not to upload a <b>Product Image</b>.<BR></SPAN>" ; } elseif ($_FILES['product_image']['size'] > 100000) { echo "<FONT FACE=\"Verdana\"><SPAN CLASS=\"content\">The file size is bigger than 300kb.<BR></SPAN>" ; } else { move_uploaded_file($_FILES['product_image']['tmp_name'], "/httpdocs/product_images/".$_FILES['product_image']['name']) ; echo "<FONT FACE=\"Verdana\"><SPAN CLASS=\"content\"><B>Your front image has successfully uploaded.</B><BR></SPAN>" ; } } # setup SQL statement for update $SQL = " UPDATE products SET product_title = '{$_POST['product_title']}', product_description = '{$_POST['product_description']}', standard_price = '{$_POST['standard_price']}', deluxe_price = '{$_POST['deluxe_price']}', product_image = '{$_FILES['product_image']['name']}' WHERE product_id = '{$_REQUEST['product_id']}' "; } #execute SQL statement $result = mysql_db_query( *****,"$SQL",$connection ); # check for error if (!$result) { echo("ERROR: " . mysql_error() . "\n$SQL\n"); } I have a simple database set up for story awards given out by different groups. Here are some of the fields I'm using: Author Title Year Summary Some of the rows are nearly identical with the same Author and Title, but sometimes a different year or or others have a Summary for the story where otherwise it would be empty for a different row. The problem I'm having is I want to fetch the summary of the story from all rows where the Author and Title are identical. Currently the code is fetching a random row with the same Author and Title, but won't show the Summary if that specific row doesn't have the summary filled in (and where other rows of nearly identical information would have the summary). Here's an example of nearly identical rows: 'Boring Title', 'Boring Author', '', '2012' 'Boring Title', 'Boring Author', 'Boring Summary', '2012' How would I go about fetching all rows with the identical Author and Title and then showing the Summary even if only one of those rows has information in the Summary field? Much obliged for any help. Here's the code I'm working with: Code: [Select] <?php mysql_connect(localhost,db_user,pw); @mysql_select_db(db_database) or die( "Unable to select database"); $quer="SELECT * FROM archives ORDER BY RAND() DESC LIMIT 1"; $re = mysql_query($quer); while ($ro = mysql_fetch_array($re)) { extract($ro); $please = ''; if (strlen($ro[summary]) == 0) { $please = "The summary is empty. Please help the Archives by <a href=\"edit.php?cmd=edit&author=$author&title=$title\">filling out the summary</a>."; } $select_author = mysql_query("SELECT DISTINCT author FROM archives WHERE `title` = '" . mysql_real_escape_string($title) . "' ") or die (mysql_error()); $aut = ""; while ($row2 = mysql_fetch_array($select_author)) { $aut .= "<a href=\"author.php?author={$row2[author]}\">$row2[author]</a> & "; } $aut = substr($aut,0,-3); echo '<div style="margin: 5px;"> '.$summary.''.$please.' <p style="float: right; width: 100%; margin-top: 10px; text-align: right;"><i> ~ <a href="info.php?author='.$author.'&title='.$title.'">'.$title.'</a> by <a href="author.php?author='.$author.'">'.$aut.'</a></i> </div>'; } ?> This code works just fine to display a list of reservations for a given day and sums the guest and table counts just fine for the entire day. Sums all reservations returned.
What I am trying to do or figure out is a way to get totals based on specific time intervals. For example how many guests and tables at 8:00, 9:00, 10:00, etc....
I can see where the sums are calculated, but need help adding a way to add a variable to look at the reservation_time and sum by hour rather than just daily total.
$tablesum ++;
$guestsum += $row->reservation_pax;
<?php echo $guestsum;?> <?php echo _guest_summary;?>
The full code that pulls in the data and then sums it up in total:
<!-- Begin reservation table data -->
<br/>
<table class="global resv-table-small" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<?php
echo "<td class='noprint'> </td>";
echo "<td>Time</td>";
echo "<td>Guests/Type</td>";
echo "<td>Name</td>";
echo "<td>Special Instructions/Notes</td>";
echo "<td class='noprint'>Table</td>";
echo "<td class='noprint'>Status</td>";
echo "<td class='noprint'>Created</td>";
echo "<td class='noprint'>Details/Delete</td>";
echo "</tr>";
// Clear reservation variable
$reservations ='';
if ($_SESSION['page'] == 1) {
$reservations = querySQL('all_reservations');
}else{
$reservations = querySQL('reservations');
}
// reset total counters
$tablesum = 0;
$guestsum = 0;
if ($reservations) {
//start printing out reservation grid
foreach($reservations as $row) {
// reservation ID
$id = $row->reservation_id;
$_SESSION['reservation_guest_name'] = $row->reservation_guest_name;
// check if reservation is tautologous
$tautologous = querySQL('tautologous');
echo "<tr id='res-".$id."'>";
echo "<td";
// daylight coloring
if ($row->reservation_time > $daylight_evening){
echo " class='evening noprint'";
}else if ($row->reservation_time > $daylight_noon){
echo " class='afternoon noprint'";
}else if ($row->reservation_time < $daylight_noon){
echo " class='morning noprint'";
}
echo " style='width:10px !important; padding:0px;'> </td>";
echo "<td id='tb_time'";
// reservation after maitre message
if ($row->reservation_timestamp > $maitre['maitre_timestamp'] && $maitre['maitre_comment_day']!='') {
echo " class='tautologous' title='"._sentence_13."' ";
}
echo ">";
echo "<strong>".formatTime($row->reservation_time,$general['timeformat'])."</strong></td>";
echo "<td id='tb_pax'><strong class='big'>".$row->reservation_pax."</strong> <span class='noprint'>";
printType($row->reservation_hotelguest_yn);
//echo "<img src='images/icons/user-silhouette.png' class='middle'/>";
echo "</span></td><td style='width:10%' id='tb_name'><span class='noprint'>".printTitle($row->reservation_title)."</span><strong> <a id='detlbuttontrigger' href='ajax/guest_detail.php?id=".$id."'";
// color guest name if tautologous
if($tautologous>1){echo" class='tautologous tipsy' title='"._tautologous_booking."'";}
echo ">".$row->reservation_guest_name."</a></strong>";
// old reservations symbol
if( (strtotime($row->reservation_timestamp) + $general['old_days']*86400) <= time() ){
echo "<img src='images/icons/clock-bolt.png' class='help tipsyold middle smicon' title='"._sentence_11."' />";
}
// recurring symbol
if ($row->repeat_id !=0) {
echo " <img src='images/icons/loop-alt.png' alt='"._recurring.
"' title='"._recurring."' class='tipsy' border='0' >";
}
echo"</td><td style='width:10%' id='tb_note'>";
if ($_SESSION['page'] == 1) {
echo $row->outlet_name;
}else{
echo $row->reservation_notes;
}
echo "</td>";
if($_SESSION['wait'] == 0){
echo "<td class='big tb_nr' style='width:85px;' id='tb_table'><img src='images/icons/table_II.png' class='tipsy leftside noprint' title='"._table."' /><div id='reservation_table-".$id."' class='inlineedit'>".$row->reservation_table."</div></td>";
}
echo "<td class='noprint'><div>";
getStatusList($id, $row->reservation_status);
echo "</div></td>";
echo "<td class='noprint'>";
echo "<small>".$row->reservation_booker_name." | ".humanize($row->reservation_timestamp)."</small>";
echo "</td>";
echo "<td class='noprint'>";
// MOVE BUTTON
// echo "<a href=''><img src='images/icons/arrow.png' alt='move' class='help' title='"._move_reservation_to."'/></a>";
// WAITLIST ALLOW BUTTON
if($_SESSION['wait'] == 1){
$leftspace = leftSpace(substr($row->reservation_time,0,5), $availability);
if($leftspace >= $row->reservation_pax && $_SESSION['outlet_max_tables']-$tbl_availability[substr($row->reservation_time,0,5)] >= 1){
echo" <a href='#' name='".$id."' class='alwbtn'><img src='images/icons/check-alt.png' name='".$id."' alt='"._allow."' class='help' title='"._allow."'/></a> ";
}
}
// EDIT/DETAIL BUTTON
echo "<a href='?p=102&resID=".$id."'><img src='images/icons/pen-fill.png' alt='"._detail."' class='help' title='"._detail."'/></a> ";
// DELETE BUTTON
if ( current_user_can( 'Reservation-Delete' ) && $q!=3 ){
echo"<a href='#modalsecurity' name='".$row->repeat_id."' id='".$id."' class='delbtn'>
<img src='images/icons/delete.png' alt='"._cancelled."' class='help' title='"._delete."'/></a>";
}
echo"</td></tr>";
$tablesum ++;
$guestsum += $row->reservation_pax;
}
}
?>
</tbody>
<tfoot>
<tr style="border:1px #000;">
<td class=" noprint"></td><td></td>
<td colspan="2" class="bold"><?php echo $guestsum;?> <?php echo _guest_summary;?></td>
<td></td>
<td colspan="2" class="bold"><?php echo $tablesum;?> <?php echo _tables_summary;?></td>
<?php
if($_SESSION['wait'] == 0){
//echo "<td></td>";
}
?>
</tr>
</tfoot>
</table>
<!-- End reservation table data -->
Edited by mfandel, 26 October 2014 - 02:06 AM. |
|||||||