Using php/html to administer a mysql database

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
User avatar
mckinnon81
Forum Newbie
Posts: 12
Joined: Mon Mar 15, 2004 8:02 pm
Location: Sydney
Contact:

Using php/html to administer a mysql database

Post by mckinnon81 »

Hi Guys,

I am new here and need some help.

I am stuck on this and am not sure how to approach it any help you can give would be great.

Here is the code I am currently using:

Code: Select all

<?php

$db = mysql_connect("localhost","root","") or die("could not connect");
mysql_select_db("makdap", $db) or die("could not get database");
$result = mysql_query("select firstname,lastname,extension from internalphone order by firstname") or die("error w/ query");
//$result = mysql_query("select * from cathchurch order by diocese") or die("error w/ query");
$XX = "No Record Found, to search again please close this window"; 
//query details table begins
?>
<table width="100%" border="0">
<td width="20%">
	<table border="0" bgcolor="#336699" align="center">
		<th><font color="white"> Administration </th>
		<tr><td bgcolor="#CDDBEB"><font color="#006699"><center>Internal Phone Boook</td></tr>
	</table>
<tr>
<td width="20%">&nbsp;&nbsp;</td>
<td align="center">
<p>

<center>
<table width="100%" border="0" bgcolor="#336699">
	<tr>
<?			

for ($i = 0; $i < mysql_num_fields($result); $i++) &#123;
   print "<td><b><font color="white">".mysql_field_name($result, $i)."</td>";
&#125; 

?>
<?
    while($myrow = mysql_fetch_row($result)) &#123;
              echo "<tr>";
              foreach ($myrow as $field)&#123;
                     echo "<td bgcolor=#CDDBEB><font color=#006699>$field</td>";
                     &#125;
			  echo "<td bgcolor=#CDDBEB><font color=#006699>&#1111;<a href="">Edit</a>]&nbsp&#1111;<a href="">Delete</a>]</td>";	
              echo "</tr>";
        &#125;
      echo '</table>';
 mysql_close($db);
?>
What I want to acomplish is that when the link Edit or Delete are pressed it will delete or allow you to edit the entry it is next to, How can I do this?

There are a few other things, but I would like to get this one done first.

Cheers;
Matthew
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post by tim »

well what does your SQL table look like? the common method is make a 'id' column and set it as auto_increment, that way when a new row of data is injected into the table, it will add a special number to it.

this gives you a way to target specific rows in a table in a db.

ie:

Code: Select all

<?php
$sql = "UPDATE table_name SET whatever =  whatever WHERE id='$id'";

?>
to give you an example of what u could do by adding the auto inc id =]
User avatar
mckinnon81
Forum Newbie
Posts: 12
Joined: Mon Mar 15, 2004 8:02 pm
Location: Sydney
Contact:

Post by mckinnon81 »

Currently my table looks like this:

Code: Select all

mysql> select * from internalphone;
+----+-----------+---------------+-----------+
| id | FirstName | LastName      | Extension |
+----+-----------+---------------+-----------+
|  1 | Alex      | Kohn          | 236       |
|  2 | Amy       | Chu           | 207       |
|  3 | Ana       | Faaeteete     | 269       |
|  4 | Anne      | Bice          | 210       |
|  5 | Beverley  | Keogh         | 244       |
|  6 | Beyhan    | Balci         | 255       |
|  7 | Bill      | d'Apice       | 213       |
|  8 | Cheryl    | Mackenzie     | 264       |
|  9 | Debbie    | Casey         | 246       |
| 10 | Diane     | Barker        | 234       |
| 11 | Effie     | Hritis        | 247       |
| 12 | Elizabeth | Evans         | 212       |
| 13 | Fiona     | Dimitriadis   | 220       |
| 14 | Frances   | Foti          | 243       |
| 15 | Graham    | Martin        | 230       |
| 16 | Hugh      | d'Apice       | 228       |
| 17 | Helen     | Pinder        | 265       |
| 18 | Jackie    | Timewell      | 242       |
| 19 | Janine    | Crutch        | 263       |
| 20 | Jason     | Keane         | 239       |
| 21 | Javiera   | Plaza         | 224       |
| 22 | John      | d'APice       | 227       |
| 23 | John      | Baxter        | 237       |
| 24 | Karen     | Stark         | 251       |
| 25 | Maria     | Liu           | 249       |
| 26 | Maria     | Liu           | 229       |
| 27 | Mary      | Vitalone      | 240       |
| 28 | Matthew   | McKinnon      | 257       |
| 29 | Michelle  | Larnach       | 260       |
| 30 | Mila      | Zarate        | 245       |
| 31 | Nancy     | Bramley-Moore | 209       |
| 32 | Vera      | Visevic       | 283       |
| 33 | Suzanne   | Lyndon        | 226       |
| 34 | Stewart   | Roberts       | 241       |
| 35 | Sharon    | Hetherington  | 222       |
| 36 | Rosemary  | Carreras      | 208       |
| 37 | Richard   | d'Apice       | 211       |
| 38 | Rhonda    | D'Arcy        | 266       |
| 39 | Particia  | Hanna         | 219       |
| 40 | Pam       | Miller        | 225       |
| 41 | Normon    | Donato        | 231       |
| 42 | Noela     | Cini          | 8         |
| 43 | Nicholas  | d'Apice       | 256       |
+----+-----------+---------------+-----------+
43 rows in set (0.00 sec)

mysql>
Description of fields:

Code: Select all

mysql> describe internalphone;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     |      | MUL | NULL    | auto_increment |
| FirstName | varchar(20) |      |     |         |                |
| LastName  | varchar(20) |      |     |         |                |
| Extension | varchar(10) |      |     |         |                |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql>
Is this any more help?

Cheers;
Matthew
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post by tim »

well what dont u understand about the example I gave you? you have your table set-up correctly, just target the id column n use the UPDATE feature MySQl offers to edit and use the DELETE feature to delete.
User avatar
mckinnon81
Forum Newbie
Posts: 12
Joined: Mon Mar 15, 2004 8:02 pm
Location: Sydney
Contact:

Post by mckinnon81 »

But how do i do that from a hyperlink? How do i tell the hyperlink to run the mysql code to delete the enrty?

Thats what i dont know/understand.

Thanks
Matthew
User avatar
mckinnon81
Forum Newbie
Posts: 12
Joined: Mon Mar 15, 2004 8:02 pm
Location: Sydney
Contact:

Post by mckinnon81 »

Ok I've change the code to look like this:

Code: Select all

<?
    while($myrow = mysql_fetch_row($result)) &#123;
              echo "<tr>";
              foreach ($myrow as $field)&#123;
                     echo "<td bgcolor=#CDDBEB><font color=#006699>$field</td>";
                     &#125;
              echo "<td bgcolor=#CDDBEB><font color=#006699>&#1111;<a href="delete.php?id=".$myrow&#1111;"id"]."">delete</a>";	
              echo "</tr>";
        &#125;
      echo '</table>';
 mysql_close($db);
?>
Now when I hover the pointer over the link it only displays delete.php?id= with know id so when i click delete it goes to delete.php and has an error with query.

Here is what my delete.php looks like:

Code: Select all

<?php

$db = mysql_connect("localhost","root","") or die("could not connect");
mysql_select_db("makdap", $db) or die("could not get database");
$result = mysql_query("delete from internalphone where id=".$id) or die("error w/ query");
$XX = "No Record Found, to search again please close this window"; 
//query details table begins
?>
What else to i need to do to get this to work?

Thanks
Matthew
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

mckinnon81 wrote:But how do i do that from a hyperlink? How do i tell the hyperlink to run the mysql code to delete the enrty?

Thats what i dont know/understand.

Thanks
Matthew
by pointing the hyperlink to a php script that does just that... and then you could redirect the user from that same script to the page you want them to view... or echo out what you want them to see as a result.

if you want to retain the values and pass them to th e other page, safest route would be to just include the script that has the values on the update script, and go from there..

include('myfile.php');
$sql = "insert into mytable (name, lastname) values ('".$some_var_in_myFile."', '".$some_other_var."')";
User avatar
mckinnon81
Forum Newbie
Posts: 12
Joined: Mon Mar 15, 2004 8:02 pm
Location: Sydney
Contact:

Post by mckinnon81 »

I've decided to change my thinking on this and do it like this:

Code: Select all

<?php

$db = mysql_connect("localhost", "root");
mysql_select_db("makdap",$db);
$result = mysql_query("SELECT * FROM internalphone order by firstname",$db);

if ($myrow = mysql_fetch_array($result)) &#123;
echo "<table width="65%" border="0" bgcolor="#336699">\n";
echo "<tr><td><b><font color="white">First Name</td><td><b><font color="white">Last Name</td><td><b><font color="white">Extension</tr><td><b><font color="white">Action\n";

do &#123;
printf("<tr><td bgcolor=#CDDBEB><font color=#006699>%s </td><td bgcolor=#CDDBEB><font color=#006699>%s</td><td bgcolor=#CDDBEB><font color=#006699>%s</td><td bgcolor=#CDDBEB><font color=#006699>&#1111;<a href="%s?id=%s&delete=yes">delete</a>]</tr>\n", $myrow&#1111;"FirstName"], $myrow&#1111;"LastName"], $myrow&#1111;"Extension"], $PHP_SELF, $myrow&#1111;"id"]);
&#125; 

while ($myrow = mysql_fetch_array($result));
echo "</table>\n";
&#125; else &#123;
echo "Sorry, no records were found!";
&#125;

if ($delete) 
&#123;
$sql = "DELETE FROM internalphone WHERE id=$id";
mysql_query($sql);
echo "$id record deleted";
&#125;

?>
I think the problem might be this:

Code: Select all

if ($delete) 
&#123;
$sql = "DELETE FROM internalphone WHERE id=$id";
mysql_query($sql);
echo "$id record deleted";
&#125;
Or maybe it is somewhere else?

Can somebody please help me?

Thanks
Matthew
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post by tim »

i didnt look at your code in depth, but try the isset() function to see if $delete is present and then query your SQL statements.

also, wrap $id in ' ', like this '$id' in your sql statements.
Post Reply