|
PHP - Php/mysql - Where Clause - Won't Update
Hi,
Im having a problem and I can't seem to figure it out or find anything on the net.
If I use the following code the script successfully updates every row in the table:
mysqli_query($con,"UPDATE Ads SET Ads_LocalArea='Stroud'");However if I try updating the table using the WHERE clause in any of the combinations below nothing happens. mysqli_query($con,"UPDATE Ads SET Ads_LocalArea='Stroud' WHERE Ads_ID=$DBROWID");---------------------------------------------------------------------- My Script:
mysqli_query($con,"INSERT INTO Ads (Ads_ID, Ads_AID, Ads_Title)
VALUES ('', '$Polished_AdRef', '$Polished_AdTitle')");
$DBROWID = mysqli_insert_id($con);
mysqli_query($con,"UPDATE Ads SET Ads_AID='Stroud' WHERE Ads_ID=$DBROWID");
// TRIED THESE TOO
// mysqli_query($con,"UPDATE Ads SET Ads_AID='Stroud' WHERE Ads_ID='$DBROWID'");
// mysqli_query($con,"UPDATE Ads SET Ads_AID='Stroud' WHERE Ads_ID='5'");
Does any one know where I am going wrong?
Similar TutorialsI have this code: Code: [Select] if (isset($_POST['update'])){ $ids = implode(",", array_map('intval', $_POST['m'])); $ranks = implode(",", array_map('intval', $_POST['ranks'])); if ($ids < 1) message("Incorrect Data"); if ($ranks < 0) message("Incorrect Data"); echo $ids; echo "<br>"; echo $ranks; exit; $db->query('UPDATE friends set RANKS WHERE friend_id IN ('.$db->escape($ids).') AND user_id = '.$pun_user['id'].'') or error('Unable to remove users from online list', __FILE__, __LINE__, $db->error()); redirect("s.php?section=Friends","Thanks, Ranks Updated"); } I grab the id's from the rank This will spit out 3,4 for $ids and for $ranks, 1,2 as you can see, set for 1 and 2 respectivaly. My problem is how can I use MYSQL to update the data correspond to each id using a IN clause? Code: [Select] $db->query('UPDATE friends set RANKS = "LOST $RANKS? HERE" WHERE friend_id IN ('.$db->escape($ids).') AND user_id = '.$pun_user['id'].'') or error('Unable to remove users from online list', __FILE__, __LINE__, $db->error()); I'm a newbie, can you please help? I'm getting a mysql error "Unknown column 'E0000001' in 'where clause'" The id is in the URL: ...user-profile.php?id=E0000001 My Query $query = mysqli_query($con, "SELECT * FROM UserList WHERE UserID=".$id) or die (mysqli_error($con)); Thank you Edited September 18, 2019 by ACBMSEThis topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=342695.0 I've a simple Inset List (jQuery Mobile) of several items with a Flip switch, Drop down, check box and Slider elements. I have wrapped this Inset List inside a HTML Form. <code> <div data-role="fieldcontain"> <ul data-role="listview" data-inset="true"> <li data-role="list-divider"> Inhouse capabilities </li> <li> <label for="flip-b">Project Manager</label> <select name="slider" id="flip-b" data-role="slider"> <option value="no">No</option> <option value="yes">Yes</option> </select> </li> <li> <label for="flip-b">Site Supervisor</label> <select name="slider" id="flip-b" data-role="slider"> <option value="no">No</option> <option value="yes">Yes</option> </select> </li> <li><label for="flip-b">Architect</label> <select name="slider" id="flip-b" data-role="slider"> <option value="no">No</option> <option value="yes">Yes</option> </select> </li> <li><label for="flip-b">Builderworks</label> <select name="slider" id="flip-b" data-role="slider"> <option value="no">No</option> <option value="yes">Yes</option> </select> </li> <li><label for="flip-b">Electrical</label> <select name="slider" id="flip-b" data-role="slider"> <option value="no">No</option> <option value="yes">Yes</option> </select> </li> <li><label for="flip-b">Mechanical</label> <select name="slider" id="flip-b" data-role="slider"> <option value="no">No</option> <option value="yes">Yes</option> </select> </li> <li><label for="flip-b">Hydraulics</label> <select name="slider" id="flip-b" data-role="slider"> <option value="no">No</option> <option value="yes">Yes</option> </select> </li> <li><label for="flip-b">Joinery</label> <select name="slider" id="flip-b" data-role="slider"> <option value="no">No</option> <option value="yes">Yes</option> </select> </li> <li> <label for="select-choice-1" class="select">Main Location:</label> <select name="select-choice-1" id="select-choice-1"> <option value="NSW">NSW</option> <option value="ACT">ACT</option> <option value="VIC">VIC</option> <option value="SA">SA</option> <option value="QLD">QLD</option> <option value="NT">NT</option> <option value="WA">WA</option> <option value="TAS">TAS</option> </select> </li> <!-- Select Menus: Main Location--> <li> <div data-role="fieldcontain"> <fieldset data-role="controlgroup"> <legend>CBA Reference</legend> <input type="checkbox" name="checkbox-1a" id="checkbox-1a" class="custom" /> <label for="checkbox-1a">Commercial</label> <input type="checkbox" name="checkbox-2a" id="checkbox-2a" class="custom" /> <label for="checkbox-2a">Retail</label> </fieldset> </div> </li> </ul> </div> <!-- End of Field Contain div tag --> <a href="#additionalinfo" data-role="button" data-inline="true" data-iconpos="left" data-theme="b">Additional Info</a> <a href="./search2.html" rel="external" data-ajax="false" data-role="button" data-inline="true" data-icon="search" data-iconpos="left" data-theme="e">Search</a> </code> I want to know, how I can include only the elements the user selected in my SQL query. Lets say, User wants to find the Electrical workers in QLD, then he will select Yes for Electrical and QLD as Main Location and the other items he would not touch. Now, how can I include only those two values in the select clause. Say, Select Electrical, Main Location from table where Main location='QLD'. I used POST method to gather users input. I use PHP, MySQL for this project. Any ideas on how to achieve this. Regards Sandeep Can anyone post a generic update function to update mysql table. The manual approach: update $tablename set $column1='a', $column2='b' where $id=$value; Hi all, Just curious why this works: Code: [Select] while (($data = fgetcsv($handle, 1000, ",")) !== FALSE){ $import="INSERT into $prodtblname ($csvheaders1) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]')"; } And this does not: $headdata_1 = "'$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]'"; while (($data = fgetcsv($handle, 1000, ",")) !== FALSE){ $import="INSERT into $prodtblname ($csvheaders1) values($headdata_1)"; }it puts $data[#'s] in the database fields instead of the actual data that '$data[0]','$data[1]'... relates to. I wrote a script to create the values in $headdata_1 based on the number of headers in $csvheaders1 but can't seem to get it working in the sql statement. Thanks Please help me with this: been doing this for 2 days and still cannot solve it. it won't update... I have attached my file: <?php include('connect-database.php'); if (isset($_POST['submit']) AND $_POST['submit'] == 'update') { $error = false; extract($_POST); if (empty($full_name)) $error = true; if (!$error) { $update = "UPDATE users SET full_name = '$full_name', WHERE id=$id "; mysql_query($update) or die(mysql_error() . "<br><br>" . $update); $full_name = ''; $message = "Record successfully updated."; } else { $message = "There is an error in your entry"; } } else { $id = intval(isset($_GET['id']) ? $_GET['id'] : 0); } if ($id == 0) { echo "<div style='margin-bottom:10px'>Invalid ID.</div>"; exit; } $query = "SELECT * FROM users WHERE id=$id"; $result = mysql_query($query) or die(mysql_error() . "<br><br>" . $query); if (mysql_num_rows($result) == 0) { echo "<div style='margin-bottom:10px'>Record Not Found in Database!</div>"; exit; } $row = mysql_fetch_array($result); extract($row); ?> <div id="mainContent" class="tab_container"> <div id="tab1" class="tab_content"> <h1>Edit Personal Data</h1> <div style="color:red;font-weight:bold"><? echo $message; ?></div> <form name="submit" action="" method="post"> <ul> <li><b>ID # :</b> <?php echo $id?></li> <li><b>Name :</b> <input type="text" name="full_name" id="" size="30" value="<?php echo $full_name ?>" /></li> <li><b>Address :</b> <input type="text" name="address" id="" size="55" maxlength="100" value="<?php echo $address ?>" /></li> <li><b>Contact Number :</b> <input type="text" name="contact_number" id="" size="11" value="<?php echo $contact_number ?>" /></li> <li><b>Email Address :</b> <input type="text" name="user_email" id="" size="30" value="<?php echo $user_email ?>" /></li> <li><b>Status :</b> <input type="text" name="status" id="" size="10" value="<?php echo $status ?>" /></li> <li><b>Nationality :</b> <input type="text" name="nationality" id="" size="20" value="<?php echo $nationality ?>" /></li> <li><b>Religion :</b> <input type="text" name="religion" id="" size="20" value="<?php echo $religion ?>" /></li> </ul> <h2>Self Description</h2> <p><textarea rows="9" cols="40" name="self_description" ><?php echo $self_description ?></textarea></p> <input type="hidden" name="id" value="<?php echo $id; ?>" /> <input type="submit" name="submit" value="update" /> </form> </div> this is what i have so far but i cant get it to update the data update page <?php $host="localhost"; // Host name $username="**"; // Mysql username $password="*****"; // Mysql password $db_name="manews"; // Database name $tbl_name="Shows"; // Table name // Connect to server and select database. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); // get value of id that sent from address bar $id=$_GET['id']; // Retrieve data from database $sql="SELECT * FROM $tbl_name WHERE id='$id'"; $result=mysql_query($sql); $rows=mysql_fetch_array($result); ?> <table width="400" 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"> </td> <td align="center"><strong>Day</strong></td> <td align="center"><strong>Month</strong></td> <td align="center"><strong>Year</strong></td> <td align="center"><strong>Song</strong></td> <td align="center"><strong>Data</strong></td> </tr> <tr> <td> </td> <td align="center"><input name="Day" type="text" id="Day" value="<? echo $rows['Day']; ?>"></td> <td align="center"><input name="Month" type="text" id="Month" value="<? echo $rows['Month']; ?>"></td> <td align="center"><input name="Year" type="text" id="Year" value="<? echo $rows['Year']; ?>" size="15"></td> <td align="center"><input name="Song" type="text" id="Song" value="<? echo $rows['Song']; ?>"></td> <td align="center"><input name="Data" type="text" id="Data" value="<? echo $rows['Data']; ?>" size="15"></td> </tr> <tr> <td> </td> <td><input name="id" type="hidden" id="id" value="<? echo $rows['id']; ?>"></td> <td align="center"><input type="submit" name="Submit" value="Submit"></td> <td> </td> </tr> </table> </td> </form> </tr> </table> <? // close connection mysql_close(); ?> update script <?php $host="localhost"; // Host name $username="**"; // Mysql username $password="**"; // Mysql password $db_name="manews"; // Database name $tbl_name="Shows"; // Table name // Connect to server and select database. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); // update data in mysql database $sql="UPDATE $tbl_name SET Day='$Day', Month='$Month', Year='$Year', Year='$Song', Year='$Data' WHERE id='$id'"; $result=mysql_query($sql); // if successfully updated. if($result){ echo "Successful"; echo "<BR>"; echo "<a href='list_records.php'>View result</a>"; } else { echo "ERROR"; } ?> I am trying to update the content of my website which is all stored in a MySQL database. Currently on the page I am displaying all the data stored in the database using MySQL SELECT and a while loop. Using JavaScript when the text is clicked the element changes into a text box allowing the text to be edited. What I want to do is be able to change the data in the text box and then store it into the database updating the current data. Code: [Select] <form method="post" action="editindex.php"> <?php $sql = "SELECT * FROM home ORDER BY id ASC"; $result = mysql_query($sql); while($row = mysql_fetch_assoc($result)){ echo "<span id=\"itm1\" onclick=\"exchange(this);\">" . $row['title'] . "</span><input ondblclick=\"exchange(this);\" id=\"itm1b\" class=\"replace\" type=\"text\ name=\"title\">";?> <input type="submit" value="Save" name="submit"> <input type="hidden" name="id" value="<?php $row['id'] ?>" </form> <?php if (isset ($_POST['submit'])) { $update = "UPDATE home SET title=".$_POST['title']." WHERE id=".$_POST['id'].""; mysql_query($update); } ?> I have more text being output from the while loop below but I just want to get it working on the title first. Any ideas of why the update is not working? So there is this code I have here : http://pastebin.com/rwcN7FeJ and I need to find a way to incorporate this code that user Barand gave me Code: [Select] $referName = mysql_real_escape_string($gateway_data['refer']); $query = "UPDATE users a INNER JOIN users b ON a.refer = b.nick SET a.refer = '', b.balance = b.balance + 5 WHERE b.refer = '$referName'"; mysql_query($query); But I havent the damnedest how to do that. If anyone could help me at all, it would be appreciated. I have the following code that when used seperately work. How would I write an if..else statement that will make them work together? The following code give me an Parse Error, syntax error unexpected T_string. The following if my code I have tried, Code: [Select] if (SELECT tournament FROM weekly_picks WHERE tournament = '$tournament') mysql_query("UPDATE weekly_picks SET player = '$golfer' WHERE tournament = '$tournament' AND user = '$usr'"); else mysql_query("INSERT INTO weekly_picks (t_id, tournament, user, player, backup, timestamp) VALUES ('$t_id', '$tournament', '$usr', '$golfer', '$backup', '$time')",$link) or die('Error, insert query failed'); I also know that values are being stored in the variables. So i have this following code that doesn't work I have lost my record when I run the code below, all fields are empty... Code: [Select] <a href='edit.php?id={$info['person_id']}'><font color='#00AE0A'>Edit User</font> </a> Code: [Select] $sql = "SELECT * FROM person WHERE person_id='{$_REQUEST['id']}' "; $result = mysql_query($sql); if (!$result) { echo("<p>Error performing query: " . mysql_error() . "</p>"); exit(); } if ($row = @mysql_fetch_array($result, MYSQL_ASSOC)) { print " <form action='<?php echo $PHP_SELF;?>' method=\"post\"> <table> <tr> <td><strong>Name</strong></td> <td><input type=\"text\" name=\"fname\" size=\"60\" value=\"$row[fname]\"></td> </tr> <tr> <td><strong>Surname</strong></td> <td><input type=\"text\" name=\"lname\" size=\"60\" value=\"$row[lname]\"></td> </tr> <tr> <td><strong>Email</strong></td> <td><input type=\"text\" name=\"email\" size=\"60\" value=\"$row[email]\"></td> </tr> <tr> <td><strong>Committee</strong></td> <td><input type=\"text\" name=\"committee_no\" size=\"60\" value=\"$row[committee_no]\"></td> </tr> <tr> <td><INPUT type=\"submit\" value=\"Send\"> </td> </tr> </table></form> "; } else { echo("There has been an error" . mysql_error()); } /* closes connection */ ?> <?php $control = mysql_query(" UPDATE person SET fname='{$_REQUEST['fname']}' , lname='{$_REQUEST['lname']}' , email='{$_REQUEST['email']}', committee_no='{$_REQUEST['committee_no']}' WHERE person_id='{$_REQUEST['id']}'"); if($control) { header("Location:".$_SERVER["HTTP_REFERER"].""); } ?> I have made this, although i can not seem to the status... changeapp.php Code: [Select] <form method="post" name="memberadd" action="change_app_complete.php"> <label>Name:</label> <select name="member"> <?php $con = mysql_connect("host","user","password"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("database",$con); $sqlquery="SELECT * FROM `application` Order By Name"; $result=mysql_query($sqlquery,$con); while($row=mysql_fetch_array($result)) { echo "<option value='".$row['ID']."'>".$row['Name']." (".$row['Status'].")</option>"; } ?> </select> <br> <label>Status:</label> <select name="Status"> <option value="PENDING">PENDING</option> <option value="ACCEPTED">ACCEPTED</option> <option value="DENIED">DENIED</option> <br> <input type="submit" value="submit" /> </form> change_app_complete.php Code: [Select] <?php $status=$_POST['Status']; $con = mysql_connect("host","user","password"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("database",$con); $sql="UPDATE application SET Status = '$status' WHERE Name= '$member'"; if(mysql_query($sql,$con)) { echo 'Status Changed.<br /><a href="../applications.php">Return To Members List</a>'; } else { die('Could not submit: ' . mysql_error()); } mysql_close($con); ?> What is wrong? Code: [Select] <?php $querySelect = mysql_query("SELECT * FROM `G4V_Videos` WHERE `counter`"); $row = mysql_fetch_assoc($querySelect); $counter = $row['counter']; if (empty($counter)) { $counter = 1; $insert = mysql_query ("INSERT INTO counter VALUES ('counter')"); $printid = (print $data['id']); $printlink = (print $data['link']); } $add = $counter+1; $insertNew = mysql_query("UPDATE G4V_Videos SET counter=('$add') WHERE id = ('$printid') AND link = ('$printlink')") or die (mysql_error()); echo "Video Views"; echo ":"; echo" "; echo"<br />"; echo "$counter"; ?> It shows the same amount of hits on everyvideo instead of just the one video with the id www.game4vids.com Is there something seriously wrong with this code that im missing ? I have written it at least 10-12 different ways and it will NOT update my db.. Code: [Select] <?php mysql_connect('localhost','root',''); mysql_select_db('myDB'); $dates = $_POST['date']; $store = $_POST['store']; // these actually display the correct information mysql_query = ("UPDATE Agency Stores - Table 1 SET date = '".$date."' WHERE F3 = '".$store."' "); ?> there are several other columns in the table. I have tried to put them in as the value they already have and it still didnt work... any ideas ? Code: [Select] if(isset($_POST['npassword']) and ($_POST['cpassword'])){ $npassword = $_POST['npassword']; $cpassword = $_POST['cpassword']; $username = $_SESSION['SESS_login']; if($npassword==$cpassword){ $password = md5($npassword); $lol = mysql_query("UPDATE `members` SET `passwd` = '$password' WHERE `login`= '$username';"); echo"Password Changed!"; } } It echos password changed, but its not updating the password. If I run it in phpmyadmin it works fine... So i'm really stuck. I have this query: Code: [Select] $sql3 = "UPDATE users SET hours = '$newHours', pay = '$newPay', virtualLocation = '$arrival', expDate = '$expDate' WHERE pilotID = '$pilotid'"; I've echoed it out and all the variables put out the correct values. The query works except for the pay section of it. It will update the hours, virtual location, and expiration date but it will not update the pay. It inputs 1.00 when the value echoed in the query is not 1.00. I'm not sure if, because it is a decimal, it changes how the query works. Any help? Thanks. Hey, I have this code, and it's ment to change the name / content of a page that is being put onto a page. Both of the include files are fine as it works for other actions, but this one just returns the error. <?php include "../includes/mysql_connect.php"; include "../includes/info_files.php"; if(isset($_POST['submitted'])) { mysql_query("UPDATE `pages` SET name='$_POST[name]' AND SET content='$_POST[content]' AND SET catt='$_POST[catt]' AND SET page='$_POST[page]' WHERE id='$_POST[id]'") or die('Edit failed'); echo "Page made.<br /><br />"; }else{ $result = mysql_query("SELECT * FROM pages WHERE id='$_GET[page]'"); while($row = mysql_fetch_array($result)) { echo '<form action="" method="post">'; echo '<input type="hidden" name="id" value="' . $row['id'] . '" /><br />'; echo '<strong>Edit: ' . $row['name'] . '</strong><br />'; echo 'Name: <input type="text" name="name" value="' . $row['name'] . '" /><br />'; echo 'Category: <input type="text" name="catt" value="' . $row['catt'] . '" /><br />'; echo 'Page: <input type="text" name="page" value="' . $row['page'] . '" /><br />'; echo '<textarea rows="25" cols="60" name="content">' . $row['content'] . '</textarea><br />'; echo '<input type="submit" name="submitted" value="Edit" />'; echo '</form>'; } } ?> Any help would be great. Hi, I'm only just getting started with php and mysql but I like to go off the deep end. something is wrong with my script. it seems to work up untill it comes to the UPDATE part. It wont actually update the table and the mysql_affected_rows bit returns -1 I've searched for an explanation and can't find one. Can someone tell me what I'm doing wrong Thanks. //connect to mysql $con = mysql_connect("xxx","yyy","zzz"); if (!$con) { die('Could not connect: ' . mysql_error()); } //use the right database mysql_select_db("foo_bar"); //select all cancelled tickets $status_query = "SELECT * FROM jos_vm_orders where order_status = 'X'"; $status_result = mysql_query($status_query) or die(mysql_error()); while ($newArray = mysql_fetch_array($status_result, MYSQL_ASSOC)) { $order_id = $newArray['order_id']; $quantity_query = "SELECT * FROM jos_vm_order_item where order_id = $order_id"; $quantity_result = mysql_query($quantity_query) or die(mysql_error()); while ($newArray2 = mysql_fetch_array($quantity_result, MYSQL_ASSOC)) { $product_quantity = $newArray2[product_quantity]; $order_item_sku = $newArray2[order_item_sku]; echo ("$order_id cancelled $product_quantity tickets sku was $order_item_sku \n"); mysql_query("UPDATE LOW_PRIORITY jos_vm_product SET product_in_stock = product_in_stock + $product_quantity WHERE product_sku = $order_item_sku AND STR_TO_DATE($order_item_sku, '%Y-%d-%m') $quantity_query = "SELECT * FROM jos_vm_order_item where order_id = $order_id"; $quantity_result = mysql_query($quantity_query) or die(mysql_error()); while ($newArray2 = mysql_fetch_array($quantity_result, MYSQL_ASSOC)) { $product_quantity = $newArray2[product_quantity]; $order_item_sku = $newArray2[order_item_sku]; echo ("$order_id cancelled $product_quantity tickets sku was $order_item_sku \n"); mysql_query("UPDATE LOW_PRIORITY jos_vm_product SET product_in_stock = product_in_stock + $product_quantity) WHERE product_sku = $order_item_sku AND STR_TO_DATE($order_item_sku, '%Y-%d-%m') >= CURDATE()"); echo ("$product_quantity tickets released into $order_item_sku \n"); echo ("Affected rows "); echo (mysql_affected_rows()); echo ("\n"); } } mysql_free_result($status_result); mysql_free_result($quantity_result); mysql_close($con); ?> |
|||||||