Jump to content


Photo

MySQL searching


  • Please log in to reply
13 replies to this topic

#1 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 15 August 2006 - 06:05 AM

Here is my code

// counting the offset

$offset = ($pageNum - 1) * $rowsPerPage;



$keywords = explode(" ", $search);



$query = "SELECT * FROM articles " .

"WHERE body LIKE '%".$keywords['0']."%'";



for ($i=1; $i<count($keywords); $i++) {

$query = $query." AND body LIKE '%".$keywords[$i]."%'";

}

$query = $query." ORDER BY id DESC LIMIT $offset, $rowsPerPage";



$result2 = mysql_query($query) or die(mysql_error());

?>



<form method="GET" action="search.php">

<b>Search:</b> <input type="text" name="search" size="20" />

<input type="submit" value="Search!" />

</form><br><br>

<table width="50%" style="border:1px solid #000000;">

<?php

$keywords = explode(" ", $search);



$query = "SELECT COUNT(id) AS numrows FROM articles " .

"WHERE body LIKE '%".$keywords['0']."%'";



for ($i=1; $i<count($keywords); $i++) {

$query = $query." AND body LIKE '%".$keywords[$i]."%'";

}



$result = mysql_query($query) or die('Error, query failed');

$row = mysql_fetch_array($result, MYSQL_ASSOC);

$numrows = $row['numrows'];



// how many pages we have when using paging?

$maxPage = ceil($numrows/$rowsPerPage);


But I would also like to search both 'body' and 'title'. Due to syntax issues, and not knowing which exact line to edit, I have failed at trying to get it to search both rows.

Thanks in advanced to anyone who can help me with this.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 15 August 2006 - 01:55 PM

Post the final value of the $query variable.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 15 August 2006 - 09:52 PM

Not sure what you're asking for, but here is all of my search.php page

http://phpfi.com/141089

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 15 August 2006 - 10:56 PM

I don't want your search page... I want you to echo the value of the query variable.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 15 August 2006 - 11:34 PM

says

SELECT COUNT(id) AS numrows FROM articles WHERE body LIKE '%ubuntu%'


#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 16 August 2006 - 03:54 PM

Well, then for some reason, your keywords array is empty, becuase you're never concatenating the other fields.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 17 August 2006 - 04:26 AM

SELECT * FROM articles WHERE body LIKE '%ubuntu%'

There is another query being ran. But in the code it's chopped up in an odd way i dont understand

$query = "SELECT * FROM articles " .

"WHERE body LIKE '%".$keywords['0']."%'";


#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 17 August 2006 - 01:12 PM

Well, yes, there are two queries, but again, it'll make more sense if you see the final SQL query, not it's PHP equivalent.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 17 August 2006 - 08:50 PM

the first pasted code is an echo of the bottom query

#10 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 18 August 2006 - 04:06 PM

Then your keywords array is empty.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#11 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 18 August 2006 - 10:11 PM

I dunno what that means. wouldnt the keyword be 'ubuntu', which i see is in there

#12 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 19 August 2006 - 01:14 AM

Then I'm totally confused as to what you're having trouble with.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#13 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 19 August 2006 - 02:23 AM

right now it only searches for the 'body' row, i'd like it to search the body and title row, and return the article if either shows a match. i've tried adding it, but due to the php format, and it being chopped up like that, i can't get the syntax correct

#14 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 19 August 2006 - 12:38 PM

Right -- all I'm saying is that the following code is never being executed.
for ($i=1; $i<count($keywords); $i++) {

$query = $query." AND body LIKE '%".$keywords[$i]."%'";

}

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users