|
PHP - Sql Php Update Records Query...
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 Similar TutorialsHi ive been stuck for a while now and i just cant get my head around it so if you could help i'd be well chuffed. I'm trying to update multple rows in a table depending upon the results of another. I have a form that enables me to update a table called fleet. Another table called ships is realted to the fleet table as each ship is in a fleet: fleet ----- Fleetname* Location Detected ships ------ ShipID* Fleetname * Shipname The particular fleet i am editing is parsed as a url parameter. I made a query to search for all the ships that are in the fleet i am editing: Code: [Select] $colname_ShipsInfleet = "-1"; if (isset($_GET['recordID'])) { $colname_ShipsInfleet = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']); } mysql_select_db($database_swb, $swb); $query_ShipsInfleet = sprintf("SELECT * FROM ships WHERE FleetName = %s", GetSQLValueString($colname_ShipsInfleet, "text")); $ShipsInfleet = mysql_query($query_ShipsInfleet, $swb) or die(mysql_error()); $row_ShipsInfleet = mysql_fetch_assoc($ShipsInfleet); $totalRows_ShipsInfleet = mysql_num_rows($ShipsInfleet); It searched the ships WHERE the FleetName (which is the primary key of the fleet table) is parsed as a url parameter. The appropriate ship records that are generated i want to use so a field can be updated. For example im updating a fleet record and and setting the location (Planet Name) to a different value. I want the Ship records that are of that particular fleet to update a field (PlanetName) in ship table when the fleet table is updated. Code: [Select] if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) { $updateSQL = sprintf("UPDATE ships WHERE ShipID = $row_ShipsInfleet['ShipID'] SET PlanetName=%s", GetSQLValueString($_POST['select'], "text")); mysql_select_db($database_swb, $swb); $Result1 = mysql_query($updateSQL, $swb) or die(mysql_error()); $updateSQL = sprintf("UPDATE fleet SET PlanetName=%s, Detected=%s, Faction=%s WHERE FleetName=%s", GetSQLValueString($_POST['select'], "text"), GetSQLValueString(isset($_POST['checkbox']) ? "true" : "", "defined","1","0"), GetSQLValueString($_POST['hiddenField2'], "int"), GetSQLValueString($_POST['hiddenField'], "text")); mysql_select_db($database_swb, $swb); $Result1 = mysql_query($updateSQL, $swb) or die(mysql_error()); I'm really not sure how to proceed here so please if you can help me out that would be ace. Thank You I am really not sure if this is a problem with me (probably is), php or mysql. I have a calendar that I am adding events to. If I want to edit only the date I can but only once after I make this update I am out. If I attempt to update the time or event title the database is never updated and I am no longer able to update the date. here is the controller for the save. Code: [Select] <?php function eventsave() { $details = $_POST; $this->load->model('eventsmodel'); $this->eventsmodel->eventsave($details,'', TRUE); redirect('/admin/events', 'location'); } ?> I have turned off the redirect to see if I was getting any errors and they seem to be non existent. Here is the model that is used to create a new entry and update a record. Code: [Select] <?php function eventsave($event) { $startdate = explode('-', $event['startdate']); $start = mktime($event['starthour'], $event['startmin'], 0, $startdate['0'], $startdate['1'], $startdate['2']); $end = mktime($event['endhour'], $event['endmin'], 0, $startdate['0'], $startdate['1'], $startdate['2']); $name = $event['name']; if (isset($event['id'])) { // if is true then this is an update not a new event $data = array( 'name' => $name, 'start' => $start, 'end' => $end ); $this->db->where('id', $id); $this->db->update('events', $data); }else{ $this->db->set('name', $name); $this->db->set('start', $start); $this->db->set('end', $end); $this->db->insert('events'); } } ?> I am using code igniter if that helps and the information being submitted by the form looks good. This is the submitted update array and the new event array is just missing the "id" Code: [Select] Array ( [name] => test [startdate] => 05-14-2011 [starthour] => 1 [startmin] => 0 [endhour] => 1 [endmin] => 0 [id] => 54 [saveevent] => save ) Any suggestions would be appreciated. Thank you. Hi guys, I am trying to UPDATE some records on a mySQL database but can't seem to find out why it is not working. This is my code. Code: [Select] <?php $latitude = $_POST['lat_location']; $longitude = $_POST['long_location']; $unique_ID = $_POST['unique_ID']; include('connect2.php'); $query = mysql_query("SELECT * FROM user_location WHERE unit = '$unique_ID'"); $numrows = mysql_num_rows($query); if ($numrows == 1) { $query2="UPDATE user_location SET lat = '$latitude', long = '$longitude' WHERE unit = '$unique_ID'"; mysql_query($query2); $test = "matches"; } else { mysql_query("INSERT INTO user_location VALUES ('','$unique_ID','$latitude','$longitude')"); $test = "not match"; } echo $test . "<br />"; echo $numrows; ?> The script receives the data via the POST method and assigns it to variables. Then I query the database for one of those variables and check how many results are found. If 0 results are found then a new record is created on the database, but if there is 1 record found then the record that is found has to be UPDATED. When the result is 0 the scripts creates the new record fine, but if the result is 1 it doesn't update. I just can't figure out why. Any help will be greatly appreciated. Thanks in advanced, Hi there, Im fairly new at php and mysql but nevertheless im trying to build a web based campaign game for starwars minatures boardgame. Its fairly straightforward and going well.. However ive been thinking about how it will work and have stumbled across a possible problem (because of my limited craft at programming). I want to have a single form that updates all records in the tables, this would simulate an "end of turn" button. Im sure i can do this if doing stuff in the game (e.g. move, create , modify, delete, fleets/ships and auto increment ships hulls simulating repair) if it was done on the same page. However i wanted to to initiate some of these adjustments in their respective pages (fleet.php, planets.php, ships.php etc) and then once the user is happy to click the end turn button somewhere which would update all of these things at the same time.. Is this possible at all??? If anybody could help that would be brilliant. Thank you p.s. if anybody wants a copy of the finished product when its complete, your most welcome. I have a problem . I 've been trying for a long time to make an update for php mysql to change the data. but every time I do not manage to make it work with a form. but it works if I only if I put this ($ sql = "UPDATE users SET username = 'value' WHERE id = 10 " ; ) so it only works when I put the value of the id. but I want in an html form to indicate what I want to change and what id goes. but I have tried so long that I do not feel like I so want someone help me. make the same database and same as my records and make the code and test it if it works show me please my database name : web test my table called : users my records are called : id int ( 11) AUTO_INNCREMENT username , varchar ( 255 ) password , varchar ( 255 ) first_name , varchar ( 255 ) last_name , varchar ( 255 ) email, varchar ( 255 ) Age, int ( 11) Look, my update.php is like this now <?php $servername = "localhost"; $username = "root"; $password = "....."; $dbname = "webtest"; $conn = new mysqli($servername, $username, $password, $dbname); if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "UPDATE users SET password='cotton candy' WHERE id=10"; if ($conn->query($sql) === TRUE) { echo "Record updated successfully"; } else { echo "Error updating record: " . $conn->error; } $conn->close(); ?> but now i have still have to go into the php file to change the valeu or the id but i looked on site and youtube how to put it in a simple html form but it still does not work. i want it in a html from. I want that when I enter the ID that the data of the user appears and that I can change any valeu separately. Hi there, hope have some help. I have a form where a user select time and days of the week (monday, tuesday..) that he is available. The insert script works fine, it insert and the days of week he checked in the table, the only problme is when i try to update each of them.
I used this code above, but is not working, instead of updating/inserting days that is selected is inserting multi-records with the same day of the week value.
here it is:
foreach($weekDay as $DaysofWeek){
$sql_days = "UPDATE available SET day_time = '$day_time', week_day = '$DaysofWeek' WHERE user_id = '$id_user'";
$update_availability = mysql_query($sql_days);
}
Hi is it possible to update multiple records of the same table using a single hidden field with a while loop??? For example: Code: [Select] <?php if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) { $updateSQL = sprintf("UPDATE ships SET HealthA=%s WHERE ShipID=%s", GetSQLValueString($_POST['healtha'], "int"), GetSQLValueString($_POST['shipid'], "text")); ?> <input name="healtha" type="hidden" id="healtha" value="<?php echo $row_Ships['HealthA'] + 1; ?>" /> <input name="shipid" type="hidden" id="shipid" value="<?php while($row = mysql_fetch_assoc($Ships)){ echo $row_Ships['ShipID']; }?>" /> If its not possible what else could i do to achieve this please? Thank you I've got query. Hello just wondering if someone can help me... I have a select multiple form that sends the id numbers of records I want to get... how do I construct the query to the mysql db to do this? $array-$_POST['form-array'] so something like $r = ("SELECT column from 'Table' WHERE ID = (array, values, here)") Thanks for any help. When I run this query to exclude records where id = $userarray['id'], $q = "SELECT * FROM users WHERE inst_id = '". $userarray['inst_id'] ."' AND id != '". $userarray['id'] ."' "; it actually ONLY returns records with $userarray['id'] as opposed to excluding them. Isn't the way to exclude them to use != ? 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
Have a web page that displays a number of update forms depending on the amout of records that get returned from a mysql query, if query returns 4 records then the page will display 4 identical forms, the trouble i'm having is getting the each of the 4 forms to display a different record as at the moment they all show just first record of query. I have played around with loops but not getting anywhere which may be due to my limited knowledge code for page is below Code: [Select] <?php $numberofrow =mysql_num_rows($Recordset1); for($counter = 1;$counter<=$numberofrow;$counter++){ ?> <label for="hometeam2"></label> <form id="form1" name="form1" method="POST" action="<?php echo $editFormAction; ?>"> <label for="hometeam"></label> <input name="fixid" type="text" id="fixid" value="<?php echo $row_Recordset1['FixtureID']; ?>" /> <input name="hometeam" type="text" id="hometeam2" value="<?php echo $row_Recordset1['hometeamname']; ?>" /> <label for="homescore"></label> <input name="homescore" type="text" id="homescore" value="<?php echo $row_Recordset1['homescore']; ?>" /> <label for="awayscore"></label> <label for="fixid"></label> <input name="awayscore" type="text" id="awayscore" value="<?php echo $row_Recordset1['awayscore']; ?>" /> <label for="awayteam"></label> <input name="awayteam" type="text" id="awayteam" value="<?php echo $row_Recordset1['awayteamname']; ?>" /> <input type="submit" name="update" id="update" value="Submit" /> <input type="hidden" name="MM_update" value="form1" /> Hello guys, I have an website where I have 5 category blocks in my index page to show last 3 posts in each category. But I having problems showing the content in my "last added" block. Sometimes works, and sometimes not. Dont know what is happening... Look: http://screensnapr.com/e/Zm4ssA.jpg Only "Filmes" is showing, but not "Series"... the movies block sometimes have this issue too... the code is almost the same as above, maybe is the same problem... For each category that I want to show their posts, I have a file called newhome_x.php In my templates/dirvideo/main.tpl file, I have this code: Code: [Select] <div id="page_content" class="clearfix"> {info} {newhome_final} {content} </div> In my templates/dirvideo/newhome.tpl file, I have this code: Code: [Select] <li style="margin-right: 45px;" onclick="window.location='{link}'"> <a title='{home_title}' href="{link}"><img src="[xfvalue_poster]" alt='{home_title}' /></a> <h3 style="overflow:hidden"><a title='{home_title}' href="{link}">{home_title}</a></h3> </li> in my index.php I have this code: require_once ENGINE_DIR . '/modules/newhome.php'; require_once ENGINE_DIR . '/modules/newhome_b.php'; require_once ENGINE_DIR . '/modules/newhome_c.php'; require_once ENGINE_DIR . '/modules/newhome_d.php'; require_once ENGINE_DIR . '/modules/newhome_e.php'; if (clean_url ( $_SERVER['HTTP_HOST'] ) != clean_url ( $config['http_home_url'] )) { $replace_url = array (); $replace_url[0] = clean_url ( $config['http_home_url'] ); $replace_url[1] = clean_url ( $_SERVER['HTTP_HOST'] ); } else $replace_url = false; if (!$is_logged && !in_array($_GET['do'], array('register', 'lostpassword'))) { include 'index.html'; die; } $tpl->load_template ( 'main.tpl' ); $tpl->set ( '{lastusers}', $lastusers); $tpl->set('{forumStyle}', require_once ROOT_DIR . '/engine/bullet_energy/modules/load_style.php'); require_once ENGINE_DIR . '/modules/3news.php';$source = '<div class="index_top1" style="width: 100%; padding-top: 10px; padding-bottom: 10px; " > <span style="font-size: 24px; margin-left: 10px;"><img border="0" src="templates/dirvideo/images/movies.gif"> </span> <ul class="clearfix"> {newhome} </ul> </div> <div class="index_top2" style="width: 100%; padding-top: 10px; padding-bottom: 10px; " > <span style="font-size: 24px; margin-left: 10px;"><img border="0" src="templates/dirvideo/images/serie.gif"> </span> <ul class="clearfix"> {newhome_b} </ul> </div> <div class="index_top3" style="width: 100%; padding-top: 10px; padding-bottom: 10px; " > <span style="font-size: 24px; margin-left: 10px;"><img border="0" src="templates/dirvideo/images/music.gif"> </span> <ul class="clearfix"> {newhome_c} </ul> </div> <div class="index_top6" style="width: 100%; padding-top: 10px; padding-bottom: 10px; " > <span style="font-size: 24px; margin-left: 10px;"><img border="0" src="templates/dirvideo/images/jogo.gif"> </span> <ul class="clearfix"> {newhome_e} </ul> </div> <div class="index_top4" style="width: 100%; padding-top: 10px; padding-bottom: 10px; " > <span style="font-size: 24px; margin-left: 10px;"><img border="0" src="templates/dirvideo/images/programas.gif"> </span> <ul class="clearfix"> {newhome_d} </ul> </div>'; if (!isset($_REQUEST["catalog"]) && !isset($_REQUEST["newsid"]) && !isset($_REQUEST["do"]) && !isset($_REQUEST["page"]) && !isset($_REQUEST["category"]) && !isset($_REQUEST["cat"]) && !isset($_REQUEST["action"]) && !isset($_REQUEST["id"]) && !isset($_REQUEST["name"]) && !isset($_REQUEST["news_name"]) && !isset($_REQUEST["seourl"]) && !isset($_REQUEST["findpost"]) && !isset($_REQUEST["subaction"])) { $tpl->set ( '{newhome_final}', $source);} else { $tpl->set ( '{newhome_final}', "");} $tpl->set ( '{referer}', $referer_block ); $tpl->set ( '{calendar}', $tpl->result['calendar'] ); $tpl->set ( '{top_news}', $top_news ); $tpl->set ( '{last_news}', $last_news ); $tpl->set ( '{rand_news}', $rand_news ); $tpl->set ( '{archives}', $tpl->result['archive'] ); $tpl->set ( '{tags}', $tpl->result['tags_cloud'] ); $tpl->set ( '{vote}', $tpl->result['vote'] ); $tpl->set ( '{topnews}', $topnews ); $tpl->set ( '{login}', $tpl->result['login_panel'] ); $tpl->set ( '{info}', $tpl->result['info'] ); $tpl->set ( '{speedbar}', $tpl->result['speedbar'] ); $tpl->set ( '{catlist}', $catlist ); $tpl->set ( '{moviepopular}', $tpl->result['moviepopular'] );$tpl->set ( '{newhome}', $tpl->result['newhome'] );$tpl->set ( '{newhome_b}', $tpl->result['newhome_b'] );$tpl->set ( '{newhome_c}', $tpl->result['newhome_c'] );$tpl->set ( '{newhome_d}', $tpl->result['newhome_d'] );$tpl->set ( '{newhome_e}', $tpl->result['newhome_e'] ); In my engine/modules folder I have the files: newhome.php newhome_b.php newhome_c.php newhome_d.php newhome_e.php This is the code of engine/modules/newhome_c.php file (Series block): <?php if (!isset($_REQUEST["catalog"]) && !isset($_REQUEST["newsid"]) && !isset($_REQUEST["do"]) && !isset($_REQUEST["page"]) && !isset($_REQUEST["category"]) && !isset($_REQUEST["cat"]) && !isset($_REQUEST["action"]) && !isset($_REQUEST["id"]) && !isset($_REQUEST["name"]) && !isset($_REQUEST["news_name"]) && !isset($_REQUEST["seourl"]) && !isset($_REQUEST["findpost"]) && !isset($_REQUEST["subaction"])) { if( ! defined( 'DATALIFEENGINE' ) ) { die( "Hacking Attempt!" ); } $is_change = false; if ($config['allow_cache'] != "yes") { $config['allow_cache'] = "yes"; $is_change = true;} $tpl->result['newhome_c'] = dle_cache("newhome_c", $config['skin']); $tpl->load_template( 'newhome.tpl' ); if( strpos( $tpl->copy_template, "[xfvalue_" ) !== false ) { $xfound = true; $xfields = xfieldsload();} else $xfound = false; if ($tpl->result['newhome_c'] === false) { $sql = $db->query( "SELECT id, title, date, alt_name, category,xfields,autor FROM " . PREFIX . "_post WHERE category like '%7%' ORDER BY date DESC LIMIT 3" ); while ($row = $db->get_row($sql)) { $catchecks = explode(",",$row["category"]); $catpass = false; foreach ($catchecks as $catcheck) { if ($catcheck == "7") $catpass = true; } if ($catpass) { $sql2 = $db->query( "SELECT alt_name FROM " . PREFIX . "_category WHERE id='$row[category]'" ); $row2 = $db->get_row($sql2); if( $xfound ) { $xfieldsdata = xfieldsdataload( $row['xfields'] ); foreach ( $xfields as $value ) { $preg_safe_name = preg_quote( $value[0], "'" ); if( empty( $xfieldsdata[$value[0]] ) ) { $tpl->copy_template = preg_replace( "'\\[xfgiven_{$preg_safe_name}\\](.*?)\\[/xfgiven_{$preg_safe_name}\\]'is", "", $tpl->copy_template ); } else { $tpl->copy_template = preg_replace( "'\\[xfgiven_{$preg_safe_name}\\](.*?)\\[/xfgiven_{$preg_safe_name}\\]'is", "\\1", $tpl->copy_template ); } $tpl->copy_template = str_replace( "[xfvalue_{$preg_safe_name}]", stripslashes( $xfieldsdata[$value[0]] ), $tpl->copy_template ); } } if($config['seo_type']=='1') { $link= "$row[id]".'-'."$row[alt_name]".'.html'; } else { $link="$row2[alt_name]".'/'."$row[id]".'-'."$row[alt_name]".'.html'; } $tpl->set( '{home_title}',stripslashes($row['title'])); $tpl->set( '{home_author}', stripslashes($row['autor'])); $tpl->set( '{home_date}', $row['date']); $tpl->set( '{link}',$link); $tpl->compile('newhome_c'); } } create_cache ("newhome_c", $tpl->result['newhome_c'], $config['skin']); $tpl->clear(); $db->free(); } if ($is_change) $config['allow_cache'] = false; } ?> I think that is everything here :-) Can please someone help me? This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=325939.0 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 downloadsI am having trouble showing reports for a given date range. Currently if I specify something like 11/03/2010 to 11/05/2010 I get results for all years within that month and day such as I may get results for 11/03/2008 11/03/2009 11/03/2010 11/04/2008 11/04/2009 11/04/2010 11/05/2008 11/05/2009 11/05/2010. I am using the following code $result = mysql_query("SELECT * FROM report WHERE date>='$date_begin' and date<='$date_end' ORDER BY 'date'",$db); I use the following format in my date feild mm/dd/yyyy 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 |
|||||||