Jump to content


Photo

Edit database with PHP


  • Please log in to reply
18 replies to this topic

#1 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 21 August 2006 - 08:24 PM

I know how to add data to a table in a database using PHP and also how to delete it.

However, I'm uncertain when it comes to editing. Could anyone give me a run through of how I can get information in a database to be loaded in the form fields, which I can then edit live and submit to edit the data?

Thank-you.
~ Mutley.

#2 theblain

theblain
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 21 August 2006 - 08:55 PM

<?
if ($submit){
<$sql = update foo where bar = $bar>;
}
<select * from foo>
?>
<input type=text name=bar value=<?echo $bar?>>

<input type=submit>

#3 SharkBait

SharkBait
  • Members
  • PipPipPip
  • Advanced Member
  • 845 posts
  • LocationMetro Vancouver, BC

Posted 21 August 2006 - 09:09 PM

So in non-pseudo code:

<?php
if (isset($_POST['submit'])) {
   $strqry = "UPDATE foo SET field ='{$_POST['value']}' WHERE bar = 'woo'";
   $query = mysql_query($strqry) or die("MySQL Error: <br /> {$strqry} <br />". mysql_error());
   $num = mysql_affected_rows($query);
   if($num > 0) {
     // Database was updated
    } else {
     // database was not updated
    }
}

// Display Form
$strqry = "SELECT * FROM foo WHERE bar = 1";
$query = mysql_query($strqry) or die("MySQL Error: <br /> {$strqry} <br />". mysql_error());
$item = mysql_fetch_array($query, MYSQL_ASSOC);
?>
<!-- Do your form -->
Enter Value <input type="text" name="bar" value="<?php echo $item['bar'];?>" />
<input type="submit" value="submit" name="submit />
<!-- End Form -->

Of course you'll need data validation etc but thats the jist of updating a field in SQL using an HTML form.

#4 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 23 August 2006 - 10:41 AM

Still can't get the jist of it.

For example, my database is "scores" and I want to show all the entries in a row called "home" each entry would have a field showing the current sql/data which you can edit then resubmit.

Thanks for your time so far, it is seeming alot more complicated than I origionaly thought!
~ Mutley.

#5 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 27 August 2006 - 03:07 PM

Heres what I tried to do:

<table cellspacing="0" class="scores">
<tr>
  <td class="greenfill">Date:</td>
  <td class="greenfill">Home Team:</td>
<td class="greenfill">Away Team:</td>
<td class="greenfill">Result:</td>
<td class="greenfill" colspan="2">Score <i></i>:</td>
<td class="greenfill">Description</td>
</tr>
<?php
require_once("connection.php");

if (isset($_POST['submit'])) {
   $strqry = "UPDATE scores SET field ='{$_POST['value']}'";
   $query = mysql_query($strqry) or die("MySQL Error: <br /> {$strqry} <br />". mysql_error());
   $num = mysql_affected_rows($query);
   if($num > 0) {
     // Database was updated
    } else {
     // database was not updated
    }
}

// Display Form
$strqry = "SELECT * FROM scores";
$query = mysql_query($strqry) or die("MySQL Error: <br /> {$strqry} <br />". mysql_error());
$item = mysql_fetch_array($query, MYSQL_ASSOC);


	while(list($date, $home, $away, $scorehome, $scoreaway) = mysql_fetch_row($result)) {
		
		?>
  <tr>
  	<td class="vs" colspan="8"><?=$home?> vs <?=$away?></td>
  </tr>
  <tr>
    <td>05-09-2006</td>
    <td><input type="text" name="scoreaway" value="<?php echo $item['home'];?>" /></td>
    <td><input type="text" name="scoreaway" value="<?php echo $item['away'];?>" /></td>
    <td>
	
		<?
		if($home == York) {
		if($scorehome > $scoreaway) {
		$home?> <p class="win">Win</p> <?
		}
		else {
		$away?> <p class="loss">Loss</p> <?
		}
		}
		
		else {
		if($away == York) {
		if($scorehome < $scoreaway) {
		$home?> <p class="win">Win</p> <?
		}
		else {
		$away?> <p class="loss">Loss</p> <?
		}
		}
		}
		?>	</td>
    <td><input type="text" name="scorehome" value="<?php echo $item['scorehome'];?>" /></td>
    <td><input type="text" name="scoreaway" value="<?php echo $item['scoreaway'];?>" /></td>
    <td>N/A</td>
  </tr>
  }
  }
<input type="submit" value="submit" name="submit />
</table>

Make it list all the entries in the database table "scores" so they are all editable fields in a PHP form. Unfortunatly, as you can see, lots of errors which I'm sure to you are obvious, if anyone could quickly debug it for me please?  :)
~ Mutley.

#6 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 28 August 2006 - 06:50 PM

I'm suprised how quickly my topic went about 5 pages back.

Could anyone help me please?  :(
~ Mutley.

#7 Iceman512

Iceman512
  • Members
  • PipPipPip
  • Advanced Member
  • 81 posts

Posted 28 August 2006 - 07:28 PM

Hi Mutley,

I've just submitted this in another post, but it should work fine for your needs as well, just needs some modification to work for your situation.
<?php
$con = mysql_connect("dbhost","dbuser","dbpass");
if (!$con)
  {
  die('Could not connect to the database: ' . mysql_error());
  }

if(isset($_POST['id'])){
$id = $_POST['id'];
mysql_select_db("dbname", $con);
$result = mysql_query("SELECT * FROM dbname WHERE id='$id';");
while($row = mysql_fetch_array( $result ))
    {
{
?>
<form action="Update/Form/Page" method="POST">
<input type="hidden" name="id" value="<?php echo $row['id']; ?>">
Author:
<input type="text" name="author" value="<?php echo $row['author']; ?>">
<br />
Date: 
<input type="text" name="date" value="<?php echo $row['date']; ?>">
Body: </font><br />
<textarea name="body"><?php echo $row['body']; ?></textarea><br /><br />
<table align="center" width="800"><tr><td align="center">
<input type="submit" value="Update Form">
</td></tr></table>
</form>
</td></tr></table>
<?php
      }
   }
}
?>

Note that it gets everything according to the unique id of each row in the db.
Post again if you need help making modifications to this.

Regards,
Iceman

#8 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 28 August 2006 - 09:10 PM

<?php
require_once("connection.php");

if(isset($_POST['id'])){
$id = $_POST['id'];
mysql_select_db("scores");
$result = mysql_query("SELECT * FROM scores WHERE team_id='$id';");
while($row = mysql_fetch_array( $result ))
    {
{
?>
<form action="Update/Form/Page" method="POST">
<input type="hidden" name="id" value="<?php echo $row['team_id']; ?>">
Author:
<input type="text" name="author" value="<?php echo $row['home']; ?>">
<br />
<input type="text" name="author" value="<?php echo $row['away']; ?>">
<br />
<input type="text" name="author" value="<?php echo $row['homescore']; ?>">
<br />
<input type="text" name="author" value="<?php echo $row['awayscore']; ?>">
<br />
<input type="text" name="date" value="<?php echo $row['date']; ?>">
</font><br />
<table align="center" width="800"><tr><td align="center">
<input type="submit" value="Update Form">
</td></tr></table>
</form>
</td></tr></table>
<?php
      }
   }
}
?>

That's what I'm trying. My "ID" row is called "team_id". It just brings up a blank page.
~ Mutley.

#9 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 28 August 2006 - 09:55 PM

Done this now, changed my "team_id" to "id" and some other small fixes:

<?php
require_once("connection.php");

if(isset($_POST['id'])){
$id = $_POST['id'];
mysql_select_db("scores");
$result = mysql_query("SELECT * FROM scores WHERE id='$id';");
while($row = mysql_fetch_array( $result ))
    {
{
?>
<form action="update" method="POST">
<input type="hidden" name="id" value="<?php echo $row['id']; ?>">
Author:
<input type="text" name="home" value="<?php echo $row['home']; ?>">
<br />
<input type="text" name="away" value="<?php echo $row['away']; ?>">
<br />
<input type="text" name="homescore" value="<?php echo $row['homescore']; ?>">
<br />
<input type="text" name="awayscore" value="<?php echo $row['awayscore']; ?>">
<br />
<input type="text" name="date" value="<?php echo $row['date']; ?>">
</font><br />
<table align="center" width="800"><tr><td align="center">
<input type="submit" value="Update Form">
</td></tr></table>
</form>
</td></tr></table>
<?php
      }
   }
}
?>

Still a blank white page.
~ Mutley.

#10 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 28 August 2006 - 10:19 PM

You have one too many opening curly braces ( { ) after the while statement.

<?php
require_once("connection.php");

if(isset($_POST['id'])){
	$id = $_POST['id'];
	mysql_select_db("scores");
	$result = mysql_query("SELECT * FROM scores WHERE id='$id';");
	while($row = mysql_fetch_array($result)) {
		echo '
		<form action="update" method="POST">
		<input type="hidden" name="id" value="' . $row['id'] . '">
		Author:
		<input type="text" name="home" value="' . $row['home'] . '">
		<br />
		<input type="text" name="away" value="' . $row['away'] . '">
		<br />
		<input type="text" name="homescore" value="' . $row['homescore'] . '">
		<br />
		<input type="text" name="awayscore" value="' . $row['awayscore'] . '">
		<br />
		<input type="text" name="date" value="' . $row['date'] . '">
		</font><br />
		<table align="center" width="800">
			<tr>
				<td align="center"><input type="submit" value="Update Form"></td>
			</tr>
		</table>
		</form>';
      }
   }
}
?>


#11 spfoonnewb

spfoonnewb
  • Members
  • PipPipPip
  • Advanced Member
  • 276 posts

Posted 29 August 2006 - 02:48 AM

If that didnt fix it I noticed your not telling the script to connect;

mysql_select_db("scores");

To

mysql_select_db("scores, $connect");


#12 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 29 August 2006 - 06:35 AM

1st thing i see, or don't see, rather, is any kind of query to actually update the database.  Sharkbait gave an example waaay up there ^^^^^^^ but then in all your code since then..where did it go? Did you seperate that part into a target processing script that the form will send the posted variables to? 

This leads me to 2nd thing I see: i look at your form action, and all you have there is action="update".  perhaps you meant update.php ? or is the script that you have shown, update.php?  In other words, are you wanting to do all of this in one script, or are you making your form on this script here, and the part that processes the info will be in some other script?

Let's assume that you were meaning for it to all be on on script.  It seems to me that you want a form that auto-fills in the blanks, and you can change the values, click submit, and it updates the information in the database. is this correct?

Here is a sample piece of code i threw together. it doesn't really do a whole lot. It's all on one script. There are 2 forms. One where you can enter in the id to show information on.  If you enter in an existing id, it will show the information currently in the db associated with the id, in the 2nd form.  you can then modify it, click on update, and it will update the db. i have 2 fields that can be altered, as an example. expand the update query and the form to suit your needs. Also, you need to change the db connection info to your own, as well as the table and column names.  The ones used below are what i used to test the script.
<?php
   // connect to the database
   $link = mysql_connect("localhost", "username", "password") or die(mysql_error());
   //select the database
   $db_selected = mysql_select_db("databasename", $link) or die(mysql_error());

   // make some vars out of the post array for easier coding
   if ($_POST) { extract($_POST); } 
   
   // if user clicked the submit update button
   if ($update) {
      // query string to update the information. 
      $sql = "update test set name = '$name', type = '$type' where serialnum = '$serialnum'";
      // run the query. 
      $result = mysql_query($sql) or die(mysql_error());
   } // end if user clicked submit
   
   // check to see if number was entered, and is numeric. if
   // either is false, set default number to zero. this is 
   // mostly for security.
   $findnum = (is_numeric($_POST['findnum'])) ? $_POST['findnum'] : 0;
	 
   // select information from db. if the user entered 
   // in a serialnum then it will return a result
   $sql = "select * from test where serialnum = '$findnum'";
   $result = mysql_query($sql) or die(mysql_error());
   
   // if there is a result...
   $rows = mysql_num_rows($result);
   if ($rows > 0) {
      // get the information from the result source
      $info = mysql_fetch_assoc($result);
      // extract the data into easy to use vars
      extract($info);
   } // end if results were found
?>

<-- plain old generic table with a border to seperate the two forms -->
<table border = '1'>
   <tr>
      <td>
         <-- form to enter in the number to search for -->
         <form action = "<?php echo $_SERVER['PHP_SELF']; ?>" method = "post">
	    id <input type = 'text' name = 'findnum'>
	       <input type = 'submit' name = 'getinfo' value = 'getinfo'>
	 </form>
      </td>
   </tr>
   <tr>
      <td align = 'right'>
         <-- form that shows the info if found, edit it and update it in db -->
         <form action = "<?PHP echo $_SERVER['PHP_SELF']; ?>" method = "post">
	    id : <?php echo $serialnum; ?><br>
            name <input type = 'text' name = 'name' value = '<?php echo $name; ?>'><br>
            type  <input type = 'text' name = 'type' value = '<?php echo $type; ?>'><br>
                    <input type = 'submit' name = 'update' value = 'update'>
         </form>
      </td>
   </tr>
</table>

please understand that the only reason why i did this "out of the box" thing for you is because you and everybody else is spending a lot of time trying to debug some code that will not do what you are trying to do -- update the database --  even if you work out the syntax errors.  It is not the normal function of this forum for people to write your code for you.  I was just really bored, and to be honest, felt it was a lot easier to do this rather than try to get you back on track from where you're at.

also note that i am not some l337 scripter so this is by no means the most elegant way of doing it. i'm sure plenty of people will offer their 2 cents on how to make it better.
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#13 Iceman512

Iceman512
  • Members
  • PipPipPip
  • Advanced Member
  • 81 posts

Posted 29 August 2006 - 07:42 AM

Hi all,

Crayon Violent is absoloutely right. Man! I feel like an idiot!
If the snippet above doesn't work, try this:

<?php
$con = mysql_connect("dbhost","dbuser","dbpass");
if (!$con)
  {
  die('Could not connect to the database: ' . mysql_error());
  }

if(isset($_POST['team_id'])){
$team_id = $_POST['team_id'];
mysql_select_db("scores", $con);

$result = mysql_query("SELECT * FROM scores WHERE team_id='$team_id';");
while($row = mysql_fetch_array( $result ))
   {
{
?>
<form action="update_scores.php" method="POST">
<input type="hidden" name="team_id" value="<?php echo $row['team_id']; ?>">
Home:
<input type="text" name="home" value="<?php echo $row['home']; ?>">
<br />
Away:
<input type="text" name="away" value="<?php echo $row['away']; ?>">
<br />
Home Score:
<input type="text" name="homescore" value="<?php echo $row['homescore']; ?>">
<br />
Away Score:
<input type="text" name="awayscore" value="<?php echo $row['awayscore']; ?>">
<br />
Date:
<input type="text" name="date" value="<?php echo $row['date']; ?>">
</font><br />
<table align="center" width="800"><tr><td align="center">
<input type="submit" value="Update Details">
</td></tr></table>
</form>
</td></tr></table>
<?php
      }
   }
}
?>
Notice that the form action is set to 'update_scores.php' Here is the code for that file:
<?php
$con = mysql_connect("dbhost","dbuser","dbpass");
if (!$con)
  {
  die('Could not connect to the database: ' . mysql_error());
  }

if(isset($_POST['team_id'])){
$team_id = $_POST['team_id'];
$home = $_POST['home'];
$away = $_POST['away'];
$homescore = $_POST['homescore'];
$awayscore = $_POST['awayscore'];
$date = $_POST['date'];

mysql_select_db("scores", $con);

mysql_query("UPDATE scores SET home='$home', away='$away', homescore='$homescore', 
awayscore='$awayscore', date='$date' WHERE team_id = '$team_id'");

mysql_close($con);
  echo "The selected information was updated!";
  echo "<a href='somewhere.php'>Click here</a> to continue";
?>

<!-- Some HTML Here If You Like -->

<?php 
}
?>

Regards,
Iceman

#14 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 29 August 2006 - 08:18 AM

I got Crayon Violent to work but I want it to repeat like Icemans is supposed to do (but it just brings up a blank page still, if I use Icemans).

I think it's a problem in the update_scores.php?

Thanks so much for the help/work guys, it really means alot!
~ Mutley.

#15 Iceman512

Iceman512
  • Members
  • PipPipPip
  • Advanced Member
  • 81 posts

Posted 29 August 2006 - 08:32 AM

Hi Mutley,

I think you have a problem in your connection file ('connection.php').

I have modified my previous post above. Notice that I removed the following line :
<?php
require_once("connection.php");
?>

Just insert your information for the db fields ('dbhost, dbuser, dbpass') and give it another try.

Regards,
Iceman

#16 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 29 August 2006 - 08:59 AM

Doesn't work still. I tried your way but I can assure you my connection file is fine.
~ Mutley.

#17 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 29 August 2006 - 09:35 AM

I fixed it partly, changed POST to GET in the id lines. So it calls it from the URL ?team_id=2 etc.

However I get this error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource on line 13

Which is:
while($row = mysql_fetch_array( $result ))

There is rows in the table, can't see where this error is coming from.

~ Mutley.

#18 SharkBait

SharkBait
  • Members
  • PipPipPip
  • Advanced Member
  • 845 posts
  • LocationMetro Vancouver, BC

Posted 29 August 2006 - 12:24 PM

What is $result set to?

Should be something similar to

<?php
$result = mysql_query($Whatevermyqueryis) or die("MySQL Error: <br />{$Whatevermyqueryis}<br />". mysql_error());
?>

Not sure if your current code has it but make sure there one Curly Brace {  after your while()

#19 Iceman512

Iceman512
  • Members
  • PipPipPip
  • Advanced Member
  • 81 posts

Posted 29 August 2006 - 01:47 PM

Hi Mutley,

With the code I supplied earlier, I assumed you were using a form to get the data. If you want to use a link such as:
http://www.your-site.com/the_page.php?team_id=32, then just change the MySQL query on the first page to look like this:

<?php
$result = mysql_query("SELECT * FROM scores WHERE team_id='".$team_id."'");
?>

Let's see if that works,
Iceman




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users