# Solved: Editing mysql database with PHP



## Damonc (May 9, 2001)

I'm designing a website, but I've probably gotten a little bit out of my depth with.. but I've learned a fair bit so far... soo....

The website has two sides - a user side, and an 'admin' side. My problem is this: On the user side, I want 3 fields to display from a given record in the SQL database - thats no problems. However on the admin side I'd like to display those 3 fields in a form (populating the data from the database in the form), I couldn't seem to get that to work so I moved onto just having a table with an edit button - I found a tutorial for that, but I can't get the ******* to work!

When click update on the first screen, it goes to a form to populate the data - yep thats fine, except it doesn't do it.. it gives me the "echo" part of the script for the value of the field.. I can delete that, but when i click submit it just responds with "ERROR"... Can someone have a look at the code and see WTF is wrong? please?...I'm going mental.

announcers.php

```
<?php
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password="-removed-"; // Mysql password 
$db_name="fm1003"; // Database name 
$tbl_name="announcers"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
?>
[TABLE]
[TR]
[TD]
[TABLE]
[TR]
[TD][B]List data from mysql [/B] [/TD]
[/TR]

[TR]
[TD][B]Announcer[/B][/TD]
[TD][B]Air Time[/B][/TD]
[TD][B]Bio[/B][/TD]
[TD][B]Update[/B][/TD]
[/TR]
<?php
while($rows=mysql_fetch_array($result)){
?>
[TR]
[TD]<? echo $rows['announcer']; ?>[/TD]
[TD]<? echo $rows['days']; ?>[/TD]
[TD]<? echo $rows['bio']; ?>[/TD]

[TD][URL=update.php?id=<? echo $rows['id']; ?>]update[/URL][/TD]
[/TR]
<?php
}
?>
[/TABLE]
[/TD]
[/TR]
[/TABLE]
<?php
mysql_close();
?>
```
update.php


```
<?php
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password="-removed-"; // Mysql password 
$db_name="fm1003"; // Database name 
$tbl_name="announcers"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// get value of id that sent from address bar
$id=$_GET['id'];

// Retrieve data from database 
$sql="SELECT * FROM $tbl_name WHERE id='$id'";
$result=mysql_query($sql);

$rows=mysql_fetch_array($result);
?>
[TABLE]
[TR]

[TD]
[TABLE]
[TR]
[TD] [/TD]
[TD][B]Update Announcer Pages:[/B] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][B]Announcer:[/B][/TD]
[TD][B]Air Time:[/B][/TD]
[TD][B]Bio[/B][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]">[/TD]
[TD]" size="15">[/TD]
[TD]" size="15">[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]">[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[/TABLE]
[/TD]

[/TR]
[/TABLE]

<?

// close connection 
mysql_close();

?>
```
and finally update_ac.php


```
<?php
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password="-removed-"; // Mysql password 
$db_name="fm1003"; // Database name 
$tbl_name="announcers"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// update data in mysql database 
$sql="UPDATE $tbl_name SET announcer='$announcer', days='$days', bio='$bio' WHERE id='$id'";
$result=mysql_query($sql);

// if successfully updated. 
if($result){
echo "Successfully updated";
echo "
";
echo "[URL=announcers.php]View result[/URL]";
}

else {
echo "ERROR";
}

?>
```


----------



## dukevyner (Nov 4, 2011)

ok just tested it here http://channeljackers.co.cc/demo/announcers.php and i didn't get an error i got "Successfully updated"... but it didn't update.... ah ok i got it.. still not seeing the "ERROR" that you mentioned, but i added the _POST stuff that you left out on update_ac.php

```
<?php 
$host="localhost"; // Host name  
$username="admin"; // Mysql username  
$password="password"; // Mysql password  
$db_name="database"; // Database name  
$tbl_name="table"; // Table name

// Connect to server and select database. 
mysql_connect("$host", "$username", "$password")or die("cannot connect");  
mysql_select_db("$db_name")or die("cannot select DB");
[COLOR="Red"]$announcer=$_POST['announcer'];
$days=$_POST['days'];
$bio=$_POST['bio'];
$id=$_POST['id'];[/COLOR]
// update data in mysql database  
$sql="UPDATE $tbl_name SET announcer='$announcer', days='$days', bio='$bio' WHERE id='$id'"; 
$result=mysql_query($sql); 

// if successfully updated.  
if($result){ 
echo "Successfully updated"; 
echo "<BR>"; 
echo "<a href='announcers.php'>View result</a>"; 
} 

else { 
echo "ERROR"; 
} 

?>
```
 *I added the stuff in red*

which explains why it didn't update for me... but your question is a little confusing... if you wanted a admin side on the same page... why not use a login to set a session, then have and if statement like so

```
<?php session_start();
if ($_SESSION['admin'] == 'username'){include("update.php"); die(); } else{ include("announcers2.php"); die(); }
?>
```
 I put the include update.php in the if statement and the include announcers.php in the else

*this will make it display the announcers page if they are a person viewing the page and display the update page if they have logged in as an admin*
i've got a little demo here try it out if that's what you want i'll give you the code
http://channeljackers.co.cc/demo/


----------



## Damonc (May 9, 2001)

Thanks heaps for your help. As for the admin side of things, its basically a whole second site that will be password protected probably via .htaccess or similar..


----------



## dukevyner (Nov 4, 2011)

yeah that's one way to do it... I prefer php... can make it look nice... but that doesn't matter if you're the only one that sees it... if you have any more trouble, I and any one else who checks the web development forum would be happy to help


----------



## Damonc (May 9, 2001)

I just put the code that you added, into what I already have and it still doesn't work (strange)

an observation though, with your demo page you don't get the "echo" appearing in the fields when you click to edit.. but mine does. - Heres the page: http://www2.carsons.net.au:8080/announcers.php

I've double checked to make sure the database is there, and working correctly too.. but another strange thing - when I used a form to insert data to the table, it creates a "blank" record in the table.. the same form works perfectly on another table.


----------



## dukevyner (Nov 4, 2011)

Damonw said:


> I just put the code that you added, into what I already have and it still doesn't work (strange)
> 
> an observation though, with your demo page you don't get the "echo" appearing in the fields when you click to edit.. but mine does. - Heres the page: http://www2.carsons.net.au:8080/announcers.php


the page gives me 404


----------



## Damonc (May 9, 2001)

Whoops. http://www2.carsons.net.au:8080/station/announcers.php

....Haven't had my morning caffeine injection yet


----------



## dukevyner (Nov 4, 2011)

hmmm thats interesting... well as i copy pasted your code when i tested it... the issue is with your server... maybe php is not configured right... try changing one of them to use 
<?php echo $rows['bio']; ?> instead of the short hand of just <?


----------



## Damonc (May 9, 2001)

its Mac OS X server.. and php was installed by default - so quite possible! haha


----------



## dukevyner (Nov 4, 2011)

mmmm i've never used a mac server


----------



## dukevyner (Nov 4, 2011)

that did it


----------



## dukevyner (Nov 4, 2011)

but your still getting an error


----------



## Damonc (May 9, 2001)

its pretty much the same as a linux box.. its all apache2

still returns error at the end.. wonder if I should try it on a different server..


----------



## dukevyner (Nov 4, 2011)

well there has to be a solution here... but that's always an option
ok so did you add the "$id=$_POST['id'];" on the update_ac.php?


----------



## dukevyner (Nov 4, 2011)

oh and your home button doesn't work


----------



## Damonc (May 9, 2001)

Yep.. Heres what I've got now:


```
<?php
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password="pword"; // Mysql password 
$db_name="fm1003"; // Database name 
$tbl_name="announcers"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");
$announcer=$_POST['announcer'];
$days=$_POST['days'];
$bio=$_POST['bio'];
$id=$_POST['id'];

// update data in mysql database 
$sql="UPDATE $tbl_name SET announcer='$announcer', days='$days', bio='$bio' WHERE id='$id'";
$result=mysql_query($sql);

// if successfully updated. 
if($result){
echo "Successfully updated";
echo "
";
echo "[URL=announcers.php]View result[/URL]";
}

else {
echo "ERROR";
}

?>
```


----------



## Damonc (May 9, 2001)

dukevyner said:


> oh and your home button doesn't work


Yep.. I discovered that one yesterday, and added it to my list of things to do.


----------



## dukevyner (Nov 4, 2011)

on announcers you need to change the "<? echo... " to "<?php echo..." this probably wont fix it but it does need to be done... ok... perhaps the problem is in our database and not a php code... what does the structure of the database look like?


----------



## Damonc (May 9, 2001)

```
delimiter $$

CREATE TABLE `announcers` (
  `announcer` text,
  `days` text,
  `bio` text
) ENGINE=MyISAM DEFAULT CHARSET=latin1$$
```


----------



## dukevyner (Nov 4, 2011)

this might be an issue too lol(incase you didn't know)... im using chrome and my screen res is 1024x768


----------



## dukevyner (Nov 4, 2011)

ok you don't have an "id" row... it should be auto increment btw... well it might not need to be... but it seems like you would want it to be


----------



## Damonc (May 9, 2001)

wow.. someone still uses 1024x768? lol ...I'm using 1920x1080 but thats easily fixed, if I remove the "Online!" from the menu bar that will fix it.


----------



## dukevyner (Nov 4, 2011)

Damonw said:


> wow.. someone still uses 1024x768? lol ...I'm using 1920x1080 but thats easily fixed, if I remove the "Online!" from the menu bar that will fix it.


lol yes actually its still widely used... i often code with a low screen res to ensure that it looks ok for those people... my laptop has a much higher res


----------



## Damonc (May 9, 2001)

I fixed that <? echo bit and now it doesn't throw an error.. but the table isn't recording the data.. which as mentioned earlier I had a working form that couldnt save to that table either.. I might create a new one and see if that makes any difference..


----------



## dukevyner (Nov 4, 2011)

insert a new one and try again... i tried to take a look... but you must have removed the data


----------



## Damonc (May 9, 2001)

I changed them.. to a new table already.. so I've just put them back again


----------



## dukevyner (Nov 4, 2011)

ok... ur right errors gone but the data's not showing


----------



## dukevyner (Nov 4, 2011)

ok... so it's not getting the id like you want


----------



## dukevyner (Nov 4, 2011)

ok on the announcers.php... the link still has "<? echo".... it needs the "<?php"


----------



## Damonc (May 9, 2001)

nope still created an empty record.. strange.


----------



## Damonc (May 9, 2001)

dukevyner said:


> ok on the announcers.php... the link still has "<? echo".... it needs the "<?php"


fixed.


----------



## dukevyner (Nov 4, 2011)

the id is not showing up... is there one in the database?


----------



## Damonc (May 9, 2001)

I added the id column to the table if thats what you mean.


----------



## dukevyner (Nov 4, 2011)

does it have a number in it?


----------



## dukevyner (Nov 4, 2011)

tada


----------



## Damonc (May 9, 2001)

OK.. What did you do?


----------



## dukevyner (Nov 4, 2011)

lol... not quite, make sure that the id row is auto increment so that doesn't happen in the future


----------



## Damonc (May 9, 2001)

yeah its set.


----------



## dukevyner (Nov 4, 2011)

(always make sure the id is working so it knows which entry to select when it updates a db)
alright... glad to help... if you need any more help let me know


----------



## Damonc (May 9, 2001)

Thank you heaps!


----------



## dukevyner (Nov 4, 2011)

no problem


----------



## dukevyner (Nov 4, 2011)

mark as solved


----------

