Klaus Andersen
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.
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;
What error do you get?
ASKER
Rather than an error, is just that I dont get the results from the query
ASKER
I copy the rest of the code related to print the results in the case may be useful
http://pastebin.com/g5eJ6u0a
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.
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.
ASKER
I included in the comment above the link to pastebin with the rest of the code.
In the line 204 I run the $sql2
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.
ASKER
var_dump for $sql2 ( Selected case 3) I searched by heavy machinery, and its showing the results properly
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%') OR ( 1 AND description LIKE '%heavy%' AND description LIKE '%machinery%') OR ( 1 AND table_content LIKE '%heavy%' AND table_content LIKE '%machinery%') LIMIT "
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"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
I found the problem. It was just a matter of a space after the LIMIT.
Thanks for the help.
Thanks for the help.
ASKER
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 ";
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"
$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.
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);
>>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).
@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!
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);
}
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) ...
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:
this will also simplify the code alot.
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.
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...
this will also simplify the code alot.