Updating a field in a database not working

  • (2 Pages)
  • +
  • 1
  • 2

19 Replies - 765 Views - Last Post: 12 November 2012 - 03:29 AM Rate Topic: -----

#16 phpnovice2012  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 01-November 12

Re: Updating a field in a database not working

Posted 09 November 2012 - 10:23 AM

Oh ok, thanks very much for the help. I tried to make it dynamic by simply substituting the static URL for the variable $page
but it didn't work. Even with a static URL it didn't update my database, it only held the lyrics in the textarea after I pressed
submit.


The line "echo 'failed';" always executes, so that is why I thought it was a problem with my query.

$theLyrics = $_POST['thelyrics'];
	$edit = $_POST['edit'];
	
	if ($edit){
		
		
		//add to database using update query!
		$query = mysql_query("UPDATE lyricdata SET `lyrics` = '$theLyrics' WHERE `page_name` = '$page'");
		
		if ($query){
		echo "success";
		}
		else {
		echo "failed";              
		}
		
	
		
		
		
	
		
		
		
		
		if (empty($theLyrics)) {
		//display error saying "You didn't enter any lyrics"
		
		echo "<div id='fail'><center>You didn't enter any lyrics</center></div>";
		
		
		}
		
		}
	else{
	header("Location: http://localhost/home");
	}



This post has been edited by Dormilich: 09 November 2012 - 02:14 PM
Reason for edit:: removed unnecessary quote

Was This Post Helpful? 0
  • +
  • -

#17 Kruithne  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 99
  • View blog
  • Posts: 442
  • Joined: 28-July 09

Re: Updating a field in a database not working

Posted 09 November 2012 - 10:30 AM

Well, providing your table is called lyricdata and follows the same setup as what you posted, your query should not be failing (it does not when I run it under a duplicate setup).

Perhaps because you are passing $page which has not been defined in that snippet, it's throwing an issue as there are no records with page_name being blank. Although I can't see it throwing an error just because 0 rows were changed.

It almost sounds as though your database is not connected. You are including the connect.php on the edit.php page, right?
Was This Post Helpful? 0
  • +
  • -

#18 phpnovice2012  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 01-November 12

Re: Updating a field in a database not working

Posted 09 November 2012 - 10:32 AM

Yes I am including connect.php on all pages.

Oh well, I'll keep trying and let you know how I get on.

Thanks a lot for the help.

This post has been edited by Dormilich: 09 November 2012 - 02:14 PM
Reason for edit:: removed quote

Was This Post Helpful? 0
  • +
  • -

#19 phpnovice2012  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 01-November 12

Re: Updating a field in a database not working

Posted 09 November 2012 - 10:40 AM

I am getting this error when trying to submit changes:

<b>Notice</b>: Undefined variable: lyrics in <b>C:\xampp\htdocs\Home\edit.php</b> on line <b>46</b><br />
Was This Post Helpful? 0
  • +
  • -

#20 Kruithne  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 99
  • View blog
  • Posts: 442
  • Joined: 28-July 09

Re: Updating a field in a database not working

Posted 12 November 2012 - 03:29 AM

Hello again,

I feel like when trying to fix this issue I am just farting in the fog, so I have taken a little bit of time to write up a very basic version of the page you have shown me from the ground up and will now walk you through it.

Feel free to either write yours again based on what I have put or use it merely for reference (just don't copy and paste it, that's cheating). Normally I would be against giving out so much code but you've already given evidence of writing it, there just seems to be an unseen error.

Let's get started shall we? I've only used two pages this time, connect.php and index.php. The contents of connect.php can be found below.
<?php
	// This is where we connect to the database.
	
	$server = 'es-dev-01'; // Our database server.
	$user = 'user'; // The user we will authenticate with.
	$password = 'insertyourpasswordhere'; // The password for the above user.
	$database = 'test'; // The database we want to select.
	
	if (mysql_connect($server, $user, $password)) // Attempt to connect.
		mysql_select_db($database) or die('MySQL Error: ' . mysql_error()); // Try to select our DB or throw the error.
	else
		die('MySQL Error: ' . mysql_error()); // We could not connect, throw the error.
?>

Obviously, you will want to put your own connection details in there, especially the database name. Now, to make sure we are on the same page in every aspect, here is the code used to create the tables I used, make sure you are using the correct database before running this if you do.
DROP TABLE IF EXISTS lyricdata;

CREATE TABLE `lyricdata` (
	`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
	`title` VARCHAR(255) NULL DEFAULT NULL,
	`artist` VARCHAR(255) NULL DEFAULT NULL,
	`lyrics` LONGTEXT NULL DEFAULT NULL,
	`youtube` VARCHAR(50) NULL DEFAULT NULL,
	PRIMARY KEY (`ID`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

REPLACE INTO `lyricdata` VALUES (1, 'Code Monkey', 'Jonathan Coulton', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vestibulum metus nunc, dignissim sed vehicula ut, volutpat id ipsum. Maecenas sollicitudin tempus vulputate. Phasellus ipsum leo, lobortis id tempor a, posuere ut augue. Integer quam nisl, sagittis ut placerat aliquam, eleifend a elit. Curabitur nec velit nunc, eu congue purus. Quisque eu lectus nec purus consequat pellentesque et in justo. Vestibulum vel odio diam. Suspendisse potenti. Quisque molestie quam ut velit euismod volutpat. Nulla vitae ipsum justo. Vivamus diam lacus, rutrum quis semper vitae, aliquet et augue.', 'dQw4w9WgXcQ');

REPLACE INTO `lyricdata` VALUES (2, 'The Greatest Hits of Test', 'Tina Tester', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vestibulum metus nunc, dignissim sed vehicula ut, volutpat id ipsum. Maecenas sollicitudin tempus vulputate. Phasellus ipsum leo, lobortis id tempor a, posuere ut augue. Integer quam nisl, sagittis ut placerat aliquam, eleifend a elit. Curabitur nec velit nunc, eu congue purus. Quisque eu lectus nec purus consequat pellentesque et in justo. Vestibulum vel odio diam. Suspendisse potenti. Quisque molestie quam ut velit euismod volutpat. Nulla vitae ipsum justo. Vivamus diam lacus, rutrum quis semper vitae, aliquet et augue.\r\n\r\nNulla facilisi. Ut sagittis gravida purus ac sollicitudin. Nam id pretium magna. Pellentesque sollicitudin iaculis augue, et commodo massa congue congue. Phasellus feugiat eros sit amet augue convallis vestibulum. Praesent condimentum, nisl at pretium feugiat, neque ipsum condimentum sapien, non malesuada quam lorem ut metus. Nullam ut facilisis sapien. Curabitur lobortis sem eget nibh volutpat vel semper risus molestie. Aenean quis libero magna, at porta neque. Nunc aliquam egestas metus eget scelerisque. Nulla vitae risus purus, ac pharetra nibh.\r\n\r\nPellentesque vitae felis urna, sed placerat massa. Curabitur nulla nunc, iaculis eu tincidunt vel, fermentum ut sapien. Ut a metus sed massa volutpat fringilla. Ut dignissim vulputate neque, egestas bibendum justo malesuada sit amet. Sed dignissim semper rhoncus. Quisque diam tortor, accumsan id tristique nec, iaculis ac libero. Nunc sollicitudin metus sit amet dolor fermentum in congue quam egestas. Cras euismod lacus et ligula dictum in luctus nisi facilisis. Curabitur viverra lorem a velit mollis dictum. Vivamus et quam tellus, ut porta mi. Fusce varius lacinia lorem eget bibendum. Suspendisse dui est, ultricies a luctus ut, eleifend in arcu. Integer turpis tortor, pretium id lobortis id, volutpat nec arcu. Cras et nisi vel leo porta scelerisque. Proin eu elit non metus tincidunt pharetra in malesuada orci.\r\n\r\nNulla ac vulputate sem. Vestibulum hendrerit sagittis luctus. Pellentesque id neque at ligula consequat sollicitudin. Etiam id sollicitudin felis. Suspendisse potenti. Nam molestie, metus ultricies blandit rhoncus, mi enim suscipit turpis, quis consequat neque nunc sit amet nisl. In hac habitasse platea dictumst. Duis vel tortor ut nibh dapibus vestibulum. Phasellus et lorem vitae dolor posuere venenatis. Nullam vehicula bibendum interdum. Aenean et est vitae magna pharetra cursus id ut lorem. In fermentum magna vel nunc congue vulputate.\r\n\r\nUt enim ipsum, pellentesque id ultricies a, lobortis in orci. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Aliquam erat metus, feugiat sed viverra et, malesuada id est. Curabitur quis mauris vel metus pharetra semper. Donec vitae ligula est, non ullamcorper dolor. Aliquam suscipit, felis a tempus consectetur, enim est sodales tellus, vitae luctus quam sapien at lacus. Integer dapibus sagittis scelerisque. Aenean dictum, ligula nec fringilla dignissim, augue eros eleifend odio, scelerisque malesuada eros lorem eget odio. In sit amet libero lectus.', 'dQw4w9WgXcQ');

REPLACE INTO `lyricdata` VALUES (3, 'Debug Discography', 'Debug Duo', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vestibulum metus nunc, dignissim sed vehicula ut, volutpat id ipsum. Maecenas sollicitudin tempus vulputate. Phasellus ipsum leo, lobortis id tempor a, posuere ut augue. Integer quam nisl, sagittis ut placerat aliquam, eleifend a elit. Curabitur nec velit nunc, eu congue purus. Quisque eu lectus nec purus consequat pellentesque et in justo. Vestibulum vel odio diam. Suspendisse potenti. Quisque molestie quam ut velit euismod volutpat. Nulla vitae ipsum justo. Vivamus diam lacus, rutrum quis semper vitae, aliquet et augue.\r\n\r\nNulla facilisi. Ut sagittis gravida purus ac sollicitudin. Nam id pretium magna. Pellentesque sollicitudin iaculis augue, et commodo massa congue congue. Phasellus feugiat eros sit amet augue convallis vestibulum. Praesent condimentum, nisl at pretium feugiat, neque ipsum condimentum sapien, non malesuada quam lorem ut metus. Nullam ut facilisis sapien. Curabitur lobortis sem eget nibh volutpat vel semper risus molestie. Aenean quis libero magna, at porta neque. Nunc aliquam egestas metus eget scelerisque. Nulla vitae risus purus, ac pharetra nibh.\r\n\r\nPellentesque vitae felis urna, sed placerat massa. Curabitur nulla nunc, iaculis eu tincidunt vel, fermentum ut sapien. Ut a metus sed massa volutpat fringilla. Ut dignissim vulputate neque, egestas bibendum justo malesuada sit amet. Sed dignissim semper rhoncus. Quisque diam tortor, accumsan id tristique nec, iaculis ac libero. Nunc sollicitudin metus sit amet dolor fermentum in congue quam egestas. Cras euismod lacus et ligula dictum in luctus nisi facilisis. Curabitur viverra lorem a velit mollis dictum. Vivamus et quam tellus, ut porta mi. Fusce varius lacinia lorem eget bibendum. Suspendisse dui est, ultricies a luctus ut, eleifend in arcu. Integer turpis tortor, pretium id lobortis id, volutpat nec arcu. Cras et nisi vel leo porta scelerisque. Proin eu elit non metus tincidunt pharetra in malesuada orci.\r\n\r\nNulla ac vulputate sem. Vestibulum hendrerit sagittis luctus. Pellentesque id neque at ligula consequat sollicitudin. Etiam id sollicitudin felis. Suspendisse potenti. Nam molestie, metus ultricies blandit rhoncus, mi enim suscipit turpis, quis consequat neque nunc sit amet nisl. In hac habitasse platea dictumst. Duis vel tortor ut nibh dapibus vestibulum. Phasellus et lorem vitae dolor posuere venenatis. Nullam vehicula bibendum interdum. Aenean et est vitae magna pharetra cursus id ut lorem. In fermentum magna vel nunc congue vulputate.\r\n\r\nUt enim ipsum, pellentesque id ultricies a, lobortis in orci. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Aliquam erat metus, feugiat sed viverra et, malesuada id est. Curabitur quis mauris vel metus pharetra semper. Donec vitae ligula est, non ullamcorper dolor. Aliquam suscipit, felis a tempus consectetur, enim est sodales tellus, vitae luctus quam sapien at lacus. Integer dapibus sagittis scelerisque. Aenean dictum, ligula nec fringilla dignissim, augue eros eleifend odio, scelerisque malesuada eros lorem eget odio. In sit amet libero lectus.', 'dQw4w9WgXcQ');



Onto some actual code. The contents of index.php can be found below, with comments dotted all over explaining the process. I have also put extra notes on the code below that.
<?php
	require_once('connect.php'); // Include our database connection.

	$current_page = (isset($_GET['page']) ? $_GET['page'] : false); // If we have 'page' set, declare that or false.
	
	if (is_numeric($current_page)) // Was a number given to us?
	{
		if ($_SERVER['REQUEST_METHOD'] == 'POST') // First, we check if we have an update submitted.
		{
			$lyrics = (isset($_POST['lyrics']) ? $_POST['lyrics'] : null); // If lyrics is set, declare it otherwise null.
			
			if ($lyrics !== null)
				mysql_query(sprintf('UPDATE lyricdata SET lyrics = "%s" WHERE ID = %s', $lyrics, $current_page)); // Try to update silently.
			
			header('HTTP/1.1 303 See Other'); // Make sure they have no knowledge of ever coming to this part.
			header('Location: index.php?page=' . $current_page); // Send them back to the page they came from.
		}
		else // We don't have a submission, show the stuff.
		{
			$query = mysql_query('SELECT title, artist, lyrics, youtube FROM lyricdata WHERE ID = ' . $current_page) // Query the song ID given
				or die('Sorry, we could\'t find any data on that song!'); // Presume no data because query failed.
			
			$row = mysql_fetch_object($query); // We can only get one result (all going to plan), so no need to loop it.
			
			?>
				<h1><?php echo $row->title; ?> - <?php echo $row->artist; ?></h1>
				<form method="POST" action="index.php?page=<?php echo $current_page; ?>">
					<textarea name="lyrics" style="width: 600px; height: 300px;"><?php echo $row->lyrics; ?></textarea><br/>
					<input type="submit" value="Update Lyrics"/><br/>
					<a href="index.php">Click here to return the listing</a>
				</form>
			<?php
		}
	}
	else
	{
		// No page was set, therefore we want to list some links I would guess!
		$query = mysql_query('SELECT ID, title, artist FROM lyricdata') or die('MySQL Error: ' . mysql_error()); // Let's get some data or die trying!
		
		while ($row = mysql_fetch_object($query)) // Loop through whatever was returned from the query
			echo sprintf('<a href="index.php?page=%s">%s - %s</a><br/>', $row->ID, $row->title, $row->artist); // Format the output, all shiny like.
	}
?>

Okay, so first thing you will notice this is not perfect. One main thing that I would suggest is you start using a database abstraction layer such as PDO. (See here)

Basically, we check if the $_GET['page'] was supplied. If it was, we display a small editor for the lyrics of that song. However, if it was not, we display a list of songs that we have in the table.

Every query (bar one) has a death statement, so if anything goes wrong you should get some debug output to your screen or Apache/IIS log (providing your PHP settings are not hiding it).

Keep me updated with how you get on and let me (us) know if you need any further help.
Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2