PDA

View Full Version : How can i search a database and return the results in a page


LNinio
07-17-2006, 10:51 PM
Let's say that I have the next database.

-- phpMyAdmin SQL Dump
-- version 2.8.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 18, 2006 at 12:46 AM
-- Server version: 5.0.21
-- PHP Version: 5.1.4
--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `students`
--

CREATE TABLE `students` (
`id` int(6) NOT NULL auto_increment,
`name` char(50) collate utf8_unicode_ci NOT NULL,
`phone` char(15) collate utf8_unicode_ci NOT NULL,
`year` char(4) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`,`phone`,`year`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;

--
-- Dumping data for table `students`
--

INSERT INTO `students` VALUES (1, 'Δημήτρης', '00302310000000', '2006');
INSERT INTO `students` VALUES (2, 'Dimitris', '00306977000000', '2005');



How can i have a "field search area" that will be able to search this database and returns thw results in a webpage?

davidj
07-18-2006, 08:33 AM
i take it you have used the above to create you db and this has been successfull

you need to follow the basic tutorials within DW.

Creative Insanity
07-18-2006, 08:02 PM
Right you have a database.,. that is a start.
Now make a simple page with a form on it and make the action go to your working page, let's call that search.php just for the hell of it ah.
The search page can give all the info you want or just a snippet of info and you could also create a detail page to hold more info on something they may select from the search page.
Like DJ said.. DW help has a good walk through for this.

kona72
07-28-2006, 03:17 PM
Hey CI,

Are you using DW8? I have looed for the tutorial in DW2004mx and can't seem to locate it... I'll try the Adobe site and see what they offer...

Creative Insanity
07-28-2006, 09:02 PM
Look in the help files (F1) and do a search for search page and that will give you the run down on how to create a basic one field search option.

Once you get one field working from there you can experiment on making the search options more advanced with more sql in the recordset. It was the one in the docs that helped me figure them out some time ago.

kona72
07-31-2006, 09:50 PM
VERY COOL!!!
I have followed the tutorial and it works like a charm....
I do have a question though....

When i do the search i have to search for EXACTLY how it is in the databse or else i get no results.... I know there is a way around this but not sure how??

Any ideas???

Here is my search code...

<?php require_once('../Connections/telematics.php'); ?>
<?php
$maxRows_rsResults = 10;
$pageNum_rsResults = 0;
if (isset($_GET['pageNum_rsResults'])) {
$pageNum_rsResults = $_GET['pageNum_rsResults'];
}
$startRow_rsResults = $pageNum_rsResults * $maxRows_rsResults;

$colname_rsResults = "1";
if (isset($_GET['search'])) {
$colname_rsResults = (get_magic_quotes_gpc()) ? $_GET['search'] : addslashes($_GET['search']);
}
mysql_select_db($database_telematics, $telematics);
$query_rsResults = sprintf("SELECT * FROM cnp WHERE subject = '%s'", $colname_rsResults);
$query_limit_rsResults = sprintf("%s LIMIT %d, %d", $query_rsResults, $startRow_rsResults, $maxRows_rsResults);
$rsResults = mysql_query($query_limit_rsResults, $telematics) or die(mysql_error());
$row_rsResults = mysql_fetch_assoc($rsResults);

if (isset($_GET['totalRows_rsResults'])) {
$totalRows_rsResults = $_GET['totalRows_rsResults'];
} else {
$all_rsResults = mysql_query($query_rsResults);
$totalRows_rsResults = mysql_num_rows($all_rsResults);
}
$totalPages_rsResults = ceil($totalRows_rsResults/$maxRows_rsResults)-1;
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Search Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="../stylesheet.css" rel="stylesheet" type="text/css">
</head>

<body>

<p align="center">

Search Results</p>


</p>

<table border="0" align="center" class="chart">
<tr>
<td>number</td>
<td>subject</td>
<td>type</td>
<td>status</td>
<td>expected</td>
<td>file</td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_rsResults['number']; ?></td>
<td><?php echo $row_rsResults['subject']; ?></td>
<td><?php echo $row_rsResults['type']; ?></td>
<td><?php echo $row_rsResults['status']; ?></td>
<td><?php echo $row_rsResults['expected']; ?></td>
<td><?php echo $row_rsResults['file']; ?> (/cnp/file/<?php echo $row_rsResults['file']; ?>)</td>
</tr>
<?php } while ($row_rsResults = mysql_fetch_assoc($rsResults)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($rsResults);
?>

davidj
08-02-2006, 04:55 PM
where you have the SQL that gets passed to the db

change the operator to LIKE

SELECT * FROM cnp WHERE subject LIKE '%s'

in your form field you can add wild cards to your search

example...
dream% will return everything that starts with dream

kona72
08-02-2006, 04:58 PM
This is so bizarre...
I was just going to post the answer to this. I did some more searching and found it my self....

do do do doo do do do doo do do doo do dooooooo (supposed to be the twilight zone theme..)

Thanks David!!