Viewing Topic: Database query |
TracNav |
Subject: "Database query"
Posted: @ 7:47 pm on Mar 15 2008 |
|
|
Member #: 629 Rank: user - (11) Since: 03/15/08 Posts: 11 From: Wokingham, UK.
|
Hello
AllSyntax, Ive
been following
your tutorials
for my ICT
coursework and
have found them
amazingly
helpful - so
thanks for the
good work!
My trouble: Im
having a
problem with my
code for a
database
search. I
remember having
this problem
before with
something else,
but have
fprgotten how I
got round it -
Oops!
Anywho, my PHP
file named
search.php:
Code:
<?php
$var =
@$_GET['q']
;
$trimmed =
trim($var);
//trim
whitespace from
the stored
variable
$limit=10;
if ($trimmed ==
"")
{
echo
"<p>
Please enter a
search...</p
>";
exit;
}
if
(!isset($var))<
br />
{
echo
"<p>
We dont seem to
have a search
parameter!</
p>";
exit;
}
mysql_connect(&
quot;localhost&
quot;,"SEC
RET!",&quo
t;NOT_FOR_YOU!!
");
mysql_select_db
("SECRET!&
quot;) or
die("Unabl
e to select
database")
;
$query =
"select *
FROM `contacts`
WHERE `web`
LIKE
\"%$trimme
d%\"
order by
1st_field"
;
$numresults=my
sql_query($quer
y);
$numrows=mysql
_num_rows($numr
esults);
if ($numrows ==
0)
{
echo
"<h4>
;Results</h4
>";
echo
"<p>
Sorry, your
search:
""
; . $trimmed .
""
; returned zero
results</p&g
t;";
echo
"<p>
<a
href=\"htt
p://www.google.
com/search?q=&q
uot;
. $trimmed .
"\"
target=\"_
blank\"
title=\"Lo
ok up
" .
$trimmed .
" on
Google\"&g
t;Click
here</a>
to try the
search on
google</p>
;";
}
if
(empty($s))
{
$s=0;
}
$query .=
" limit
$s,$limit"
;
$result =
mysql_query($qu
ery) or
die("Could
n't execute
query");
echo
"<p>
You searched
for:
""
; . $var .
""
;</p>&quo
t;;
echo
"Results&q
uot;;
$count = 1 + $s
;
while ($row=
mysql_fetch_arr
ay($result))
{
$title =
$row["1st_
field"];
echo
"$count.)&
amp;nbsp;$title
" ;
$count++ ;
}
$currPage =
(($s/$limit) +
1);
echo
"<br
/>";
if ($s>=1)
{ // bypass
PREV link if s
is 0
$prevs=($s-$lim
it);
print
" 
;<a
href=\"$PH
P_SELF?s=$prevs
&q=$var\&qu
ot;><
<
Prev
10</a>&am
p;nbsp 
;";
}
$pages=intval($
numrows/$limit)
;
if
($numrows%$limi
t) {
// has
remainder so
add one page
$pages++;
}
if
(!((($s+$limit)
/$limit)==$page
s) &&
$pages!=1) {
$news=$s+$limit
;
echo
" 
;<a
href=\"$PH
P_SELF?s=$news&
amp;q=$var\&quo
t;>Next 10
>>
;</a>&quo
t;;
}
$a = $s +
($limit) ;
if ($a >
$numrows) { $a
= $numrows ;
}
$b = $s + 1
;
echo
"<p>
Showing results
$b to $a of
$numrows</p&
gt;";
?>
And my HTML
file:
Code:
<body>
<form
name="form
"
action="se
arch.php"
method="ge
t">
<input
type="text
"
name="q&qu
ot; />
<input
type="subm
it"
name="Subm
it"
value="Sea
rch"
/>
</form>
</body>
div>
And the
error:
Stupid
computer :P
said...<
i>Warning:
mysql_num_rows(
): supplied
argument is not
a valid MySQL
result resource
in
/home/a51seem/p
ublic_html/ben/
search.php on
line 35
Results
Sorry, your
search:
"Search&qu
ot; returned
zero results
Click here to
try the search
on google
Couldn't
execute
query
Hmm. This is
for a followed
tutorial as Im
a bit short of
time as we have
a week
deadline. Any
ideas? Thanks
in advance,
TracNav
When replying
please bear in
mind the severe
simplicity of
my brain.
Thanks. |
Viewed: 15,097 Times | |  |
TracNav |
Subject: "Database query"
Posted: @ 11:38 am on Mar 21 2008
|
|
|
Member #: 629 Rank: user - (11) Since: 03/15/08 Posts: 11 From: Wokingham, UK.
|
Aaah cmon guys
this really
cant be that
hard Can
noone help me
out? My
coursework
deadline for
this is in 5
days, so a
quick response
would be
appreciated!
Trac
When replying
please bear in
mind the severe
simplicity of
my brain.
Thanks. |
Viewed: 15,048 Times | |  |
TracNav |
Subject: "Database query"
Posted: @ 11:39 am on Mar 21 2008
|
|
|
Member #: 629 Rank: user - (11) Since: 03/15/08 Posts: 11 From: Wokingham, UK.
|
Ok
problem solved.
Where this code
was:
Code:
$query =
"select *
FROM `contacts`
WHERE `web`
LIKE
\"%$trim
med%\"
order by
1st_field"
;;
I hadnt changed
1st_field to
id. Simplest of
things huh...
Although the
search works
well enough
now, I cannot
work out how to
format it
nicely using
HTML. PHP
coders, prepare
to be appauled.
Code:
<?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
","
**",&quo
t;**" ;
//(host,
username,
password)
//specify
database **
EDIT REQUIRED
HERE **
mysql_select_db
("**&quo
t or
die("Unab
le to select
database"<
img
src=http://www.
allsyntax.com/f
orums/images/sm
ilies/wink.gif
border=0>;
//select which
database we're
using
// Build SQL
Query
$query =
"select *
FROM `contacts`
WHERE `id` LIKE
\"%$trim
med%\"
order by
id"; //
EDIT HERE and
specify your
table and field
names for the
SQL query
$numresults=my
sql_query($quer
y);
$numrows=mysql
_num_rows($numr
esults);
$result =
mysql_query
($query)
or
die("Quer
y error:
".
mysql_error());
// If we have
no results,
offer a google
search as an
alternative
if ($numrows ==
0)
{
echo
"<h4&g
t;Results</h
4>";
echo
"<p>
;Sorry, your
search:
"&quo
t; . $trimmed .
"&quo
t; returned
zero
results</p&g
t;";
// google
echo
"<p>
;<a
href=\"h
ttp://www.googl
e.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($qu
ery) or
die("Coul
dn't execute
query" ;
// display what
the person
searched for
echo
"<p>
;You searched
for:
"&quo
t; . $var .
"&quo
t;</p>&q
uot;;
// begin to
show results
set
echo
"Results
";
$count = 1 + $s
;
//now you
can display the
results
returned
while ($row=
mysql_fetch_arr
ay($result)) {
$title =
$row["id
"];
$title1 =
$row["fir
st"];
$title2 =
$row["las
t"];
$title3 =
$row["pho
ne"];
$title4 =
$row["mob
ile"];
$title5 =
$row["fax
"];
$title6 =
$row["ema
il"];
$title7 =
$row["web
"];
$title8 =
$row["pic
"];
echo
"$count.)
$titl
e  
$title1
 
$title2
 
$title3
 
$title4
 
$title5
 
$title6 $title7
 
$title8"
;
$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-$lim
it);
print
"&nbs
p;<a
href=\"$
PHP_SELF?s=$pre
vs&q=$var\
">&
;lt;<
Prev
10</a>&am
p;nbsp 
;";
}
// 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%$limi
t) {
// has
remainder so
add one page
$pages++;
}
// check to see
if last page
if
(!((($s+$limit)
/$limit)==$page
s) &&
$pages!=1) {
// not last
page so give
NEXT link
$news=$s+$limit
;
echo
"&nbs
p;<a
href=\"$
PHP_SELF?s=$new
s&q=$var\
">Next
10
>>
;</a>&qu
ot;;
}
$a = $s +
($limit) ;
if ($a >
$numrows) { $a
= $numrows ; }
$b = $s + 1 ;
echo
"<p>
;Showing
results $b to
$a of
$numrows</p&
gt;";
?>
Insted of the
boldened text
(I think...) I
would like
something like
this:
Code:
<html>
<body>
<table
border="0
"
cellspacing=&q
uot;2"
cellpadding=&q
uot;2">
;
<tr>
<th><f
ont
face="Ari
al, Helvetica,
sans-serif&quo
t;
color="wh
ite">P
roduct</font
></th>
<th><f
ont
face="Ari
al, Helvetica,
sans-serif&quo
t;
color="wh
ite">N
umber</font&
gt;</th>
<th><f
ont
face="Ari
al, Helvetica,
sans-serif&quo
t;
color="wh
ite">I
n
Stock?</font
></th>
<th><f
ont
face="Ari
al, Helvetica,
sans-serif&quo
t;
color="wh
ite">P
rice (
Edited at 12:45:42 pm on 03/21/08
When replying
please bear in
mind the severe
simplicity of
my brain.
Thanks. |
Viewed: 15,042 Times | |  |
bs0d |
Subject: "re: Database
query"
Posted: @ 2:02 pm on Mar 21 2008
|
|
|
 Member #: 1 Rank: Admin. - (1,510) Since: 02/06/05 Posts: 604 From: USA
|
So what about
just using CSS
to format? This
way, you set it
once in CSS,
and it applies
to all
elements.
|
Viewed: 15,035 Times | |  |
TracNav |
Subject: "re: Database
query"
Posted: @ 3:02 pm on Mar 21 2008
|
|
|
Member #: 629 Rank: user - (11) Since: 03/15/08 Posts: 11 From: Wokingham, UK.
|
I will, but
after I have
got the
information
returned into a
HTML table
Simply, what Im
asking is 'can
you put this
HMTL into the
right place and
make it match
with the php'?
Thanks, I know
this probably
seems petty but
I havent learn
php and have no
time to do so
at the moment
:( So its all
tutorials for
me until my
coursework is
in...
Thanks
again,
Trac
When replying
please bear in
mind the severe
simplicity of
my brain.
Thanks. |
Viewed: 15,032 Times | |  |
bs0d |
Subject: "re: Database
query"
Posted: @ 5:36 pm on Mar 21 2008
|
|
|
 Member #: 1 Rank: Admin. - (1,510) Since: 02/06/05 Posts: 604 From: USA
|
Well see, still
don't
understand the
question I
guess. Because
the 2nd sample
shows HTML +
PHP.
|
Viewed: 15,026 Times | |  |
TracNav |
Subject: "re: Database
query"
Posted: @ 9:19 am on Mar 24 2008
|
|
|
Member #: 629 Rank: user - (11) Since: 03/15/08 Posts: 11 From: Wokingham, UK.
|
Ok, dont worry,
I've worked
around this.
Sorry I didnt
explain very
well, I was a
bit confused as
to what I was
doing
myself...
Thanks anyway.
Oh, I have
another
question above
this topic too
When replying
please bear in
mind the severe
simplicity of
my brain.
Thanks. |
Viewed: 14,899 Times | |  |
TracNav |
Subject: "re: Database
query"
Posted: @ 9:19 am on Mar 24 2008
|
|
|
Member #: 629 Rank: user - (11) Since: 03/15/08 Posts: 11 From: Wokingham, UK.
|
Ok, dont worry,
I've worked
around this.
Sorry I didnt
explain very
well, I was a
bit confused as
to what I was
doing
myself...
Thanks anyway.
Oh, I have
another
question above
this topic too
When replying
please bear in
mind the severe
simplicity of
my brain.
Thanks. |
Viewed: 14,898 Times | |  |
Viewing Page: 1 of 1 |
|