asked on

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

ASKER CERTIFIED SOLUTION

oheil

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Klaus Andersen

ASKER

That was exactly what I needed. Oli, you are a master!
Thanks and cheers!

Klaus Andersen

ASKER

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?

oheil

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

oheil

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

Klaus Andersen

ASKER

Oli,

That works much better, many thanks man.

About your questions, I have this information here: https://www.experts-exchange.com/questions/28239690/Using-filters-in-search-engine-PHP-MYSQL.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.

oheil

Ah, there are already a lot of the points I mentioned also.
You should reward Ray for his efforts!

:-)

Oli

Klaus Andersen

ASKER

For sure I will ;)

Thanks for your help Oli!