Updating a database using a PHP script

[red-x]

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!!

[Xurion]

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.

[waynew]

Also, its better to use:

 

<?php

if (isset($_POST["$submit"])) 

?>

[Bendude14]

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

[Xurion]

They don't require it if it is numerical. But MySQL will assume the correct one if you use quotes.

[kenrbnsn]

Did you check that $_GET['id'] exists?

 

Ken

[Xurion]

That's what my replacement should point out.

[red-x]

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! 

 

 

[Xurion]

Is this online anywhere?

[red-x]

No is in my localhost

[Xurion]

Please post the html output.

[awpti]

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.';
}
}