ive created a database and with your help ive created a page that lets me add data to this database.
however im stuck on creating a page to search this database.
ive searched google and tryed various php scripts ive found but none seem to work.
my database simply contains customers details such as name, address, phone number etc,
can someone recomend a script that does this that works
which database?
er SQL ?
er, you mean microsoft sql server?
no didnt think it was microsoft, its a linux server and in cpanel i use phpmyadmin and mysqldatabase wizard
ah 
your database system is called mysql (see mysql.com)
go into phpmyadmin, go to the SQL tab, and run this query –
SHOW CREATE TABLE [I]yourtablename[/I]
meanwhile, we’ll get this thread moved to the mysql forum
im getting the following error
Parse error: syntax error, unexpected T_VARIABLE in /home/public_html/search7.php on line 22
<html>
<head>
<title>designplace.org search script</title>
<meta name="author" content="Steve R, http://www.designplace.org/">
</head>
<body>
<form name="form" action="search.php" method="get">
<input type="text" name="q" />
<input type="submit" name="Submit" value="Search" />
</form>
<?php
// Get the search variable from URL
$var = @$_GET['q'] ;
$trimmed = trim($var) //trim whitespace from the stored variable
// rows to return
$limit=10;
// check for an empty string and display a message.
if ($trimmed == "") ;
{
echo "<p>Please enter a search...</p>";
exit;
}
// check for a search parameter
if (!isset($var))
{
echo "<p>We dont seem to have a search parameter!</p>";
exit;
}
//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost","david","dave"); //(host, username, password)
//specify database ** EDIT REQUIRED HERE **
mysql_select_db("customers") or die("Unable to select database"); //select which database we're using
// Build SQL Query
$query = "select * from the_table where 1st_field like \\"%$trimmed%\\"
order by 1st_field"; // EDIT HERE and specify your table and field names for the SQL query
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
// If we have no results, offer a google search as an alternative
if ($numrows == 0)
{
echo "<h4>Results</h4>";
echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>";
// google
echo "<p><a href=\\"http://www.google.com/search?q="
. $trimmed . "\\" target=\\"_blank\\" title=\\"Look up
" . $trimmed . " on Google\\">Click here</a> to try the
search on google</p>";
}
// next determine if s has been passed to script, if not use 0
if (empty($s)) {
$s=0;
}
// get results
$query .= " limit $s,$limit";
$result = mysql_query($query) or die("Couldn't execute query");
// display what the person searched for
echo "<p>You searched for: "" . $var . ""</p>";
// begin to show results set
echo "Results";
$count = 1 + $s ;
// now you can display the results returned
while ($row= mysql_fetch_array($result)) {
$title = $row["1st_field"];
echo "$count.) $title" ;
$count++ ;
}
$currPage = (($s/$limit) + 1);
//break before paging
echo "<br />";
// next we need to do the links to other results
if ($s>=1) { // bypass PREV link if s is 0
$prevs=($s-$limit);
print " <a href=\\"$PHP_SELF?s=$prevs&q=$var\\"><<
Prev 10</a>  ";
}
// calculate number of pages needing links
$pages=intval($numrows/$limit);
// $pages now contains int of pages needed unless there is a remainder from division
if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}
// check to see if last page
if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {
// not last page so give NEXT link
$news=$s+$limit;
echo " <a href=\\"$PHP_SELF?s=$news&q=$var\\">Next 10 >></a>";
}
$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo "<p>Showing results $b to $a of $numrows</p>";
?>
</body>
</html>
Parse error is because you are missing a ; of the end of this line:
$trimmed = trim($var) //trim whitespace from the stored variable
please go into phpmyadmin, go to the SQL tab, and run this query –
SHOW CREATE TABLE the_table
ahh ty
now i get a search box, however when i enter something in the search box and press submit i get a “Oops! This link appears to be broken.” error.
thats because your form action is set to go to search.php and your page is called search7.php.
doh silly me
im not getting any search results back.
do i need to tell it the name of the table on the database ?
Assuming that you have edited the line:
// Build SQL Query
$query = "select * from the_table where 1st_field like \\"%$trimmed%\\"
order by 1st_field"; // EDIT HERE and specify your table and field names for the SQL query
the_table and 1st_field need changing
ok the table is called user_info and the first column/field is user_id so i have
// Build SQL Query
$query = "select * from user_info where user_id like \\"%$trimmed%\\"
order by user_id"; // EDIT HERE and specify your table and field names for the SQL query
the rest of the fields/columns are user_first_name user_surname user_email user_mobile etc etc
do i need to add all the fields/columns to this line, if so would it be
// Build SQL Query
$query = "select * from user_info where user_id like \\"%$trimmed%\\, user_first_name like \\"%$trimmed%\\, user_surname like \\"%$trimmed%\\, user_email like \\"%$trimmed%\\, user_mobile like \\"%$trimmed%\\"
order by user_id"; // EDIT HERE and specify your table and field names for the SQL query
anyway im still not getting any search results
which is why i asked what i asked in post #8
OK, try the following code:
// Build SQL Query
$query = "
select
user_first_name
, user_surname
, user_email
, user_mobile
from
user_info
where
user_id
like
'%" . $trimmed ."%'
order by
user_id"; // EDIT HERE and specify your table and field names for the SQL query
$numresults=mysql_query($query) or die(mysql_error());
see what happens
r937 i did do as you asked but it didnt seem to help me much at the time, i now see what you was getting at.
spikeZ ive added the code above and i still dont get any search results
my code now looks like this
<html>
<head>
<title>designplace.org search script</title>
<meta name="author" content="Steve R, http://www.designplace.org/">
</head>
<!-- © http://www.designplace.org/ -->
<body>
<form name="form" action="search7.php" method="get">
<input type="text" name="q" />
<input type="submit" name="Submit" value="Search" />
</form>
<?php
// Get the search variable from URL
$var = @$_GET['q'] ;
$trimmed = trim($var) ; //trim whitespace from the stored variable
// rows to return
$limit=10;
// check for an empty string and display a message.
if ($trimmed == "") ;
{
echo "<p>Please enter a search...</p>";
exit;
}
// check for a search parameter
if (!isset($var))
{
echo "<p>We dont seem to have a search parameter!</p>";
exit;
}
//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost","david","dave"); //(host, username, password)
//specify database ** EDIT REQUIRED HERE **
mysql_select_db("customers") or die("Unable to select database"); //select which database we're using
// Build SQL Query
$query = "
select
user_first_name
, user_surname
, user_email
, user_mobile
from
user_info
where
user_id
like
'%" . $trimmed ."%'
order by
user_id"; // EDIT HERE and specify your table and field names for the SQL query
$numresults=mysql_query($query) or die(mysql_error());
$numrows=mysql_num_rows($numresults);
// If we have no results, offer a google search as an alternative
if ($numrows == 0)
{
echo "<h4>Results</h4>";
echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>";
// google
echo "<p><a href=\\"http://www.google.com/search?q="
. $trimmed . "\\" target=\\"_blank\\" title=\\"Look up
" . $trimmed . " on Google\\">Click here</a> to try the
search on google</p>";
}
// next determine if s has been passed to script, if not use 0
if (empty($s)) {
$s=0;
}
// get results
$query .= " limit $s,$limit";
$result = mysql_query($query) or die("Couldn't execute query");
// display what the person searched for
echo "<p>You searched for: "" . $var . ""</p>";
// begin to show results set
echo "Results";
$count = 1 + $s ;
// now you can display the results returned
while ($row= mysql_fetch_array($result)) {
$title = $row["1st_field"];
echo "$count.) $title" ;
$count++ ;
}
$currPage = (($s/$limit) + 1);
//break before paging
echo "<br />";
// next we need to do the links to other results
if ($s>=1) { // bypass PREV link if s is 0
$prevs=($s-$limit);
print " <a href=\\"$PHP_SELF?s=$prevs&q=$var\\"><<
Prev 10</a>  ";
}
// calculate number of pages needing links
$pages=intval($numrows/$limit);
// $pages now contains int of pages needed unless there is a remainder from division
if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}
// check to see if last page
if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {
// not last page so give NEXT link
$news=$s+$limit;
echo " <a href=\\"$PHP_SELF?s=$news&q=$var\\">Next 10 >></a>";
}
$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo "<p>Showing results $b to $a of $numrows</p>";
?>
<!-- © http://www.designplace.org/ -->
</body>
</html>
ok just want to check - is there a record or records in the databse that would match with a user_id?!
yes there is, i enter the customers name, surname, email, phone etc on a form and that gets put into the database. user_id is set to AUTO_INCREMENT