Page 1 of 1

Using php/html to administer a mysql database

Posted: Mon Mar 15, 2004 8:02 pm
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

Posted: Mon Mar 15, 2004 8:09 pm
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 =]

Posted: Mon Mar 15, 2004 8:16 pm
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

Posted: Mon Mar 15, 2004 8:19 pm
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.

Posted: Mon Mar 15, 2004 9:23 pm
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

Posted: Mon Mar 15, 2004 10:13 pm
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

Posted: Mon Mar 15, 2004 10:19 pm
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."')";

Posted: Wed Mar 17, 2004 12:04 am
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

Posted: Wed Mar 17, 2004 5:15 pm
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.