Solved

how to compare an array of keywords in mysql query

Posted on 2011-02-22
15
451 Views
Last Modified: 2012-05-11
Hi;
I can get my keywords to an array then assign them to $keywords and when I  print_r that then I can see all the categories keywords in different cells of the array but when I put them in a query the only array set read is the last one and looks like my query is ignoring the records before the last array.
I have arrached my code here can anyone please tell me what am I doing wrong?
Thanks a lot for the help
mysql_select_db($database_Remoteiran79, $Remoteiran79);
$query_rsKeywords = "SELECT name,path,keywords FROM rss_categories";
$rsKeywords = mysql_query($query_rsKeywords, $Remoteiran79) or die(mysql_error());
$row_rsKeywords = mysql_fetch_array($rsKeywords);
$totalRows_rsKeywords = mysql_num_rows($rsKeywords);
while($row=mysql_fetch_assoc($rsKeywords)) 
{ 
$results[]=$row ;
}
$maxRows_rsArticles = 20;
$pageNum_rsArticles = 0;
if (isset($_GET['pageNum_rsArticles'])) {
  $pageNum_rsArticles = $_GET['pageNum_rsArticles'];
}
$startRow_rsArticles = $pageNum_rsArticles * $maxRows_rsArticles;
if (isset($_GET['totalRows_rsArticles'])) {
  $totalRows_rsArticles = $_GET['totalRows_rsArticles'];
} else {
  $all_rsArticles = mysql_query($query_rsArticles);
  //$totalRows_rsArticles = mysql_num_rows($all_rsArticles);
}
$totalPages_rsArticles = ceil($totalRows_rsArticles/$maxRows_rsArticles)-1;	
foreach ($results as &$words) {
$keywords = ($words[keywords]);
//echo ($keywords);
 {
mysql_select_db($database_Remoteiran79, $Remoteiran79);
$query_rsArticles = "SELECT *, a.id AS id, s.type AS type, a.title AS title FROM rss_articles a LEFT JOIN rss_sources s ON (s.id = source_id) 
WHERE  a.type!='trashed' AND (MATCH (a.title) AGAINST ('$keywords' IN BOOLEAN MODE))ORDER BY a.timestamp DESC";
$query_limit_rsArticles = sprintf("%s LIMIT %d, %d", $query_rsArticles, $startRow_rsArticles, $maxRows_rsArticles);
$rsArticles = mysql_query($query_limit_rsArticles, $Remoteiran79) or die(mysql_error());
$row_rsArticles = mysql_fetch_assoc($rsArticles);
}}
echo $query_rsArticles
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Array</title>
</head>
<body>
<?php 
?>
<ol>
    <?php do { ?>
      <?php 

echo   "<li><font size='2'><strong>".$row_rsArticles['title']."</strong></font></li><br>"
 ?>
      <?php } while ($row_rsArticles = mysql_fetch_assoc($rsArticles)); ?>
  
</ol>

Open in new window

0
Comment
Question by:babak62
  • 9
  • 6
15 Comments
 
LVL 8

Expert Comment

by:ropenner
ID: 34957494
line 4 is unnecessary ...
    $row_rsKeywords = mysql_fetch_array($rsKeywords);

OR
you need a
      mysql_data_seek($rsKeywords, 0);

after line 4 so that you reset the result set pointer to the first row of data.  otherwise you will always lose the first row of data.

to debug this you can
   var_dump($results);

in the while loop to see if it is setting the values you expect.
0
 
LVL 8

Expert Comment

by:ropenner
ID: 34957511
missing quotes on  line 24

$words{"keywords"}
0
 

Author Comment

by:babak62
ID: 34957563
ropeman thanks a lot for the help would you please correct the code and put it in the code box here as I might make a mistake between the posts and if you can do me this favor I can test it.  Again I really appreciate your help and attention.
0
 
LVL 8

Expert Comment

by:ropenner
ID: 34957875
mysql_select_db($database_Remoteiran79, $Remoteiran79);
$query_rsKeywords = "SELECT name,path,keywords FROM rss_categories";
$rsKeywords = mysql_query($query_rsKeywords, $Remoteiran79) or die(mysql_error());
$totalRows_rsKeywords = mysql_num_rows($rsKeywords);
while($row=mysql_fetch_assoc($rsKeywords))
{
$results[]=$row;
}
$maxRows_rsArticles = 20;
$pageNum_rsArticles = 0;
if (isset($_GET['pageNum_rsArticles'])) {
  $pageNum_rsArticles = $_GET['pageNum_rsArticles'];
}
$startRow_rsArticles = $pageNum_rsArticles * $maxRows_rsArticles;
if (isset($_GET['totalRows_rsArticles'])) {
  $totalRows_rsArticles = $_GET['totalRows_rsArticles'];
} else {
  $all_rsArticles = mysql_query($query_rsArticles);
  //$totalRows_rsArticles = mysql_num_rows($all_rsArticles);
}
$totalPages_rsArticles = ceil($totalRows_rsArticles/$maxRows_rsArticles)-1;      
foreach ($results as &$words) {
$keywords = $words["keywords"];
//echo ($keywords);
 {
mysql_select_db($database_Remoteiran79, $Remoteiran79);
$query_rsArticles = "SELECT *, a.id AS id, s.type AS type, a.title AS title FROM rss_articles a LEFT JOIN rss_sources s ON (s.id = source_id)
WHERE  a.type!='trashed' AND (MATCH (a.title) AGAINST ('$keywords' IN BOOLEAN MODE))ORDER BY a.timestamp DESC";
$query_limit_rsArticles = sprintf("%s LIMIT %d, %d", $query_rsArticles, $startRow_rsArticles, $maxRows_rsArticles);
$rsArticles = mysql_query($query_limit_rsArticles, $Remoteiran79) or die(mysql_error());
$row_rsArticles = mysql_fetch_assoc($rsArticles);
}}
echo $query_rsArticles
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Array</title>
</head>
<body>
<?php
?>
<ol>
    <?php do { ?>
      <?php

echo   "<li><font size='2'><strong>".$row_rsArticles['title']."</strong></font></li><br>"
 ?>
      <?php } while ($row_rsArticles = mysql_fetch_assoc($rsArticles)); ?>
 
</ol>
0
 
LVL 8

Expert Comment

by:ropenner
ID: 34957883
Or ... more readable for me, but if you use name and path later on then it will not work with the code below.
mysql_select_db($database_Remoteiran79, $Remoteiran79);
$query_rsKeywords = "SELECT name,path,keywords FROM rss_categories";
$rsKeywords = mysql_query($query_rsKeywords, $Remoteiran79) or die(mysql_error());
$totalRows_rsKeywords = mysql_num_rows($rsKeywords);
while($row=mysql_fetch_assoc($rsKeywords)) { 
  $results[]=$row{"keywords"};
}
$maxRows_rsArticles = 20;
$pageNum_rsArticles = 0;
if (isset($_GET['pageNum_rsArticles'])) {
  $pageNum_rsArticles = $_GET['pageNum_rsArticles'];
}
$startRow_rsArticles = $pageNum_rsArticles * $maxRows_rsArticles;
if (isset($_GET['totalRows_rsArticles'])) {
  $totalRows_rsArticles = $_GET['totalRows_rsArticles'];
} else {
  $all_rsArticles = mysql_query($query_rsArticles);
  //$totalRows_rsArticles = mysql_num_rows($all_rsArticles);
}
$totalPages_rsArticles = ceil($totalRows_rsArticles/$maxRows_rsArticles)-1;	
foreach ($results as $words) {
$keywords = $words;
//echo ($keywords);
 {
mysql_select_db($database_Remoteiran79, $Remoteiran79);
$query_rsArticles = "SELECT *, a.id AS id, s.type AS type, a.title AS title FROM rss_articles a LEFT JOIN rss_sources s ON (s.id = source_id) 
WHERE  a.type!='trashed' AND (MATCH (a.title) AGAINST ('$keywords' IN BOOLEAN MODE))ORDER BY a.timestamp DESC";
$query_limit_rsArticles = sprintf("%s LIMIT %d, %d", $query_rsArticles, $startRow_rsArticles, $maxRows_rsArticles);
$rsArticles = mysql_query($query_limit_rsArticles, $Remoteiran79) or die(mysql_error());
$row_rsArticles = mysql_fetch_assoc($rsArticles);
}}
echo $query_rsArticles
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Array</title>
</head>
<body>
<?php 
?>
<ol>
    <?php do { ?>
      <?php 

echo   "<li><font size='2'><strong>".$row_rsArticles['title']."</strong></font></li><br>"
 ?>
      <?php } while ($row_rsArticles = mysql_fetch_assoc($rsArticles)); ?>
  
</ol>

Open in new window

0
 

Author Comment

by:babak62
ID: 34957974
Thanks a LOT for the great effort but when I pasted this instead of what I had this showed up

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'بسکتبال'،شنا،کاراته،استادیومفدراسیون,مدال' at line 2

the charachters are comming out like this because they are persian and I did not have this problem before either but I do not think it is anything related to what we do.  Would you please see what could be the problem and how can I use this without this error?
0
 
LVL 8

Expert Comment

by:ropenner
ID: 34958021
It might actually be working and the 'keywords' might look like that.

uncomment line 23

  //echo ($keywords)

so that it is

  echo ($keywords)

so that you can see them before they are put in the query

Then add a print of your SQL after line 27

$query_rsArticles = "SELECT *, a.id AS id, s.type AS type, a.title AS title FROM rss_articles a LEFT JOIN rss_sources s ON (s.id = source_id) WHERE  a.type!='trashed' AND (MATCH (a.title) AGAINST ('$keywords' IN BOOLEAN MODE))ORDER BY a.timestamp DESC";

print "<BR>$query_rsArticles<BR>";

so that you can see if the query looks right.  

if you post here what you get back that would be helpful.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:babak62
ID: 34958069
OK this is what I got, please look at the code but what is bothering me is that before these changes I was actually print the right persian charachters but at this time chars are comming out a bit wiered.
THESE ARE THE KEYWORDS EXACTLY AS THEY LOOK

ولیبال،فوتبال،قایقرانی‌،شمشیربازی،'بسکتبال'،شنا،کاراته،استادیومفدراسیون,مدال,رئال مادرید,بارسلونا,افشین قطبی,نود,فولاد خوزستان, تیم ملی,منچستریونایتد,چلسی،مربی,"لیگ برتر",باشگاه,پرسپولیس,خلعتبری, 

=================================================================
THIS IS THE QUERY WITH KEYWORDS INSIDE

SELECT *, a.id AS id, s.type AS type, a.title AS title FROM rss_articles a LEFT JOIN rss_sources s ON (s.id = source_id) WHERE a.type!='trashed' AND (MATCH (a.title) AGAINST ('ولیبال،فوتبال،قایقرانی‌،شمشیربازی،'بسکتبال'،شنا،کاراته،استادیومفدراسیون,مدال,رئال مادرید,بارسلونا,افشین قطبی,نود,فولاد خوزستان, تیم ملی,منچستریونایتد,چلسی،مربی,"لیگ برتر",باشگاه,پرسپولیس,خلعتبری, ' IN BOOLEAN MODE))ORDER BY a.timestamp DESC

=================================================================
THIS IS THE ERROR IT SHOWS

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'بسکتبال'،شنا،کاراته،استادیومفدراسیون,مدال' at line 1

Open in new window

THESE ARE THE KEYWORDS EXACTLY AS THEY LOOK

ولیبال،فوتبال،قایقرانی‌،شمشیربازی،'بسکتبال'،شنا،کاراته،استادیومفدراسیون,مدال,رئال مادرید,بارسلونا,افشین قطبی,نود,فولاد خوزستان, تیم ملی,منچستریونایتد,چلسی،مربی,"لیگ برتر",باشگاه,پرسپولیس,خلعتبری, 

=================================================================
THIS IS THE QUERY WITH KEYWORDS INSIDE

SELECT *, a.id AS id, s.type AS type, a.title AS title FROM rss_articles a LEFT JOIN rss_sources s ON (s.id = source_id) WHERE a.type!='trashed' AND (MATCH (a.title) AGAINST ('ولیبال،فوتبال،قایقرانی‌،شمشیربازی،'بسکتبال'،شنا،کاراته،استادیومفدراسیون,مدال,رئال مادرید,بارسلونا,افشین قطبی,نود,فولاد خوزستان, تیم ملی,منچستریونایتد,چلسی،مربی,"لیگ برتر",باشگاه,پرسپولیس,خلعتبری, ' IN BOOLEAN MODE))ORDER BY a.timestamp DESC

=================================================================
THIS IS THE ERROR IT SHOWS

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'بسکتبال'،شنا،کاراته،استادیومفدراسیون,مدال' at line 1

Open in new window

THIS IS THE CODE WITH ALL THE ADJUSTMENTS WE DID TO IT

Function get_associations($cat_ids) {
	$db = mysql_select_db($database_Remoteiran79, $Remoteiran79);
	$query_associations = ("SELECT * FROM rss_associations WHERE cat_id IN ($cat_ids)");
	$rsAssociations  = mysql_query($query_rsAssociations , $Remoteiran79) or die(mysql_error());
	$totalRows_rsAssociations  = mysql_num_rows($rsAssociations);
	            
	while ($associations = mysql_fetch_assoc($rsAssociations)) {
		$sources[] = $associations{"source_id"};
	}
	if (empty($sources)) { 
		$sources = "0";       
	} else {                  
		$sources = implode(", ", $sources);                  
	}
	echo ($sources);
	return $sources;       
}


mysql_select_db($database_Remoteiran79, $Remoteiran79);
$query_rsKeywords = "SELECT name,path,keywords FROM rss_categories";
$rsKeywords = mysql_query($query_rsKeywords, $Remoteiran79) or die(mysql_error());
$totalRows_rsKeywords = mysql_num_rows($rsKeywords);
while($row=mysql_fetch_assoc($rsKeywords)) 
{ 
$results[]=$row;
}
$maxRows_rsArticles = 20;
$pageNum_rsArticles = 0;
if (isset($_GET['pageNum_rsArticles'])) {
  $pageNum_rsArticles = $_GET['pageNum_rsArticles'];
}
$startRow_rsArticles = $pageNum_rsArticles * $maxRows_rsArticles;
if (isset($_GET['totalRows_rsArticles'])) {
  $totalRows_rsArticles = $_GET['totalRows_rsArticles'];
} else {
  $all_rsArticles = mysql_query($query_rsArticles);
  //$totalRows_rsArticles = mysql_num_rows($all_rsArticles);
}
$totalPages_rsArticles = ceil($totalRows_rsArticles/$maxRows_rsArticles)-1;      
foreach ($results as &$words) {
$keywords = $words["keywords"];
echo ($keywords);
 {
mysql_select_db($database_Remoteiran79, $Remoteiran79);
$query_rsArticles = "SELECT *, a.id AS id, s.type AS type, a.title AS title FROM rss_articles a LEFT JOIN rss_sources s ON (s.id = source_id) WHERE  a.type!='trashed' AND (MATCH (a.title) AGAINST ('$keywords' IN BOOLEAN MODE))ORDER BY a.timestamp DESC";

print "<BR>$query_rsArticles<BR>";
$query_limit_rsArticles = sprintf("%s LIMIT %d, %d", $query_rsArticles, $startRow_rsArticles, $maxRows_rsArticles);
$rsArticles = mysql_query($query_limit_rsArticles, $Remoteiran79) or die(mysql_error());
$row_rsArticles = mysql_fetch_assoc($rsArticles);
}}
echo $query_rsArticles
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Array</title>
</head>
<body>
<?php 
?>
<ol>
    <?php do { ?>
      <?php 

echo   "<li><font size='2'><strong>".$row_rsArticles['title']."</strong></font></li><br>"
 ?>
      <?php } while ($row_rsArticles = mysql_fetch_assoc($rsArticles)); ?>
  
</ol> 
</body>
</html> 
<?php
mysql_free_result($rsKeywords);
mysql_free_result($rsArticles);
?>

Open in new window

0
 

Author Comment

by:babak62
ID: 34958388
I found the perian charachters problem here is the problem.  I still do not see the difference causing these many problems but maybe you can tell by looking at it.

 None functional one
mysql_select_db($database_Remoteiran79, $Remoteiran79);
$query_rsArticles = "SELECT *, a.id AS id, s.type AS type, a.title AS title FROM rss_articles a LEFT JOIN rss_sources s ON (s.id = source_id) WHERE  a.type!='trashed' AND (MATCH (a.title) AGAINST ('$keywords' IN BOOLEAN MODE))ORDER BY a.timestamp DESC";

print "<BR>$query_rsArticles<BR>";
$query_limit_rsArticles = sprintf("%s LIMIT %d, %d", $query_rsArticles, $startRow_rsArticles, $maxRows_rsArticles);
$rsArticles = mysql_query($query_limit_rsArticles, $Remoteiran79) or die(mysql_error());
$row_rsArticles = mysql_fetch_assoc($rsArticles);
}}
echo $query_rsArticles

Functional one without error

mysql_select_db($database_Remoteiran79, $Remoteiran79);
$query_rsArticles = "SELECT *, a.id AS id, s.type AS type, a.title AS title FROM rss_articles a LEFT JOIN rss_sources s ON (s.id = source_id)
WHERE  a.type!='trashed' AND (MATCH (a.title) AGAINST ('$keywords' IN BOOLEAN MODE))ORDER BY a.timestamp DESC";
$query_limit_rsArticles = sprintf("%s LIMIT %d, %d", $query_rsArticles, $startRow_rsArticles, $maxRows_rsArticles);
$rsArticles = mysql_query($query_limit_rsArticles, $Remoteiran79) or die(mysql_error());
$row_rsArticles = mysql_fetch_assoc($rsArticles);
}}
echo $query_rsArticles
0
 

Author Comment

by:babak62
ID: 34958422
This was an another part of the problem.  If you can please tell me what would be the solution in this case I really appreciate it because in this one I am collecting all the keywords but the array in the query $keywords is the last [] in the array so I do not know if I am doing right in this case or not. at the same time the row echo ($sources);  in the first function does not print anything either.  

Working one

mysql_select_db($database_Remoteiran79, $Remoteiran79);
$query_rsKeywords = "SELECT name,path,keywords FROM rss_categories";
$rsKeywords = mysql_query($query_rsKeywords, $Remoteiran79) or die(mysql_error());
$row_rsKeywords = mysql_fetch_array($rsKeywords);
$totalRows_rsKeywords = mysql_num_rows($rsKeywords);
while($row=mysql_fetch_assoc($rsKeywords))
{
$results[]=$row ;
}

The one which caused the error

mysql_select_db($database_Remoteiran79, $Remoteiran79);
$query_rsKeywords = "SELECT name,path,keywords FROM rss_categories";
$rsKeywords = mysql_query($query_rsKeywords, $Remoteiran79) or die(mysql_error());
$totalRows_rsKeywords = mysql_num_rows($rsKeywords);
while($row=mysql_fetch_assoc($rsKeywords))
{
$results[]=$row;
}
0
 
LVL 8

Accepted Solution

by:
ropenner earned 500 total points
ID: 34958457
there is no difference in the part of the code you are showing except the 'print' statement, so comment that out with //

or delete that line

The problem with the query is that there is a single quote (') in the keywords and it is a special character for SQL and needs to be escaped \'

on line 44 above change it to:

     $keywords = mysql_real_escape_string($words["keywords"]);

I don't know anything about language or character sets but the query will run if there are no special characters in it.
0
 

Author Comment

by:babak62
ID: 34958539
Perfect  I made the change and everything is comming out.  Between the keywords is ¿\ but still my question is when I echo the query I find the last row in it.  Does it mean that it has processed the other rows before and it only shows the last row or is it like that it only picks the last row.
One more thing I need to ask you is regarding the $catid in the function when I do this

echo ($sources);
return $sources;

it does not show anything and I know in assiciations table there are records,  Do you think we are doing the right thing there?

Again thanks a lot for all the help and patience.
0
 

Author Comment

by:babak62
ID: 34958573
One more thing there is a problem with the $sources because as soon as I add this to the query
OR source_id IN ($sources)

SELECT *, a.id AS id, s.type AS type, a.title AS title FROM rss_articles a LEFT JOIN rss_sources s ON (s.id = source_id)
WHERE  a.type!='trashed' AND (MATCH (a.title) AGAINST ('$keywords' IN BOOLEAN MODE)OR source_id IN ($sources))   ORDER BY a.timestamp DESC";

it breaks and gives me this error

check the manual that corresponds to your MySQL server version for the right syntax to use near ')) ORDER BY a.timestamp DESC LIMIT 0, 20' at line 2
0
 

Author Comment

by:babak62
ID: 34958577
Also I noticed the above problem with OR source_id IN ($sources) breaks when the query is done with the first row of keywords also it prints the first row of the keywords then it give this error if it helps.
0
 

Author Closing Comment

by:babak62
ID: 34964234
Excellent help and patience
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now