edit in php/mysql

Question

raf08 0 Newbie Poster

14 Years Ago

hi can anyone help me with this ive trying for 2days to get this sorted but cant figure it out. i want to be able to list my data in the table which works fine then i want a link next to each data line to amend it heres my code so far.

<?php


$db = mysql_connect("localhost", "", "");
mysql_select_db(db) or die ('Unable to connect to database');

$q="SELECT * FROM mobilephones";

$result = mysql_query( $q )
or die(" - Failed More Information:<br><pre>$q</pre><br>Error: " . mysql_error());

$num_rows = mysql_num_rows($result);
if ($myrow = mysql_fetch_array($result)) {

echo "<br>A Quick View<BR><br>";
echo "<table border=1>\n";
echo "<tr><td><b>make</b></td><td>model</td><td>camera</td><td>bluetooth</td><td>internet</td><td>smartphone</td></tr>\n";

do {
printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>\n", $myrow["make"], $myrow["model"], $myrow["camera"], $myrow["bluetooth"], $myrow["internet"], $myrow["smartphone"]);
} while ($myrow = mysql_fetch_array($result));
echo "</table>\n";
} else {
echo "$ref: That record appears to be unavailable"; 
} 

mysql_free_result($result);
mysql_close($db);
?></DIV></TD></html>

any help wud be great. thanks

mysql php

3 Contributors
13 Replies
172 Views
6 Days Discussion Span
Latest Post 14 Years Ago Latest Post by Caeon

hielo 65 Veteran Poster

14 Years Ago

you would need a primary key in your table `mobilephones`. Do you have a primary key? If yes, what is it?

Also, for effiency purposes, try to avoid SELECT * if you are NOT going to use all the fields/columns in your table. Instead list only the fields/columns you need.

Caeon 0 Light Poster

14 Years Ago

Like hielo said, a primary key is necessary. I would suggest making your primary key 'id' ('id' would be a numerical column that auto-increments for each entry so that they all have a unique id).

Then you just make a link on your table like:

<?
echo "<a href=\"www.yoursite.com/edit_phone.php?id=$row[id]\">Edit</a>";
?>

Then you can just use $_GET in your SQL statement to pull up all the info about it.

There's lots of ways to go about it, but that is a pretty simple way if you just want to "get the job done".

Hope this helps!

Edited 14 Years Ago by Caeon because: forgot quote at the end of echo

hielo 65 Veteran Poster

14 Years Ago

change line 17 on your first post to:

printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td><a href=\"edit_phone.php?model=%s\">Edit</a></td></tr>\n", $myrow["make"], $myrow["model"], $myrow["camera"], $myrow["bluetooth"], $myrow["internet"], $myrow["smartphone"],$myrow['model']);

then create a page named "edit.php" where you can retrieve the specified record and provide edit fields for the specified record. You would be able to retrieve the selected record using $_GET['model']

Caeon 0 Light Poster

14 Years Ago

Change Line 12 to:

$sql = "UPDATE mobilephones SET make='$make' , model='$model' , camera='$camera' , internet='$internet' , smartphone='$smartphone' WHERE model='$_POST[ud_id]'";
mysql_query($sql);

echo $sql;

Then you can make sure all your variables are being inputed correctly.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

raf08 0 Newbie Poster · Answer 1 · 2010-11-10T15:19:47+00:00

the primary key is model andi would like to edit all fileds, can some1 help and write all the syntax for this to work as im confused stll can i replace the $_get to $_get

raf08 0 Newbie Poster · Answer 2 · 2010-11-11T21:44:10+00:00

thanks im making progress i have got the edits next to each phone and when i click on the edit link it brings up a form with the details in there but for some reason i can only edit the 1st record when i click update it says recored upadted but does not show the recored updated on the homepage. pls can u have a look at this code and tell me where i am going wrong.

<html><head><title>Edit</title>
<style type="text/css">
td {font-family: tahoma, arial, verdana; font-size: 10pt }
</style>
<h1>Edit the Required fields and submit</h1>

</head>
<body>
<?
  $_GET=$_GET['model'];
  
  $dbServer=mysql_connect("localhost","","");
  if (!$dbServer) {echo "Failed to connect to MySQL"; exit; }
      
  mysql_select_db("",$);
  $query=" SELECT * FROM mobilephones ";
  $result=mysql_query($query);
  $num=mysql_num_rows($result);

  $i=0;
  while ($i < $num) {
  $make=mysql_result($result,$i,"make");
  $model=mysql_result($result,$i,"model");
  $camera=mysql_result($result,$i,"camera");
  $internet=mysql_result($result,$i,"internet");
  $smartphone=mysql_result($result,$i,"smartphone");
?>
  <table width="600" cellpadding="10" cellspacing="0" border="2">
  <tr align="center" valign="top">
  <td align="center" colspan="1" rowspan="1" bgcolor="#64b1ff">
  <h3>Edit and Submit</h3>
  <form action="change_record.php" method="post">
  <input type="hidden" name="ud_id" value="<? echo "$model" ?>">
  make:<input type="text" name="make" value="<? echo "$make"?>"><br>
  model: <input type="text" name="model" value="<? echo "$model"?>"><br>
  camera: <input type="text" name="camera" value="<? echo "$camera"?>"><br>
  internet:<input type="text" name="internet" value="<? echo "$internet"?>"><br>
  smartphone:<input type="text" name="smartphone" value="<? echo "$smartphone"?>"><br>
  <input type="Submit" value="Update">
  </form>
  </td></tr></table>

<?
++$i;
}
?>
</body>
</html>

then the submit button takes me to this

<html><head><title></title></head>
<body>
<?
$make=$_POST['make'];
$model=$_POST['model'];
$camera=$_POST['camera'];
$internet=$_POST['internet'];
$smartphone=$_POST['smartphone'];
$dbServer=mysql_connect("localhost","","");
if (!$dbServer) {echo "Failed to connect to MySQL"; exit; }
mysql_select_db("",$);
mysql_query(" UPDATE mobilephones SET make='$make' , model='$model' , camera='$camera' , internet='$internet' , smartphone='$smartphone'");
echo "Record Updated";

?>
</body>
</html>

cud it b the $post im using but im not sure. thanks for ur help

hielo 65 Veteran Poster · Answer 3 · 2010-11-11T23:07:02+00:00

In your edit page, $_GET=$_GET['model']; really makes no sense AND You are NOT using the model value (from the previous Edit link) to filter out the results. I was expecting to see: $query=sprintf("SELECT * FROM mobilephones WHERE model='%s'", mysql_real_escape_string($_GET['model'])); which should give you the details of the specified/selected item. Furthermore, after executing that statement I was NOT expecting to see a while construct after that because if the model if in fact your primary key, it should be unique and you would get at most ONE record.

raf08 0 Newbie Poster · Answer 4 · 2010-11-13T04:04:41+00:00

thanks that works fine for me now it updates the records when the required field is updated and the update button is pressed. I would now like to make a delete function any help on how i would make the 2 forms on how to do this would it be as simple as making the same forms as edit ones but jus changing the update or edit command to a delete one.

Caeon 0 Light Poster · Answer 5 · 2010-11-13T06:28:27+00:00

I personally never delete anything in my databases. I make an additional column called "deleted" and set the value to 1 if the item is deleted. That way if something gets "deleted" on accident you can recover it.

So, if something gets deleted just use an "UPDATE" statement to set deleted = '1'.

raf08 0 Newbie Poster · Answer 6 · 2010-11-13T17:38:12+00:00

this would be a good idea but i need to have a delete command that would delete the data completley, i now have the edit and update pages sorted jus need some tips for the delete fucntion what wud b the beest way to go about it.

Caeon 0 Light Poster · Answer 7 · 2010-11-13T23:54:28+00:00

Then use "mysql_query(DELETE FROM... WHERE...)" command.

raf08 0 Newbie Poster · Answer 8 · 2010-11-15T02:00:20+00:00

im a bit confused where in my code do i put that line

<html><head><title></title></head>
<body>
<?
$make=$_POST['make'];
$model=$_POST['model'];
$camera=$_POST['camera'];
$internet=$_POST['internet'];
$smartphone=$_POST['smartphone'];
$dbServer=mysql_connect("localhost","","");
if (!$dbServer) {echo "Failed to connect to MySQL"; exit; }
mysql_select_db("",$d);
$sql = "DELETE mobilephones SET make='$make' , model='$model' , camera='$camera' , internet='$internet' , smartphone='$smartphone' WHERE model='$_POST[ud_id]'";mysql_query($sql); echo $sql;$sql = "DELETE mobilephones SET make='$make' , model='$model' , camera='$camera' , internet='$internet' , smartphone='$smartphone' WHERE model='$_POST[ud_id]'";
mysql_query($sql);



echo "Record DELETED";

?>

Caeon 0 Light Poster · Answer 9 · 2010-11-15T04:16:55+00:00

Just make a link on your display that says delete.

<a href="www.yoursite.com/delete.php?model_id=$model_id">Delete</a>

Then change your sql to this. (Info about DELETE statement: http://www.w3schools.com/sql/sql_delete.asp)

$sql = "DELETE FROM mobilephones WHERE model='$_GET[model_id]";
mysql_query($sql);