Link to home
Start Free TrialLog in
Avatar of Klaus Andersen
Klaus AndersenFlag for Germany

asked on

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

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

What error do you get?
Avatar of Klaus Andersen

ASKER

Rather than an error, is just that I dont get the results from the query
I copy the rest of the code related to print the results in the case may be useful

http://pastebin.com/g5eJ6u0a
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.
I included in the comment above the link to pastebin with the rest of the code.

In the line 204 I run the $sql2
What is the content of the $sql2 query?  Please use var_dump() to print it out and post that string here, thanks.
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

ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
I found the problem. It was just a matter of a space after the LIMIT.
Thanks for the help.
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 ";
This is still not completely solved.  Line 10 on your original post should be:
$sql1 = "...";

You are missing the "1"
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

>>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 (https://www.experts-exchange.com/questions/28250384/Object-of-class-could-not-be-converted-to-string.html).
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

the LIMIT stuff is also what I posted in my 2nd comment (https://www.experts-exchange.com/questions/28256254/PHP-query-working-just-for-3th-case.html?anchorAnswerId=39541281#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:https://www.experts-exchange.com/questions/28256254/PHP-query-working-just-for-3th-case.html?anchorAnswerId=39541498#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.