How to show all matched keyword

Question

Im new in PHP and I have create a table (bookstore) really look like this

no_id | author | id_book | id_topic | quote | comments | no_page

id_book and id_topic have another table eg

table for book :

id_book | book_name

table for topic :

id_topic | topic_name

I made this sql statement for show the output in my system,but my problem is the system show only one output when submit a keyword. even though there are few similar word in the database.

"SELECT a.*, b.book_name 
   FROM bookstore AS a 
   LEFT JOIN book AS b  ON  a.id_book=b.id_book 
  WHERE quote LIKE '%".

can anyone help me how to show all match quote? i am so confuse *_*

Edit: This is my php code.

$colname_Recordset1 = "-1";
if (isset($_GET['quote'])) {
    $colname_Recordset1 = $_GET['quote'];
}

mysql_select_db($database_config, $config);
$query_Recordset1 = "SELECT a.*, b.book_name FROM bookstore a 
    LEFT OUTER JOIN book b ON a.id_book = b.id_book 
    WHERE a.quote LIKE '%'". $colname_Recordset1."%%'";

$Recordset1 = mysql_query($query_Recordset1, $config) 
    or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

Have you tried running your query directly in mysql to see how many rows are returned. Could it be that in your php code you are using `$row = fetchrow()` instead of `while($row = fetchrow())`? — Sean, May 21 '13 at 00:20
You should try using double %% signs for LIKE query instead of just one. LIKE '%$keyword%' — samayo, May 21 '13 at 00:22
@phpNoOb that means I change '% to '%%' ? I have changed just like what you said, the output still show the same... — confusingOne, May 21 '13 at 00:31
@confusingOne you really are confusingOne. So, tell me what are you trying to get, all similar names from topic_name or book_name or both? — samayo, May 21 '13 at 00:35
@Sean I've try to run in mysql but when I copy that statement it seems like there have error too.. for php code, is it what you means? $colname_Recordset1."%'"; $Recordset1 = mysql_query($query_Recordset1, $config) or die(mysql_error()); $row_Recordset1 = mysql_fetch_assoc($Recordset1); $totalRows_Recordset1 = mysql_num_rows($Recordset1); — confusingOne, May 21 '13 at 00:35
@phpNoOb I want to view all similar word with quote. for example if i submit keyword "car" in my system, the output will display all data which have keyword "car" — confusingOne, May 21 '13 at 00:42
you may want to consider a full text index on quote so you can use match against instead of like. of course, for a word like car you may have to either set the match against minimum to 2 or switch to like anyhow. — Kai Qing, May 21 '13 at 01:16
When you do `$row_Recordset1 = mysql_fetch_assoc($Recordset1);` you only get 1 row - [`Returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead`](http://php.net/manual/en/function.mysql-fetch-assoc.php). You need to do a loop through all the rows - ie. `while($row_Recordset1 = mysql_fetch_assoc($Recordset1)){//do something here, like print_r($row_Recordset1);}` — Sean, May 21 '13 at 04:06
@KaiQing how can i set the match become min 2 or more in php?can you explain to me.. — confusingOne, May 21 '13 at 05:22
match against: http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html - set min word length: http://dev.mysql.com/doc/refman/5.1/en/fulltext-fine-tuning.html — Kai Qing, May 21 '13 at 15:49

score 0 · Answer 1 · answered May 21 '13 at 01:22

0

I think, if I understand correctly, that your query should be more like:

SELECT a.*, b.book_name 
  FROM bookstore a 
  LEFT OUTER JOIN book b ON a.id_book = b.id_book 
  WHERE a.quote LIKE '%'

If you are still getting a single result (where you know there is more than one result), you need to post the PHP code you are using to extract records from the DB.

answered May 21 '13 at 01:22

Tigger

8,980
5
36
40

im still getting a single result :( – confusingOne May 21 '13 at 02:02
You will need to show your PHP code. I 'think' the error is there. Also, what happens if you run the SQL directly (at a terminal for example). Does the query return the expected results? – Tigger May 21 '13 at 03:25
this is my php code. `$colname_Recordset1 = "-1"; if (isset($_GET['quote'])) { $colname_Recordset1 = $_GET['quote']; } mysql_select_db($database_config, $config); $query_Recordset1 = "SELECT a.*, b.book_name FROM bookstore a LEFT OUTER JOIN book b ON a.id_book = b.id_book WHERE a.quote LIKE '%'". $colname_Recordset1."%%'"; $Recordset1 = mysql_query($query_Recordset1, $config) or die(mysql_error()); $row_Recordset1 = mysql_fetch_assoc($Recordset1); $totalRows_Recordset1 = mysql_num_rows($Recordset1);` – confusingOne May 21 '13 at 05:38
OK, number of issues with your PHP code. The first is a [SQL injection](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php). Next issue, you are using [depreciated](http://au1.php.net/manual/en/faq.databases.php#faq.databases.mysql.deprecated) `mysql_` functions. You should update these to `mysqli_` or `PDO`. Another issue is the ['LIKE' statement](http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html). You have too many '%' included. If I get time, I'll update my answer. – Tigger May 21 '13 at 06:21
okay thank you so much @Tigger for your help..im really glad for everyone who respond with my question..you guys really awesome :) – confusingOne May 21 '13 at 06:49
until now I cant find the answer..can you help me with your answer? – confusingOne May 22 '13 at 17:09
@confusingOne: The answer by Wing Leong is correct (from this side of the monitor). Read the [PHP manual](http://www.php.net/manual/en/function.mysql-fetch-assoc.php) link. It will explain why you are only getting a single result with the code you have posted. Hint: The key is the `while` loop. – Tigger May 23 '13 at 10:05

Steely Wing · Answer 2 · 2013-05-21T09:22:53.463

0

You should fetch the result like this, reference http://www.php.net/manual/en/function.mysql-fetch-assoc.php

while ($row = mysql_fetch_assoc($Recordset1)) {
    print_r($row);
}

and you should escape the user input with mysql_escape_string, the full php code

mysql_select_db($database_config, $config);

$filter = '';
if (isset($_GET['quote'])) {
    $filter = " WHERE a.quote LIKE '%" . mysql_escape_string($_GET['quote']) . "%'";
}

$result = mysql_query(
    "SELECT a.*, b.book_name FROM bookstore a 
    LEFT OUTER JOIN book b ON a.id_book = b.id_book" . $filter,
    $config
) or die(mysql_error());

while ($row = mysql_fetch_assoc($result)) {
    // If you need to output table, put the code here
    print_r($row);
}

edited May 21 '13 at 09:22

answered May 21 '13 at 07:06

Steely Wing

16,239
8
58
54

do i need to change this code to make it a loop? @Wing Leong `if($totalRows_Recordset1<=0){
Not In Record
} 0){?> Quote : ${1}', $row_Recordset1['quote'] ); ?> author : page: book : comments : ` – confusingOne May 21 '13 at 07:41
I need to view all information on table bookstore (just like the question above) base on user input keyword from my system.. – confusingOne May 21 '13 at 07:45
update of what Wing Leong? im so sorry ~ my system does not have update function..only function view is needed.. – confusingOne May 21 '13 at 09:19
I've got some error..maybe there are error in my code to output table – confusingOne May 21 '13 at 18:48

How to show all matched keyword

2 Answers2