Jump to content

Searching a SQL database and displaying it in a HTML table in PHP

blikecray

By blikecray
in PHP Coding Help

Recommended Posts

blikecray Newbie

blikecray

Posted
Posted

I am beginner in PHP and I was hoping someone here could help me with this question. This is probably a really easy task but I am still learning all the in's and out's of this language.

 

I have a SQL database and have created a form to update/add/delete entries through a PHP page on my web server. Basically, it's an address book where I can add entries, remove them, and update them. I want the ability to search this table of entries I have created. I would like a search box and when I submit the search box it brings up all of the entries (in a HTML table) that have the search word within them. I want this to display those results in a clean HTML table format that I can later customize with CSS.

 

This is my current code I am working with. 

<?php
ob_start();//this just buffers the header so that you dont recieve an error for returning to the same page
if(isset($_GET['id']) && $_GET['mode'] == 'edit'){
//lets get the details for the paage title
$title = "We are editing: " . $_GET['name'] . " are you sure!!!";
}
?>
<html>
<head>
<script language="javascript" type="text/javascript">
function limitText(limitField, limitCount, limitNum) {
if (limitField.value.length > limitNum) {
	limitField.value = limitField.value.substring(0, limitNum);
} else {
	limitCount.value = limitNum - limitField.value.length;
}
}
</script>
<title><?php if(!$title){?>Address Book<?php }else{ echo $title; }//end if?></title>
<style>
body{font-family:Verdana, Arial, Helvetica, Sans-Serif;font-size:11px;}
table.tableStyleClass{border-collapse:collapse;border:1px solid #cccccc;background-color:#f1f1f1;width:750px;font-family:Arial, Helvetica, sans-serif;font-size:11px;}
table.tableStyleClassTwo{border-collapse:collapse;border:1px solid #cccccc;background-color:#f1f1f1;width:350px;font-family:Arial, Helvetica, sans-serif;font-size:11px;}
th{background-color:#999999;color:#ffffff;margin:1px;}
td{border-right:1px solid #cccccc;padding:2px;text-align:center;}
.oddClassStyle{background-color:#ffffff;border-bottom:1px solid #cccccc;}
.evenClassStyle{background-color:#f1f1f1;border-bottom:1px solid #cccccc;}
</style>


</head>
<body>
<?php
// Connects to your Database
define('DB_USER', '#####');
define('DB_PASSWORD', '#####');
define('DB_HOST', '####');
define('DB_NAME', '#####');

$dbc = @mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) or die('Could not connect to MySQL: ' . mysql_error());
mysql_select_db(DB_NAME);


//we will use a case switch to look for the variable to make the decisions on what to show
//this is the variable that will control the switch case
//first lets set it looking for a query string or a post version of it
/*if(isset($_GET['id'])){
$mode = $_GET['mode'];//address bar version
$id = $_GET['id'];
}else{
$mode = $_POST['mode'];//form based version
$id = $_POST['id'];
}// now we know yay*/
//begin the switch
$mode = $_GET['mode'];
//look to see if the book is full
$checkSql="select count(id) as eCount from address";
$result = mysql_query($checkSql);
$row = mysql_fetch_assoc($result);
if($row['eCount'] == 50){
$disable = 1;
}
switch($mode){
//add a record
case 'add':
?>
<h2>Add Contact</h2>
<form name="form1" action="<?=$_SERVER['PHP_SELF'];?>?mode=added" method="post">
<table class="tableStyleClassTwo">
<tr><td>Name:</td><td><div align="left">
<input type="text" name="name" />
</div></td></tr>
<tr><td>Address:</td><td><div align="left">
<input type="text" name="address" />
</div></td></tr>
<tr><td>City:</td><td><div align="left">
<input type="text" name="city" />
</div></td></tr>
<tr><td>State:</td><td><div align="left">
<input type="text" name="state" onKeyDown="limitText(this.form.limitedtextfield,this.form.countdown,2);" 
onKeyUp="limitText(this.form.limitedtextfield,this.form.countdown,2);" maxlength="2" />
</div></td></tr>
<tr><td>Zip:</td><td><div align="left">
<input type="text" name="zip" onKeyDown="limitText(this.form.limitedtextfield,this.form.countdown,5);" 
onKeyUp="limitText(this.form.limitedtextfield,this.form.countdown,5);" maxlength="5" />
</div></td></tr>
<tr><td>Phone:</td><td><div align="left">
<input type="text" name="phone" />
</div></td></tr>
<tr><td>Email:</td><td><div align="left">
<input type="text" name="email" />
</div></td></tr>
<tr><td colspan="2" align="center"><a href="javascript:history.go(-1);">Back</a> | <input name="Submit" type="submit" id="Submit" value="Add New Contact" <?php if($disable ==1){?>disabled<?php } ?>/></td></tr>
<input type="hidden" name="mode" value="added">
</table>
</form>
<?php
break;
//added a record
case 'added':
//first setup the vars
$name = $_POST['name'];
$address = $_POST['address'];
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];
$phone = $_POST['phone'];
$email = $_POST['email'];
//then lets use'em
$sql = "INSERT INTO address (name, address, city, state, zip, phone, email) VALUES ('" . $name . "','" . $address . "','" . $city . "','" . $state . "','" . $zip . "','" . $phone . "','" . $email . "')";
//echo $sql;
//return;
mysql_query($sql);
//done take me back to the main page
header('location: ' . $_SERVER['PHP_SELF']);
break;

case 'edit':
?>
<h2>Editing: <?=$_GET['name'];?></h2>
<form name="form1" action="<?=$_SERVER['PHP_SELF'];?>?mode=edited" method="post">
<table width="399" class="tableStyleClassTwo">
<tr><td width="87">Name:</td>
<td width="551"><div align="left">
<input type="text" value="<?=$_GET['name'];?>" name="name" />
</div></td></tr>
<tr><td>Address:</td><td><div align="left">
<input type="text" value="<?=$_GET['address'];?>" name="address" />
</div></td></tr>
<tr><td>City:</td><td><div align="left">
<input type="text" value="<?=$_GET['city'];?>" name="city" />
</div></td></tr>
<tr><td>State:</td><td><div align="left">
<input type="text" value="<?=$_GET['state'];?>" name="state" onKeyDown="limitText(this.form.limitedtextfield,this.form.countdown,2);" 
onKeyUp="limitText(this.form.limitedtextfield,this.form.countdown,2);" maxlength="2" />
</div></td></tr>
<tr><td>Zip:</td><td><div align="left">
<input type="text" value="<?=$_GET['zip'];?>" name="zip" onKeyDown="limitText(this.form.limitedtextfield,this.form.countdown,5);" 
onKeyUp="limitText(this.form.limitedtextfield,this.form.countdown,5);" maxlength="5" />
</div></td></tr>
<tr><td>Phone:</td><td><div align="left">
<input type="text" value="<?=$_GET['phone'];?>" name="phone" />
</div></td></tr>
<tr><td>Email:</td><td><div align="left">
<input type="text" value="<?=$_GET['email'];?>" name="email" />
</div></td></tr>
<tr><td colspan="2" align="center"><a href="javascript:history.go(-1);">Back</a> |<input name="Submit" type="submit" value="Save Changes" /></td></tr>
<input type="hidden" name="mode" value="edited">
<input type="hidden" name="id" value="<?=$_GET['id'];?>">
</table>
</form>
<?php
break;

case 'edited':
//again clarify the vars
$name = $_POST['name'];
$address = $_POST['address'];
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];
$phone = $_POST['phone'];
$email = $_POST['email'];
$id = $_POST['id'];
//do the query
$sql = "UPDATE address SET name = '" . $name ."', address = '" . $address ."', city = '" . $city ."', state = '" . $state ."', zip = '" . $zip ."', phone = '" . $phone . "', email = '" . $email . "' WHERE id = '" . $id . "'";
mysql_query($sql);
//echo $sql;
//return;
//below you can either redirect show a message or put a link, and if you think harder you can probably do alot more
//done take me back to the main page
header('location: ' . $_SERVER['PHP_SELF']);
break;

case 'remove':
$id = $_GET['id'];
//lets remove the record this one is easy
$sql ="delete from address where id= '" . $id ."'";
//run the query
mysql_query($sql);
//echo $sql;
//return;
//done take me back to the main page
header('location: ' . $_SERVER['PHP_SELF']);
break;

//this will show the table scructure by default (ie, no actions)
default:
//opening query
$sql ="SELECT * FROM address ORDER BY name ASC";
$data = mysql_query($sql);
//you can put in an error statement if no records or just display, just do what makes sense to you, the rest will come
?>
<h2>Kevin & Beth's Address Book</h2>

<table class="tableStyleClass">
<td colspan="9" align="right" border="0"><?php if($disable!=1){?><div align="right"><a href="<?=$_SERVER['PHP_SELF'];?>?mode=add"?mode=add><img src="addButtonSmall.png" border="0"></a><?php }else{?>Contact Book is Full<?php } ?></div></td>
<tr>
<th width="300">Name</th>
<th width="300">Address</th>
<th width="100">City</th>
<th width="50">State</th>
<th width="75">Zip</th>
<th width="100">Phone</th>
<th width="200">Email</th>
<?
if($session->isAdmin()){
?>
<th width="100" colspan="2">Admin</th>
</tr>
<?
}
?>
<?php
//lets set a variable for offest coloered rows
$rowColor = 0;
//here is the loop using the statement above
while($info = mysql_fetch_array( $data )){
if($rowColor==0){
?>
<tr class="oddClassStyle">
<?php
$rowColor =1;
}elseif($rowColor==1){
?>
<tr class="evenClassStyle">
<?php
$rowColor = 0;
}
?>
<td><?=$info['name'];?></td>
<td><?=$info['address'];?></td>
<td><?=$info['city'];?></td>
<td><?=$info['state'];?></td>
<td><?=$info['zip'];?></td>
<td><?=$info['phone'];?></td>
<td><a href="mailto:<?=$info['email'];?>"><?=$info['email'];?></a></td>
<?
if($session->isAdmin()){
?>
<td><a href="<?=$_SERVER['PHP_SELF'];?>?id=<?=$info['id'];?>&name=<?=$info['name'];?>&address=<?=$info['address'];?>&city=<?=$info['city'];?>&state=<?=$info['state'];?>&zip=<?=$info['zip'];?>&phone=<?=$info['phone'];?>&email=<?=$info['email'];?>&mode=edit" ><img src="editButtonSmall.png" border="0"></a></td>
<td><a href="<?=$_SERVER['PHP_SELF'];?>?id=<?=$info['id'];?>&mode=remove"><img src="removeButtonSmall.png" border="0"></a></td>
</tr>
<?
}
?>
<?php
}
?>
</table>
<?php
break;

}//end the switch
?>
</body>
</html>
<?php ob_flush();?>
<?
}
?>

 

 

Archived

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

Go to topic listing
×
×
  • Create New...

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.