PHP query working just for 3th case

Hello,

I have a search engine, that has custom queries according to the radio option that the user select.


It´s divided in 3 cases, and actually my search is working only for  case 3
I needed to modify my query to add a pagination, and this 3th option is currently working was adviced by an expert of E.E. But now that I´m trying to copy the code structure for case 1 and case 2, it doesn´t work.

I think I might be applying this line of code  unproperly  ".$name_clause." LIMIT" in each query of the cases 1 and 2, because is the only one modification I did lately.


Here is the extract of the script where I have the queries.
I would be thankful if the experts can give me a hand.

$radio = $_GET['radio'];
    // TABLES T BE SEARCHED
    switch ($_GET['radio'])       
    {	
case 1:

//A) IF THE USER SELECTED A CATEGORY AND A REGION	
if ($id_cat != 'any' AND $id_reg!='any') {
	
$sql = "SELECT COUNT(*) as total FROM reports AS T1, sector_index_reports AS T2, country_index_reports AS T3 WHERE ($id_cat) AND  (T1.id=T2.id_obiekt) AND  ($id_reg) AND (T1.id=T3.id_obiekt)  AND ".$name_clause ; 

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

//B)IF THE USER SELECTED ONLY A CATEGORY
else if ($id_cat != 'any' AND $id_reg =='any') {

$sql1 = "SELECT COUNT(*) as total FROM reports AS T1, sector_index_reports AS T2  WHERE ($id_cat) AND  (T1.id=T2.id_obiekt) AND  ".$name_clause ;
	
$sql2 = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports AS T1, sector_index_reports AS T2  WHERE 
($id_cat) AND  (T1.id=T2.id_obiekt) AND  ".$name_clause." LIMIT" ;

 }		
			 
//C)IF THE USER SELECTED ONLY REGION
else if ($id_cat == 'any' AND $id_reg!='any') {
	
$sql1 = "SELECT COUNT(*) as total FROM reports AS T1, country_index_reports AS T3 WHERE  ($id_reg) AND (T1.id=T3.id_obiekt) AND ".$name_clause ;

$sql2 = "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." LIMIT" ; 


 }
      
// D)GENERAL SEARCH, NOT APPLYING FILTERS

else if ($id_cat == 'any' AND $id_reg == 'any') 
{  $sql1 = "SELECT COUNT(*) as total FROM reports WHERE ".$name_clause;
   $sql2 = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports WHERE ".$name_clause." LIMIT"; }

	 
break;	
// END FOR MARKET REPORT TITLES OPTIONS			
			
case 2:

//A) IF THE USER SELECTED A CATEGORY AND A REGION			
if ($id_cat != 'any' AND $id_reg!='any') {
	
$sql1 = "SELECT COUNT(*) as total FROM swots AS T1, sector_index_reports AS T2, country_index_reports AS T3 WHERE ($id_cat) AND  (T1.id=T2.id_obiekt) AND  ($id_reg) AND (T1.id=T3.id_obiekt)  AND ".$name_clause ;

$sql2 = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM swots AS T1, sector_index_reports AS T2, country_index_reports AS T3 WHERE ($id_cat) AND  (T1.id=T2.id_obiekt) AND  ($id_reg) AND (T1.id=T3.id_obiekt)  AND ".$name_clause." LIMIT" ;

 }

//B)IF THE USER SELECTED ONLY A CATEGORY
else if ($id_cat != 'any' AND $id_reg =='any') {
	
$sql1 = "SELECT COUNT(*) as total FROM swots AS T1, sector_index_reports AS T2  WHERE ($id_cat) AND  (T1.id=T2.id_obiekt) AND  ".$name_clause ;

$sql2 = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM swots AS T1, sector_index_reports AS T2  WHERE 
($id_cat) AND  (T1.id=T2.id_obiekt) AND  ".$name_clause." LIMIT" ;


 }		
			 
//C)IF THE USER SELECTED ONLY REGION
else if ($id_cat == 'any' AND $id_reg!='any') {

$sql1 = "SELECT COUNT(*) as total FROM swots AS T1, country_index_reports AS T3 WHERE ($id_reg) AND (T1.id=T3.id_obiekt) AND ".$name_clause ; 

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

}


// D)GENERAL SEARCH, NOT APPLYING FILTERS

else if ($id_cat == 'any' AND $id_reg == 'any') { 
 $sql1 = "SELECT COUNT(*) as total FROM swots WHERE ".$name_clause ;  
 $sql2 = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM swots WHERE ".$name_clause." LIMIT" ;  
 
 }

	 
break;	
// END FOR SWOTS OPTIONS		
			
			

case 3:
        
			//A) IF THE USER SELECTED A CATEGORY AND A REGION	
			if ($id_cat != 'any' AND $id_reg!='any') {
				$sql1 = "SELECT COUNT(*) as total FROM reports AS T1, sector_index_reports AS T2, country_index_reports AS T3 WHERE ($id_cat) AND  (T1.id=T2.id_obiekt) AND  ($id_reg) AND (T1.id=T3.id_obiekt)  AND ((".$name_clause.") OR (".$description_clause.") OR (".$table_clause."))"; 
				
				
				$sql2 = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports AS T1, sector_index_reports AS T2, country_index_reports AS T3 WHERE ($id_cat) AND  (T1.id=T2.id_obiekt) AND  ($id_reg) AND (T1.id=T3.id_obiekt)  AND ((".$name_clause.") OR (".$description_clause.") OR (".$table_clause.")) LIMIT "; 
			}
			//B)IF THE USER SELECTED ONLY A CATEGORY
			else if ($id_cat != 'any' AND $id_reg =='any') {
				$sql1 = "SELECT COUNT(*) as total FROM reports AS T1, sector_index_reports AS T2  WHERE ($id_cat) AND  (T1.id=T2.id_obiekt) AND ((".$name_clause.") OR (".$description_clause.") OR (".$table_clause."))"; 
				$sql2 = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports AS T1, sector_index_reports AS T2  WHERE ($id_cat) AND  (T1.id=T2.id_obiekt) AND ((".$name_clause.") OR (".$description_clause.") OR (".$table_clause.")) LIMIT "; 
			}
			//C)IF THE USER SELECTED ONLY REGION
			else if ($id_cat == 'any' AND $id_reg!='any') {
				$sql1 = "SELECT COUNT(*) as total 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."))"; 
				$sql2 = "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.")) LIMIT "; 
			}
			// D)GENERAL SEARCH, NOT APPLYING FILTERS
			else if ($id_cat == 'any' AND $id_reg == 'any')  {
				$sql1 = "SELECT COUNT(*) as total FROM reports WHERE (".$name_clause.") OR (".$description_clause.") OR (".$table_clause.")";  
				$sql2 = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports WHERE (".$name_clause.") OR (".$description_clause.") OR (".$table_clause.") LIMIT ";
			}
			break;
			
			// END FOR ALL CONTENT 

        default:
            trigger_error('SCRIPT UNDER ATTACK, RUN LIKE HELL', E_USER_ERROR);
    }
  // DEBUGGING INFORMATION TO SHOW THE REQUEST DATA AND THE RESULTING QUERY
  
 
 $res = $mysqli->query($sql1);


// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESULT OBJECT IN $res
// AND SO WE CAN NOW USE $res IN OTHER MYSQLI FUNCTIONS


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->fetch_assoc();
$total_pages=(int)$num['total'];
$res->free();

$num_fmt = number_format($num['total']);
if (!$num)
{
    echo "<br/>QUERY: $sq2 ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql2 ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;

Open in new window

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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
What error do you get?
0
Klaus AndersenAuthor Commented:
Rather than an error, is just that I dont get the results from the query
0
Klaus AndersenAuthor Commented:
I copy the rest of the code related to print the results in the case may be useful

http://pastebin.com/g5eJ6u0a
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Guy Hengel [angelIII / a3]Billing EngineerCommented:
the code snippet you show actually doesn't output any data (and actually doesn't run $sql2), so we are missing some of the code.

also, the mysql LIMIT alone isn't enough, so I presume the $sql is not complete yet, and that part is only appended after the code you posted so far.
0
Klaus AndersenAuthor Commented:
I included in the comment above the link to pastebin with the rest of the code.

In the line 204 I run the $sql2
0
Ray PaseurCommented:
What is the content of the $sql2 query?  Please use var_dump() to print it out and post that string here, thanks.
0
Klaus AndersenAuthor Commented:
var_dump for $sql2  ( Selected case 3)   I searched by heavy machinery, and its showing the results properly

"SELECT id,name,organizer_id,no_pages,publication_date,price,currency FROM reports WHERE ( 1 AND name LIKE '%heavy%' AND name LIKE '%machinery%') OR ( 1 AND description LIKE '%heavy%' AND description LIKE '%machinery%') OR ( 1 AND table_content LIKE '%heavy%' AND table_content LIKE '%machinery%') LIMIT "

Open in new window


var_dump for $sql2  ( Selected case 1)   I searched by heavy machinery, doesnt show the results, but recognize the number of results should be shown

FOUND 2 ROWS OF DATA

"SELECT id,name,organizer_id,no_pages,publication_date,price,currency FROM reports WHERE 1 AND name LIKE '%heavy%' AND name LIKE '%machinery%' LIMIT"

Open in new window

0
Ray PaseurCommented:
Here are the two queries, laid out so they are easy to read.  I have never seen a LIMIT clause without any arguments and I don't pretend to know what that would do.  Maybe the case-1 query could use the parenthesized expressions of the case-3 query?  Or maybe there is some other logic at work in this process that prevents the case-1 query from creating its output?

// CASE-3
"
SELECT 
  id
, name
, organizer_id
, no_pages
, publication_date
, price
, currency 
FROM reports 
WHERE ( 1 AND name LIKE '%heavy%' AND name LIKE '%machinery%') 
OR    ( 1 AND description LIKE '%heavy%' AND description LIKE '%machinery%') 
OR    ( 1 AND table_content LIKE '%heavy%' AND table_content LIKE '%machinery%') 
LIMIT
"

// CASE-1
"
SELECT 
  id
, name
, organizer_id
, no_pages
, publication_date
, price
, currency 
FROM reports 
WHERE 1 AND name LIKE '%heavy%' AND name LIKE '%machinery%' 
LIMIT
"

Open in new window

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:
Maybe the case-1 query could use the parenthesized expressions of the case-3 query?

I already tried using the parenthesized expressions and I get the same result.

Or maybe there is some other logic at work in this process that prevents the case-1 query from creating its output?

That's the reason of my question. The script is working for the case 3, but not for the cases 1 and 2.
But anyway, making a var_dump I see that in all the cases, the query is done properly and the system recognize the number of results that it should show, but somehow it doesnt come up for case 1 and 2.
I think must to be some silly issue, but I look at the code and I can't figure out what can be.
0
Klaus AndersenAuthor Commented:
I found the problem. It was just a matter of a space after the LIMIT.
Thanks for the help.
0
Klaus AndersenAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for _PJ_'s comment #a39543379

for the following reason:

I had this

name_clause." LIMIT";

And should be this

name_clause." LIMIT ";
0
hieloCommented:
This is still not completely solved.  Line 10 on your original post should be:
$sql1 = "...";

You are missing the "1"
0
Ray PaseurCommented:
I'm going to object to closing this question, because we are nowhere near a practical answer, and because the space after LIMIT is either factually wrong or the information is taken out of context.  A LIMIT clause without the argument(s) results in a query failure.  Both of the queries you posted above have the LIMIT without the arguments.  The PHP script must test for the failure and visualize the query and the failure information.  

This script shows that your query will fail and it teaches how to isolate the query and show the failure information.

<?php // RAY_temp_pj_.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// THIS SCRIPT DEMONSTRATES FAILURE OF MySQLi QUERY WHEN LIMIT IS CODED WRONG


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING A QUERY
$sql
=
"
SELECT
  id
, name
, organizer_id
, no_pages
, publication_date
, price
, currency
FROM reports
WHERE 1 AND name LIKE '%heavy%' AND name LIKE '%machinery%'
LIMIT
"
;

// RUN THE QUERY
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW THE RESULTS OF THE QUERY
var_dump($res);

Open in new window

0
hieloCommented:
>>the space after LIMIT is either factually wrong or the information is taken out of context.
@Ray: The LIMIT clause applies only to $sql2 variable and later in the code (not shown above) the $sql2 variable is executed as:

$res=$mysqli->query( $sql2 .$start . ', '. $limit);

The poster simply posted the relevant fragment.  We helped him on the original question (http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28250384.html).
0
Ray PaseurCommented:
Ahh, thanks for that, @hielo.  This is EXACTLY why some time ago I advised the author to build the query in a separate string variable so that it could be printed out for diagnostic purposes.  I see my advice fell upon deaf ears, and once again we are dealing with fragments of data that omitted the vital components.  Oh, well.

Anti-Practice #9a

Looking back at the pastebin version of the script I found this, which would use one string variable in the query and another string variable in the error message.  That's almost worse than no error message!

$res = $mysqli->query($sql1);
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the LIMIT stuff is also what I posted in my 2nd comment (http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28256254.html#a39541281) ...
the code snippet you show actually doesn't output any data (and actually doesn't run $sql2), so we are missing some of the code.

also, the mysql LIMIT alone isn't enough, so I presume the $sql is not complete yet, and that part is only appended after the code you posted so far. 

Open in new window



anyhow, if the question is to be closed, it would be with Ray's comment:http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28256254.html#a39541498

where the code post posted with LIMIT with a crlf behind, and would hence have solved the issue also.

_PJ_,
hopefully you will learn from the experts how to write your code in a more structured way.
for example, I would not build $sql1 and $sql2 like that, but with 1 $sql_from variable that holds all the part starting and including from FROM.


in practice:
$sql_from  = " FROM reports AS T1, sector_index_reports AS T2  WHERE ($id_cat) AND  (T1.id=T2.id_obiekt) AND  ".$name_clause ;
$sql_columns = " id,name,organizer_id,no_pages,publication_date,price,currency  ";
$sql_limit = " LIMIT 1,20 "; // this is what is in the additional code somewhere ...


$sql_count  = "SELECT COUNT(*) as total " . $sql_from; // this would be $sql1
$sql_data = "SELECT " . $sql_columns . $sql_from . $sql_limit; // this would be the full $sql2
	
// and then you execute $sql_count and later $sql_data, and have both full sql variables to debug... 

Open in new window

     

this will also simplify the code alot.
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.