Posts by empoor

I have solved the issue myself by trying to fix things with experimentation.

My first problem was that the code I used to remove duplicate entries from the search array was basically looping twice. So, I edited the source to keep it from looping twice.

do
		{
			$adid_array[] = $row['page_id'];
		}
	
	while($row = mysql_fetch_array($numresults));

$tmparr = array_unique($adid_array); 
	$i = 0; 
	foreach ($tmparr as $newarr_v)
		{ 
			$newarr[$i] = $newarr_v; 
			$i++; 
		}

And then I use newarr to execute the search query (with foreach).

Secondly, because this is my first experience with building a search page, I used stock code and didn't notice that it was erroneously building a query for each keyword entered, instead of a query that incorporates all keywords (if multiple). So I removed

foreach ($search_array as $search_keyword)

, and wrote a little code to separate each keyword with AND.

$var = addslashes(htmlspecialchars($_GET['q'])) ;
// trim whitespace from the stored variable
$trimmed = trim($var);  
// separate key-phrases into keywords
$trimmed_array = explode(" ",$trimmed);
// count keywords
$trimm_total = count($trimmed_array);
$i = 0;
$searchstring = '';

// looping to get the search string
foreach ($trimmed_array as $trimm)
{ 
	if ($i != 0 and $i != $wordcount)
	{ 
		$searchstring .= " AND ";
	} 

	$searchstring .= "text LIKE '%$trimm%' OR page_title LIKE '%$trimm%' OR author LIKE '%$trimm%'"; 
	
	// incrementing the value 
	$i = $i + 1; 
}

And now it finally works.

Explode on spaces and use ORs for the extra terms. http://php.net/explode

I'm already using explode to separate terms. And shouldn't it be "AND", 'cause I want the results to match all the results. It's basically already doing an "OR" search, which results in duplicate search results showing.

Hi. I tried looking for this problem on Google, and found a lot, but nothing that actually seems to work. I basically have a search script that works fine when using one keyword, but when one uses more than that, it returns every single row per keyword. So a search for "hello kitty" doesn't look for "hello" and "kitty", but for "hello" and then another time for "kitty".

I know there's something wrong with my code, but, what? And how can I solve this issue.

// get the search variable from URL
$var = @$_GET['q'] ;
// trim whitespace from the stored variable
$trimmed = trim($var);  
// separate key-phrases into keywords
$trimmed_array = explode(" ",$trimmed);

// build SQL Query for each keyword entered 
foreach ($trimmed_array as $trimm)
{
	
	$query = "SELECT * FROM (articles LEFT JOIN authors ON articles.author_id = authors.author_id) WHERE text LIKE '%$trimm%' ORDER BY issue DESC" ;

	// and then the queries and everything; with which nothing's wrong

}

Can someone help me?

Ah, that's a good fix, thanks! (Learned something new, again. Going to use that AS syntax more often now.)

But I already sorted it out. I actually didn't use SELECT * in the code, but SELECT [whatever I needed], and my problem was that I was both selecting 'articles.page_id' and 'authorisation.page_id'. I removed the second 'page_id' and it worked.

Tried it out, and, by golly, it works perfectly! Well, for some reason it can't identify the 'articles.page_id' any more, but that's not an issue right now.

I also fiddled with the data in 'authorisation' and 'articles' to make sure the data on the webpage really changed, and it did, so it finally works.

Thank you very much for the help and explanation!

Thanks for the reply! I added the extra level, but it didn't change anything.

It's not working in the sense that it isn't outputting any rows, even though it should. There's no error or anything, just an empty table.

I'm guessing the problem lies somewhere in the area around:

WHERE articles.page_id NOT IN (authorisation.page_id)

Hey. I've got this code,

// query article
	$query1 = "SELECT * FROM (articles LEFT JOIN authorisation ON articles.page_id = authorisation.page_id LEFT JOIN authors ON articles.author_id = authors.author_id) WHERE articles.page_id NOT IN (authorisation.page_id) ORDER BY issue ASC";

	$mysql_result1 = mysql_query ($query1)
		or die ("Query '$query1' failed with error message: \"" . mysql_error () . '"');
	
	// Print
	
	while($row = mysql_fetch_array($mysql_result1))
	{
	
		echo '							<tr>
							<td>"' . $row['page_title'] . '"</td>
							<td> (' . $row['page_id'] . ')</td>
							<td class="lightgrey">' . $row['author'] . '</td>
						</tr>
						<tr class="comment">
							<td colspan="3" id="comment' . $row['page_id'] . '">' . $row['authorisation_comment'] . '</td>
						</tr>';
	}

, and it's supposed to look for the following: if a row in 'articles' does not exist in 'authorisation' (based upon its "page_id"), it should return said row and put it into the table. When it does exist in both tables, ignore.

But obviously it's not working. Can anyone help me out?

(P.S. For convenience, I have left out every column that needs to be selected and replaced it with the all-encompassing *. In the actual code it only selects the needed columns.)

YES! That's exactly what solved it :) Not sure why I defined $fiction_articles, when I wasn't really using it, but, yes, thanks :)

The final, working, code is:

// query article
	$query1 = "SELECT articles.page_id,articles.page_title,articles.author_id,authors.author FROM (articles LEFT JOIN authors ON articles.author_id = authors.author_id) WHERE category = 'fiction' ORDER BY page_id";

	$mysql_result1 = mysql_query ($query1)
		or die ("Query '$query1' failed with error message: \"" . mysql_error () . '"');
	
	// Print
	
	while($row = mysql_fetch_assoc($mysql_result1))
	{
		echo '<li>' . $row['author'] . ': "' . $row['page_title'] . '"</li>';
	}

Thanks to all. Now I can move on to work on permalinks and URIs, without having to worry about this pesky little problem :)

Thanks for all the answers (and the little discussion :P)

I deleted the

$row = 0;

and

$row++;

And basically nothing changed. It still only displays two of the three entries. (So, I guess it was pointless code. Thanks.)

mysql_fetch_assoc actually does return more than one row, as it is doing now. The remaining problem is that it isn't processing the first row.

Thanks for the MySQL LEFT JOIN tip! That solved at least one problem. It now correctly identifies the author corresponding to its id.

The query problem was this: whatever I do, it doesn't pick all the entries. The table "articles" has three (test) entries in the category 'fiction', but it only identifies the second two. Tried solving that with "$row = 0;" but it's still not working.

I'll start by publishing my code:

// query article
	$query1 = "SELECT page_title,author_id FROM articles WHERE category = 'fiction'";

	$mysql_result1 = mysql_query ($query1)
		or die ("Query '$query1' failed with error message: \"" . mysql_error () . '"');
	
	$fiction_articles = mysql_fetch_assoc($mysql_result1);
	
	// query author
	$auth_result = &$fiction_articles['author_id'];
	$query2 = "SELECT author FROM authors WHERE author_id = '$auth_result'";

	$mysql_result2 = mysql_query ($query2)
		or die ("Query '$query2' failed with error message: \"" . mysql_error () . '"');
	
	$auth_row = mysql_fetch_assoc($mysql_result2);
	
	// Parameters
	$fiction_page_title = $fiction_articles['page_title'];
	
	$fiction_author = $auth_row['author'];
	
	// Print
	
	$row = 0;
	while($row = mysql_fetch_assoc($mysql_result1))
	{
		echo '								<li>' . $fiction_author . ': "' . $row['page_title'] . '"</li>';
		$row++;
	}

Now, I have to admit, I know some PHP and MySQL stuff, but I'm still a-learning.

So, I basically have two tables in my database. "articles" and "authors". In this specific case I have to grab 'page_title' and 'author_id' from "articles" and 'author' from "authors". Why the last bit? Well, 'author_ids' in "articles" are set in code (so, for example, 'w001') and the corresponding author information is stored in "authors".

Now, I want to create an archive list of all the entries in "articles" which have as 'category' fiction.

Problem is, it's not working. It grabs a random fiction entry to use and processes that, but 1) only does it once and then stops and 2) doesn't connect the 'author_ids'.

I'm doing a lot wrong in this code, but I've changed it so often after having found a …