|
PHP - Give New Value To Variable From Mysql Update Statement
Please take a look at the following code (this would work if the statement was SELECT instead of UPDATE) But I need to grab the following:
Code: [Select] $result = mysql_query("UPDATE `AUCTIONS_MAIN` SET Current_Price = Current_Price + '$increase_price', Last_Bidder_Time = '$last_bidder_time' WHERE PId = '$id'"); //grab the new updated current price to save on different bidding history DB while($row=mysql_fetch_array($result)) { $current_price = $row['Current_Price']; } As you can see, I need to give the new Current_Price value to the $current_price variable. Obviously the while function is incorrect, I would really appreciate any ideas and/or suggestions on how I can grab this value. PS: I can't make a new query call with a select statement in order to know the new Current_Price. I need the direct updated value coming from the Update Statement, is this possible? Thanks a lot in advance!! Similar Tutorialsive tried to write the $sql in so many ways and this looks the best and its still not working and ive checked the correct syntax but still. this is how i wrote: $sql2 = "UPDATE `tvchaty`.`episodes` SET `showid` = ".($showid).", `epname` = ".($epname).", `season` = ".($season).", `episode` = ".($episode).", `info` = ".($info).", `airdate` = ".($airdate).", `directwatch` = ".($directwatch)." WHERE `episodes`.`id` = ".($id)." LIMIT 1;"; this is the error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Prideses, `season` = 6, `episode` = 11, `info` = , `airdate` = 2010-11-01, `dire' at line 1 what could be the problem? tnx.... This isn't the entire code just enough to see what I'm trying to do. Everything was working until I added the mysql update query in the if statement. Is this possible or am I doing something wrong? When I run the script it just echos "No results found" twice as $num_results = 2. Code: [Select] <?php include("../includes/connect.php"); $query = "SELECT ........ "; $result = $db->query($query); $num_results = $result->num_rows; if ($num_results == 0) { exit; } else { $i=0; while ($i < $num_results) { $row = $result->fetch_assoc(); $id = $row['id']; if ($expiration_date > $today) { ### EMAIL CODE HERE ### $update = "UPDATE model SET reminder_sent = '1' WHERE id = '$id' "; $result_2 = $db->query($update); $i++; } else { echo "No results found."; $i++; } } } ?> Hi there, is it possible to join a variable and a string inside a $_POST variable inside a mysql query (UPDATE in this case) Here is what i am trying to accomplish: $update = "UPDATE mona SET STKFF='$_POST[$counter."NAME"]' WHERE id='$userid'"; if (!mysql_query($update)) { die('Error: ' . mysql_error()); } else echo " <br> Update Complete"; Its the '$_POST[$counter."NAME"]' bit that im worried about, is this possible without having to do: $foo=$counter."NAME" '$_POST[$foo]' Thanks Chris When I run 'select 1700-price as blah from goldclose as t2 order by dayid desc limit 1' by itself in mysql I get a numerical result: one row, one column. In my php script, the 1700 is actually a variable. so here it is $changequery = sprintf("select $goldprice-price as change from goldclose order by dayid desc limit 1"); $change = mysql_query(changequery); while ($row = mysql_fetch_array($change)) { printf("$row[0]"); } mysql_free_result($changeresult); I get the following error, Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in <b>/root/fuzzy/htmlmain4.php on line 99 Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in <b>/root/fuzzy/htmlmain4.php on line 103 Not sure why? All i want is to get the result of that select statement into a variable such as $change I'm using the following code. I have tried placing parenthesis around $date1 and $date2 to separate them. No matter where I place the parenthesis Dreamweaver keeps giving me a syntax error. If I remove the second operator $date2 with the && the error goes. checked all my books but I can't see what is probably a stupid error ! Code: [Select] if ($row_sales_shipping_tax['cdate'] >= $date1 && <=$date2 ) Thanks for the help ! Can anyone post a generic update function to update mysql table. The manual approach: update $tablename set $column1='a', $column2='b' where $id=$value; Having update statement trouble... The error I'm getting is... "Could not query the database - : " Not sure what I'm missing, I had this working before also, but now it's not. Here is my code for the update page... Code: [Select] <html> <head> <title>Update User</title> </head> <body> <form method="post" action="update_user2.php"> <?php $dbc = mysqli_connect('localhost', 'se266_user', 'pwd', 'se266') or die(mysql_error()); //delete users echo '<b>Delete or Update User</b>.<br />'; if (isset($_POST['remove'])) { foreach($_POST['delete'] as $delete_id) { $query = "DELETE FROM users WHERE course_id = $delete_id"; mysqli_query($dbc, $query) or die ('can\'t delete user'); } echo 'user has been deleted.<br />'; } if (isset($_POST['update'])) { $course_id = $_POST['course_id']; $course_name = $_POST['course_name']; $student_id = $_POST['student_id']; $query = "UPDATE users SET course_name ='". $course_name ."' WHERE course_id = $course_id"; $updres = mysqli_query($query); if(!$updres) { die(" Could not query the database - : <br/>". mysqli_error() ); } else { echo 'course has been updated.<br />'; } } //display users info with checkbox to delete $query = "SELECT * FROM users"; $result = mysqli_query($dbc, $query); while($row = mysqli_fetch_array($result)) { echo '<input type="checkbox" value="' .$row['course_id'] . '" name="delete[]" />'; echo ' ' .$row['course_name'] .' '. $row['student_id']; echo '<br />'; } mysqli_close($dbc); ?> <form method="POST" action="update_user2.php"> <label for="course_id">Course ID:</label> <input type="text" id="course_id" name="course_id" /> <br /> <label for="course_name">Course Name:</label> <input type="text" id="course_name" name="course_name" /> <br /> <label for="course_name">Student ID:</label> <input type="text" id="student_id" name="student_id" /> <br /> <input type="submit" name="remove" value="Remove" /> <input type="submit" name="update" value="Update" /> <br><br> </form> </body> </html> Sorry I'm still new to php. So I'm trying to get my update statement to work. I will post the statement I have for it, I will also post sql code from the database it is trying to update and I will also post my form page. Appreciate any help, thanks. Here is my update statement... Code: [Select] <?php if (isset($_POST['update'])) { foreach($_POST['update'] as $update_id) { $query = "UPDATE FROM users WHERE course_id = $update_id"; mysqli_query($dbc, $query) or die ('can\'t update course'); $course_id = $_POST['course_id']; $course_name = $_POST['course_name']; $student_id = $_POST['student_id']; $sql = "INSERT INTO users (course_id, course_name, student_id) VALUES ('$course_id', '$course_name', '$student_id');"; $db->exec($sql); } echo 'course has been updated.<br />'; } ?> Here is the page that the update statement is on... Code: [Select] <html> <head> <title>Update User</title> </head> <body> <form method="post" action="update_user2.php"> <?php $dbc = mysqli_connect('localhost', 'se266_user', 'pwd', 'se266') or die(mysql_error()); //delete users echo '<b>Delete or Update User</b>.<br />'; if (isset($_POST['remove'])) { foreach($_POST['delete'] as $delete_id) { $query = "DELETE FROM users WHERE course_id = $delete_id"; mysqli_query($dbc, $query) or die ('can\'t delete course'); } echo 'user has been deleted.<br />'; } if (isset($_POST['update'])) { foreach($_POST['update'] as $update_id) { $query = "UPDATE FROM users WHERE course_id = $update_id"; mysqli_query($dbc, $query) or die ('can\'t update course'); $course_id = $_POST['course_id']; $course_name = $_POST['course_name']; $student_id = $_POST['student_id']; $sql = "INSERT INTO users (course_id, course_name, student_id) VALUES ('$course_id', '$course_name', '$student_id');"; $db->exec($sql); } echo 'course has been updated.<br />'; } //display users info with checkbox to delete $query = "SELECT * FROM users"; $result = mysqli_query($dbc, $query); while($row = mysqli_fetch_array($result)) { echo '<input type="checkbox" value="' .$row['course_id'] . '" name="delete[]" />'; echo ' ' .$row['course_name'] .' '. $row['student_id']; echo '<br />'; } mysqli_close($dbc); ?> <input type="submit" name="remove" value="Remove" /> <input type="submit" name="update" value="Update" /> <br> <br> <form method="GET" action="update_user2.php"> <label for="course_id">Course ID:</label> <input type="text" id="course_id" name="course_id" /><br /> <label for="course_name">Course Name:</label> <input type="text" id="course_name" name="course_name" /><br /> <label for="course_name">Student ID:</label> <input type="text" id="student_id" name="student_id" /><br /> </form> </body> </html> Here is my sql database... Code: [Select] DROP DATABASE IF EXISTS se266; CREATE DATABASE se266; use se266; CREATE TABLE users ( course_id INT(11) NOT NULL AUTO_INCREMENT, course_name VARCHAR(50), student_id VARCHAR(50) NOT NULL, PRIMARY KEY (course_id) ); -- create the users and grant priveleges to those users GRANT SELECT, INSERT, DELETE, UPDATE ON se266.* TO se266_user@localhost IDENTIFIED BY 'pwd'; INSERT INTO users (course_id, course_name, student_id) VALUES ('se255', 'Web Design Using PHP', '43253256'); use se266; SELECT * FROM users; I have <?php /** This file is responsible for processing all the add and modify requests for the movies, games and customer section of the admin page. The various functions here can be called into the pages we need inorder to make code management cleaner and easier to handle. **/ include('databaseconfig.php'); //Add a new customer to the data base. $query = mysql_query("update customer set customer_id = '" .$_POST['cust_id'] . "', first_name = '" .$_POST['fname'] . "', last_name = '" .$_POST['lastname'] . "', adress = '" .$_POST['address'] . "', postal_code= '" .$_POST['postalcode'] . "', phone_number= '" .$_POST['phonenumber'] . "', user_name = '" .$_POST['fname'] . "', user_pass = '" .$_POST['lastname'] . "'"); echo("<h1>Output</h1>"); echo($query); echo("<h1>Possible Errors</h1>"); //Echo errors echo mysql_errno($query) . ": " . mysql_errno($query); ?> Which should get the information from the forum feilds and insert that into the data base, throwing errors if I left feilds null. But when I run it and leave everything blank and hit submit or even actually enter the required data all I get is a page that states Code: [Select] Output Possible Errors : Believe me!! I have looked and tried so many snippets of code I am exausted! TableName: keywords Row: id productid keywords Good morning... I have been struggling with update, I dont know why... it seems easy eneough and it dosent look that different than a simple insert. Would someone please take a look at the if statement below, more specificly the update section. I am getting an error (Parse error: syntax error, unexpected T_STRING in...... ). Code: [Select] <? $keywd = substr(implode(",", array_keys($arr_tem)),0,200); $productid = intval($_GET['product_id']); // Make a MySQL Connection mysql_connect("localhost", "xxxxxxxxx", "xxxxxxxxx") or die(mysql_error()); mysql_select_db("xxxxxxxxx") or die(mysql_error()); $get = "SELECT * FROM keywords WHERE productid = $productid"; $SQ_query = mysql_query($get) or die("Query failed: $get\n" . mysql_error()); $fetch = mysql_fetch_array($SQ_query); $id = $fetch['productid']; $key = $fetch['keywords']; // Evaluates to true because $var is empty if (empty($id)) { mysql_query("insert into keywords (productid, keywords) VALUES('$productid','$keywd')"); } else{ if (isset($id)) { echo '$var is set even though it is empty'; $mysql_query = mysql_query(UPDATE keywords SET keywords = '$keywd' WHERE productid = '$productid'); // $mysql_query = mysql_query(UPDATE keywords set keywords = "$keywd" WHERE productid = "$productid"); } endif; ?> I'm looking for an update statement that will remove all the extra space from a specific field. For example, all data on this field a "Apple " "Orange " "Grapes Banana " I'd like to update the database to remove the space at the end. Thanks. I can't get my Updated On timestamp to work in the following query... Code: [Select] // ****************************** // Create Temporary Password. * // ****************************** $tempPass = substr(md5(uniqid(rand(), true)), 3, 10); // Build query. $r = "UPDATE member SET pass=?, updated_on=? WHERE email=? LIMIT 1"; // Prepare statement. $stmt2 = mysqli_prepare($dbc, $r); // Bind variables to query. mysqli_stmt_bind_param($stmt2, 'sss', $tempPass, NOW(), $email); // Execute query. mysqli_stmt_execute($stmt2); I used similar code for an INSERT and it worked fine?! Now sure what is going on here... Debbie HI All, I am writing a prepared statement to update some user information. Included in this table are the username and password fields. In this particular form, i dont want the user to have access to this information and have built a form that only shows what i want them to be able to change. The bit that i am not sure about is the prepared statement that i am writing. I am getting a boolean error suggesting that my prepare failed and i think this may be because i have not named every field in the table. To give an idea of the table fields i have pulled this from php my_admin (this is not the sql i am running) UPDATE `ssm_user` SET `user_id`=[value-1],`user_email`=[value-2],`user_password`=[value-3], `user_firstname`=[value-4],`user_lastname`=[value-5],`user_accountlevel`=[value-6], `user_mobile`=[value-7],`user_role`=[value-8],`user_lastlogondate`=[value-9] WHERE 1 my prepared statement is
$stmt = $conn->prepare("
UPDATE ssm_user
SET
user_email=?,
user_firstname=?,
user_lastname=?,
user_accountlevel=?,
user_mobile=?,
WHERE user_id = ?
");
$stmt->bind_param('sssssi', $email, $fname, $lname, $accountlevel, $mobile, $uid);
$stmt->execute();
return $stmt->affected_rows;
Do i have to declare every field in the table or is there something that i am missing here. the sql statement dont execute: what would be the problem?
$sql = "UPDATE ".prefix("loanapplication")." SET
firstname = :firstname,
secondname = :secondname,
surname = :surname,
officialworkemail = :officialworkemail";
$sql.= " WHERE username=:username";
$sql.= " WHERE username=:username";
echo "$sql: $sql<br>";
//print_r($sql);
$stmt = $database->connection->prepare($sql);
$stmt->bindParam(':username',$session->username);
$stmt->bindParam(':firstname',$firstname);
$stmt->bindParam(':secondname',$secondname);
$stmt->bindParam(':surname',$surname);
$stmt->bindParam(':officialworkemail',$officialworkemail);
$stmt->execute();
sql =UPDATE `emr_loanapplication` SET firstname = :firstname, secondname = :secondname, surname = :surname, officialworkemail = :officialworkemail WHERE username=:username
There are about 400 records in a database with a field zabp_package = T_SIMT. They were uploaded in an order where specific category lists were uploaded together. For example, first 100 dining, then 75 insurance, then 150 health, then 75 cars. So from an auto increment standpoint they were inserted in that order. I want to select at random 100 of these 400 and update two fields. My update statement is: update `usersOld` SET `m_org` = 'ZABP.org Corporate HQ' , `m_orgID` = 'PFL2a96bW' WHERE `zabp_package` = 'T_SIMT' This works for all, I just need to limit a random selection to 100. Any Help on this, thanks in advance.
In the same $-POST , i wanted to perform update and delete. With the updated and deleted database, I need to select the updated data from the database. However, it tells me: Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in... .I am new to sql in php, is it true that i cannot perform sql in this way? Or is there any suggestion to perform select straight after deleting and updating?
CANCEL BOOK RESERVATION
if(isset($_POST['cancelres']))
{
$query = "DELETE FROM reserved_books WHERE id='$resid';"; //delete reservation
$query .="UPDATE reserved_books SET queue = queue - 1 WHERE bookid = '$bookid' AND queue > '$bookqueue';";//update queue number where reservation that queue behind/after/ the current reservation
$query_run = mysqli_multi_query($connection, $query);
if($query_run)
{
$_SESSION['success'] = "Reservation Cancelled";
}
else
{
$_SESSION['status'] = " Reservation Not Cancelled";
}
$query= "SELECT title FROM books WHERE id = '$bookid';"; //get book title from db
$query_run = mysqli_query($connection,$query);
if(mysqli_num_rows($query_run)>0)
{
foreach ($query_run as $row) {
$title = $row['title'];
}
}
$bookres = "SELECT * FROM reserved_books WHERE bookid = '$bookid' AND queue = 1";
$bookres_run = mysqli_query($connection,$bookres);
foreach($bookres_run as $row)
{
$res_username = $row['username'];
$res_id = $row['id'];
}
$query= "SELECT option_value FROM settings WHERE option_name = 'email_temp_rescollect'"; //get email template from db
$query_run = mysqli_query($connection,$query);
if(mysqli_num_rows($query_run)>0)
{
$row = mysqli_fetch_array($query_run);
$rescollect_template = $row[0];
}}
I am trying to allow the user to update a variable he chooses by radio buttons, which they will then input text into a box, and submit, to change some attributes. I really need some help here. It works just fine until I add the second layer of variables on top of it, and I can't find the answer to this question anywhere. <?PHP require('connect.php'); ?> <form action ='' method='post'> <select name="id"> <?php $extract = mysql_query("SELECT * FROM cars"); while($row=mysql_fetch_assoc($extract)){ $id = $row['id']; $make= $row['make']; $model= $row['model']; $year= $row['year']; $color= $row['color']; echo "<option value=$id>$color $year $make $model</option> ";}?> </select> Which attribute would you like to change?<br /> <input type="radio" name="getchanged" value="make"/>Make<br /> <input type="radio" name="getchanged" value="model"/>Model<br /> <input type="radio" name="getchanged" value="year" />Year<br /> <input type="radio" name="getchanged" value="color" />Color<br /><br /> <br /><input type='text' value='' name='tochange'> <input type='submit' value='Change' name='submit'> </form> //This is where I need help... <?PHP if(isset($_POST['submit'])&&($_POST['tochange'])){ mysql_query(" UPDATE cars SET '$_POST[getchanged]'='$_POST[tochange]' where id = '$_POST[id]' ");}?> Hi guys, I have an interesting situation and I was hoping someone could explain what is going on to me. I have an enquiry form on a site that sends an e-mail. All the code is on one page with the form submitting back to the same page. I have a variable, msg, which at the top of the page is set to nothing and then an if statement to see if the form was submitted. If the form was submitted then msg will have one of three messages in it by the end (regardless of anything else). However when I check the msg variable after the if statement it is empty. To test if it was being affected I changed the initial value from nothing to TEST. After the IF statement the variable still held the value TEST, thus confirming it wasn't being affected by the IF statement. Now onto my code (the whole page is over 600 lines long so I'm only posting the relevant code): Code: [Select] <?php session_start(); $msg = ''; $input = array( 24 variables from form here... ); if(isset($_POST['form-submitted'])) { $missing_info = FALSE; $array = array( variables along with their filter validation/sanitize types... ); $input = filter_input_array(INPUT_POST, $array); /* PHP validation code checking appropriate fields were not empty. If they were set $missing_info = TRUE */ if(!$missing_info) { /* Construct e-mail, this sends successfully. */ if(mail($to, $subject, $content, $headers)) { $msg = '<div class="message"><h1>Form Sent</h1><br />Your form has been sent to us. We endevour to respond to all booking requests with 2 working days. If you have not heard from us in that time please contact us on 01462 893620. If you chose to confirm by post please allow 5 working days before contacting us.</div>'; $_SESSION['msg'] = $msg; } else { $msg = '<div class="message"><h1>Technical Issues</h1><br />We appologise but the website is currently experiencing technical difficulties. Please try again later or print this form out and post / fax it to us. Alternatively you can call us on 01462 893620.</div>'; $_SESSION['msg'] = $msg; } } else { $msg = '<div class="message"><h1>Incomplete Form<h1><br />Please make sure to complete all fields of the form before submitting your booking request.</div>'; $_SESSION['msg'] = $msg; } } if(isset($_SESSION['msg'])) { $msg = $_SESSION['msg']; unset($_SESSION['msg']); } ?> If I don't load the message into session and then back out of session after the if statement then $msg will still hold whatever it was initially set to (in this case an empty string). Why isn't it set in the if statement? Okay so after echoing awardType it says Match which would mean it would go to the else part of the statement but it doesn't. It still shows the dropdown for the characters and don't know why? Code: [Select] <?php require ('php/awardsshowNom.php'); ?> <script type="text/javascript" src="forms/edit/js/awardsshowNom.js"></script> <!-- Form --> <form action="#" id="awardsshowNomForm" > <fieldset> <legend>Edit Award Nominations</legend> <?php echo $awardType; ?> <?php if ($awardType = 'Singular') { ?> <div class="field required"> <label for="nominees">Nominees</label> <select class="dropdown" name="charactersDrop" id="charactersDrop" title="Characters Drop"> <option value="">- Select -</option> <?php while ( $row = mysqli_fetch_array ( $charactersResult, MYSQL_ASSOC ) ) { print "<option value=\"".$row['ID']."\">".$row['characterName']."</option>\r"; } ?> </select> <input type="button" value="Add Character" class="" onclick="HandlerCharacters()"/> <ul id="characterList"> </ul> <span class="required-icon tooltip" title="Required field - This field is required, it cannot be blank, and must contain something that is different from emptyness in order to be filled in. ">Required</span> </div> <? } else { ?> <div class="field required"> <label for="nominees">Nominees</label> <select class="dropdown" name="events" id="events" title="Events for <?php echo date("Y") ?>"> <option value="">- Select -</option> <?php while ( $row = mysqli_fetch_array ( $matchResult, MYSQL_ASSOC ) ) { print "<option value=\"".$row['ID']."\">".$row['segmentTitle']."</option>\r"; } ?> </select> <input type="button" value="Add Match" class="" onclick="AddMatch()"/> <ul id="matchList"> </ul> <span class="required-icon tooltip" title="Required field - This field is required, it cannot be blank, and must contain something that is different from emptyness in order to be filled in. ">Required</span> </div> <?php } ?> <input type="hidden" name="awardID" id="awardID" value="<?php echo $awardID; ?>" /> <input type="hidden" name="awardShowID" id="awardShowID" value="<?php echo $awardShowID; ?>" /> <input type="hidden" name="awardName" id="awardName" value="<?php echo $awardName; ?>" /> <input type="submit" class="submit" name="editAwardNom" id="editAwardNom" title="Edit Award Nominees" value="Edit Awards Nominees"/> </fieldset> </form> <!-- /Form --> <!-- Messages --> <div class="message message-error"> <h6>Required field missing</h6> <p>Please fill in all required fields. </p> </div> <div class="message message-success"> <h6>Operation succesful</h6> <p>Arena was added to the database.</p> </div> <!-- /Messages --> Hi, I have been on and off this forum loads of time and it has helped me out so much over the past few months with different projects. But today there is one thing i simply cant figure out or find any kind of answer anywhere and would be really grateful if someone could give me a pointer. I have a form which which i use to input data into a mysql database. One of the variables Code: [Select] $budget=$_POST['budget']; which I use to add data to the 'budget' field in my database. In the form, budget is a dropdown with a few options: Code: [Select] <select name="budget" id="budget"> <option selected="selected">Please Choose One</option> <option>up to 500 Euros</option> <option>500 to 1000 Euros</option> <option>1000 to 1500 Euros</option> <option>1500 to 3000 Euros</option> <option>3000 to 5000 Euros</option> <option>Over 5000</option> <option>No Budget Set Yet</option> </select> I have another field in my database called 'price'. What I would like to do is, if someone chooses a budget of 3000 to 5000 I would like this to enter 5.00 in the price field in the database. I have tried a number of things, but nothing is working for me. This is where I am at now, which doesn't work either but thought it might give a better explanation of what I am trying to achieve. Code: [Select] $budget=$_POST['budget']; $price=$budget['up to 500 Euros']='3.50'; $price=$budget['500 to 1000 Euros']='5.00'; $price=$budget['1000 to 1500 Euros']='4.50'; $price=$budget['1500 to 3000 Euros']='5.00'; $price=$budget['3000 to 5000 Euros']='5.50'; $price=$budget['Over 5000']='6.00'; $price=$budget['No Budget Set Yet']='4.00'; Also keep in mind that there is no 'price' field in the form. So no price is being posted to the processing page. I need to price to be worked out by what is posted from the budget field. This is the only thing I am stuck with without having to ask for any help, and these forums have been a saviour to me and an excellent place for learning. But sometimes I think you have to give in and just ask for help! lol Many thanks, DB |
|||||||