Sign in to follow this  

[web] Problem with SQL query now. Query works in PHPMyAdmin but not script

This topic is 4662 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

NEW PROBLEM HERE I have an update query that updates a given row in my MySQL database. The problem is, I can run it in PHPMyAdmin and it'll work fine if I just replase the variables with raw data values but it won't work if it's run through my PHP script. Here's the source: news_admin.php
This is to create shorter variable names
	if (isset($_POST['submit']))
		$submit = $_POST['submit'];
	if (isset($_POST['news']))
		$news = $_POST['news'];
	if (isset($_POST['subject']))
		$subject = $_POST['subject'];	

THIS GOES THROUGH IF YOU CLICK THE EDIT LINK

// Has a request to edit something been sent through?
	if (isset($edit) && ($edit))
	{
		// First set a query to get the news and subject with the correctponding news_id
		$query = "SELECT * FROM tps_news_test_1 WHERE news_id = $news_id";
		$query_result = mysql_query($query);
		
		if (mysql_query ($query))
		{
			while ($row = mysql_fetch_array ($query_result))
			{
				
				// First strip all the slashes
				if (!get_magic_quotes_gpc())
				{
					$row[news] =stripslashes($row[news]);
					$row[subject] = stripslashes($row[subject]);
				}
			
				echo "
						<form action=\"news_admin.php?news_id=$row[news_id]\" method=\"post\">
						 ID: $row[news_id] <br />
					   Subject: <input type=\"text\" name=\"subject\" size=\"150\" maxlength=\"250\" value=\"$row[subject]\"><br />
					   News:  <textarea name=\"news\" rows=\"10\" cols=\"113\">$row[news]</textarea><br />
					   <input type=\"submit\" name=\"submit\" value=\"Submit!\" />
					   </form>";
	  	}
  	}
	  else
	  {
		  echo "You are unable to edit anything at this time!" . mysql_error();
	  }
	}

THIS GOES THROUGH WHEN YOU CLICK SUBMIT AFTER YOU'VE FINISHED EDITING SOMETHING

	// Has a request to update something been sent through?
	if (isset($submit))
	{
		// Set a query to update the subject and news
		$query = "UPDATE tps_news_test_1 SET subject = $subject, news = $news WHERE news_id = $news_id";  <------- ERROR
		$query_result = mysql_query($query);
		
		if (mysql_query ($query))
		{
			echo "The news and subject was successfully updated!";
		}
		else
		{
			echo "The update was not successfully completed!" . mysql_error();
		}
	}

On the website, I go to edit an entry with a news_id of 36, a subject of "llamas are cool' and a news of 'llamas are cool' and I go to edit it, I leave the values as they are and when I click Submit, I get this error: The update was not successfully completed! 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 'are cool, news = llamas are cool WHERE news_id = 36' at line 1 If you would like me to just post the entire script, I will be glad too. [Edited by - Ekim_Gram on March 9, 2005 8:37:02 PM]

Share this post


Link to post
Share on other sites
You check for edit but you have nothing which checks for the submit value to execute the appropriate code, which of course results in a blank page. Add an if($_POST['submit']) at the end to do the update query.

Also where you have that big echo statment with all the slashes, you know you can close the php tag, write standard html and then reopen the php tag to add all teh closing braces?

Share this post


Link to post
Share on other sites
Quote:
$query = "UPDATE tps_news_test_1 SET subject = $subject, news = $news WHERE news_id = $news_id"; <------- ERROR

You need quote marks around text fields.

subject='$subject', and so forth.

Also, make sure you are escaping the string properly via mysql_escape_string().

On a more general level, I'd simply have the script abort if magic quotes or auto globals are enabled. You can force a disable via an .htaccess file or the php.ini file.

Also, I'd look into a DB abstraction layer. Not only does it allow you to switch DB's easily, they add a lot of nice shortcut commands.

PEAR's DB class, for instance, allows you to do:

$db->getRow($sql);
$db->getOne($sql);
$db->getCol($sql);
$db->getAssoc($sql);

Share this post


Link to post
Share on other sites

This topic is 4662 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this