dropfaith Posted September 25, 2008 Share Posted September 25, 2008 any idea why this wont work? im new with update so i have no real idea what im doing here the echo $sql; is a test and everything echos out fine but nothing gets updated into the database http://www.lawrenceguide.org/user/addhours.php?Id=15 username admin password twisted <?php $sql = "UPDATE hours SET Name = '$Name', Id = '$Id', OpenSun = '$OpenSun', CloseSun = '$CloseSun', OpenMon = '$OpenMon', CloseMon = '$CloseMon', OpenTue = '$OpenTue', CloseTue = '$CloseTue', OpenWed = '$OpenWed', CloseWed = '$CloseWed', OpenThur = '$OpenThur', CloseThur = '$CloseThur', OpenFri = '$OpenFri', CloseFri = '$CloseFri', OpenSat = '$OpenSat', CloseSat = '$CloseSat',"; $result = mysql_query($sql); // generate and execute query echo $sql; ?> Quote Link to comment https://forums.phpfreaks.com/topic/125739-solved-error-in-update-insert/ Share on other sites More sharing options...
F1Fan Posted September 25, 2008 Share Posted September 25, 2008 I see two things. First, you're ending with a comma, and second, you don't have a WHERE clause. Quote Link to comment https://forums.phpfreaks.com/topic/125739-solved-error-in-update-insert/#findComment-650182 Share on other sites More sharing options...
dropfaith Posted September 25, 2008 Author Share Posted September 25, 2008 its from a form that submits to the same page does it need a where clause? it just updates the record its displaying Quote Link to comment https://forums.phpfreaks.com/topic/125739-solved-error-in-update-insert/#findComment-650183 Share on other sites More sharing options...
F1Fan Posted September 25, 2008 Share Posted September 25, 2008 If you ran the update that you listed (assuming you remove the comma), it will update every row in the table with that information. If there is only one row in the table, and there will only ever have one, then that's OK. But if there's more than one, you need a WHERE clause. It would be structured the same way your SELECT WHERE clauses whould be (for example, "WHERE id = $id" or whatever). Quote Link to comment https://forums.phpfreaks.com/topic/125739-solved-error-in-update-insert/#findComment-650185 Share on other sites More sharing options...
Maq Posted September 25, 2008 Share Posted September 25, 2008 its from a form that submits to the same page does it need a where clause? it just updates the record its displaying The database doesn't know what record you're displaying. You need the WHERE clause to update the desired record. Like F1Fan said, if you don't have the WHERE clause it will update every record in that table. Quote Link to comment https://forums.phpfreaks.com/topic/125739-solved-error-in-update-insert/#findComment-650189 Share on other sites More sharing options...
dropfaith Posted September 25, 2008 Author Share Posted September 25, 2008 comma removed adding a where clause now it seems to only update the latest record without it tho not all of them Quote Link to comment https://forums.phpfreaks.com/topic/125739-solved-error-in-update-insert/#findComment-650203 Share on other sites More sharing options...
Maq Posted September 25, 2008 Share Posted September 25, 2008 Could you post your current query? Quote Link to comment https://forums.phpfreaks.com/topic/125739-solved-error-in-update-insert/#findComment-650207 Share on other sites More sharing options...
dropfaith Posted September 25, 2008 Author Share Posted September 25, 2008 <?php $Id = mysql_escape_string($_GET['Id']); $sql = "UPDATE hours WHERE Id = '$Id' SET Name = '$Name', Id = '$Id', OpenSun = '$OpenSun', CloseSun = '$CloseSun', OpenMon = '$OpenMon', CloseMon = '$CloseMon', OpenTue = '$OpenTue', CloseTue = '$CloseTue', OpenWed = '$OpenWed', CloseWed = '$CloseWed', OpenThur = '$OpenThur', CloseThur = '$CloseThur', OpenFri = '$OpenFri', CloseFri = '$CloseFri', OpenSat = '$OpenSat', CloseSat = '$CloseSat'"; $result = mysql_query($sql); // generate and execute query Quote Link to comment https://forums.phpfreaks.com/topic/125739-solved-error-in-update-insert/#findComment-650213 Share on other sites More sharing options...
Maq Posted September 25, 2008 Share Posted September 25, 2008 The WHERE clause belongs at the end of the query statement. Look at: http://www.tizag.com/mysqlTutorial/mysqlupdate.php Quote Link to comment https://forums.phpfreaks.com/topic/125739-solved-error-in-update-insert/#findComment-650215 Share on other sites More sharing options...
dropfaith Posted September 25, 2008 Author Share Posted September 25, 2008 UPDATE hours WHERE Id = '' SET Name = 'New', Id = '', OpenSun = '1am', CloseSun = '1am', OpenMon = '1am', CloseMon = '1am', OpenTue = '1am', CloseTue = '1am', OpenWed = '1am', CloseWed = '1am', OpenThur = '1am', CloseThur = '1am', OpenFri = '1am', CloseFri = '1am', OpenSat = '1am', CloseSat = '1am' is now displayed as the echo of $sql Quote Link to comment https://forums.phpfreaks.com/topic/125739-solved-error-in-update-insert/#findComment-650216 Share on other sites More sharing options...
Maq Posted September 25, 2008 Share Posted September 25, 2008 Did you read my last post? Quote Link to comment https://forums.phpfreaks.com/topic/125739-solved-error-in-update-insert/#findComment-650217 Share on other sites More sharing options...
dropfaith Posted September 25, 2008 Author Share Posted September 25, 2008 yea im not sure why i put it at the begining i new it needed to be in the end i think im just getting frustrated haha oh and with this nothing updates <?php $Id = mysql_escape_string($_GET['Id']); $sql = "UPDATE hours SET Name = '$Name', Id = '$Id', OpenSun = '$OpenSun', CloseSun = '$CloseSun', OpenMon = '$OpenMon', CloseMon = '$CloseMon', OpenTue = '$OpenTue', CloseTue = '$CloseTue', OpenWed = '$OpenWed', CloseWed = '$CloseWed', OpenThur = '$OpenThur', CloseThur = '$CloseThur', OpenFri = '$OpenFri', CloseFri = '$CloseFri', OpenSat = '$OpenSat', CloseSat = '$CloseSat' WHERE Id = '$Id'"; $result = mysql_query($sql); echo $sql; // generate and execute query echo of sql UPDATE hours SET Name = 'test', Id = '', OpenSun = '12am', CloseSun = '1am', OpenMon = '1am', CloseMon = '1am', OpenTue = '1am', CloseTue = '1am', OpenWed = '1am', CloseWed = '1am', OpenThur = '1am', CloseThur = '1am', OpenFri = '1am', CloseFri = '1am', OpenSat = '1am', CloseSat = '1am' WHERE Id = ''Update successful.Go Home. Quote Link to comment https://forums.phpfreaks.com/topic/125739-solved-error-in-update-insert/#findComment-650219 Share on other sites More sharing options...
Maq Posted September 25, 2008 Share Posted September 25, 2008 Put this in for error debugging: At the beginning of your PHP script: ini_set ("display_errors", "1"); error_reporting(E_ALL); And this on the end of this line: $result = mysql_query($sql) or die(mysql_error()); Quote Link to comment https://forums.phpfreaks.com/topic/125739-solved-error-in-update-insert/#findComment-650221 Share on other sites More sharing options...
F1Fan Posted September 25, 2008 Share Posted September 25, 2008 Your "Id" field is empty. If you want to update a row where the Id field is blank, then it would work. Otherwise, you need to add an "Id." Quote Link to comment https://forums.phpfreaks.com/topic/125739-solved-error-in-update-insert/#findComment-650223 Share on other sites More sharing options...
dropfaith Posted September 25, 2008 Author Share Posted September 25, 2008 i see my problem not sure how to fix it tho look at the echo of sql you see this? did i not close the where properly? im lost WHERE Id = ''Update successful.Go Home. it should be getting the id from the page $Id = mysql_escape_string($_GET['Id']); ill add error reporting now Quote Link to comment https://forums.phpfreaks.com/topic/125739-solved-error-in-update-insert/#findComment-650225 Share on other sites More sharing options...
dropfaith Posted September 25, 2008 Author Share Posted September 25, 2008 no errors its just not getting the id like it is supposed to Quote Link to comment https://forums.phpfreaks.com/topic/125739-solved-error-in-update-insert/#findComment-650226 Share on other sites More sharing options...
Maq Posted September 25, 2008 Share Posted September 25, 2008 Then make up a fake record and hard code the number in instead of using $Id to make sure everything works. Quote Link to comment https://forums.phpfreaks.com/topic/125739-solved-error-in-update-insert/#findComment-650233 Share on other sites More sharing options...
dropfaith Posted September 25, 2008 Author Share Posted September 25, 2008 solved i changed it up alot tho i ended up going with the form submitting to thanks the whole removing the comma and where clause were big but in the end it was basicly submitting without the id attached this seems to work now i just gotta fiqure out how to get the selected data from mysql and get it as the pre selected option of a select box <? // includes include("../template/conf.php"); // open database connection $connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!"); // select database mysql_select_db($db) or die ("Unable to select database!"); $Id = mysql_escape_string($_GET['Id']); $query = "SELECT * FROM hours WHERE Id = '$Id'"; $result = mysql_query($query) or die ("Error in query: $query. " . mysql_error()); echo $query; $Name = mysql_escape_string($_POST['Name']); $Id = mysql_escape_string($_POST['Id']); $OpenSun = mysql_escape_string($_POST['OpenSun']); $CloseSun = mysql_escape_string($_POST['CloseSun']); $OpenMon = mysql_escape_string($_POST['OpenMon']); $CloseMon = mysql_escape_string($_POST['CloseMon']); $OpenTue = mysql_escape_string($_POST['OpenTue']); $CloseTue = mysql_escape_string($_POST['CloseTue']); $OpenWed = mysql_escape_string($_POST['OpenWed']); $CloseWed = mysql_escape_string($_POST['CloseWed']); $OpenThur = mysql_escape_string($_POST['OpenThur']); $CloseThur = mysql_escape_string($_POST['CloseThur']); $OpenFri = mysql_escape_string($_POST['OpenFri']); $CloseFri = mysql_escape_string($_POST['CloseFri']); $OpenSat = mysql_escape_string($_POST['OpenSat']); $CloseSat = mysql_escape_string($_POST['CloseSat']); $sql = "UPDATE hours SET Name = '$Name', Id = '$Id', OpenSun = '$OpenSun', CloseSun = '$CloseSun', OpenMon = '$OpenMon', CloseMon = '$CloseMon', OpenTue = '$OpenTue', CloseTue = '$CloseTue', OpenWed = '$OpenWed', CloseWed = '$CloseWed', OpenThur = '$OpenThur', CloseThur = '$CloseThur', OpenFri = '$OpenFri', CloseFri = '$CloseFri', OpenSat = '$OpenSat', CloseSat = '$CloseSat' WHERE Id = '$Id'"; $result = mysql_query($sql) or die(mysql_error()); echo $sql; ?> Quote Link to comment https://forums.phpfreaks.com/topic/125739-solved-error-in-update-insert/#findComment-650238 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.