Add new value to query, PHP MySQL

Hello,

I have this extract of my script
$keywords=explode(" ",$keyword);
$name_clause = " 1";
$table_clause = " 1";
$description_clause = " 1";
foreach( $keywords as $keyword ) {
  $part=trim($keyword);
  $name_clause = $name_clause . " AND name LIKE '%".$part."%'";
  $table_clause =  $table_clause . " AND table_content LIKE '%".$part."%'";
  $description_clause =  $description_clause . " AND description LIKE '%".$part."%'";

}

Open in new window

And I need to put this in the query:   $name_clause, $table_clause and $description_clause.

For the first variable is working good:
{  $sql = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports WHERE ".$name_clause; }

Open in new window

But if I add the other 2, I get a QUERY FAIL syntax error
This is how I'm doing:

{  $sql = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports WHERE ".$name_clause .$description_clause .$table_clause; }

Open in new window

How can I add the 3 values in a correct way?
Thanks in advance
Klaus AndersenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

oheilCommented:
Hi, here I am.
You miss an " AND " or an " OR " inbetween, and put it into ().

$sql = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports WHERE (".$name_clause.") OR (".$description_clause.") OR (".$table_clause.")";

Open in new window


I think, "OR" is what you intend.

Oli
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Klaus AndersenAuthor Commented:
That was exactly what I needed. Oli, you are a master!
Thanks and cheers!
0
Klaus AndersenAuthor Commented:
Oli,

I was trying to apply the new variables for this query:

$sql = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports AS T1, country_index_reports AS T3 WHERE  
($id_reg) AND (T1.id=T3.id_obiekt) AND ".$name_clause; }

Open in new window


In this way,
$sql = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports AS T1, country_index_reports AS T3 WHERE  
($id_reg) AND (T1.id=T3.id_obiekt) AND (".$name_clause.") OR (".$description_clause.") OR (".$table_clause.")"; }

Open in new window


I dont get syntax error, but the query is taking quite long. am I doing something wrong?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

oheilCommented:
To be on the safe side, use two more parentheses:
$sql = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports AS T1, country_index_reports AS T3 WHERE  
($id_reg) AND (T1.id=T3.id_obiekt) AND ((".$name_clause.") OR (".$description_clause.") OR (".$table_clause."))";

Open in new window

I do not know exactly the precedence of the logical operators in SQL, but now it is, as I believe, you want the logic to be.

But this does not address the performance problem, and I fear, that your overall approach to the problem of searching for more than 1 keyword, is now showing what I mentioned in my first answer in the yesterday discussion.

What I would like to know:
1) How big are the tables "reports" and "country_index_reports" or how big do you expect them to be in future?
2) Which columns from your above select belong to table "reports" and which belong to "country_index_reports"?

I don't believe it, but maybe, if you/we rearrange your SQL statement, you can gain some speed.

Oli
0
oheilCommented:
Or, another thought:
Do the columns name, description and table_content only belong to table "reports" ?

Maybe you must add an "T1." to the clauses, like in:
$name_clause = $name_clause . " AND T1.name LIKE '%".$part."%'";

Open in new window


All your other SQL statements can easily extended to
SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports AS T1 WHERE ...

Open in new window

So you do not have to create the clauses again.

Oli
0
Klaus AndersenAuthor Commented:
Oli,

That works much better, many thanks man.

About your questions, I have this information here: http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28239690.html

The code I have posted there has already changed completely but, the information about the tables I use in the engine are the same.

The tables that  has the main content are reports and swots. Aproximately 150,000 rows each.

The tables for categories and regions are really small.
0
oheilCommented:
Ah, there are already a lot of the points I mentioned also.
You should reward Ray for his efforts!

:-)

Oli
0
Klaus AndersenAuthor Commented:
For sure I will ;)

Thanks for your help Oli!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.