|
PHP - Update Values From Col1 To Col2
Friends, I have the following table:
SELECT * FROM tbl1; +----+-------+-------+ | id | col_1 | col_2 | +----+-------+-------+ | 1 | 1 | NULL | | 2 | 2 | 2 | | 3 | NULL | 3 | | 4 | 4 | NULL | +----+-------+-------+ 4 ROWS IN SET (0.00 sec)So, if I wanna update only the null values of col_2 with these from col_1 or vice versa I'm running the following update statement with mysql ifnull() func. mysql> UPDATE test.tbl1 t1 SET t1.col_2 = ifnull(t1.col_2, t1.col_1), t1.col_1= ifnull(t1.col_1, t1.col_2); Query OK, 3 ROWS affected (0.06 sec) ROWS matched: 4 Changed: 3 Warnings: 0Is there a way to use LEFT JOIN to get all null values from every columns and rows, then to update the columns in the same way? I notice that the sub-query failed for me within an update statement. Similar Tutorials
Table Issue - Multiple Location Values For User Pushes Values Out Of Row Instead Of Wrapping In Cell
Hi all, I'm trying to figure out how to update values I have in an array. I'm a bit stuck and hope someone can advise. I've read about all sorts of bits but I'm stuck, I dont really understand associative so thought I'd post an example of my code for someone to hopefully assist.. so, if I have a table $fees, which is filled from a select, for example.. select * from `cases_fees`. "contains fields for id, type, amount and others now I want to do is foreach through that table and update a field called `amount`. but I cant understand the foreach >= as key part. what is a key and what isn't ? (I know what the keys of the db table arem but that's not the same eh?) I'm fine with normal foreach, but I cant update back to the array so I need to find out how to be able to set valus back into $fees[`amount`] hope someone can point me in the right direction please... Ted Hi guys, i have a select box which has been populated with values from the database however i would like to be able to select the value from the select box and change the qty. Code: [Select] <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>stock manager</title> </head> <body> <?php $stockqty =&$_POST['stock_qty1']; ?> <center> <table> <td> <table> <td> <form action='stockview.php' method='POST'> Please Enter a Stock Name and Stock Value <table> <tr> <td> Stock Name: </td> <td> <input name="stock_name" type="text" /><BR /> </td> </tr> <tr> <td> Stock Qty: </td> <td> <input name="stock_qty" type="text" /> </td> </tr> </table> <input name="submit" type="submit" value="Add New Stock Items" /> </form> </td> </table> </td> <td> <table> <td> <form action='stockmanager.php' method='POST' enctype="multipart/form-data"> Please Select from the list the item you wish to update <table> <tr> <td> Stock Name: </td> <td> <?php $connect = mysql_connect("localhost","root", "") or die ("Couldn't Connect!"); mysql_select_db("stock", $connect) or die("Couldn't find db"); // select database $query=("SELECT id, stockname FROM stocks"); $result = mysql_query ($query); echo "<select name=stock value=''>Edit Stock QTY</option>"; while($nt=mysql_fetch_array($result)) { //Array or records stored in $nt echo "<option value=$nt[id]>$nt[stockname]</option>"; /* Option values are added by looping through the array */ } $queryreg = mysql_query("SELECT * FROM stocks WHERE stockqty='$stockqty'"); $numrows = mysql_num_rows($queryreg); $update = mysql_query("UPDATE stocks SET stockqty='$stockqty' WHERE stockname = '$stockname'"); echo("Its updated"); ?> </td> </tr> <tr> <td> Stock Qty: </td> <td> <input name="stock_qty1" type="text" /> </td> </tr> </table> <input name="submit" type="submit" value="Update stock items" /> </form> </td> </table> </td> </table> </center> </body> </html> Any help would be greatly appreciated... ive been working on this for a while and cant seem to find the answer. Thanks Lance I am trying to get info from a table, add rows in that table, and put the added value back into the table in another field...I want to add field1 and field2 of every row and put the total in the total row. Heres what the echo gives me Susan 14 Mary 18 Bob 13 Sam 21 heres the database mysql> SELECT total_val FROM table; +-----------+ | total_val | +-----------+ | 21 | | 21 | | 21 | | 21 | +-----------+ heres the code.... <?php require("connection.php"); mysql_select_db("database", $connection); echo "<br />"; $result = mysql_query("SELECT * FROM table"); while($row = mysql_fetch_array($result)) { $values = ($row['field1'] + $row['field2']); $sql=mysql_query("UPDATE table SET total = '$values'"); echo $row['user_name'] . " " . $values; echo "<br />"; } ?> also note that if I put a INT in place of $values - the echo changes to match...why?? PLEASE help...I have been working on this for 2 entire days.... Hi guys, Im developing a stock system and so far new stock and can be added with the quantity, however im trying to update the stock by using a select box and selecting the type of stock and inputting a new qty. the form and everything is set up and i can select files from the database, however i dont know how to update files from a select box The code i got so far is Code: [Select] <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>stock manager</title> </head> <body> <?php $stockqty =&$_POST['stock_qty1']; ?> <center> <table> <td> <table> <td> <form action='stockview.php' method='POST'> Please Enter a Stock Name and Stock Value <table> <tr> <td> Stock Name: </td> <td> <input name="stock_name" type="text" /><BR /> </td> </tr> <tr> <td> Stock Qty: </td> <td> <input name="stock_qty" type="text" /> </td> </tr> </table> <input name="submit" type="submit" value="Add New Stock Items" /> </form> </td> </table> </td> <td> <table> <td> <form action='stockmanager.php' method='POST' enctype="multipart/form-data"> Please Select from the list the item you wish to update <table> <tr> <td> Stock Name: </td> <td> <?php $connect = mysql_connect("localhost","root", "") or die ("Couldn't Connect!"); mysql_select_db("stock", $connect) or die("Couldn't find db"); // select database $query=("SELECT id, stockname FROM stocks"); $result = mysql_query ($query); echo "<select name=stock value=''>Edit Stock QTY</option>"; while($nt=mysql_fetch_array($result)) { //Array or records stored in $nt echo "<option value=$nt[id]>$nt[stockname]</option>"; /* Option values are added by looping through the array */ } $queryreg = mysql_query("SELECT * FROM stocks WHERE stockqty='$stockqty'"); $numrows = mysql_num_rows($queryreg); $update = mysql_query("UPDATE stocks SET stockqty='$stockqty' WHERE stockname = '$stockname'"); echo("Its updated"); ?> </td> </tr> <tr> <td> Stock Qty: </td> <td> <input name="stock_qty1" type="text" /> </td> </tr> </table> <input name="submit" type="submit" value="Update stock items" /> </form> </td> </table> </td> </table> </center> </body> </html> Any help would be greatly appreciated. Thanks Lance I 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 everyone I need code for this question
would you help me please..?
I am pulling 5 addresses and I am wanting to store these 5 addresses in columns in a separate table. On the query string I put LIMIT 5. How can I store the results of each of the 5 loops into columns in a separate table? The columns are A1(for name), A2(for address), A3 (for city), A4 (for state), A5 (for zip), A6 (for phone), A7 (for miles), .... then I also have B1-B7, C1-C7, D1-D7, and E1-E7. I am basically wanting to store up to 5 addresses in an array and then update them into a mysql table. Code: [Select] foreach ($zips as $key => $value) { $result = mysql_query("SELECT State FROM extreme WHERE Zip = '$key' AND Type='store' AND `InStore` <> 'NO' LIMIT 5", $db); $num_rows = mysql_num_rows($result); while($myrow = mysql_fetch_array ($result)) { $Dealer=$myrow['Dealer']; $Address=$myrow['Address']; $City=$myrow['City']; $State=$myrow['State']; $Zip=$myrow['Zip']; $Phone=$myrow['Phone']; $Miles = $value; } } Hi all, I have the following MySQL insert query: Code: [Select] $insert= mysql_query ("INSERT INTO tablename (column1,`".$EXPfields."`) VALUES ('$something','".$EXPvalues."')"); where $EXPfields is an array of table-field-names and $EXPvalues is an array of table-field-values. Now I want to write an equivalent query, but using UPDATE instead of INSERT INTO, but I don't want to write out all the field names/values separately, but again want to use $EXPfields and $EXPvalues. So something like this: Code: [Select] $update = mysql_query ("UPDATE tablename SET (column1,`".$EXPfields."`) = ('$something','".$EXPvalues."') WHERE .... "); Is this possible? If so, what is the proper syntax? Thanks! Hello All,
Being a newbie at PHP coding I'm at my wits end trying to figure out:
a) how to pull-in values from a delimeted text field (in a MySQL table) and check/select the appropriate checkboxes, based on the values that were stored in the text field,
b) how to write any changes (made by the user) back to the tables' text field.
Note that this is for an "update.php" file/process.
My "create.php" file/process uses the following HTML to display and accept the checkbox values:
<div class="control-group"> <label class="control-label">Limited Time Sale Days:</label> <div class="lts-checkboxes-container"> <label class="indent-to-the-left"> <input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='F'><span class='no-highlight'>Fri</span> </label> <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Sa'><span class='no-highlight'>Sat</span></label> <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Su'><span class='no-highlight'>Sun</span></label> <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='M'><span class='no-highlight'>Mon</span></label> <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Tu'><span class='no-highlight'>Tue</span></label> <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='W'><span class='no-highlight'>Wed</span></label> <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Th'><span class='no-highlight'>Thu</span></label> </div> </div>And the code (certainly not the best code in the world, but it works) to collect, delemit/concatenate, and save to DB is as follows:
if(empty($_POST['limited_time_sale']))
{
// echo("You didn't select any weekday.");
$selected_lts = '';
$limited_time_sale = '';
}
else
{
$limited_time_sale = $_POST['limited_time_sale'];
$N = count($limited_time_sale);
$selected_lts = '';
// echo("You selected $N DoW(s): ");
for($i=0; $i < $N; $i++)
{
// echo($limited_time_sale[$i] . " ");
if ($i < ($N - 1))
{
$selected_lts .= $limited_time_sale[$i] . "-";
}
else
{
$selected_lts .= $limited_time_sale[$i];
}
}
}
if(!empty($selected_lts)) { $limited_time_sale = $selected_lts; }
Now, I've figured out how to bring-in, and separate the stored values using the following code, however I have no idea what to do next...in order to have only the approprite boxes checked/selected (in the event that all boxes were not selected during the create stage).
$limited_time_sale = isset($values['limited_time_sale']) ? $values['limited_time_sale'] : '';
$checked_lts = explode("-", $limited_time_sale);
In my "update.php" file, the HTML for the forms' checkboxes is as follows:
<div class="control-group"> <label class="control-label">Limited Time Sale Days:</label> <div class="lts-checkboxes-container"> <label class="indent-to-the-left"> <input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='F' <?php echo $lts1; ?>><span class='no-highlight'>Fri</span> </label> <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Sa' <?php echo $lts2; ?>><span class='no-highlight'>Sat</span></label> <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Su' <?php echo $lts3; ?>><span class='no-highlight'>Sun</span></label> <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='M' <?php echo $lts4; ?>><span class='no-highlight'>Mon</span></label> <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Tu' <?php echo $lts5; ?>><span class='no-highlight'>Tue</span></label> <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='W' <?php echo $lts6; ?>><span class='no-highlight'>Wed</span></label> <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Th' <?php echo $lts7; ?>><span class='no-highlight'>Thu</span></label> </div> </div>Attached picture shows that selected checkboxes are saved to the text field as/in the format of "F-Sa-Su-M-Tu-W-Th" - if all checkboxes/weekdays were selected....or as "Sa-M-W-Th" - if only Sat, Mon, Wed, and Thu checkboxes were selected Thanks. Attached Files checkbox-Question.png 403.99KB 0 downloads When I echo the POST, it echoes the correct value. The MySQL portion seems to just ignore it all together. I've tried changing the dropdown option to just a text field, same thing occurred. I have text fields right above this particular one that update just fine with the SAME exactly scripting. if POST, update query, done. Works. This one for some reason will not. MySQL portion: if ($_POST['bUpdate']){ mysql_query("UPDATE `Patients` SET `b` = '$_POST[bUpdate]' WHERE `id` = '".$_GET['id']."'"); } echo $_POST['bUpdate']; Form Portion: Code: [Select] <tr onmouseover="color(this, '#baecff');" onmouseout="uncolor(this);"> <td width="310" colspan="2" align="center"><span class="fontoptions">Postcard Status </span><br /> <? if ($data['b'] == 1){ echo '<select name="bUpdate"><option value="1" selected>Yes</option><option value="0">No</option></select>'; } else { echo '<select name="bUpdate"><option value="1">Yes</option><option value="0" selected>No</option></select>'; } ?> </td> </tr> 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 Can anyone post a generic update function to update mysql table. The manual approach: update $tablename set $column1='a', $column2='b' where $id=$value; what Im basically trying to do is just like a phpmyadmin function... you select rows you want to update with a checkbox and then it takes you to a page where the rows that are clicked are shown in forms so that you can view and edit info in them... and then have 1 submit button to update them all at once. Dear All Members here is my table data.. (4 Columns/1row in mysql table)
id order_no order_date miles How to split(miles) single column into (state, miles) two columns and output like following 5 columns /4rows in mysql using php code.
(5 Columns in mysql table) id order_no order_date state miles 310 001 02-15-2020 MI 108.53 310 001 02-15-2020 Oh 194.57 310 001 02-15-2020 PA 182.22
310 001 02-15-2020 WA 238.57 ------------------my php code -----------
<?php
if(isset($_POST["add"]))
$miles = explode("\r\n", $_POST["miles"]);
$query = $dbh->prepare($sql);
$lastInsertId = $dbh->lastInsertId(); if($query->execute()) {
$sql = "update tis_invoice set flag='1' where order_no=:order_no"; $query->execute();
} ----------------- my form code ------------------
<?php -- Can any one help how to correct my code..present nothing inserted on table
Thank You Edited February 8, 2020 by karthicbabuHi, My company has 240+ locations and as such some users (general managers) cover multiple sites. When I run a query to pull user information, when the user has multiple sites to his or her name, its adds the second / third sites to the next columns, rather than wrapping it inside the same table cell. It also works the opposite way, if a piece of data is missing in the database and is blank, its pull the following columns in. Both cases mess up the table and formatting. I'm extremely new to any kind of programming and maybe this isn't the forum for this question but figured I'd give it a chance since I'm stuck. The HTML/PHP code is below: <table id="datatables-column-search-select-inputs" class="table table-striped" style="width:100%"> <thead> <tr> <th>ID</th> <th>FirstName</th> <th>LastName</th> <th>Username</th> <th>Phone #</th> <th>Location</th> <th>Title</th> <th>Role</th> <th>Actions</th> </tr> </thead> <tbody> <?php //QUERY TO SELECT ALL USERS FROM DATABASE $query = "SELECT * FROM users"; $select_users = mysqli_query($connection,$query);
// SET VARIABLE TO ARRAY FROM QUERY while($row = mysqli_fetch_assoc($select_users)) { $user_id = $row['user_id']; $user_firstname = $row['user_firstname']; $user_lastname = $row['user_lastname']; $username = $row['username']; $user_phone = $row['user_phone']; $user_image = $row['user_image']; $user_title_id = $row['user_title_id']; $user_role_id = $row['user_role_id'];
// POPULATES DATA INTO THE TABLE echo "<tr>"; echo "<td>{$user_id}</td>"; echo "<td>{$user_firstname}</td>"; echo "<td>{$user_lastname}</td>"; echo "<td>{$username}</td>"; echo "<td>{$user_phone}</td>";
//PULL SITE STATUS BASED ON SITE STATUS ID $query = "SELECT * FROM sites WHERE site_manager_id = {$user_id} "; $select_site = mysqli_query($connection, $query); while($row = mysqli_fetch_assoc($select_site)) { $site_name = $row['site_name']; echo "<td>{$site_name}</td>"; } echo "<td>{$user_title_id}</td>"; echo "<td>{$user_role_id}</td>"; echo "<td class='table-action'> <a href='#'><i class='align-middle' data-feather='edit-2'></i></a> <a href='#'><i class='align-middle' data-feather='trash'></i></a> </td>"; //echo "<td><a href='users.php?source=edit_user&p_id={$user_id}'>Edit</a></td>"; echo "</tr>"; } ?>
<tr> <td>ID</td> <td>FirstName</td> <td>LastName</td> <td>Username</td> <td>Phone #</td> <td>Location</td> <td>Title</td> <td>Role</td> <td class="table-action"> <a href="#"><i class="align-middle" data-feather="edit-2"></i></a> <a href="#"><i class="align-middle" data-feather="trash"></i></a> </td> </tr> </tbody> <tfoot> <tr> <th>ID</th> <th>FirstName</th> <th>LastName</th> <th>Username</th> <th>Phone #</th> <th>Location</th> <th>Title</th> <th>Role</th> </tr> </tfoot> </table>
I'm new to PHP and I was able to figure out how to populate data from my database into my text fields. I am trying to add the update information to the same php file; however, I am now receiving errors within the data I was able to populate,
Notice: Undefined variable: stmt in C:\xampp\htdocs\Cust_App\update.php on line 47 and errors with the Update statement
Notice: Undefined variable: stmtupdate in C:\xampp\htdocs\Cust_App\update.php on line 96 I had defined the customerID variable above in the code, but it isn't being captured from here. I tried setting up this query like the one which gathered the data, but I'm off by a little bit. I would like the option to be able to update all fields. Any help is appreciated. I'm trying to learn as I may get asked to update other forms in the future. (new boss asks a lot)
<?php
require_once('database.php');
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<!-- the head section -->
<head>
<title>My Guitar Shop</title>
<link rel="stylesheet" type="text/css" href="main.css" />
</head>
<!-- the body section -->
<body>
<div id="page">
<div id="header">
<h1>SportsPro Technical Support</h1>
<p>Sports management software for the sports enthusiast.</p></h1>
</div>
<div id="main">
<h1>View/Update Customer</h1>
<form action="update.php" method="get" >
<?php
if(isset($_GET['customerID'])) {
$customerID = filter_input(INPUT_GET, 'customerID', FILTER_SANITIZE_NUMBER_INT);
$sql = "SELECT * FROM customers WHERE customerID =$customerID ";
$stmt = $db->query($sql);
}
?>
<div id="content">
<!-- display a table of products -->
<h2>Customers</h2>
<form method = "edit">
<?php foreach ($stmt as $cust) { ?>
<div>
<label>First Name</label>
<input type="text" name="name" class ="form-control" value ="<?php echo $cust['firstName']; ?>">
</div><br>
<div>
<label>Last Name</label>
<input type="text" name="name" class ="form-control" value ="<?php echo $cust['lastName']; ?>">
</div><br>
<div>
<label>Address</label>
<input type="text" name="address" class ="form-control" value ="<?php echo $cust['address']; ?>">
</div><br>
<div>
<label>City</label>
<input type="text" name="city" class ="form-control" value ="<?php echo $cust['city']; ?>">
</div><br>
<div>
<label>State</label>
<input type="text" name="state" class ="form-control" value ="<?php echo $cust['state']; ?>">
</div><br>
<div>
<label>Country</label>
<input type="text" name="countryCode" class ="form-control" value ="<?php echo $cust['countryCode']; ?>">
</div><br>
<div>
<label>Zip Code</label>
<input type="text" name="postalCode" class ="form-control" value ="<?php echo $cust['postalCode']; ?>">
</div><br>
<div>
<label>Email </label>
<input type="text" name="email" class ="form-control" value ="<?php echo $cust['email']; ?>">
</div><br>
<div>
<label>Phone Number </label>
<input type="text" name="phone" class ="form-control" value ="<?php echo $cust['phone']; ?>">
</div><br>
<div>
<label>Password </label>
<input type="text" name="password" class ="form-control" value ="<?php echo $cust['password']; ?>">
</div><br>
<div>
<?php }
?>
<input type="Submit" name="Update_Data" value="Update Data"></input>
<?php
$sql2 = "UPDATE customers SET firstName = ". $stmtupdate['firstName']." WHERE customerID =$customerID ";
$stmtupdate = $db->query($sql2);
?>
</div>
</div>
<div id="footer">
<p>
© <?php echo date("Y"); ?> SportsPro, Inc.
</p>
</div>
</div><!-- end page -->
</body>
</html>
As the title says I need help with multiple update system.. Can't really get it to work at all >.< Here's some code: $selectt = mysql_query("SELECT * FROM exercises WHERE user='" . $_SESSION["user"] . "' AND exname='" . $_GET["edit"] . "'"); echo '<form method="POST" action="exercises.php?edited">'; echo '<div id="newexx">'; while($raw = mysql_fetch_array($selectt)) { echo '<input type="text" name="up[' . $raw["id"] . ']" value="' . $raw["swe"] . '">'; echo '<input type="text" name="up1[' . $raw["id"] . ']" value="' . $raw["eng"] . '"><br>'; echo '<input type="hidden" name="id[' . $raw["id"] . ']">'; } echo '</div>'; echo '<input type="submit" value="Update Exercise">'; echo '</form>'; if(isset($_GET["edited"])) { $swe = $_POST["up"]; $eng = $_POST["up1"]; $id = $_POST["id"]; foreach($id as $value => $index) { $update = "UPDATE exercises SET swe='".$swe."', eng='".$eng."' WHERE id='".$index."'"; mysql_query($update); } Would be thankful for reply! 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. Good morning. I am looking fo some help with an update script to update all rows in a table. This is what I have for a form and all looks well. form.php <?php // run the query and put the results in an array variable called $result $result = mysql_query("SELECT * FROM table ORDER BY 'id', 'title', 'text', 'number'"); print "<form method='post' action='update.php'> <table width='100%' border='0' cellspacing='1' cellpadding='1'><tr> <td align='center'><strong>ID</strong></td> <td align='center'><strong>Title</strong></td> <td align='center'><strong>text</strong></td> <td align='center'><strong>Number</strong></td> </tr>\n"; // start a loop to print all of the courses with their book information // the mysql_fetch_array function puts each record into an array. each time it is called, it moves the array counter up until there are no more records left while ($Update = mysql_fetch_array($result)) { // start displaying the info; the most important part is to make the name an array (notice bookinfo[$i]) print "<td align='center'><p>{$Update['id']}</p></td>\n"; print "<td align='center'><input type='text' name='title' value='{$Update['title']}' /></td>"; print "<td align='center'><input type='text' size='40' name='text' value='{$Update['text']}' /></td>\n"; print "<td align='center'><input type='text' size='40' name='number' value='{$Update['number']}' /></td>\n"; print "</tr>\n"; // add 1 to the count, close the loop, close the form, and the mysql connection } print "<tr> <td colspan='4' align='center'><input type='submit' value='submit' />"; print "</td> </tr> </table> </td> </tr> </form> </table>"; print "</tr>\n"; ?><br /><br /> My question is. How do I update this info into the database with the proper info. ie. Update.php? Hi Guys, I want my UPDATE function from the code below to only update those fields that are NOT empty - if they are empty on submit then do not update or change any values from that field. I know that sounds odd, but with my file fields, when I submit to change something else at a later date it will overwrite the photo, download1,2 & 3 values and as a result I loose my files from the mysql table. Cheers, S <?php include('config.php'); if (isset($_GET['Ter']) ) { $ter = (int) $_GET['Ter']; if (isset($_POST['submitted'])) { //Photo & Document Upload Upload $timestamp_photo = time(); $timestamp_download1 = time(); $timestamp_download2 = time(); $timestamp_download3 = time(); //This is the directory where the files will be saved //Photos $photo_target = "images/"; $photo_target = $photo_target .$timestamp_photo. basename( $_FILES['photo']['name']); //Documents $download_target = "documents/"; $download_target1 = $download_target .$timestamp_download1. basename( $_FILES['download1']['name']); $download_target2 = $download_target .$timestamp_download2. basename( $_FILES['download2']['name']); $download_target3 = $download_target .$timestamp_download3. basename( $_FILES['download3']['name']); //This gets all the other information from the form $photo = ($_FILES['photo']['name']); $download1 = ($_FILES['download1']['name']); $download2 = ($_FILES['download2']['name']); $download3 = ($_FILES['download3']['name']); //Pause Photo/Document Upload foreach($_POST AS $key => $value) { $_POST[$key] = mysql_real_escape_string($value); } $sql= "UPDATE `ter` SET `Ter` = '{$_POST['Ter']}' , `BranchName` = '{$_POST['BranchName']}' , `BranchAddress` = '{$_POST['BranchAddress']}' , `BranchTel` = '{$_POST['BranchTel']}' , `BranchEmail` = '{$_POST['BranchEmail']}' , `BranchLink` = '{$_POST['BranchLink']}' , `Theme` = '{$_POST['Theme']}' , `LocalInfo` = '{$_POST['LocalInfo']}' , `BranchInfo` = '{$_POST['BranchInfo']}' , `photo` = '$timestamp_photo{$_FILES['photo']['name']}' , `download1` = '$timestamp_download1{$_FILES['download1']['name']}' , `download1name` = '{$_POST['download1name']}' , `download2` = '$timestamp_download2{$_FILES['download2']['name']}' , `download2name` = '{$_POST['download2name']}' , `download3` = '$timestamp_download3{$_FILES['download3']['name']}' , `download3name` = '{$_POST['download3name']}' WHERE `Ter` = '$ter' "; mysql_query($sql) or die(mysql_error()); //Unpause Photo/Document Upload //Writes the photo to the server if(move_uploaded_file($_FILES['photo']['tmp_name'], $photo_target)) { echo "<br />The file ".$timestamp_photo. basename( $_FILES['photo']['name']). " has been uploaded. <br />"; } else { echo ""; } //End of Photo/Document Upload //Writes the photo to the server if(move_uploaded_file($_FILES['download1']['tmp_name'], $download_target1)) { echo "<br />The file ".$timestamp_download1. basename( $_FILES['download1']['name']). " has been uploaded. <br />"; } else { echo ""; } //End of Photo/Document Upload //Writes the photo to the server if(move_uploaded_file($_FILES['download2']['tmp_name'], $download_target2)) { echo "<br />The file ".$timestamp_download2. basename( $_FILES['download2']['name']). " has been uploaded. <br />"; } else { echo ""; } //End of Photo/Document Upload //Writes the photo to the server if(move_uploaded_file($_FILES['download3']['tmp_name'], $download_target3)) { echo "<br />The file ".$timestamp_download3. basename( $_FILES['download3']['name']). " has been uploaded. <br />"; } else { echo ""; } //End of Photo/Document Upload echo (mysql_affected_rows()) ? "<br />Edited Branch.<br />" : "<br />Nothing changed. <br />"; } $row = mysql_fetch_array ( mysql_query("SELECT * FROM `ter` WHERE `Ter` = '$ter' ")); ?> |
|||||||