|
PHP - Update Query Not Executing, Trying To Subtract A Value In The Database
Im trying to execute an update sql query, but its not working. IDK if I got the right query tho
what i want to do is, when the form is submitted, subtract 15 from the value 'creditleft'. So say the value is 20 , i want to subtract 15 and get 5. Here is the sql query im using; $query2="update userdata set creditleft = - 15 where username = '".$_SESSION['login_name']."'"; mysql_query($query2, $link); I dont get any errors, just no output Similar TutorialsI am facing problem to execute query by assigning NULL value to a variable and then executing query.In MySQL DB four fields Mobile,landline, pincode,dob are set as integer and date(for dob) respectively.The default is set as NULL and NULL option is selected as yes.All these fields are not mandatory.The problem is that when I edit the form my keeping the value as empty in DB these are saved as 0, 0 , 0 & 0000-00-00 inspite of Null. I have tried everything but still the defect persist. Please help me to come out of the problem The code, I have used: <?php //require_once 'includes/config.php'; $dbusername = $_POST['email']; $dbfirstname = $_POST['first_name']; $dblastname = $_POST['last_name']; //$dbmobile_number = $_POST['mobile']; if (isset($_POST['mobile'])) { $dbmobile_number = $_POST['mobile']; } else { $dbmobile_number = "NULL"; } $dblandline_number = $_POST['landline']; $dbdob = $_POST['dob']; if(isset($_POST['is_email'])) { $dbSubscribe_Email_Alert = '1'; } else { $dbSubscribe_Email_Alert = '0'; } if(isset($_POST['is_sms'])) { $dbSubscribe_SMS = 0; } else { $dbSubscribe_SMS = 0; } $dbAddress_firstname = $_POST['shipping_first_name']; $dbAddress_lastname = $_POST['shipping_last_name']; $dbAddress = $_POST['shipping_address']; $dbcity = $_POST['shipping_city']; $dbpincode = $_POST['shipping_pincode']; $dbstate = $_POST['shipping_state']; $dbcountry = $_POST['shipping_country']; echo "Welcome".$dbusername; //if($_POST['btnSave']) //if ($_POST['btnSave']) //{ //echo "Inside query loop"; $connect = mysql_connect("localhost","root","") or die("Couldn't connect!"); mysql_select_db("salebees") or die ("Couldn't find DB"); //$query = mysql_query("SELECT * FROM users WHERE username='$username'"); $query = mysql_query("update users set firstname = '$dbfirstname', lastname = '$dblastname', mobile_number = '$dbmobile_number', landline_number = '$dblandline_number', dob = '$dbdob', Subscribe_Email_Alert = '$dbSubscribe_Email_Alert', Subscribe_SMS = '$dbSubscribe_SMS', Address_firstname = '$dbAddress_firstname', Address_lastname = '$dbAddress_lastname', Address = '$dbAddress', city = '$dbcity', pincode = '$dbpincode', state = '$dbstate', country = '$dbcountry' where username = '$dbusername' "); header("location:my_account.php"); //} //else //{ //die(); //} ?> Hello, I am trying to subtract the current time from a list of times in a mysql database. The first page inserts the time into mysql.
<?php
$var_Time = date("h:i:s");
$mysqli = new mysqli('localhost','root','blah','test');
if ($mysqli->connect_error) {
die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}
$insert_row = $mysqli->query("REPLACE INTO test_table (id, time) Values(NULL, '$var_Time')");
if($insert_row){
while (false);
}else{
die('Error : ('. $mysqli->errno .') '. $mysqli->error);
}
$mysqli->close();
?>
The second page is supposed to iterate through all of the times in the database and subtract the current time, but i cant figure out how to do it.
<?php
$mysqli = new mysqli('localhost','root','blah','test');
if ($mysqli->connect_error) {
die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}
$query = "SELECT * FROM `test_table`";
$result = $mysqli->query($query) or die($mysqli->error.__LINE__);
$var_set_time = stripslashes($row['time']);
$var_current_time = date("h:i:s");
$diff = strtotime( $var_current_time .' UTC' ) - strtotime( $var_set_time .' UTC');
echo $diff;
?>
Edited by Ch0cu3r, 09 October 2014 - 01:40 PM. Added code tags I have the following query where $userid is about 100,000 userids separated by commas. Code: [Select] <? $getpostid=mysql_query("SELECT DISTINCT post.username,post.threadid,thread.threadid,thread.forumid from post,thread WHERE post.username='$username' AND post.threadid=thread.threadid AND post.userid IN ($userid) AND thread.forumid IN ($forumids)") or die (mysql_error()); ?>can i just print all the usernames from above result separated by ';' without looping it multiple times? Could someone help me out how to do above in an efficient manner? hey guys im sure this is possible im wondering how a user can execute a mysql query by a click of a button but without the page reloading...thanks guys Hi, In some cases I need to execute a default query , so I am storing the SELECT statement in a variable like this and executing it: Code: [Select] $default_query="SELECT * from user where userid='$userid'"; $user_query=mysql_query($default_query); The above code returns an empty results. But if I execute it without the variable it works fine. Code: [Select] $user_query=mysql_query("SELECT * from user where userid='$userid'"); Am I syntatically wrong somewhere? What is the best way to display the next record on a web page, from a list of records, without executing the query over and over again each time you display the next record? For example, imagine my user has a list of messages displayed on a web page, and the user filters that list to display all messages with the title "Test", and let's say that returns 12 records. He then clicks on record 1 to read that message. Once he is done reading it, he then wants to read msg 2, and then msg 3 etc. He could hit the Back button in his browser and click on the next record from the list, but it would be more useful to just click on a link that says "Next" which displays the next message in the filtered list of messages (without having to go back to the list). A lot of web sites do this sort of thing, but what is the best way to do it? Do you have to run the original query again each time a new message is loaded, so you know which message is next, or can you somehow store the query to save the server from having to run the same query over and over again? If a user is viewing a list of pictures for example, they could be flicking through the pictures quite quickly, and I would not like to have the server running the same query over and over again every few seconds when it doesn't need to. Advice? (Thanks.) Hi, I know this has to be possible but I have been unable to get this to work properly. I have a page with two forms. The first form is a list of sources associated with a subject, with checkboxes, so that it is possible to remove these associations, like so: Code: [Select] $qs = "SELECT s.source_id, s.source_name from source s, source_subject ss, subject sb where s.source_id = ss.source_id and sb.subject_id = ss.subject_id and sb.subject_id = $subject_id order by source_name"; $rs = mysqli_query($dbc, $qs) or die ('Error querying database'); while($row = mysqli_fetch_array($rs)) { echo '<input type="checkbox" value="' . $row['source_id'] . '" name="markdelete[]">' . $row['source_name'] . '<br />' ; } The second form is a list of sources that are not associated with this subject, with checkboxes enabling the addition of more sources to this subject, like so: Code: [Select] $r = "SELECT source_id, source_name FROM source WHERE source_id NOT IN (select s.source_id from source s, source_subject ss where s.source_id = ss.source_id and ss.subject_id = '$subject_id')"; $r1 = mysqli_query($dbc, $r) or die ('Update Error: '.mysqli_error($dbc)); while ($row = mysqli_fetch_array($r1)) { echo '<input type="checkbox" id="source_id" name="source_id[]" ' ; echo 'value="'. $row['source_id'] .'"'; echo '> ' . $row['source_name'] . '<br />'; } The first form works fine... when the "remove" submit button is clicked, it successfully removes any selected sources and then shows the remaining sources still associated, and then automatically populates this source in the below list (form 2) as one that is not selected. On the second form, when adding a new source to the subject, it successfully enters the data into the database, and removes this source from the list of unselected sources, but it does not seem to re-populate in the first form. The data is correct on the tables, but the page needs to reload the first query... how do I get it to do this? I'd like to be able to make this page editable, and re-editable (in case someone makes a mistake) without having to go back and reload the entire page. Note, both forms call the page itself; not sure if this is part of it. Using this: Code: [Select] <form method="POST" action="<?php echo $_SERVER['PHP_SELF'].'?subject_id='.$subject_id ; ?>"> Does anyone have any ideas? Hi i have this simple update form and scrip but somehow it doesnt seem to be update the field on the database can someone help out please. The html form is the second form bellow where the action send to status_update.php HTML FORM Code: [Select] <?php include("../header.html"); ?> <?php include("header_news.html"); extract($_REQUEST,EXTR_SKIP); ?><?php /* print("sfilm_refnum = $sfilm_refnum<BR>"); print("sfilm_addr01 = $sfilm_addr01<BR>"); print("sfilm_postcode = $sfilm_postcode<BR>"); print("Film Client = $fclient<BR>"); */ ?> <form id="search" action="list.php" method="post" name="search"> <table width="780" border="0" cellspacing="0" cellpadding="4" bgcolor="#eeeeee"> <tr> <td align="right" width="140"></td> <td width="320"><span class="hofblack10"> </span> </td> <td align="center" width="100"><input type="hidden" name="lstart" value="<?php print("$lstart"); ?>" /><input type="hidden" name="lend" value="<?php print("$lend"); ?>" /><input type="hidden" name="lamount" value="<?php print("$lamount"); ?>" /></td> <td align="center" width="100"></td> <td align="right"></td> </tr> </table> </form> <table width="780" border="0" cellspacing="0" cellpadding="4" bgcolor="#4050c4"> <tr> <td width="60" class="hofwhite10">action</td> <td width="140"><span class="hofwhite14">DATE</span></td> <td width="80"><span class="hofwhite14">ID</span></td> <td><span class="hofwhite14">News Titile</span></td> <td width="100"><span class="hofwhite14">Status</span></td> </tr> <tr height="0"> <td bgcolor="white" width="60"></td> <td bgcolor="white" width="140" height="0"></td> <td bgcolor="white" width="80" height="0"></td> <td bgcolor="white" height="0"></td> <td bgcolor="white" width="100" height="0"></td> </tr> </table><table width="780" border="0" cellspacing="0" cellpadding="4"><tr> <td width="60"></td> <td width="80"></td> <td><a class="blueullrg" href="add.php">Add News</a></td> <td align="right" width="120"></td> </tr> <tr height="0"> <td width="60" height="0"></td> <td width="80" height="0"></td> <td height="0"></td> <td align="right" width="120" height="0"></td> </tr> </table> <?php //get the DB connection variables include("../../../includes/config.php"); //connect to DB $connection = @mysql_connect($db_address,$db_username,$db_password) or die("Couldn't CONNECT."); $db = @mysql_select_db($db_name, $connection) or die("Couldn't select DATABASE."); $query2="SELECT * FROM news WHERE !(news_status='deleted')"; $result2 = mysql_query($query2) or die("Couldn't execute QUERY - Select NEWS Qty"); $fqty = mysql_num_rows($result2); //SELECT or FIND the same USERNAME $query3="SELECT * FROM news WHERE !(news_status='deleted') ORDER BY news_id DESC"; $result3 = mysql_query($query3) or die("Couldn't execute QUERY - Select NEWS"); while ($row = mysql_fetch_array($result3)) { $news_id = $row['news_id']; $news_title = $row['news_title']; $news_story = $row['news_story']; $news_image = $row['news_image']; $news_image_caption = $row['news_image_caption']; $news_image_link = $row['news_image_link']; $news_date_day = $row['news_date_day']; $news_date_month = $row['news_date_month']; $news_date_year = $row['news_date_year']; $news_status = $row['news_status']; $news_website = $row['news_website']; $news_date_created = $row['news_date_created']; $news_date_modified = $row['news_date_modified']; ?> <table width="780" border="0" cellspacing="0" cellpadding="4" bgcolor="#eeeeee"> <tr> <td width="60"><span class="hofblack10"> <?php if($news_status=="deleted"){ print("<a class='hifblack10'>deleted</span>"); }ELSE{ print("<a class='blueul' href='edit.php?id=$news_id'>edit</a>"); } ?> </span></td> <td width="140"><span class="titlegrey12"> <?php if(!$news_date_day) { echo "00"; } else{ echo $news_date_day; } echo "/"; if(!$news_date_month) { echo "00"; }else{ echo $news_date_month; } echo "/"; if(!$news_date_year) { echo "0000"; }else{ echo $news_date_year; } ?> </span></td> <td width="80"><span class="titlegrey12"><?php print("$news_id"); ?></span> </td> <td><?php if($news_status=="deleted") { print("<class='hofblack10'>$news_title</span>"); }ELSE{ print("<a class='blueul' href='edit.php?id=$news_id'>$news_title</a>"); } ?></td> <td width="100"> <form id="list_update" action="status_update.php" method="post" name="list_update"> <select name="newnstatus" size="1"> <option <?php if($row['news_status'] == "") { print("selected"); } ?> selected="selected" value="">Status...</option> <option <?php if($row['news_status'] == "on") { print("selected"); } ?> value="on">On</option> <option <?php if($row['news_status'] == "off") { print("selected"); } ?> value="off">Off</option> <option <?php if($row['news_status'] == "deleted") { print("selected"); } ?> value="deleted">Delete</option> </select> <input type="hidden" name="nstatus" value="<?php echo $row[news_status]; ?>" /> <input type="hidden" name="id" value="<?php echo $row[news_id]; ?>" /> <input type="submit" name="update" value="update" /> </form> </td> </tr> <tr height="0"> <td bgcolor="white" width="60"></td> <td bgcolor="white" width="140" height="0"></td> <td bgcolor="white" width="80" height="0"></td> <td bgcolor="white" height="0"></td> <td bgcolor="white" width="100" height="0"></td> </tr> </table> <?php } mysql_close($connection);//}?> <table width="780" border="0" cellspacing="0" cellpadding="4"> <tr> <td width="60"></td> <td width="80"></td> <td><a class="blueullrg" href="add.php">Add News</a></td> <td align="right" width="120"></td> </tr> </table><?php // include("list_navigation.html"); ?> <?php include("../footer.html"); ?> </div></body></html> The action script php Code: [Select] <?php /* echo "fstatus: ".$fstatus."<BR>"; echo "id: ".$id."<BR>"; echo "fclient: ".$fclient."<BR>"; echo "newfstatus: ".$newfstatus."<BR>";*/ //set the date of agreement $timestamp = date('l jS \of F Y h:i:s A'); //get the DB connection variables include("../../../includes/config.php"); //connect to DB $connection = @mysql_connect($db_address,$db_username,$db_password) or die("Couldn't CONNECT."); $db = @mysql_select_db($db_name, $connection) or die("Couldn't select FILMS DATABASE."); // All appears well, so enter into database $query= "UPDATE news SET news_status = '$newnstatus' WHERE news_id='$id'"; $result = mysql_query($query) or die("could not execute query - Update FILMS Record to DB"); //setup an email to the Admin @ hof, w/o attachment $emailto="xx@xxx.co.uk"; $emailfrom="no-reply@xxxx.co.uk"; $emailsubject="xx Record Updated"; $emailmessage="Hello Registrar\n\n"; $emailmessage.="News ID: ".$id."\n"; $emailmessage.="Updated on: ".$timestamp."\n\n"; $emailmessage.="Status was: ".$nstatus."\n"; $emailmessage.="Status now: ".$newfnstatus."\n"; $emailmessage.="Thank you,\n\n"; $emailmessage.="Web Site ROBOT\n"; $emailmessage.="(Administrator)\n"; $emailmessage.="xxx.co.uk | xxx.biz\n"; $emailmessage.="----------------------------------------------\n"; $emailmessage.="e. http://www.xxx.co.uk/contact.php\n"; $emailmessage.="w. http://www.xxx.co.uk\n"; $emailheader="From: xxx.co.uk<$emailfrom>"; $emailheader .= 'Cc: xxx@xxx.co.uk'."\r\n"; $emailheader .= 'Bcc: xxx@xxxxx.co.uk'."\r\n"; $ok=mail($emailto,$emailsubject,$emailmessage,$emailheader); mysql_close($connection); if ($ok) { header("Location: list.php"); /* Redirect browser */ exit; } else { $errmsg="There was a problem, please try later or telephone us direct."; $errsta="1"; include("edit_error.html"); //echo "<p>Mail could not be sent. Sorry!</p>"; exit; } ?> Thanks in advance Hello all,
Based on the suggestion of you wonderful folks here, I went away for a few days (to learn about PDO and Prepared Statements) in order to replace the MySQLi commands in my code. That's gone pretty well thus far...with me having learnt and successfully replaced most of my "bad" code with elegant, SQL-Injection-proof code (or so I hope).
The one-and-only problem I'm having (for now at least) is that I'm having trouble understanding how to execute an UPDATE query within the resultset of a SELECT query (using PDO and prepared statements, of course).
Let me explain (my scenario), and since a picture speaks a thousand words I've also inlcuded a screenshot to show you guys my setup:
In my table I have two columns (which are essentially flags i.e. Y/N), one for "items alreay purchased" and the other for "items to be purchased later". The first flag, if/when set ON (Y) will highlight row(s) in red...and the second flag will highlight row(s) in blue (when set ON).
I initially had four buttons, two each for setting the flags/columns to "Y", and another two to reverse the columns/flags to "N". That was when I had my delete functionality as a separate operation on a separate tab/list item, and that was fine.
Now that I've realized I can include both operations (update and delete) on just the one tab, I've also figured it would be better to pare down those four buttons (into just two), and set them up as a toggle feature i.e. if the value is currently "Y" then the button will set it to "N", and vice versa.
So, looking at my attached picture, if a person selects (using the checkboxes) the first four rows and clicks the first button (labeled "Toggle selected items as Purchased/Not Purchased") then the following must happen:
1. The purchased_flag for rows # 2 and 4 must be switched OFF (set to N)...so they will no longer be highlighted in red.
2. The purchased_flag for row # 3 must be switched ON (set to Y)...so that row will now be highlighted in red.
3. Nothing must be done to rows # 1 and 5 since: a) row 5 was not selected/checked to begin with, and b) row # 1 has its purchase_later_flag set ON (to Y), so it must be skipped over.
Looking at my code below, I'm guessing (and here's where I need the help) that there's something wrong in the code within the section that says "/*** loop through the results/collection of checked items ***/". I've probably made it more complex than it should be, and that's due to the fact that I have no idea what I'm doing (or rather, how I should be doing it), and this has driven me insane for the last 2 days...which prompted me to "throw in the towel" and seek the help of you very helpful and intellegent folks. BTW, I am a newbie at this, so if I could be provided the exact code, that would be most wonderful, and much highly appreciated.
Thanks to you folks, I'm feeling real good (with a great sense of achievement) after having come here and got the great advice to learn PDO and prepared statements.
Just this one nasty little hurdle is stopping me from getting to "end-of-job" on my very first WebApp. BTW, sorry about the long post...this is the best/only way I could clearly explaing my situation.
Cheers guys!
case "update-delete":
if(isset($_POST['highlight-purchased']))
{
// ****** Setup customized query to obtain only items that are checked ******
$sql = "SELECT * FROM shoplist WHERE";
for($i=0; $i < count($_POST['checkboxes']); $i++)
{
$sql=$sql . " idnumber=" . $_POST['checkboxes'][$i] . " or";
}
$sql= rtrim($sql, "or");
$statement = $conn->prepare($sql);
$statement->execute();
// *** fetch results for all checked items (1st query) *** //
$result = $statement->fetchAll();
$statement->closeCursor();
// Setup query that will change the purchased flag to "N", if it's currently set to "Y"
$sqlSetToN = "UPDATE shoplist SET purchased = 'N' WHERE purchased = 'Y'";
// Setup query that will change the purchased flag to "Y", if it's currently set to "N", "", or NULL
$sqlSetToY = "UPDATE shoplist SET purchased = 'Y' WHERE purchased = 'N' OR purchased = '' OR purchased IS NULL";
$statementSetToN = $conn->prepare($sqlSetToN);
$statementSetToY = $conn->prepare($sqlSetToY);
/*** loop through the results/collection of checked items ***/
foreach($result as $row)
{
if ($row["purchased"] != "Y")
{
// *** fetch one row at a time pertaining to the 2nd query *** //
$resultSetToY = $statementSetToY->fetch();
foreach($resultSetToY as $row)
{
$statementSetToY->execute();
}
}
else
{
// *** fetch one row at a time pertaining to the 2nd query *** //
$resultSetToN = $statementSetToN->fetch();
foreach($resultSetToN as $row)
{
$statementSetToN->execute();
}
}
}
break;
}
CRUD Queston.png 20.68KB
0 downloads
I created this code to upload a member's main picture on his member page on website. I'll only include the query part of the code since that's what is relevant to my problem. The idea is basically to upload a new picture onto the database if no picture already exists for that member and display the picture on the page. If a picture already exists, then the script replaces the old picture with the new one upon upload. But for whatever reason I don't understand, when I try to replace the old pic, it gets inserted in a new row on the database instead of replacing the old row, and the new pic gets displayed on the web page alongside the old. Code: [Select] $query = "SELECT username FROM images WHERE member_id = '".$_SESSION['id']."' AND image_cartegory = 'main'"; $result = @mysql_query($query); $num = @mysql_num_rows($result); if ($num> 0) { //Update the image $update = mysql_query("UPDATE images SET image = '" . $image['name'] . "' WHERE member_id = '".$_SESSION['id']."' AND image_cartegory = 'main'"); $_SESSION['error'] = "File updated successfully."; //really should be session success message. header("Location: member.php"); exit; } else { // NOTE: This is where a lot of people make mistakes. // We are *not* putting the image into the database; we are putting a reference to the file's location on the server $sql = "insert into images (member_id, image_cartegory, image_date, image) values ('{$_SESSION['id']}', 'main', NOW(), '" . $image['name'] . "')"; $result = mysql_query($sql) or die ("Could not insert data into DB: " . mysql_error()); $_SESSION['error'] = "File uploaded succussfully."; //really should be session success message. header("Location: member.php"); } So can anyone tell me what the problem is? Could the fact that my insert script actually uploads the image onto a folder on my server and only stores the path name in the database have anything to contribute to the mixup? Appreciate your responses in advance. After a ton of trial and error and searching and searching for the answer, I have finally resorted to posting to ask for help. I am very new to php so please bear with me.
I have been building an employee training database at work. Basically to make sure that for example we have the current drivers liscense on file for those employees who drive and have gone through the required company training. I initially built an access database with a frontend that does everything needed. However, the company is very decentralized and we have several users who would regularly need access to the information when they are offsite. The database does work offsite however it is extremely slow. After purchasing a buffalo linkstation, I discovered it has the capability for a mysql server.
I have been able to migrate the data over to the mysql server and build a simple read only php form but cant get the changes you make to update back into the database.
I found a method of fetching the data using json that is based on a drop down selection, upon change, it goes and gets the correct data.
<script type="text/javascript">
function insertResults(json){
$("#Separated").val(json["Separated"]);
$("#id").val(json["id"]);
$("#Employee_ID").val(json["Employee_ID"]);
$("#Last_Name").val(json["Last_Name"]);
$("#First_Name").val(json["First_Name"]);
$("#Date_Of_Birth").val(json["Date_Of_Birth"]);
$("#Original_Hire_Date").val(json["Original_Hire_Date"]);
$("#Current_Hire_Date").val(json["Current_Hire_Date"]);
However, lets say you correct a mis-spelling in their name, I cant seem to write back to the db. I have a submit button that uses this code
<a href="InputFormSubmit.php?id=<?php echo $rows['id']; ?>"><input type="submit" name="submit2" value="Submit"></a>But I guess I cant seem to #1 completely grasp how to pass on the selected information using POST (correct?) to the next form "InputFormSubmit" and #2 correctly build the query for it? Code for it below:
<?php
include_once('db.php');
$Employee_ID = $_POST['Employee_ID'];
$Last_Name = $_POST['Last_Name'];
$First_Name = $_POST['First_Name'];
$id=$_POST['id'];
echo "$id";
echo "$Employee_ID";
// update data in mysql database
$con=mysql_connect("localhost","usernamegoeshere","passwordgoeshere","databasenamegoeshere");
// Check connection
mysql_query("UPDATE tbl_Employee_Master SET Last_Name='$Last_Name', First_Name='$First_Name' WHERE id='$id'");
mysql_close($con);
?>
The proper EmployeeID is passed from the previous form into this one, but the query dies I guess.
What I would like to do is pass the updated info into InputFormSubmit and then run the query and redirect back to the search page. Potentially passing back the employee ID and setting the drop down to that value so you would see the employees file you just updated again.
Super long post, thanks for any direction anyone can give me, Im sure my code looks awful from trying a thousand different things.
Thanks again.
Currently I show a list of users and their information. You can than click that specific user and it shows you their information to edit. My problem is it's not updating the query with the new input information.
Here is the page that displays the registration information:
<?php
$con=mysqli_connect('localhost', 'root', '');
/* check connection */
if (mysqli_connect_errno($con)) {
trigger_error('Database connection failed: ' . mysqli_connect_error(), E_USER_ERROR);
}
$query = "SELECT * FROM `bencobricks` . `users`";
$result = mysqli_query($con, $query) or trigger_error("Query Failed! SQL: $query - Error: ". mysqli_error($con), E_USER_ERROR);
?>
<table width="1000" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="1000" border="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="4"><strong>List data from mysql </strong> </td>
</tr>
<tr>
<td align="center"><strong>Username</strong></td>
<td align="center"><strong>Email</strong></td>
<td align="center"><strong>Membership</strong></td>
<td align="center"><strong>First Name</strong></td>
<td align="center"><strong>Last Name</strong></td>
<td align="center"><strong>Gender</strong></td>
<td align="center"><strong>Birthdate</strong></td>
<td align="center"><strong>Sets</strong></td>
<td align="center"><strong>Checkbox</strong></td>
<td align="center"><strong>Admin Flag</strong></td>
</tr>
<?php
while($rows=mysqli_fetch_array($result)){
?>
<tr>
<td><?php echo $rows['username']; ?></td>
<td><?php echo $rows['email']; ?></td>
<td><?php echo $rows['membership']; ?></td>
<td><?php echo $rows['firstName']; ?></td>
<td><?php echo $rows['lastName']; ?></td>
<td><?php echo $rows['gender']; ?></td>
<td><?php echo $rows['dateOfBirth']; ?></td>
<td><?php echo $rows['sets']; ?></td>
<td><?php echo $rows['checkbox']; ?></td>
<td><?php echo $rows['adminFlag']; ?></td>
<td align="center"><a href="update.php?userID=<?php echo $rows['userID']; ?>">update</a></td>
</tr>
<?php
}
?>
</table>
</td>
</tr>
</table>
<?php
mysqli_close($con);
?>
Page that displays specific user for updating:
<?php
$con=mysqli_connect('localhost', 'root', '');
/* check connection */
if (mysqli_connect_errno($con)) {
trigger_error('Database connection failed: ' . mysqli_connect_error(), E_USER_ERROR);
}
$id=$_GET['userID'];
$query = "SELECT * FROM `bencobricks` . `users` WHERE `userID` = '$id'";
$result = mysqli_query($con, $query) or trigger_error("Query Failed! SQL: $query - Error: ". mysqli_error($con), E_USER_ERROR);
$rows=mysqli_fetch_array($result);
?>
<table width="1000" border="0" cellspacing="1" cellpadding="0">
<tr>
<form name="form1" method="post" action="update_ac.php">
<td>
<table width="100%" border="0" cellspacing="1" cellpadding="0">
<tr>
<td> </td>
<td colspan="3"><strong>Update data in mysql</strong> </td>
</tr>
<tr>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
</tr>
<tr>
<td align="center"><strong>Username</strong></td>
<td align="center"><strong>Email</strong></td>
<td align="center"><strong>Membership</strong></td>
<td align="center"><strong>First Name</strong></td>
<td align="center"><strong>Last Name</strong></td>
<td align="center"><strong>Gender</strong></td>
<td align="center"><strong>Birthdate</strong></td>
<td align="center"><strong>Sets</strong></td>
<td align="center"><strong>Checkbox</strong></td>
<td align="center"><strong>Admin Flag</strong></td>
</tr>
<tr>
<td align="center">
<input name="username" type="text" id="username" value="<?php echo $rows['username'] ;
echo (isset($_POST['username']) ? $_POST['username'] : ''); ?>">
</td>
<td align="center">
<input name="email" type="text" id="email" value="<?php echo $rows['email']; ?>" size="15">
</td>
<td>
<input name="membership" type="text" id="membership" value="<?php echo $rows['membership']; ?>" size="15">
</td>
<td>
<input name="firstName" type="text" id="firstName" value="<?php echo $rows['firstName']; ?>" size="15">
</td>
<td>
<input name="lastName" type="text" id="lastName" value="<?php echo $rows['lastName']; ?>" size="15">
</td>
<td>
<input name="gender" type="text" id="gender" value="<?php echo $rows['gender']; ?>" size="15">
</td>
<td>
<input name="dateOfBirth" type="text" id="dateOfBirth" value="<?php echo $rows['dateOfBirth']; ?>" size="15">
</td>
<td>
<input name="sets" type="text" id="sets" value="<?php echo $rows['sets']; ?>" size="15">
</td>
<td>
<input name="checkbox" type="text" id="checkbox" value="<?php echo $rows['checkbox']; ?>" size="15">
</td>
<td>
<input name="adminFlag" type="text" id="adminFlag" value="<?php echo $rows['adminFlag']; ?>" size="15">
</td>
</tr>
<tr>
<td> </td>
<td>
<input name="id" type="hidden" id="id" value="<?php echo $rows['userID']; ?>">
</td>
<td align="center">
<input type="submit" name="Submit" value="Submit">
</td>
<td> </td>
</tr>
</table>
</td>
</form>
</tr>
</table>
<?php
// close connection
mysqli_close($con);
?>
Finally the update query:
<?php
function test_input($data){
$data = trim($data);
$data = stripslashes($data);
$data = htmlspecialchars($data);
return $data;
}
$con=mysqli_connect('localhost', 'root', '');
/* check connection */
if (mysqli_connect_errno($con)) {
trigger_error('Database connection failed: ' . mysqli_connect_error(), E_USER_ERROR);
}
// update data in mysql database
if (empty($_POST["username"]))
{/* can never be empty due to design */}
else
{$username= test_input($_POST['username']);}
$id = 'id';
$query = "UPDATE `bencobricks`. `users` SET `username` = '$username', `password` = 'NULL', `email` = 'email', `membership` = 'membership', `firstName` = 'firstName', `lastName` = 'lastName',
`gender` = 'gender', `dateOfBirth` = 'dateOfBirth', `date` = 'NULL', `sets` = 'sets', `checkbox` = 'checkbox', `adminFlag` = 'adminFlag' WHERE `userID` = '$id'";
$result = mysqli_query($con, $query) or trigger_error("Query Failed! SQL: $query - Error: ". mysqli_error($con), E_USER_ERROR);
// if successfully updated.
if($result){
echo "Successful";
echo "<BR>";
echo "<a href='editUser.php'>View Users Page</a>";
}
else {
echo "ERROR";
}
?>
Attached Files
1.PNG 19.06KB
0 downloads
2.PNG 47.07KB
0 downloadsokay, what i have is a page part of my ACP, and what i am trying to do is list all images from database, the list of images will have a "Checkbox" beside it. the checkbox is for setting where the image will be displayed. reason for the checkbox is cuz the images are only being displayed on 2 separate pages. basicly the whole list of images are in 1 form so when submit is activated its suppose to update the image list where checkboxs have been changed. hope i explained this clearly since im sick haha. but heres what i got for code Code: [Select] <form method="post"> <input type="submit" name="submit" value="Submit" /><br /> <?php $result = mysql_query("SELECT * FROM items") or die("Query Failed: ".mysql_error()); while($row = mysql_fetch_array($result)){ list($id, $item_info, $item_img, $price, $sale, $location) = $row; if($location == 'home'){ $set_checked = 'checked="checked"'; }else{ $set_checked = ''; } if(isset($_POST['location'])){ foreach($_POST['location'] as $value){ $id = $_POST['id']; $insert = mysql_query("UPDATE items SET location='$value' WHERE id='$id'") or die('Insert Error: '.mysql_error()); } } echo '<img src="../images/items/'.$item_img.'" width="100" /> Home Page Slide Show: <input type="checkbox" id='.$id.' value="home" name="location[]" '.$set_checked.' /><br />'; } ?> <input type="hidden" name="hdnLine" value="<? echo $count ?>" /> <input type="submit" name="submit" value="Submit" /> </form> i also added a pic of how it looks like lol Hi This does not make sense to me, I hope someone can tell me why this query don't work: Code: [Select] safe_query("UPDATE ".PREFIX."cup_challenges SET reply_date='$postdate', $select_map $select_date challenged_info='".$_POST['info']."', status='2' WHERE chalID='".$_GET['challID']."'");; and this query does work: Code: [Select] safe_query("UPDATE ".PREFIX."cup_challenges SET reply_date='$postdate', map1='".$_POST['map1']."', map2='".$_POST['map2']."', map3='".$_POST['map3']."', date1='".$_POST['date1']."', date2='".$_POST['date2']."', date3='".$_POST['date3']."', date4='".$_POST['date4']."', challenged_info='".$_POST['info']."', status='2' WHERE chalID='".$_GET['challID']."'"); Variables in $select_map and $select_date makes the query fail but when I copy/paste this (bold) in query it works: $select_map = map1='".$_POST['map1']."', map2='".$_POST['map2']."', map3='".$_POST['map3']."', $select date = date1='".$_POST['date1']."', date2='".$_POST['date2']."', date3='".$_POST['date3']."', date4='".$_POST['date4']."', am I using the variable in query incorrectly? Hi all, Firstly here is my code Code: [Select] <?php if (isset($_POST['update'])) { include('../config.php'); $name = $_POST['name']; $email = $_POST['email']; $id = $_POST['id']; $uquery=mysql_query("UPDATE customers SET name='$name' AND email='$email' WHERE id='$id'"); if($uquery) { echo mysql_error(); } else { echo mysql_error(); } } ?> <link href="../styles/clientbox.css" rel="stylesheet" type="text/css"> <link href="cancelform.css" rel="stylesheet" type="text/css" /> <link href="../styles/form_dark.css" rel="stylesheet" type="text/css" /> <body><br> <h3>My Details</h3> <div class="text"> Please keep your details updated below, for security reasons you cannot change your password.<br> <?php $id = $_GET['id']; include('../config.php'); $query=mysql_query("SELECT * FROM customers WHERE id='$id'"); while($row = mysql_fetch_assoc($query)) { $name = $row['name']; $email = $row['email']; } ?> <form class="dark" action="" method="post"> <ol> <li> <fieldset> <legend>My Details</legend> <ol> <li> <label for="name">Account Holder</label> <input type="text" id="name" name="name" value="<?php echo $name;?>" /> </li> <li> <label for="email">Contact/Login Email Address</label> <input type="text" id="email" name="email" value="<?php echo $email;?>" /> <input type="hidden" id="id" name="id" value="<?php echo $id;?>" /> </li> </ol> </fieldset> </li> </ol> <p style="text-align:right;"> <input type="reset" value="CANCEL" /> <input type="submit" value="UPDATE" name="update" /> </p> </form> Basically what is happening is that whatever you enter into the name box returns a 0 if its text or 1 if numbers are entered as opposed to storing the value inputed, if the email address is changed then nothing happens at all. Can someone see if I have made a noob mistake somewhere? The name and email fields are both varchar(256) in my database Thanks Hi, i am currently designing a new website and am testing a few different compnents of it. One part gathers two variables of the url using the GET method and selects the relevant data from the database. It then performs an UPDATE of a field called balance before INSERTING a new record. Everything works and gets inserted except for the UPDATE. Here is the code Code: [Select] <?php include ("connect.php"); $id = ""; $offerid = ""; $id = $_GET['user_id']; $offerid = $_GET['offer_id']; $sql_user = mysql_query("SELECT * FROM user_info WHERE id = $id"); $sql_offer = mysql_query("SELECT * FROM offers WHERE offerid = $offerid"); $balance += $payout; mysql_query("UPDATE user_info SET balance = $balance WHERE id = $id"); mysql_query ("INSERT INTO offer_credited (id, offerid, date_credited, payout_to_user) VALUES ('$id', '$offerid', CURDATE(), '$payout')") or die(mysql_error()); echo "Your Offer has been recorded and Updated" ?> The balance field is a float that i want to increase by the amount of the payout variable which is from the offers table. However, the balance never gets updated and remains at 0. Any ideas what it could be, i am thinking it is something to do with, Code: [Select] $balance += $payout; But i can't think of what else to do. Hopefully this isn't too confusing. Thanks for the help I would like to edit a record that I already have in my database but my query is not working for some reason. Hope someone can help. The errors i get are Notice: Undefined variable: first_name in C:\chemicaluser\www\editinfo.php on line 40 Notice: Undefined variable: last_name in C:\chemicaluser\www\editinfo.php on line 40 Notice: Undefined variable: log_number in C:\chemicaluser\www\editinfo.php on line 40 .... line 40 = my query $query = "UPDATE table1 SET first_name = '$first_name', last_name = '$last_name' WHERE log_number = '$log_number' "; Very simple database, 1 table w/ 2 fields table name = table1 fields = first_name, last_name my php file Code: [Select] <?php require ('/menu.php'); require_once('sqlconnect.php'); if (isset($_GET['log_number']) && isset($_GET['first_name']) && isset($_GET['last_name']) ) { $log_number = $_GET['log_number']; $first_name = $_GET['first_name']; $last_name = $_GET['last_name']; } else if (isset($_POST['log_number']) && isset($_POST['first_name']) && isset($_POST['last_name']) ) { $log_number = $_POST['log_number']; $first_name = $_POST['first_name']; $last_name = $_POST['last_name']; } if (isset($_POST['submit'])) { $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME); $query = "UPDATE table1 SET first_name = '$first_name', last_name = '$last_name' WHERE log_number = '$log_number' "; $result = mysqli_query($dbc, $query) or die ('Error querying database'); mysqli_close($dbc); } ?> my form Code: [Select] <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>"> <label for="first_name">First name</label> <input type="text" id="first_name" name="first_name" value="<?php if (!empty($first_name)) echo $first_name; ?>"/> <label for="last_name">Last Name</label> <input type="text" id="last_name" name="last_name" value="<?php if (!empty($last_name)) echo $last_name; ?>" /> <input type="submit" value="Edit Information" name="submit" /> </form> Hi Guys, I'm hoping someone can find what I clearly cant see, I have a page meant to update database records, the page will load and populate with the existing data and allow 5 out of the 7 fields to be edited, however when editing any of those 5, the remaining 2 blank out and can't be filled in again. Im at a bit of loss and have been staring at it for a bit too long i think, hoping a fresh set of eyes can help. Here's the page (the fields I'm having issues with are inv1/inv2: <?php $page_title = 'Edit a Cart'; include ('style.html'); // Check for a valid ID, through GET or POST. if ( (isset($_GET['id'])) ) { // Accessed through active_search.php $id = $_GET['id']; } elseif ( (isset($_POST['id'])) ) { // Form has been submitted. $id = $_POST['id']; } else { // No valid ID, kill the script. echo '<div id="title">Page Error</div> <p class="error">This page has been accessed in error.</p><p><br /><br /></p>'; exit(); } require_once ('mysql_connect.php'); // Connect to the db. // Check if the form has been submitted. if (isset($_POST['submitted'])) { $errors = array(); // Initialize error array. // Check for a PO. { $po = trim($_POST['po']); } // Check for a Master Serial. { $sr = trim($_POST['serial']); } // Check for a Charger Serial. { $ch = trim($_POST['charger']); } // Check for a Battery Serial. { $bt1 = trim($_POST['battery1']); } // Check for a Battery Serial. { $bt2 = trim($_POST['battery2']); } // Check for a Inverter Serial. { $in1 = trim($_POST['inv1']); } // Check for a 2nd Inverter Serial. { $in2 = trim($_POST['inv2']); } // Check for a Radio Serial. { $abc = trim($_POST['abc']); } if (empty($errors)) { // If everything's OK. // Test for unique master serial. $query = "SELECT serial FROM serials WHERE serial='$id'"; $result = mysql_query($query); if (mysql_num_rows($result) == 1) { // Make the query. $query = "UPDATE serials SET po='$po', serial='$sr', charger='$ch', batt1='$bt1', batt2='$bt2', inv1='$in1', inv2='$in2', abc='$abc' WHERE serial='$id'"; $result = @mysql_query ($query); // Run the query. if (mysql_affected_rows() == 1) { // If it ran OK. // Print a message. echo '<div id="title">Success!</div> <p>The node has been edited.<a href="javascript:window.close();">Close</a></p><p><br /><br /></p>'; } else { // If it did not run OK. echo '<div id="title">System Error</div> <p class="error">The node could not be edited due to a system error. We apologize for any inconvenience.</p>'; // Public message. echo '<p>' . mysql_error() . '<br /><br />Query: ' . $query . '</p>'; // Debugging message. exit(); } } else { // Already registered. echo '<div id="title">Error!</div> <p class="error">The Serial has already been registered.</p>'; } } else { // Report the errors. echo '<div id="title">Error!</div> <p class="error">The following error(s) occurred:<br />'; foreach ($errors as $msg) { // Print each error. echo " - $msg<br />\n"; } echo '</p><p>Please try again.</p><p><br /></p>'; } // End of if (empty($errors)) IF. } // End of submit conditional. // Always show the form. // Retrieve the user's information. $query = "SELECT * FROM serials WHERE serial='$id'"; $result = @mysql_query ($query); // Run the query. if (mysql_num_rows($result) == 1) { // Valid ID, show the form. // Get the user's information. $row = mysql_fetch_array ($result); // Create the form. echo '<div id="title">Edit a Cart</div> <form action="serial_edit.php" method="post"> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td>P.O #:</td><td><input type="text" name="po" size="15" maxlength="15" value="' . $row['po'] . '" /></td> </tr> <tr> <td>Cart Serial:</td><td><input type="text" name="serial" size="15" maxlength="30" value="' . $row['serial'] . '" /></td> </tr> <tr> <td>Charger:</td><td><input type="text" name="charger" size="20" maxlength="40" value="' . $row['charger'] . '" /> </td> </tr> <tr> <td>Battery 1:</td><td><input type="text" name="battery1" size="20" maxlength="40" value="' . $row['batt1'] . '" /> </td> </tr> <tr> <td>Battery 2:</td><td><input type="text" name="battery2" size="20" maxlength="40" value="' . $row['batt2'] . '" /> </td> </tr> <tr> <td>Inverter 1:</td><td><input type="text" name="inverter1" size="20" maxlength="40" value="' . $row['inv1'] . '" /> </td> </tr> <tr> <td>Inverter 2:</td><td><input type="text" name="inverter2" size="20" maxlength="40" value="' . $row['inv2'] . '" /> </td> </tr> <tr> <td>ABC:</td><td><input type="text" name="abc" size="20" maxlength="40" value="' . $row['abc'] . '" /> </td> </tr> <tr> <td><input type="submit" name="submit" value="Submit" /><input type="hidden" name="submitted" value="TRUE" /></td> <td><input type="hidden" name="id" value="' . $id . '" /></td></tr> </form>'; } else { // Not a valid ID. echo '<div id="title">Page Error</div> <p class="error">This page has been accessed in error.</p><p><br /><br /></p>'; } mysql_close(); // Close the database connection. ?> I have a value that in parts of my code i need to update. Its a number value, stored as TEXT. which i need to add numbers to it, at the moment I am using this to get the current value: Code: [Select] $sql2="SELECT * FROM weekly LIMIT 1"; $result2=mysql_query($sql2); $row2 = mysql_fetch_array( $result2 ); $rollingjackpot = $row2['rollingjackpot']; then i am adding the value to the varible, then using UPDATE to update it again, cant this be done in a simple query? something like: Code: [Select] $query = mysql_query("UPDATE weekly SET `rollingjackpot` + $amount WHERE `id` = '1'") or die(mysql_error()); Hi all, I'm trying to make a query that allows the user to edit the name of a entry in SQL. I believe I have the corect Syntax but I'm not sure if I have the code set up correctly. I am using 2 queries when I think it is possible with one. The first query is used to Pint out results for a certain year and the second to update the names. Code: [Select] $desiredYear = $_POST['year']; $nameEdit = $_POST['edit']; $origName = $_POST['orig']; echo ' The year you have chosen is '.$desiredYear; $describeQuery = "SELECT ID, Name, (SELECT SUM(SalesVolume) as SalesVolume FROM MonthlySales WHERE ProductCode=Products.ID AND Year = '$desiredYear') AS num_sales FROM Products"; $editQuery = "UPDATE Products SET Name = '$nameEdit' WHERE Name ='$origName'"; $results = sqlsrv_query($conn, $describeQuery); echo '<table border="1" BORDERCOLOR=Black>'; echo '<tr><th bgcolor = "LightBlue">Name</th><th bgcolor = "LightBlue" >ID</th> <th bgcolor = "LightBlue" >Sales</th></tr>'; while($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) { echo '<tr>'; echo '<td >' .$row['Name'].'</td>'; echo '<td>' .$row['ID'].'</td>'; echo '<td>' .$row['num_sales'].'</td>'; echo '</tr>'; } echo '</table>'; sqlsrv_close($conn); Can somebody help me with this? Thank you |
|||||||