red-x Posted July 16, 2008 Share Posted July 16, 2008 Hi guys I'm trying to update a database using a PHP script/code I'm trying to make. The problem is I'm getting this error and I don't know why. I've checked the code like 20 times and I think everything is right, well here's the code hope someone can help me.. <?php include ("config.php"); // connect and select your database if (!isset($_POST["submit"])) { //execute SQL statement $id = $_GET["id"]; $SQL = "SELECT * FROM news WHERE id = $id "; $result = mysql_query($SQL) or die(mysql_error()); $row = mysql_fetch_array($result); $title= $row["title"]; $news = $row["news"]; ?> <FORM NAME="fa" action="editsave.php" METHOD="POST"> <INPUT TYPE="hidden" NAME="id" VALUE="<?php echo $id; ?>" /> <B>Title:</B><br /><INPUT TYPE="text" name="title" VALUE="<?php echo $title; ?>" SIZE=40 /><br /> <B>News:</B><br /><TEXTAREA name="news" ROWS=5 COLS=40><?php echo $news; ?></TEXTAREA> <P><INPUT TYPE="submit" VALUE="Update" /></P> </FORM> <?php } if ($_POST["$submit"]) { $title = $_POST["title"]; $news = $_POST["news"]; //setup SQL statement $SQL= " UPDATE news SET news='$news', title='$title' WHERE id=$id "; //execute SQL statement $result = mysql_db_query($db, $SQL, $cid); //check for errors if (!$result) { echo("ERROR: " . mysql_error() . "\n$SQL\n"); } echo ("<P><B> News Updated</B></P>\n"); } ?> When I run the script this error comes out.. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 Here's the table just in case.. CREATE TABLE news ( id bigint(11) NOT NULL auto_increment, news mediumtext NOT NULL, title varchar(255) NOT NULL, newsdate varchar(255) NOT NULL, PRIMARY KEY (id) ); Thanks in advance!! Link to comment https://forums.phpfreaks.com/topic/114982-updating-a-database-using-a-php-script/ Share on other sites More sharing options...
Xurion Posted July 16, 2008 Share Posted July 16, 2008 Replace $result = mysql_query($SQL) or die(mysql_error()); with $result = mysql_query($SQL) or die(mysql_error().' SQL: '.$SQL); And see what it says then. Link to comment https://forums.phpfreaks.com/topic/114982-updating-a-database-using-a-php-script/#findComment-591356 Share on other sites More sharing options...
waynew Posted July 16, 2008 Share Posted July 16, 2008 Also, its better to use: <?php if (isset($_POST["$submit"])) ?> Link to comment https://forums.phpfreaks.com/topic/114982-updating-a-database-using-a-php-script/#findComment-591359 Share on other sites More sharing options...
Bendude14 Posted July 16, 2008 Share Posted July 16, 2008 This $SQL = "SELECT * FROM news WHERE id = $id "; Should be $SQL = "SELECT * FROM news WHERE id = '$id' "; I think values require to be quoted in SQL Link to comment https://forums.phpfreaks.com/topic/114982-updating-a-database-using-a-php-script/#findComment-591362 Share on other sites More sharing options...
Xurion Posted July 16, 2008 Share Posted July 16, 2008 They don't require it if it is numerical. But MySQL will assume the correct one if you use quotes. Link to comment https://forums.phpfreaks.com/topic/114982-updating-a-database-using-a-php-script/#findComment-591444 Share on other sites More sharing options...
kenrbnsn Posted July 16, 2008 Share Posted July 16, 2008 Did you check that $_GET['id'] exists? Ken Link to comment https://forums.phpfreaks.com/topic/114982-updating-a-database-using-a-php-script/#findComment-591448 Share on other sites More sharing options...
Xurion Posted July 16, 2008 Share Posted July 16, 2008 That's what my replacement should point out. Link to comment https://forums.phpfreaks.com/topic/114982-updating-a-database-using-a-php-script/#findComment-591515 Share on other sites More sharing options...
red-x Posted July 17, 2008 Author Share Posted July 17, 2008 Thanks for your replies guys, Okay I did what everyone said and the code changed a little bit . <?php $user = '***'; $pass = '***'; $host = 'localhost'; $db = 'test'; // connect to SQL $cid = mysql_connect($host,$user,$pass) or die(mysql_error()); mysql_select_db($db) or die(mysql_error()); //execute SQL statement if (!isset($_POST["submit"])) { $id = $_GET["id"]; $SQL = "SELECT * FROM news WHERE id = '$id' "; $result = mysql_query($SQL) or die(mysql_error().' SQL: '.$SQL); $row = mysql_fetch_array($result); $title= $row["title"]; $news = $row["news"]; ?> <FORM NAME="fa" action="editsave.php" METHOD="POST"> <INPUT TYPE="hidden" NAME="id" VALUE="<?php echo ("$id"); ?>" /> <B>Title:</B><br /><INPUT TYPE="text" name="title" VALUE="<?php echo ("$title"); ?>" SIZE=40 /><br /> <B>News:</B><br /><TEXTAREA name="news" ROWS=5 COLS=40><?php echo ("$news"); ?></TEXTAREA> <P><INPUT TYPE="submit" VALUE="Update" /></P> </FORM> <?php } if (isset($_POST["$submit"])) { $title = $_POST["title"]; $news = $_POST["news"]; //setup SQL statement $SQL= " UPDATE news SET news='$news', title='$title' WHERE id='$id'"; $result = mysql_db_query($db, "$SQL", $cid); //check for errors if (!$result) { echo("ERROR: " . mysql_error() . "\n$SQL\n"); } echo "news Updated"; } ?> So now the problem is when I hit submit to update the news, the title box and the news text area gets erase and nothing happens. Is just an empty form. Did you check that $_GET['id'] exists? Yes, I have a list of all the news and when I click the edit button it takes the id of the news to the edit page so it knows what news I want to edit. Hope anyone can help me.. Thanks in advance! Link to comment https://forums.phpfreaks.com/topic/114982-updating-a-database-using-a-php-script/#findComment-592089 Share on other sites More sharing options...
Xurion Posted July 17, 2008 Share Posted July 17, 2008 Is this online anywhere? Link to comment https://forums.phpfreaks.com/topic/114982-updating-a-database-using-a-php-script/#findComment-592244 Share on other sites More sharing options...
red-x Posted July 17, 2008 Author Share Posted July 17, 2008 No is in my localhost Link to comment https://forums.phpfreaks.com/topic/114982-updating-a-database-using-a-php-script/#findComment-592559 Share on other sites More sharing options...
Xurion Posted July 18, 2008 Share Posted July 18, 2008 Please post the html output. Link to comment https://forums.phpfreaks.com/topic/114982-updating-a-database-using-a-php-script/#findComment-593179 Share on other sites More sharing options...
awpti Posted July 18, 2008 Share Posted July 18, 2008 The below should work. Your code is a mess so I cleaned up what I could. Following things are unnecessary: echo ("$var"); echo $var is far more efficient. Both in speed and memory usage (it shows in larger applications). If you must put a variable inside a double quoted string, wrap it with curly braces like so: $sql = "SELECT * FROM my_db WHERE something = '{$value}'"; This prevents greedy token parsing, thereby significantly increasing the performance of your application. There is no need to set $db = mysql_connect if you are only connecting to one DB. Be consistent with your code. Also, allman style tabbing = win (look it up). HEREDOC's are awesome. Use them. Please don't take this as me being a prick, I'm just not eloquent. <?php $user = '***'; $pass = '***'; $host = 'localhost'; $db = 'test'; // connect to SQL mysql_connect($host, $user, $pass) or die(mysql_error()); mysql_select_db($db) or die(mysql_error()); //execute SQL statement if (!isset($_POST['submit'])) { $id = $_GET['id']; $sql = "SELECT * FROM news WHERE id = '{$id}'"; $result = mysql_query($sql) or die(mysql_error().' SQL: '.$sql); $row = mysql_fetch_assoc($result); $title= $row['title']; $news = $row['news']; echo <<<HTML <FORM NAME="fa" action="editsave.php" METHOD="POST"> <INPUT TYPE="hidden" NAME="id" VALUE="$id" /> <B>Title:</B><br /><INPUT TYPE="text" name="title" VALUE="$title" SIZE=40 /><br /> <B>News:</B><br /><TEXTAREA name="news" ROWS=5 COLS=40>$news</TEXTAREA> <P><INPUT TYPE="submit" VALUE="Update" /></P> </FORM> HTML; } if (isset($_POST["submit"])) { $title = $_POST['title']; $news = $_POST['news']; //setup SQL statement $sql= "UPDATE news SET news='{$news}', title='{$title}' WHERE id='{$id}'"; $result = mysql_query($sql) or die('Error: '.mysql_error()." - {$sql}"); if(mysql_affected_rows($result) == 1) { echo 'News Updated'; } else { echo 'Something is broken! And you will not see this message because die() would have kicked off.'; } } Link to comment https://forums.phpfreaks.com/topic/114982-updating-a-database-using-a-php-script/#findComment-593189 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.