Closed Thread Icon

Topic awaiting preservation: php Database update (Page 1 of 1) Pages that link to <a href="https://ozoneasylum.com/backlink?for=26539" title="Pages that link to Topic awaiting preservation: php Database update (Page 1 of 1)" rel="nofollow" >Topic awaiting preservation: php Database update <span class="small">(Page 1 of 1)</span>\
 
redroy
Bipolar (III) Inmate

From: 1393
Insane since: Dec 2003

 posted posted 08-25-2005 15:55

Hey all, I'm just learning how to use php to connect to a database and utilize/manipulate the info. I've started coding out a very simple script that allows someone to add a link to their website (like a 'links' page). My database table has four fields, id (auto increment), link, linkName, desc (description). I've managed to connect, add, and display links with no troubles at all; but when I tried to add the ability to update the links things got weird.

So here's the gist: I have a page called selectUpdate.php that displays all the links for editing

code:
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$look = "SELECT * FROM test";
$result = mysql_query($look);
$num = mysql_numrows($result);

mysql_close();

<ul>
for ($i = 0; $i < $num; $i++)
{
	$id = mysql_result($result, $i, "id");
	$link = mysql_result($result, $i, "link");
	$linkName = mysql_result($result, $i, "linkName");
	$desc = mysql_result($result, $i, "desc");
	
	echo "<li><a href=\"update.php?id={$id}\"><b>{$linkName}</b> :: Update</a><br>
		<div style=\"padding-left: 15px; font-size: 11px; color:  #333333;\">{$desc}</div></li>";
}
</ul>



Then the id is passed to update.php

code:
$id = $_GET['id'];

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query1 = "SELECT * FROM test WHERE id='$id'";
$result = mysql_query($query1);
$num = mysql_numrows($result);

mysql_close();

<form action="testUpdate.php" method="post">
for ($i = 0; $i < $num; $i++)
{
	$id = mysql_result($result, $i, "id");
	$link = mysql_result($result, $i, "link");
	$linkName = mysql_result($result, $i, "linkName");
	$desc = mysql_result($result, $i, "desc");
	
	echo "<input type=\"hidden\" name=\"ud_id\" value=\"{$id}\"><br>
		Link Name: <input type=\"text\" name=\"ud_linkName\" value=\"{$linkName}\"><br>
		Link: <input type=\"text\" name=\"ud_link\" value=\"{$link}\"><br>
		Short Description: <input type=\"text\" name=\"ud_desc\" value=\"{$desc}\"><br><br>";
}
<input type="submit" value="Update">
</form>



Then the updates are passed to testUpdate.php

code:
$ud_id = $_POST['ud_id'];
$ud_first = $_POST['ud_link'];
$ud_linkName = $_POST['ud_linkName'];
$ud_desc = $_POST['ud_desc'];

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die("Unable to select database");

$query1 = "UPDATE test SET link='$ud_link', linkName='$ud_linkName' desc='$ud_desc' WHERE id='$ud_id'";
mysql_query($query1);

mysql_close();



Which works prefectly as long as I DON'T put the desc update in. Right now, it would do nothing, but if I remove "desc='$ud_desc'" from query1 it works I'm at a loss, any ideas? Thanks!



(Edited by redroy on 08-25-2005 16:04)
DL-44
Maniac (V) Inmate

From: under the bed
Insane since: Feb 2000

 posted posted 08-25-2005 18:22

You are missing a comma -

code:
linkName='$ud_linkName' desc='$ud_desc'



s/b -

code:
linkName='$ud_linkName', desc='$ud_desc'



I would also change this -

code:
mysql_query($query1);



To this, while testing:

code:
mysql_query($query1) or DIE (mysql_error());



Which will spit out an error message that may have helped locate the problem.
redroy
Bipolar (III) Inmate

From: 1393
Insane since: Dec 2003

 posted posted 08-25-2005 18:37

Crap, sorry that comma really is there when I test it, I just messed up when I added it to show... I'll try your idea to test the query, thanks!
DL-44
Maniac (V) Inmate

From: under the bed
Insane since: Feb 2000

 posted posted 08-25-2005 19:46

Also, I found this tutorial at the GN invaluable when I first started playing with PHP/MySql -

http://www.gurusnetwork.com/tutorial/php_mysql/

If you don't get your answer by using the mysql_error(), come back with a full copy/paste of the code
H][RO
Bipolar (III) Inmate

From: Australia
Insane since: Oct 2002

 posted posted 08-26-2005 01:48

Ive noticed if your displaying this error in html or a messagebox etc, you may need to addslasshes for it to appear.
DmS
Maniac (V) Inmate

From: Sthlm, Sweden
Insane since: Oct 2000

 posted posted 08-26-2005 08:40

some things to take note of.

1. Never ever trust GET/POST/REQUEST data enough to toss it directly into an sql string!
I kid you not, you will have ppl performing sql-injections on your database...

2. this style of sql:

code:
$query1 = "UPDATE test SET link='$ud_link', linkName='$ud_linkName' desc='$ud_desc' WHERE id='$ud_id'";


should look like this:

code:
$query1 = "UPDATE test SET link='".$ud_link."', linkName='".$ud_linkName."', desc='".$ud_desc."' WHERE id='".$ud_id."'";



That is, concatenate the string together, that way you don't run the risk of inputting a string that looks like this $ud_link instead of the value that resides in the variable.

To verify that the query looks ok, first print it instead of performing it.

Then you can also test your querys at the prompt or similar by running EXPLAIN <your query>

/Dan

{cell 260} {Blog}
-{Proudly running OSX, Debian, WXP, W98, well not so proudly on the last 2...}-
-{ ?There are two major products that come out of Berkeley: LSD and UNIX. We don't believe this to be a coincidence. - Jeremy S. Anderson" }-
-{"Light travels faster than sound. This is why some people appear bright until you hear them speak.?}-
H][RO
Bipolar (III) Inmate

From: Australia
Insane since: Oct 2002

 posted posted 08-26-2005 11:52

Wont the first one actually do the same thing? If you havent set $ud_link to anything, it will treat it as "" wont it? Infact if you wanted to put $ud_link into your database you would need to do "$"."ud_link" so that it didnt parse it.
DmS
Maniac (V) Inmate

From: Sthlm, Sweden
Insane since: Oct 2000

 posted posted 08-26-2005 13:19

That suggestion from me is more a recommendation than a bugfix in this example.
In this particular case you are right H][RO, it would work since the whole string is surrounded with double quotes which tells php to parse the string for variables and similar.

In the long run though, to enclose strings with single quotes and concatenate variables into it will cut down on the work the php-parser needs to do, but if you do that and don't concatenate the strings together the $variable will indeed be parsed as a string, not a variable.

My second example should have shown that to make things clearer, like this:
'string '.$var.' string'
gives the same end result as
"string $var string"
while
'string $var string'
will make a string without parsing the variable

Proper usage of single quotes and concatenating reduces the amount of work php needs to do, imho it also improves general readability and reduces risk for errors.
/Dan

{cell 260} {Blog}
-{Proudly running OSX, Debian, WXP, W98, well not so proudly on the last 2...}-
-{ ?There are two major products that come out of Berkeley: LSD and UNIX. We don't believe this to be a coincidence. - Jeremy S. Anderson" }-
-{"Light travels faster than sound. This is why some people appear bright until you hear them speak.?}-
H][RO
Bipolar (III) Inmate

From: Australia
Insane since: Oct 2002

 posted posted 08-26-2005 16:39

yeh, i have started to do that - was just making sure i was thinking right. These days I use single quote, even for outputting html stuff because you dont have to excape the double quotes which i guess would also put less load on the parser.

There are other benefits too, like in my editor for example it shows variables as a different color but when you put them within a string it gets put as the string color - so concatenating them makes it easier too see.

A little thing i know but its nice

redroy
Bipolar (III) Inmate

From: 1393
Insane since: Dec 2003

 posted posted 08-27-2005 06:12

Alright, I've still been unseccesful getting the danged desc to update. BTW all this info (and the tutorial) have been awesome, I'm understanding this a lot better, thanks to everyone!

So here's the actual script in action: http://dev.silvergear.com/mySql/index.php

index.php

code:
<?php require("linkFunctions.php"); ?>
<?php $action = $_GET['action']; ?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html><head>
<title>Link updater</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
body, table
{
	background-color: #EEEEEE;
	font-family: Verdana, sans-serif;
	font-size: 11px;
	margin: 30px auto 30px auto;
}
hr {height: 1px; border: solid #EEEEEE 0px; border-top-width: 1px;}
form {margin: 0;}
form div
{
	text-align: right;
	padding: 0 25px 3px 0;
}
.fancyForm
{
	font-family: Verdana, sans-serif;
	font-size: 11px;
	color: #333333;
	border: 1px solid #333333;
}
.fancyBtn
{
	font-family: Verdana, sans-serif;
	font-size: 11px;
	background-color: #526E35;
	color: #FFFFFF;
	border: 1px solid #000000;
}
table.shell
{
	margin: auto;
	width: 320px;
	border: 1px solid #000000;
	background-color: #FFFFFF;
}
td.topLinks
{
	background-color: #526E35;
	border-bottom: 1px solid #000000;
	padding: 3px;
	font-weight: bold;
	text-align: center;
}
td.topLinks a {padding: 0 3px 0 3px;}
td.topLinks a:link {color: #FFFFFF; text-decoration: none;}
td.topLinks a:visited {color: #FFFFFF; text-decoration: none;}
td.topLinks a:hover {color: #FFFFFF; text-decoration: underline;}
td.topLinks a:active {color: #FFFFFF; text-decoration: none;}
td.main
{
	background-color: #FFFFFF;
	padding: 2px;
}
td.main a {padding: 0 3px 0 3px;}
td.main a:link {color: #526E35; text-decoration: none;}
td.main a:visited {color: #526E35; text-decoration: none;}
td.main a:hover {color: #000000; text-decoration: none; background-color: #EEEEEE; border-bottom: 1px solid #000000;}
td.main a:active {color: #526E35; text-decoration: none;}
</style>
</head>
<body>

<table class="shell" align="center" cellpadding="0" cellspacing="0">
	<tr>
		<td class="topLinks"><a href="index.php">Display Links</a> | 
			<a href="index.php?action=addForm">Add Link</a> | 
			<a href="index.php?action=updateSelect">Update Link</a>
			</td>
	</tr>
	<tr>
		<td class="main">
		<hr>
		<?php 
		if ($action != "addForm" && $action != "addLink" && $action != "updateSelect" && $action != "updateForm"  && $action != "update")
		{
			displayLinks(); 
		}
		if ($action == "addForm")
		{
			echo "<form action=\"index.php?action=addLink\" method=\"post\">
					<div>Link Name: <input class=\"fancyForm\" type=\"text\" name=\"linkName\"></div>
					<div>Link: <input class=\"fancyForm\" type=\"text\" name=\"link\"></div>
					<div>Short Description: <input class=\"fancyForm\" name=\"desc\" type=\"text\"></div>
					<hr>
					<div><input class=\"fancyBtn\" type=\"Submit\" value=\"Add Link\"></div>
					</form>";
		}
		if ($action == "addLink")
		{
			addLink();
		}
		if ($action == "updateSelect")
		{
			updateSelect();
		}
		if ($action == "updateForm")
		{
			updateForm();
		}
		if ($action == "update")
		{
			update();
		}
		?>
		<hr>
		</td>
	</tr>
</table>
</body>
</html>



linkFunctions.php

code:
<?php
///////////////////////////////////////////////////////////////////////////
function connectDB()
{
	$host = "*****";
	$username = "*****";
	$password = "*****";
	$database = "*****";
	
	mysql_connect($host, $username, $password);
	@mysql_select_db($database) or die( "Unable to select database");
}

/////////////////////////////////////////////////////////////////////////////
function addLink()
{
	$link = $_POST['link'];
	$linkName = $_POST['linkName'];
	$desc = $_POST['desc'];
        
    connectDB();

    $query = "INSERT INTO test VALUES ('', '$link', '$linkName', '$desc')"; 
	mysql_query($query);
	mysql_close();
	
	echo "<div align=\"center\"><b>{$linkName}</b> Added Successfully!<br><br><a href=\"index.php\">Display Link(s)</a></div>";
}

////////////////////////////////////////////////////////////////////////////
function displayLinks()
{
	connectDB();
	
	$query = "SELECT * FROM test";
	$result = mysql_query($query);
	$num = mysql_numrows($result);

	mysql_close();
	
	echo "<ul>";
	for ($i = 0; $i < $num; $i++)
	{
		$id = mysql_result($result, $i, "id");
		$link = mysql_result($result, $i, "link");
		if (strncmp("http://", $link, 7) == 1 || strncmp("http://", $link, 7) == -1)
		{
			if (strncmp("https://", $link, 8) == 0)
			{
				$link = $link;
			}
			else
			{
				$link = "http://".$link;
			}
		}
		$linkName = mysql_result($result, $i, "linkName");
		$desc = mysql_result($result, $i, "desc");
		
		echo "<li><a href=\"{$link}\" target=\"_blank\"><b>{$linkName}</b></a><br>
			<div style=\"padding-left: 15px; font-size: 11px; color: #333333;\">{$desc}</div>
			</li>";
	}
	echo "</ul>";
	
}

////////////////////////////////////////////////////////////////////////////
function updateSelect()
{
	connectDB();
	
	$query = "SELECT * FROM test";
	$result = mysql_query($query);
	$num = mysql_numrows($result);
	
	mysql_close();
	
	echo "<ul>";
	for ($i = 0; $i < $num; $i++)
	{
		$id = mysql_result($result, $i, "id");
		$link = mysql_result($result, $i, "link");
		$linkName = mysql_result($result, $i, "linkName");
		$desc = mysql_result($result, $i, "desc");
		
		echo "<li><a href=\"index.php?action=updateForm&amp;id={$id}\"><b>{$linkName}</b> > <span style=\"color: #990000;\">Update</span></a><br>
			<div style=\"padding-left: 15px; font-size: 11px; color: #333333;\">{$desc}</div></li>";
	}
	echo "</ul>";
}

////////////////////////////////////////////////////////////////////////////
function updateForm()
{	
	$id = $_GET['id'];
	
	connectDB();
	
	$query = "SELECT * FROM test WHERE id='$id'";
	$result = mysql_query($query);
	$num = mysql_numrows($result);
	
	mysql_close();
	
	for ($i = 0; $i < $num; $i++)
	{
		$id = mysql_result($result, $i, "id");
		$link = mysql_result($result, $i, "link");
		$linkName = mysql_result($result, $i, "linkName");
		$desc = mysql_result($result, $i, "desc");
		
		echo "<form action=\"index.php?action=update\" method=\"post\">
			<input type=\"hidden\" name=\"ud_id\" value=\"{$id}\">
			<div>Link Name: <input class=\"fancyForm\" type=\"text\" name=\"ud_linkName\" value=\"{$linkName}\"></div>
			<div>Link: <input class=\"fancyForm\" type=\"text\" name=\"ud_link\" value=\"{$link}\"></div>
			<div>Short Description: <input class=\"fancyForm\" type=\"text\" name=\"ud_desc\" value=\"{$desc}\"></div>
			<hr>
			<div><b>Delete Link?</b><br>
			Yes <input type=\"radio\" name=\"delete\" value=\"yes\"> No <input type=\"radio\" name=\"delete\" value=\"no\" checked></div>
			<hr>
			<div><input class=\"fancyBtn\" type=\"submit\" value=\"Update Link\"></div>
			</fom>";
	}
}
////////////////////////////////////////////////////////////////////////////
function update()
{	
	$ud_id = $_POST['ud_id'];
	$ud_link = $_POST['ud_link'];
	$ud_linkName = $_POST['ud_linkName'];
	$ud_desc = $_POST['ud_desc'];
	$delete = $_POST['delete'];
	
	connectDB();
	
	if ($delete == "no")
	{
		$query = "UPDATE test SET
				   link = '$ud_link',
				   linkName = '$ud_linkName',
				   desc = '$ud_desc'
				   WHERE id = '$ud_id'";
		$display = "<div align=\"center\"><b>{$ud_linkName}</b> Updated!<br><br><a href=\"index.php\">Display Link(s)</a></div>";
	}
	else if ($delete == "yes")
	{
		$query = "DELETE FROM test WHERE id='$ud_id'";
		$display = "<div align=\"center\"><b>{$ud_linkName}</b> Deleted!<br><br><a href=\"index.php\">Display Link(s)</a></div>";
	} 
	
	mysql_query($query) or DIE (mysql_error());
	mysql_close();
	
	echo $display;
}

?>



If you try to update a link you'll see the mysql error... Same as before if I get rid of desc = '$ud_desc' it works just fine



(Edited by redroy on 08-27-2005 06:17)
H][RO
Bipolar (III) Inmate

From: Australia
Insane since: Oct 2002

 posted posted 08-27-2005 09:33

What is the type of your $ud_id if its an integer or something then dont put any quotes around it. The same applies for stuff that isnt numbers, varchar, text etc - they must have quotes arround it. So check them .
redroy
Bipolar (III) Inmate

From: 1393
Insane since: Dec 2003

 posted posted 08-27-2005 15:25

id is an int, everything else is varchar...
I updated update() at

code:
WHERE id = '$ud_id'";

to

code:
WHERE id = $ud_id";

same error... I also updated updateForm() at

code:
<input type=\"hidden\" name=\"ud_id\" value=\"{$id}\">

to

code:
<input type=\"hidden\" name=\"ud_id\" value={$id}>

and every combination of these two updates with the same error...
redroy
Bipolar (III) Inmate

From: 1393
Insane since: Dec 2003

 posted posted 08-31-2005 04:21

Ok, I fixed it by changing 'desc' to 'description' alltogether... I think 'desc' is designated for some kind of a command or something...? Anyways, thanks again for the help.
H][RO
Bipolar (III) Inmate

From: Australia
Insane since: Oct 2002

 posted posted 08-31-2005 05:06

with the second change you made you dont need to do that, having the quotation marks there is part of the HTML markup so you should have them. You dont need the squigly brackets though just put

code:
<input type=\"hidden\" name=\"ud_id\" value=\"".$id."\">

Also if you use single quotations it will save you putting alot of slashes in, you can just do

print '<input type="hidden" name="ud_id" value="'.$id.'">';



Makes life alot easier at the end of the day - the only thing to note is you cant newline or tab in single quotes, it prints it straight out. So... if you need to do that just do.


code:
print '<input type="hidden" name="ud_id" value="'.$id.'">' . "\n";
DmS
Maniac (V) Inmate

From: Sthlm, Sweden
Insane since: Oct 2000

 posted posted 08-31-2005 16:01

in sql "desc" is commonly used in ORDER BY <collumn> DESC
which is a command for descending... so that might just be it...
Hard to spot, good find!
/Dan

{cell 260} {Blog}
-{Proudly running OSX, Debian, WXP, W98, well not so proudly on the last 2...}-
-{ ?There are two major products that come out of Berkeley: LSD and UNIX. We don't believe this to be a coincidence. - Jeremy S. Anderson" }-
-{"Light travels faster than sound. This is why some people appear bright until you hear them speak.?}-
Pugzly
Paranoid (IV) Inmate

From: 127.0.0.1
Insane since: Apr 2000

 posted posted 08-31-2005 16:27

You might also want to rip out all of those IF statements in favor of a switch, and replace

code:
if ($action == "addForm")
		{
			echo "<form action=\"index.php?action=addLink\" method=\"post\">
					<div>Link Name: <input class=\"fancyForm\" type=\"text\" name=\"linkName\"></div>
					<div>Link: <input class=\"fancyForm\" type=\"text\" name=\"link\"></div>
					<div>Short Description: <input class=\"fancyForm\" name=\"desc\" type=\"text\"></div>
					<hr>
					<div><input class=\"fancyBtn\" type=\"Submit\" value=\"Add Link\"></div>
					</form>";
		}
		if ($action == "addLink")
		{
			addLink();
		}
		if ($action == "updateSelect")
		{
			updateSelect();
		}
		if ($action == "updateForm")
		{
			updateForm();
		}
		if ($action == "update")
		{
			update();
		}



with this:

code:
switch ($action){
  case addForm:
		echo '<form action="index.php?action=addLink" method="post">
					<div>Link Name: <input class="fancyForm" type="text" name="linkName"></div>
					<div>Link: <input class="fancyForm" type="text" name="link"></div>
					<div>Short Description: <input class="fancyForm" name="desc" type="text"></div>
					<hr>
					<div><input class="fancyBtn" type="Submit" value="Add Link"></div>
					</form>';
    break;
	case addLink:		
		addLink();
		break;
	case updateSelect:
		updateSelect();
		break;
	case updateForm:
		updateForm();
		break;
	case update:
		update();
		break;
	default:
		break;
}
redroy
Bipolar (III) Inmate

From: 1393
Insane since: Dec 2003

 posted posted 09-01-2005 21:06

Ah yes, the switch, thanks pugzly.

« Backwards — Onwards »

Show Forum Drop Down Menu