Solved

how to compare an array of keywords in mysql query

Posted on 2011-02-22
15
457 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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
 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
remote mysql 8 50
Difference between PHPClasse.php and SimplXLS.php 5 44
Best hashing method? 1 15
Sending JSON Via POST In PHP 7 16
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

738 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