Page 1 of 1

This is a long one but I would appreciate the help

Posted: Wed Dec 22, 2004 6:51 am
by mohson
Ok guys Ill keep it simple I have a page which displays my table results in alternating coloured rows this page also has a pagination function which allows you to scroll through results.

I have created a search text box on this page but when a search is conducted - the results are printed in plain text with no formatting I want to know how I could incorporate my original formatting i.e described above into my search results page, I thought all I would need to do is copy all the code but simply change the query - this doesnt seem to work though

Below is the code for my original page:

Code: Select all

<?php


// config-------------------------------------
$host = "**********8"; //your database host
$user = "*******"; // your database user name
$pass = "********"; // your database password
$db = "contact_management_system"; // your database name

$filename = "people.html"; // name of this file
$option = array (5, 10, 20, 50, 100, 200);
$default = 10; // default number of records per page
$action = $_SERVER['PHP_SELF']; // if this doesn't work, enter the filename
$query = "SELECT * FROM people ORDER BY firstname";  // database query. Enter your query here
// end config---------------------------------

	
$opt_cnt = count ($option);

$go = $_GET['go'];
// paranoid
if ($go == "") {
$go = $default;
}
elseif (!in_array ($go, $option)) {
$go = $default;
}
elseif (!is_numeric ($go)) {
$go = $default;
}
$nol = $go;
$limit = "0, $nol";
$count = 1; 

echo "<form name="form1" id="form1" method="get" action="$action">\r\n";
echo "<select name="go" id="go">\r\n";

for ($i = 0; $i <= $opt_cnt; $i ++) {
if ($option[$i] == $go) {
echo "<option value="".$option[$i]."" selected="selected">".$option[$i]."</option>\r\n";
} else {
echo "<option value="".$option[$i]."">".$option[$i]."</option>\r\n";
}
}

echo "</select>\r\n";
echo "<input type="submit" name="Submit" id="Submit" value="Go" />\r\n";
echo "</form>\r\n";

$connection = mysql_connect ($host, $user, $pass) or die ("Unable to connect");
mysql_select_db ($db) or die ("Unable to select database $db");



// control query------------------------------
/* this query checks how many records you have in your table.
I created this query so we could be able to check if user is
trying to append number larger than the number of records
to the query string.*/
$off_sql = mysql_query ("$query") or die ("Error in query: $off_sql".mysql_error());
$off_pag = ceil (mysql_num_rows($off_sql) / $nol);
//-------------------------------------------- 


$off = $_GET['offset'];
//paranoid
if (get_magic_quotes_gpc() == 0) {
$off = addslashes ($off);
}
if (!is_numeric ($off)) {
$off = 1;
}
// this checks if user is trying to put something stupid in query string
if ($off > $off_pag) {
$off = 1;
}

if ($off == "1") {
$limit = "0, $nol";
}
elseif ($off <> "") {
for ($i = 0; $i <= ($off - 1) * $nol; $i ++) {
$limit = "$i, $nol";
$count = $i + 1;
}
} 




// Query to extract records from database.
$sql = mysql_query ("$query LIMIT $limit") or die ("Error in query: $sql".mysql_error()); 



// Define your colors for the alternating rows

$color1 = "#ADD8E6";$color2 = "#E0FFFF";
$color = $color2;echo 

"<table width="50%" border="0" cellpadding="2" cellspacing="2">
    <tr>
		<td><b><small>RecNo</small></b></td>
		<td><b><small>ID</small></b></td>
		<td><b><small>Title</small></b></td>
		<td><b><small>First Name</small></b></td>
		<td><b><small>Surname</small></b></td>
		<td><b><small>Organisation</small></b></td>
		<td><b><center><small>Role</small></center></b></td>
		<td><b><small>Address(1)</small></b></td>
		<td><b><small>Address(2)</small></b></td>
		<td><b><small>City</small></b></td>
		<td><b><small>Post Code</small></b></td>
		<td><b><small>Telephone</small></b></td>
		<td><b><small>Mobile</small></b></td>
		<td><b><small>Fax</small></b></td>
		<td><b><small>Last Contact</small></b></td>
		<td><b><small>Contact Again</small></b></td>
		<td><b><small>Notes</small></b></td>";while ($row = mysql_fetch_object($sql)) 






{($color==$color2)? $color = $color1 : $color = $color2;


echo "<tr bgcolor="$color"><td>".$count . '</td><td> ' . $row->person_id .'</td><td>'.        
	
	$row->salutation .'</td><td>'.         
	
	'<a href=mailto:'.$row->email.'>'.$row->firstname .'</a></td><td>'.
	'<a href=mailto:'.$row->email.'>'.$row->surname .'</a></td><td>'.       
 	
	$row->organisation.'</td><td>'.   
	$row->role.'</td><td>'.       
 	$row->address1 .'</td><td>'.        
	$row->address2 .'</td><td>'.       
 	$row->city .'</td><td>'.       
 	$row->postcode .'</td><td>'.        
	$row->telephone .'</td><td>'.        
	$row->mobile .'</td><td>'.        
	$row->fax .'</td><td>'.        
	$row->dateoflastcontact.'</td><td>'.        
	$row->datecontactagain  .'</td><td>'.          
	$row->notes .'</td></tr>';

$count += 1;
}

echo "</table>"; 

echo "<br /><br />\r\n";
if ($off <> 1) {
$prev = $off - 1;
echo "[ < <a href="$filename?offset=$prev&go=$go">prev</a> ] \r\n";
}
for ($i = 1; $i <= $off_pag; $i ++) {
if ($i == $off) {
echo "[<b> $i </b>] \r\n";
} else {
echo "[ <a href="$filename?offset=$i&go=$go">$i</a> ] \r\n";
}
}
if ($off < $off_pag) {
$next = $off + 1;
echo "[ <a href="$filename?offset=$next&go=$go">next</a> > ] \r\n";
}

echo "<br /><br />\r\n";
echo "Page $off of $off_pag<br />\r\n";

?>

Here is the code for my results page - I thought ALL i would need to do is change the query from my above code and put it in here but that doesnt work. Can anyone help?

Code: Select all

<?php
/* Connecting, selecting database */
$link = mysql_connect("****", "******", "*****")
   or die("Could not connect : " . mysql_error());
echo "";
mysql_select_db("contact_management_system") or die("Could not select database");

foreach($HTTP_POST_VARS as $varname => $value)
        $formVars[$varname]=$value;

// the query used to search the DB
$query = "SELECT * FROM people WHERE firstname LIKE '$formVars[firstname]%'";
$result = mysql_query($query);


if ($result) {

    while ($array= mysql_fetch_assoc($result)) {
        
		print "<B>Person ID: &nbsp </B> $array[person_id]<BR>";
		print "<B>Salutation: &nbsp  </B>$array[salutation]<BR>";
		print "<B>First Name: &nbsp </B>$array[firstname]<BR>";
		print "<B>Surname: &nbsp </B>$array[surname]<BR>";
		print "<B>Organisation: &nbsp </B>$array[organisation]<BR>";
		print "<B>Role: &nbsp </B>$array[role]<BR>";
		print "<B>Address 1: &nbsp </B>$array[address1]<BR>";
		print "<B>Address 2: &nbsp</B>$array[address2]<BR>";
		print "<B>City: &nbsp </B>$array[city]<BR>";
		print "<B>Postcode:&nbsp</B>$array[postcode]<BR>";
		print "<B>Telephone:&nbsp </B>$array[telephone]<BR>";
		print "<B>mobile:&nbsp </B>$array[mobile]<BR>";
		print "<B>fax:&nbsp </B>$array[fax]<BR>";
		print "<B>dateoflastcontact:&nbsp</B>$array[dateoflastcontact]<BR>";
		print "<B>datecontactagain:&nbsp </B>$array[datecontactagain]<BR>";
		print "<B>notes:&nbsp </B>$array[notes]<BR>";
		print "<B>emai:&nbsp <a href="mailto: $array[email]"> </B>$array[email]<BR>";
		print "<BR>";

		// 
		echo "<a href="deleteRecord.html"><B>Delete this record:  $array[Title] </B></a>\n";
		print "<BR>";
		print "<BR>";
		print "<HR>";
		
	}

} else {

    print "<li>No results.</li>";

}

?>

Posted: Wed Dec 22, 2004 10:10 am
by pickle
Without looking much at your code, I can tell you you don't want to duplicate the page - there's no need to. Line 12 has the query that determines what gets displayed. Rather than re-do the whole page, just put some code in that changes that query to do the search, rather than display everything. Then you can just re-use the display logic.

Posted: Wed Dec 22, 2004 3:57 pm
by mohson
thanks pickle-

the code at line 12 already does the search it works fine its just that it displays the record in plain text with no table formatting - I want to display the result with table formatting - the same as when i display my original results with all my features like alternating coloured rows and the pagination - well actually I could leave out the pagination beacuse there will be less resuts - but how would I incorporate my table formatting and alternating color scheme into my PRINT search results code???

Posted: Wed Dec 22, 2004 4:04 pm
by pickle
Well, it'll take some re-working, but what I'd suggest is have your inital page and your search page, the same page. When the page is loaded, just display the results of a query. Whether something has been POSTed to the page or not (meaning, if someone has done a search or not), should determine what query is done, and what is displayed. If someone hasn't done a search, just load the default data like you currently are.

In another vein of thinking: it shouldn't be hard at all to copy over the formatting by just using CSS. If you set up table and cell classes, it's trivial to put those few words of HTML into THIS page, to get the formatting to match up. As for alternating row colours - that's trivial as well so it doesn't take too much effort to duplicate that.

Posted: Thu Dec 23, 2004 6:04 am
by mohson
any examples of how to do it - either way????

Posted: Thu Dec 23, 2004 9:53 am
by pickle
Just do some research - I'm sure there's examples and tutorials about stuff like this all over. The CSS method is pretty basic too - just look at how to apply stylesheets to tables and cells - that's probably all over too.

Posted: Thu Dec 23, 2004 12:12 pm
by timvw
i like to have separate pages though ;)

in search.php i present the user a form with all the fields he can search on (default a = will be used | but user can also start value with >, <=, like, etc... | if a % appears in the value, like will be used automagically)

then i store that 'search restriction' in a session_variable, usually
$_SESSION['list_a.php']['search'] = "tits='big'";

and everytime i'm in list_a.php i look in the $_SESSION to find out if there are restrictions active... and if there are, i append them to my where clause...

Posted: Thu Dec 23, 2004 12:36 pm
by pickle
Sorry, I had a complete brain fart on my last entry - I thought I was replying to a different post.

CSS-wise, you could just set

Code: Select all

<table class = 'pretty_table'>
in your page(s), then in your CSS file,

Code: Select all

.pretty_table&#123;
  background-color:#333333;
  border-spacing:1px;
&#125;
.pretty_table td, .pretty_table th&#123;
  background-color:#FFFFFF;
&#125;

If the formatting is more complex than that, would it be possible to put your display logic in an include file? That way, you could do whatever query you want, and just make sure a result set is present before including that file. That file could then run through that result set and output what's in it.

Hopefully this entry makes more sense than my last one.