Loops, queries, and other dizziness

please help me smooth out a search script

[AutumnTD]

I have a client who needs a customized search program for a MySQL database, which will produce a biblography (much like a library search).

I've tweaked PHP before, but never written code myself. I have the basics down for this script, but the intricate touches are giving me problems. I would really appreciate some fresh eyes and assistance.

I'm aware that numerous things are going wrong, but my biggest concern is how to manage the data from the checkboxes. They all search the same column in the database: "keywords", yet they need to treat their info separately.

In other words, a visitor should be able to search for [Alberta AND Quebec] AND [French OR English] AND [phonetics] and get the right results.

Here are the snippets of code that worry me most.

The loops:

----------------
if ($province)
{
$num_fields = count ($_GET['province']);

for ($i = 0; $i < $num_fields; $i++){

if ($i < ($num_fields-1)) $province2 = "keywords LIKE \"%" . $province[$i] . "%\" " . $provincevar;

else $province2 = "keywords LIKE \"%" . $province[$i] . "%\"";

}
}

if ($language)

{
$num_fields = count ($_GET['language']);

for ($i = 0; $i < $num_fields; $i++){

if ($i < ($num_fields-1)) $language2 = "keywords LIKE \"%" . $language[$i] . "%\" " . $languagevar;

else $language2 = "keywords LIKE \"%" . $language[$i] . "%\"";

}
}

if ($topic)
{

$num_fields = count ($_GET['topic']);

for ($i = 0; $i < $num_fields; $i++){

if ($i < ($num_fields-1)) $topic2 = "keywords LIKE \"%" . $topic[$i] . "%\" " . $topicvar;

else $topic2 = "keywords LIKE \"%" . $topic[$i] . "%\"";

}
}
----------------

And the query:

----------------
$query = "select * from canada_resources where title like \"%$title2%\" and author like \"%$author2%\" and year like \"%$year2%\" and $province2 and $language2 and $topic2
order by refType";

$numresults=mysql_query($query);

$numrows=mysql_num_rows($numresults);
----------------

The script returns no results and I get the error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/autumn/public_html/canada/search-ww.php on line 306

(Line 306 is the $numrows line, right after the query. When I'm not adding in those loops to the query, I get results instead of the error.)

If you can give me any direction or clarity at all, I'll be very grateful. I'm pretty sure something's very wrong with the loops.

[edited by: jatar_k at 10:22 pm (utc) on Jan. 7, 2005]
[edit reason] removed personal url [/edit]

[jatar_k]

Welcome to WebmasterWorld AutumnTD,

the first thing to do is to diagnose why the query itself is dying, try this

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

that error means you passed a bad resource identifier to mysql_num_rows, which means the query returned an error.

[orion_rus]

In each loops u make new string but not add to a previous one. Try to use string+=addstring; construction.
And i don't clear understand ur search.
For example u need to find 1,2,3 and 5 condition by and in the 1 loop and 1,2 and 4 in a second loop by or
u search string would be like so
search 1 and 2 and 3 and 5 or 1 or 2 or 4 if i clear understand ur right spelling script.
But ur results may not consist ur second loop because u can find first loop matches and no second loop i think the right string would be so:
(1 and 2 and 3 and 5) and (1 or 2 or 4)
correct me please if i'm wrong?

[AutumnTD]

Thanks for the responses! I'm glad the moderating powers that be allowed me link for this post. Thank you!

Jatar:

When I add that code, I get this error:

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 'and and order by refType' at line 1

I don't know what they mean by line 1, but the 'and and order by refType' is from the query line... that suggests that my loops aren't ending with their "else" statements, which prevent the extra "and".... but I'm not sure how/why.

Orion:

Yes, you're right. I'm not adding the old string into my new string. How exactly do I write that into my existing code?

You're also right: I want the string to function as:

(1 and 2 and 3 and 5) and (1 or 2 or 4)

...where parentheses represent the loop constructions. I'm a beginner on syntax, though. How do I do this? I assume it will be on the query line.

I feel like I'm getting close. A little more help might save me. Thanks for helping out this eager beginner!

[orion_rus]

i suggest you to form in query only 1 loop. then it'll be work fine u can make more loops there.
1. i suggest you to remove such sintaxis to make clear understand what u type: "\""
u can change it to the follows: "'"
to add string u can make it so
$language2='';

if ($language)

{
$num_fields = count ($_GET['language']);

for ($i = 0; $i < $num_fields; $i++){

if ($i < ($num_fields-1)) $language2+= "keywords LIKE '%" . $language[$i] . "%' " . $languagevar;

else $language2 += "keywords LIKE '%" . $language[$i] . "%'";
}
}
try make it with only one loop because construction with () is very hard it coulbe be more simple make select in select.
We are waiting ur response) good luck to you

[jatar_k]

"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 'and and order by refType' at line 1"

that is the actual error from mysql, it is a single line query so that's why it says line 1.

Another thng that helps is to just echo the query and not send it to mysql.

I think I found the problem, it looks to be your actual query construction. The trick is the and's. This is the code I used after your form to test, this doesn't fix anything but it may help you get a better understanding of the data you are manipulating.

<? 
if ($province) { 
 $num_fields = count ($_GET['province']); 
 for ($i = 0; $i < $num_fields; $i++) { 
 if ($i < ($num_fields-1)) $province2 = "keywords LIKE \"%" . $province[$i] . "%\" " . $provincevar; 
 else $province2 = "keywords LIKE \"%" . $province[$i] . "%\""; 
 } 
} 
if ($language) { 
 $num_fields = count ($_GET['language']); 
 for ($i = 0; $i < $num_fields; $i++){ 
 if ($i < ($num_fields-1)) $language2 = "keywords LIKE \"%" . $language[$i] . "%\" " . $languagevar; 
 else $language2 = "keywords LIKE \"%" . $language[$i] . "%\""; 
 } 
} 
if ($topic) { 
 $num_fields = count ($_GET['topic']); 
 for ($i = 0; $i < $num_fields; $i++){ 
 if ($i < ($num_fields-1)) $topic2 = "keywords LIKE \"%" . $topic[$i] . "%\" " . $topicvar; 
 else $topic2 = "keywords LIKE \"%" . $topic[$i] . "%\""; 
 } 
} 
$query = "select * from canada_resources where title like  
\"%$title2%\" and author like \"%$author2%\" and year like  
\"%$year2%\" and $province2 and $language2 and $topic2 order  
by refType"; 
echo '<p>query: ',$query; 
echo '<pre>'; 
print_r($_POST); 
echo '</pre>'; 
?>

now select a bunch of options and submit it, at the bottom it should show you the query it created and then prints out the POST array and it's values.

[jatar_k]

I am working as I am going along

I don't understand why you used count ($_GET['province'])

I changed all references of $_GET to $_POST

[jatar_k]

ok, try this

<?
if ($_POST['province']) {
$province2 = '';
$num_fields = count($_POST['province']);
for ($i = 0; $i < $num_fields; $i++) {
if ($i < ($num_fields-1)) $province2 .= " keywords LIKE \"%" . $_POST['province'][$i] . "%\" " . $_POST['provincevar'];
else $province2 .= " keywords LIKE \"%" . $_POST['province'][$i] . "%\"";
}
}
if ($_POST['language']) {
$language2 = '';
$num_fields = count($_POST['language']);
for ($i = 0; $i < $num_fields; $i++) {
if ($i < ($num_fields-1)) $language2 .= " keywords LIKE \"%" . $_POST['language'][$i] . "%\" " . $_POST['languagevar'];
else $language2 .= " keywords LIKE \"%" . $_POST['language'][$i] . "%\"";
}
}
if ($_POST['topic']) {
$topic2 = '';
$num_fields = count($_POST['topic']);
for ($i = 0; $i < $num_fields; $i++) {
if ($i < ($num_fields-1)) $topic2 .= " keywords LIKE \"%" . $_POST['topic'][$i] . "%\" " . $_POST['topicvar'];
else $topic2 .= " keywords LIKE \"%" . $_POST['topic'][$i] . "%\"";
}
}

// query construction begins 
$output = 'no'; 
$query = "select * from canada_resources where"; 
if (isset($_POST['title']) &&!empty($_POST['title'])) { 
 $query .= ' title like "%' . $title2 . '%"'; 
 $output = 'yes'; 
} 
if (isset($_POST['author']) &&!empty($_POST['author'])) { 
 if ($output == 'yes') $query .= ' and'; 
 $query .= ' author like "%' . $author2 . '%"'; 
} 
if (isset($_POST['year']) &&!empty($_POST['year'])) { 
 if ($output == 'yes') $query .= ' and'; 
 $query .= ' year like "%' . $year2 . '%"'; 
} 
if (isset($province2) &&!empty($province2)) { 
 if ($output == 'yes') $query .= ' and'; 
 $query .= ' ' . $province2; 
} 
if (isset($language2) &&!empty($language2)) { 
 if ($output == 'yes') $query .= ' and'; 
 $query .= ' ' . $language2; 
} 
if (isset($topic2) &&!empty($topic2)) { 
 if ($output == 'yes') $query .= ' and'; 
 $query .= ' ' . $topic2; 
} 
$query .= ' order by refType'; 
echo '<p>query: ',$query; 
echo '<pre>'; 
print_r($_POST); 
echo '</pre>'; 
?> 

[jatar_k]

also your radios for 'languagevar' are named wrong, you presenlty have them as 'andor'

[AutumnTD]

Thank you, both of you. You've been incredibly helpful. I'm still working through it, an your solutions are cluing me in.

Orion, thanks for helping me build that loop construction.

Jatar, on the code you gave me, I added "$output = 'yes';" to the bottom of each "if" statement in the query, in case "title" doesn't exist.

Everything seems to be working well, except for the logic with the and/or. If everything is an "AND", we're golden. "OR"s confuse things.

It looks like if I throw in one OR, it counts all of the options as an OR, even where I placed AND.

I've tried using "AND" between checkbox areas, and also using the radio button value between checkbox area. Right now, it's set to use the radio button area.

If you have a chance, try testing the page. It's set to return results and also show the query. It's not making sense to me.

[jatar_k]

I thought this might be an issue

I assume you mean the logic isn't working out in regards to the results returned from MySQL.

So, you probably need to use parentheses [dev.mysql.com]. This page is short but the first user comment sums it up quite quickly, if you need more explanation then come on back. ;)

[AutumnTD]

Oh, thank you! It works now. :)

Query setup:

// query construction begins
$output = 'no';
$query = "select * from canada_resources where";
if (isset($_POST['title']) &&!empty($_POST['title'])) {
$query .= ' title like "%' . $title2 . '%"';
$output = 'yes';
}
if (isset($_POST['author']) &&!empty($_POST['author'])) {
if ($output == 'yes') $query .= ' and';
$query .= ' author like "%' . $author2 . '%"';
$output = 'yes';
}
if (isset($_POST['year']) &&!empty($_POST['year'])) {
if ($output == 'yes') $query .= ' and';
$query .= ' year like "%' . $year2 . '%"';
}
if (isset($province2) &&!empty($province2)) {
if ($output == 'yes') $query .= ' and';
$query .= ' (' . $province2 . ') ';
$output = 'yes';
}
if (isset($language2) &&!empty($language2)) {
if ($output == 'yes') $query .= ' and';
$query .= ' (' . $language2 . ') ';
$output = 'yes';
}
if (isset($topic2) &&!empty($topic2)) {
if ($output == 'yes') $query .= ' and';
$query .= ' (' . $topic2 . ') ';
$output = 'yes';
}
$query .= ' order by refType';

I am so grateful. This forum has made a believer out of me!

[jatar_k]

nice work, glad to help