Jump to content

Archived

This topic is now archived and is closed to further replies.

Mutley

Edit database with PHP

Recommended Posts

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.

Share this post


Link to post
Share on other sites
<?
if ($submit){
<$sql = update foo where bar = $bar>;
}
<select * from foo>
?>
<input type=text name=bar value=<?echo $bar?>>

<input type=submit>

Share this post


Link to post
Share on other sites
So in non-pseudo code:

[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 -->
[/code]

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

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
Heres what I tried to do:

[code]<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>[/code]

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?  :)

Share this post


Link to post
Share on other sites
I'm suprised how quickly my topic went about 5 pages back.

Could anyone help me please?  :(

Share this post


Link to post
Share on other sites
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.
[code]
<?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
      }
  }
}
?>
[/code]

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

Share this post


Link to post
Share on other sites
[code]<?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
      }
  }
}
?>[/code]

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

Share this post


Link to post
Share on other sites
Done this now, changed my "team_id" to "id" and some other small fixes:

[code]<?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
      }
  }
}
?>[/code]

Still a blank white page.

Share this post


Link to post
Share on other sites
You have one too many opening curly braces ( { ) after the while statement.

[code]<?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>';
      }
  }
}
?>[/code]

Share this post


Link to post
Share on other sites
If that didnt fix it I noticed your not telling the script to connect;

[code]mysql_select_db("scores");[/code]

To

[code]mysql_select_db("scores, $connect");[/code]

Share this post


Link to post
Share on other sites
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.
[code]
<?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>
[/code]

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.

Share this post


Link to post
Share on other sites
Hi all,

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

[code]
<?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
      }
   }
}
?>
[/code]
Notice that the form action is set to '[color=red]update_scores.php[/color]' Here is the code for that file:
[code]
<?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
}
?>
[/code]

Regards,
Iceman

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
Hi Mutley,

I think you have a problem in your connection file ('[color=red]connection.php[/color]').

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

Just insert your information for the db fields ('[color=red]dbhost, dbuser, dbpass[/color]') and give it another try.

Regards,
Iceman

Share this post


Link to post
Share on other sites
Doesn't work still. I tried your way but I can assure you my connection file is fine.

Share this post


Link to post
Share on other sites
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:
[code]while($row = mysql_fetch_array( $result ))[/code]

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

Share this post


Link to post
Share on other sites
What is $result set to?

Should be something similar to

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

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

Share this post


Link to post
Share on other sites
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:
[color=blue]http://www.your-site.com/the_page.php?team_id=32[/color], then just change the [color=blue][b]MySQL[/b][/color] query on the first page to look like this:

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

Let's see if that works,
Iceman

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.