MySQL Editing Question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
thoughtriot
Forum Commoner
Posts: 26
Joined: Thu Nov 07, 2002 9:32 pm

MySQL Editing Question

Post by thoughtriot »

Hi, let's say I have a MySQL table called news. I have the fields Title, User, Date, and Entry. For title, it's an input box where I type the title, entry is a textarea where I type the entry, and date is a hidden field which gets the date using php, and User is a select dropdown. I have this code to add an entry:

Code: Select all

<?
if($submit)
{
  $result=MYSQL_QUERY("INSERT INTO news (ID,User,Title,Month,Day,Year,Hour,Minute,Entry)".
  "VALUES ('NULL', '$User', '$Title', '$Month', '$Day', '$Year', '$Hour', '$Minute', '$Entry')");
  print "News added";
}
else
{
?>
<form method="post" action="add.php">
Staff Member:<br>
<select name='User'>
<option></option>
<?
$result = mysql_query ("SELECT * FROM staff ORDER BY Name ASC");
while($res=mysql_fetch_array($result))
{
$ID = $res["ID"];
$Name = $res["Name"];
if($ID == $User) {
$selected = selected;
} else {
$selected = "";
}
echo "<option value=$ID $selected>$Name</option>";
}
?>
</select><br>
Title:<br>
<INPUT TYPE='TEXT' NAME='Title' size=60><br>
Entry:<br>
<textarea name='Entry' cols=75 rows=20></textarea><br>
<INPUT TYPE='hidden' NAME='Month' VALUE='<? echo date("m", time() -10800); ?>' size=60>
<INPUT TYPE='hidden' NAME='Day' VALUE='<? echo date("d", time() -10800); ?>' size=60>
<INPUT TYPE='hidden' NAME='Year' VALUE='<? echo date("y", time() -10800); ?>' size=60>
<INPUT TYPE='hidden' NAME='Hour' VALUE='<? echo date("h", time() -10800); ?>' size=60>
<INPUT TYPE='hidden' NAME='Minute' VALUE='<? echo date("i", time() -10800); ?>' size=60>
<INPUT TYPE="submit" name="submit" value="Submit">
</form>
<?
}
?>

It works fine for adding. I'm using another table called staff to store the user data and then just extract it in the news adding form to select the staff member. But, when I try to edit an entry, this won't work. It says it edited it, but nothing happens to the news entry at all. None of the fields will edit. I use something like this:

Code: Select all

<?
if($Submit) {

$sql = "UPDATE news SET User='$User', Title='$Title', Entry='$Entry' WHERE ID='$ID'";
$query = mysql_query($sql) or die("Cannot query the database.<br>" . mysql_error());

echo "News edited";
} else {

$sql = "SELECT * FROM news WHERE ID='$ID'";
$query = mysql_query($sql) or die("Cannot query the database.<br>" . mysql_error());
$result = mysql_fetch_array($query);
$Title = $result["Title"];
$Entry = $result["Entry"];
$User = $result["User"];
?>
<form name="news" method="post" action="edit.php">
Staff Member:<br>
<select name='User'>
<option></option>
<?
$result = mysql_query ("SELECT * FROM staff ORDER BY Name ASC");
while($r=mysql_fetch_array($result))
{
$ID = $r["ID"];
$Title = $r["Title"];
if($ID == $User) {
$selected = selected;
} else {
$selected = "";
}
echo "<option value=$ID $selected>$Title</option>";
}
?>
</select><br>
Title:<br>
<input type="text" name="Title" value="<?php echo $Title; ?>" size=60><br>
Entry:<br>
<textarea name="Entry" rows=20 cols=75><?php echo $Entry; ?></textarea><br>
<input type="submit" name="Submit" value="Submit">
<input type="hidden" name="ID" value="<?php echo $ID; ?>">

</form>

<?php
}
?>

This isn't working like I said. I hope you can help me. Thanks.
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post by gurjit »

there is no connection to the database, you need to supply a connection string between these lines at the top:

Code: Select all

<?php
$sql = "UPDATE news SET User='$User', Title='$Title', Entry='$Entry' WHERE ID='$ID'"; 
$query = mysql_query($sql) or die("Cannot query the database.<br>" . mysql_error());

?>
something like this:

Code: Select all

<?php
$mysql_result_update = mysql_query($sql,$my_conn);
?>
or alternatively use

Code: Select all

<?php
$edit = MYSQL_QUERY("UPDATE news SET User='$User', Title='$Title', Entry='$Entry' WHERE ID='$ID'");

?>
Post Reply