Search
Left Quote    Every day I get up and look through the Forbes list of the richest people in America. If I
- Robert Orben    
Right Quote
 
[login] | [Register]
Forum Index » PHP and mySQL » Viewing Topic and Replies
Post Reply


        Viewing Topic: Database query
  This user is offline  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: &quot;" ; . $trimmed . "&quot ; 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: &quot;" ; . $var . "&quot ;</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 "&nbsp ;<a href=\"$PH P_SELF?s=$prevs &q=$var\&qu ot;>&lt; &lt;
Prev 10</a>&am p;nbsp&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 "&nbsp ;<a href=\"$PH P_SELF?s=$news& amp;q=$var\&quo t;>Next 10 &gt;&gt ;</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>


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: 10,203 Times | Reply to This | To top
  This user is offline  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: 10,154 Times | Reply to This | To top
  This user is offline  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: &quot;&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: &quot;&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.) &nbsp;$titl e &nbsp $title1 &nbsp $title2 &nbsp $title3 &nbsp $title4 &nbsp $title5 &nbsp $title6 $title7 &nbsp $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;&lt;
Prev 10</a>&am p;nbsp&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 &gt;&gt ;</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: 10,148 Times | Reply to This | To top
  This user is offline  bs0d
  Subject: "re: Database query" Posted: @ 2:02 pm on Mar 21 2008    

Member #: 1
Rank: Admin. - (1,505)
Since: 02/06/05
Posts: 600
From: USA

So what about just using CSS to format? This way, you set it once in CSS, and it applies to all elements.




-bs0d | AllSyntax.com

    Viewed: 10,141 Times | Reply to This | To top
  This user is offline  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: 10,138 Times | Reply to This | To top
  This user is offline  bs0d
  Subject: "re: Database query" Posted: @ 5:36 pm on Mar 21 2008    

Member #: 1
Rank: Admin. - (1,505)
Since: 02/06/05
Posts: 600
From: USA

Well see, still don't understand the question I guess. Because the 2nd sample shows HTML + PHP.




-bs0d | AllSyntax.com

    Viewed: 10,132 Times | Reply to This | To top
  This user is offline  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: 10,005 Times | Reply to This | To top
  This user is offline  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: 10,004 Times | Reply to This | To top
Viewing Page: 1 of 1


1 |

You must be logged in to post on the forums. Login or Register








"AllSyntax.com" Copyright © 2002-2018; All rights lefted, all lefts righted.
Privacy Policy  |  Internet Rank