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

asked on

Object of class could not be converted to string

Hi experts,


I'm trying to add a pagination to a search engine, but when I run the script I get the following  error.
As you can see below, it makes the query and detects the number of results, but doesnt come up.

 FOUND 979 ROWS OF DATA Catchable fatal error: Object of class mysqli_result could not be converted to string in ***/search.php on line 314  

Open in new window


This is the line 314

	$query = "SELECT COUNT(*) as nume FROM $res";

Open in new window


THIS IS AN EXTRACT OF GENERAL SCRIPT, INCLUDING QUERY AND PAGINATION

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

        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($sql);


// 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->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;



	echo "<TABLE cellspacing=\"3\" border=\"1\" cellpadding=\"8\">\n";
	
	echo "</TR>\n";


?>

<?php  ///// PAGINATION CODE STARTS
	

	// How many adjacent pages should be shown on each side?
	$adjacents = 3;
	
	/* 
	   First get total number of rows in data table. 
	   If you have a WHERE clause in your query, make sure you mirror it here.
	*/
	$query = "SELECT COUNT(*) as nume FROM $res";
	$total_pages = mysqli_fetch_array($mysqli->query($query));
	$total_pages = $total_pages[nume];
	
	/* Setup vars for query. */
	$targetpage = "search.php"; 	//your file name  (the name of this file)
	$limit = 2; 								//how many items to show per page
	$page = $_GET['page'];
	if($page) 
		$start = ($page - 1) * $limit; 			//first item to display on this page
	else
		$start = 0;								//if no page var is given, set start to 0
	
	/* Get data. */
	
	
	/* Setup page vars for display. */
	if ($page == 0) $page = 1;					//if no page var is given, default to 1.
	$prev = $page - 1;							//previous page is page - 1
	$next = $page + 1;							//next page is page + 1
	$lastpage = ceil($total_pages/$limit);		//lastpage is = total pages / items per page, rounded up.
	$lpm1 = $lastpage - 1;						//last page minus 1
	
	/* 
		Now we apply our rules and draw the pagination object. 
		We're actually saving the code to a variable in case we want to draw it more than once.
	*/
	$pagination = "";
	if($lastpage > 1)
	{	
		$pagination .= "<div class=\"pagination\">";
		//previous button
		if ($page > 1) 
			$pagination.= "<a href=\"$targetpage?page=$prev\">« previous</a>";
		else
			$pagination.= "<span class=\"disabled\">« previous</span>";	
		
		//pages	
		if ($lastpage < 7 + ($adjacents * 2))	//not enough pages to bother breaking it up
		{	
			for ($counter = 1; $counter <= $lastpage; $counter++)
			{
				if ($counter == $page)
					$pagination.= "<span class=\"current\">$counter</span>";
				else
					$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
			}
		}
		elseif($lastpage > 5 + ($adjacents * 2))	//enough pages to hide some
		{
			//close to beginning; only hide later pages
			if($page < 1 + ($adjacents * 2))		
			{
				for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
				{
					if ($counter == $page)
						$pagination.= "<span class=\"current\">$counter</span>";
					else
						$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
				}
				$pagination.= "...";
				$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
				$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";		
			}
			//in middle; hide some front and some back
			elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
			{
				$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
				$pagination.= "<a href=\"$targetpage?page=2\">2</a>";
				$pagination.= "...";
				for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
				{
					if ($counter == $page)
						$pagination.= "<span class=\"current\">$counter</span>";
					else
						$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
				}
				$pagination.= "...";
				$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
				$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";		
			}
			//close to end; only hide early pages
			else
			{
				$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
				$pagination.= "<a href=\"$targetpage?page=2\">2</a>";
				$pagination.= "...";
				for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
				{
					if ($counter == $page)
						$pagination.= "<span class=\"current\">$counter</span>";
					else
						$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
				}
			}
		}
		
		//next button
		if ($page < $counter - 1) 
			$pagination.= "<a href=\"$targetpage?page=$next\">next »</a>";
		else
			$pagination.= "<span class=\"disabled\">next »</span>";
		$pagination.= "</div>\n";		
	}


	
		while ($row = $res->fetch_array()) {
   //get the publisher name
 
     $organizerSql = $mysqli->query("SELECT nazwa FROM baza_obiektow_inne WHERE id=" . $row['organizer_id'] . " LIMIT 1");
   $organizer = mysqli_fetch_assoc($organizerSql);
   $pretty_date = date('F Y', strtotime($row['publication_date']));
   echo "<tr>\n";
   $simbolos_reemplazar = array (" ","%","/");
   printf("<td><a href='http://embs-group.com/%s,%s'>%s</a></td>", $row['id'], str_replace( $simbolos_reemplazar, "_", $row['name']), $row['name']);
   printf("<td>%s</td>", $organizer['nazwa']);
   printf("<td>%s</td>", $row['no_pages']);
 printf("<td>%s</td>", $pretty_date);
   printf("<td>%s</td>", $row['price']);
   printf("<td>%s</td>", $row['currency']);
   
	
  
   echo "</tr>\n";

}
	

$pagination?>
<?






// ITERATE OVER THE RESULTS SET AS AN ARRAY TO SHOW WHAT WE FOUND
//echo "USING MySQLi_Result::Fetch_<i>Array</i>(): ";
//echo PHP_EOL;
//while ($row = $res->fetch_array())
//{
    // ROW BY ROW PROCESSING IS DONE HERE
  
   
//    echo PHP_EOL;
//}
echo PHP_EOL;

  

// END OF ACTION SCRIPT
}


?>

Open in new window

Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

You can use var_dump($res) to see what is contained in the $res variable.  It looks like the script misuses the result resource by trying to put it into a query string.  If you're looking for a count of rows, this is what you would probably want to use instead.

$num = $res->num_rows;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of hielo
hielo
Flag of Wallis and Futuna 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
Avatar of Klaus Andersen

ASKER

Thanks @Hielo!

I tried to run the script with your changes, but I got a syntax error in the following line.I tried to figure out which was the problem, but to me looks good, so I dont know where is failing.

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

Open in new window


I also saw that you created new variables for the $sql query. The one I showed in the extract of the all code, was only one of the possible statements for the $sql variable. Should I include the new $sql1 and $sql2 inside the if statement of each $sql condition?

Please take a look to the complete code. I marked the start of your code with:
///// @ HIELO CODE STARTS 

Open in new window


Thanks in advance, seems is the only one topic to manage to make it run.


<html>

   <!-- THE DOCUMENT WILL NOT BE STYLED BECAUSE WE DO NOT HAVE THE FULL URL -->
   <link rel="stylesheet" type="text/css" href="style.css">
   <title>Test Search Engine</title>
   <body>

      <div id="leftcol">
	<p>My menu</p>
      </div>

      <div id="content">
        <p>Enter your search terms:</p>

        <!-- OMITTING THE ACTION ATTRIBUTE CAUSES THE REQUEST TO GO TO THE CURRENT URL -->
        <form method="GET" action="<?php echo $_SERVER['../PHP_SELF'];?>" name="searchForm">
          <p> Search For: <input name="keyword" type="text" size="50" maxlength="60" />

           <!-- REMOVED SUBMIT CONTROL THAT DOES NOT SEEM TO MAKE SENSE AT THIS POINT IN THE FORM -->
           <!-- input type="submit" name="submit" value="Search!" / -->
	       <label for="categories">Select category: </label>
	       <select name="categories" id="categories">

	         <!-- ADDED A DEFAULT SELECTION -->
	         <option value="any" selected>Any sector</option>
	         <option value="1">Aerospace &amp; Transportation</option>
	         <option value="2">Automotive</option>
	         <option value="3">Banking, Finance &amp; Real Estate</option>
	         <option value="4">Basic Materials</option>
	         <option value="5">Business &amp; Government</option>
	         <option value="7">Construction &amp; Heavy Industry</option>
	         <option value="6">Consumer Goods &amp; Retail</option>
	         <option value="8">Energy &amp; Utilities</option>
	         <option value="9">Food &amp; Drink</option>
	         <option value="10">Information Technology</option>
	         <option value="11">Media &amp; Leisure</option>
	         <option value="12">Pharma &amp; Healthcare</option>
	         <option value="13">Telecomunication</option>
           </select>
           <label for="regions">Select Region:</label>
           <select name="regions" id="regions">

             <!-- ADDED A DEFAULT SELECTION -->
             <option value="any" selected>Any region</option>            
             <option value="1">Africa</option>
             <option value="3">Asia</option>
             <option value="24">Australia and New Zealand</option>
             <option value="12">Central America</option>
             <option value="20"> Central and Eastern Europe</option>
             <option value="15">Central Asia</option>
             <option value="16">East Asia</option>
             <option value="4">Europe</option>
             <option value="19">Middle East</option>
             <option value="8">North Africa</option>
             <option value="13">North America</option>
             <option value="14">South America</option>
             <option value="17">South Asia</option>
             <option value="18">South East Asia</option>
             <option value="23">Western Europe</option>
             <option value="999">Global </option>
           </select>
          </p>
          <p>&nbsp;</p>
          <p>
            <input type="radio" name="radio" id="boton1" value="1" checked="checked" />
            <label for="boton1">market reports</label>
            <input type="radio" name="radio" id="boton2" value="2" />
            <label for="boton2">swots</label>
            <input type="radio" name="radio" id="boton3" value="3" />
            <label for="boton3">all content</label>
          </p>

          <!-- ADDED A SUBMIT CONTROL -->
          <p>
            <input type="submit" />
          </p>

        </form>
</div>
</body>




<?php 

error_reporting(E_ALL);

$db_host = "***";
$db_user = "****"; 
$db_word = "****"; 
$db_name =  "****";
$db_port = "****";


$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name, $db_port);

// 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);
}



// RUN THE ACTION SCRIPT IF THE REQUEST HAS BEEN SUBMITTED
if (!empty($_GET))
{
    // SECTOR / REGION INDEX REPORT ELEMENTS
	
		 $id_cat  = intval($_GET['categories']);

if  ($id_cat == 7)   {$id_cat = " T2.id_cat IN(7, 71, 72, 73, 74, 75)"; }
else if ($id_cat == 1) {$id_cat = " T2.id_cat IN(1, 14, 15, 16, 17, 18,19)"; }
else if ($id_cat == 2) {$id_cat = " T2.id_cat IN(2, 28, 25, 22, 27, 23, 24, 31)"; }
else if ($id_cat == 3) {$id_cat = " T2.id_cat IN(3, 32, 33, 34, 39)"; }
else if ($id_cat == 4) {$id_cat = " T2.id_cat IN(4, 40, 41, 42, 43 )"; }
else if ($id_cat == 5) {$id_cat = " T2.id_cat IN(5, 45, 54, 55, 58, 46, 120 )"; }
else if ($id_cat == 6) {$id_cat = " T2.id_cat IN(6, 65, 59, 64, 62, 63, 60, 66, 69, 68, 61, 70, 67 )"; }
else if ($id_cat == 8) {$id_cat = " T2.id_cat IN(8, 78, 82, 79, 77, 81, 80, 83 )"; }
else if ($id_cat == 9) {$id_cat = " T2.id_cat IN(9, 86, 92, 90, 89, 95, 88, 85, 91, 94)"; }
else if ($id_cat == 10) {$id_cat = " T2.id_cat IN(10, 97, 98, 96)"; }
else if ($id_cat == 11) {$id_cat = " T2.id_cat IN(11, 100, 101, 106, 108, 109, 107)"; }
else if ($id_cat == 12) {$id_cat = " T2.id_cat IN(12, 110, 114, 112, 111)"; }
else if ($id_cat == 13) {$id_cat = " T2.id_cat IN(13, 115, 116, 117, 113)"; }

else if ($id_cat != 'any') {$id_cat="T2.id_cat = $id_cat"; }
	
$id_reg  = intval($_GET['regions']);

if  ($id_reg == 1)   {$id_reg = " T3.id_cat IN( 1, 7, 64, 65, 66, 67, 68, 69, 70, 71, 72, 6, 46, 47, 48, 49, 50, 51, 52, 53, 54, 56, 57, 58, 59, 279, 280, 60, 61, 62, 63, 8, 73, 74, 75, 76, 77, 78, 9, 80, 81, 82, 83, 84, 10, 85, 86, 87, 89, 90, 91, 92, 88, 93, 94, 95, 96, 97, 99, 100, 101)"; }

else if ($id_reg == 3)   {$id_reg = " T3.id_cat IN(3, 15, 190, 191, 194, 157, 158, 159, 160, 161, 16, 162, 163, 167, 164, 168, 166, 169, 165, 19, 192, 174, 195, 196, 197, 198, 199, 201, 200, 202, 203, 204, 206, 207, 17, 170, 171, 172, 173, 175, 176, 177, 178, 18, 179, 180, 188, 181, 182, 183, 184, 185, 186, 187, 189 )"; }

else if ($id_reg == 24)   {$id_reg = " T3.id_cat IN(24, 255, 256)"; }
else if  ($id_reg == 12)   {$id_reg = " T3.id_cat IN(12, 130, 131, 132, 133, 134, 136, 137)"; }
else if  ($id_reg == 20)   {$id_reg = " T3.id_cat IN(20, 208, 210 ,219 ,211 ,224 ,225 ,212 ,215 ,216 ,217)"; }
else if  ($id_reg == 15)   {$id_reg = " T3.id_cat IN(15, 190 ,191 ,194 ,157, 158 ,159 ,160 ,161)"; }
else if  ($id_reg == 16)   {$id_reg = " T3.id_cat IN(16, 162 ,163 , 167 ,164 , 168 ,166 , 169 , 165)"; }
else if  ($id_reg == 4)   {$id_reg = " T3.id_cat IN(4, 228, 20,208 ,210 , 219 ,211, 224 , 225 , 212 , 215 , 216, 217, 22,229, 231, 209 ,232, 193 ,234, 240 , 245 ,213, 238 , 214 ,242 ,243 , 205, 23, 230, 246 ,247, 218, 221 ,248 , 249, 233 ,222 , 223 , 236 , 250 , 251 , 237 , 252 , 253 , 226 , 239 , 241 , 244 , 227 , 25)"; }
else if  ($id_reg == 19)   {$id_reg = " T3.id_cat IN(19, 192 ,174 ,195 ,196 ,197 ,198 ,199 ,201 ,200 ,202 ,203, 204 ,206 ,207)"; }
else if  ($id_reg == 8)   {$id_reg = " T3.id_cat IN(19, 192 ,174 ,195 ,196 ,197 ,198 ,199 ,201 ,200 ,202 ,203, 204 ,206 ,207)"; }
else if  ($id_reg == 13)   {$id_reg = " T3.id_cat IN(13, 138, 139, 140, 135, 142)"; }
else if  ($id_reg == 14)   {$id_reg = " T3.id_cat IN(14 ,143 ,144 ,145 ,146 ,147 ,148 ,149 ,150 , 151, 152, 153, 154, 155, 156)"; }
else if  ($id_reg == 17)   {$id_reg = " T3.id_cat IN(17, 170 ,171 , 172 , 173 , 175 , 176 , 177 , 178)"; }
else if  ($id_reg == 18)   {$id_reg = " T3.id_cat IN(18 ,179 ,180 ,188 ,181 , 182 , 183 , 184 , 185 , 186 ,187 ,189	)"; }
else if  ($id_reg == 23)   {$id_reg = " T3.id_cat IN(23, 230, 246, 247, 218, 221, 248, 249, 233, 222, 223, 236, 250, 251, 237, 252, 253, 226, 239, 241, 244, 227, 254, 228 )"; }
else if  ($id_reg == '999')   {$id_reg = " T3.id_cat IN(0)"; }

else if ($id_reg != 'any') {$id_reg="T3.id_cat = $id_reg"; }	
		

    // SEARCH KEYWORD
    $keyword = $_GET['keyword'];
	
	
$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."%'";

}


    // 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 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 ; }

//B)IF THE USER SELECTED ONLY A CATEGORY
else if ($id_cat != 'any' AND $id_reg =='any') {
$sql = "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 ; }		
			 
//C)IF THE USER SELECTED ONLY REGION
else if ($id_cat == 'any' AND $id_reg!='any') {
$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 ; }


// D)GENERAL SEARCH, NOT APPLYING FILTERS

else if ($id_cat == 'any' AND $id_reg == 'any') 
{  $sql = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports WHERE ".$name_clause; }

	 
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') {
$sql = "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 ; }

//B)IF THE USER SELECTED ONLY A CATEGORY
else if ($id_cat != 'any' AND $id_reg =='any') {
$sql = "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 ; }		
			 
//C)IF THE USER SELECTED ONLY REGION
else if ($id_cat == 'any' AND $id_reg!='any') {
$sql = "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 ; }

// D)GENERAL SEARCH, NOT APPLYING FILTERS

else if ($id_cat == 'any' AND $id_reg == 'any') 
{  $sql = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM swots WHERE ".$name_clause;  }

	 
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') {
$sql = "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."))"; }

//B)IF THE USER SELECTED ONLY A CATEGORY
else if ($id_cat != 'any' AND $id_reg =='any') {
$sql = "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."))"; }	
			 
//C)IF THE USER SELECTED ONLY REGION
else if ($id_cat == 'any' AND $id_reg!='any') {
$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."))"; }

// D)GENERAL SEARCH, NOT APPLYING FILTERS

else if ($id_cat == 'any' AND $id_reg == 'any')  {
$sql = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports WHERE (".$name_clause.") OR (".$description_clause.") OR (".$table_clause.")";  }





///// @ HIELO CODE STARTS 


$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();
$res->free();

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



	echo "<TABLE cellspacing=\"3\" border=\"1\" cellpadding=\"8\">\n";
	
	echo "</TR>\n";


?>

<?php  ///// PAGINATION CODE STARTS
	

	// How many adjacent pages should be shown on each side?
	$adjacents = 3;
	
	/* Setup vars for query. */
	$targetpage = "search.php"; 	//your file name  (the name of this file)
	$limit = 2; 								//how many items to show per page
	$page = $_GET['page'];
	if($page) 
		$start = ($page - 1) * $limit; 			//first item to display on this page
	else
		$start = 0;								//if no page var is given, set start to 0
	
	/* Get data. */
	
	
	/* Setup page vars for display. */
	if ($page == 0) $page = 1;					//if no page var is given, default to 1.
	$prev = $page - 1;							//previous page is page - 1
	$next = $page + 1;							//next page is page + 1
	$lastpage = ceil($total_pages/$limit);		//lastpage is = total pages / items per page, rounded up.
	$lpm1 = $lastpage - 1;						//last page minus 1
	

	$res= mysqli->query($sql2  . $start . ', ' . $limit); // LINE MARKED WITH SYNTAX ERROR

	/* 
		Now we apply our rules and draw the pagination object. 
		We're actually saving the code to a variable in case we want to draw it more than once.
	*/
	$pagination = "";
	if($lastpage > 1)
	{	
		$pagination .= "<div class=\"pagination\">";
		//previous button
		if ($page > 1) 
			$pagination.= "<a href=\"$targetpage?page=$prev\">« previous</a>";
		else
			$pagination.= "<span class=\"disabled\">« previous</span>";	
		
		//pages	
		if ($lastpage < 7 + ($adjacents * 2))	//not enough pages to bother breaking it up
		{	
			for ($counter = 1; $counter <= $lastpage; $counter++)
			{
				if ($counter == $page)
					$pagination.= "<span class=\"current\">$counter</span>";
				else
					$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
			}
		}
		elseif($lastpage > 5 + ($adjacents * 2))	//enough pages to hide some
		{
			//close to beginning; only hide later pages
			if($page < 1 + ($adjacents * 2))		
			{
				for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
				{
					if ($counter == $page)
						$pagination.= "<span class=\"current\">$counter</span>";
					else
						$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
				}
				$pagination.= "...";
				$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
				$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";		
			}
			//in middle; hide some front and some back
			elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
			{
				$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
				$pagination.= "<a href=\"$targetpage?page=2\">2</a>";
				$pagination.= "...";
				for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
				{
					if ($counter == $page)
						$pagination.= "<span class=\"current\">$counter</span>";
					else
						$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
				}
				$pagination.= "...";
				$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
				$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";		
			}
			//close to end; only hide early pages
			else
			{
				$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
				$pagination.= "<a href=\"$targetpage?page=2\">2</a>";
				$pagination.= "...";
				for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
				{
					if ($counter == $page)
						$pagination.= "<span class=\"current\">$counter</span>";
					else
						$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
				}
			}
		}
		
		//next button
		if ($page < $counter - 1) 
			$pagination.= "<a href=\"$targetpage?page=$next\">next »</a>";
		else
			$pagination.= "<span class=\"disabled\">next »</span>";
		$pagination.= "</div>\n";		
	}


	
		while ($row = $res->fetch_array()) {
   //get the publisher name
 
     $organizerSql = $mysqli->query("SELECT nazwa FROM baza_obiektow_inne WHERE id=" . $row['organizer_id'] . " LIMIT 1");
   $organizer = mysqli_fetch_assoc($organizerSql);
   $pretty_date = date('F Y', strtotime($row['publication_date']));
   echo "<tr>\n";
   $simbolos_reemplazar = array (" ","%","/");
   printf("<td><a href='http://embs-group.com/%s,%s'>%s</a></td>", $row['id'], str_replace( $simbolos_reemplazar, "_", $row['name']), $row['name']);
   printf("<td>%s</td>", $organizer['nazwa']);
   printf("<td>%s</td>", $row['no_pages']);
 printf("<td>%s</td>", $pretty_date);
   printf("<td>%s</td>", $row['price']);
   printf("<td>%s</td>", $row['currency']);
   
	
  
   echo "</tr>\n";

}
	

$pagination?>
<?






// ITERATE OVER THE RESULTS SET AS AN ARRAY TO SHOW WHAT WE FOUND
//echo "USING MySQLi_Result::Fetch_<i>Array</i>(): ";
//echo PHP_EOL;
//while ($row = $res->fetch_array())
//{
    // ROW BY ROW PROCESSING IS DONE HERE
  
   
//    echo PHP_EOL;
//}
echo PHP_EOL;

  

// END OF ACTION SCRIPT
}



?>

Open in new window

SOLUTION
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
You are not initializing $total_pages.  Change:
$num     = $res->fetch_assoc();
$res->free();

Open in new window


to:
$num     = $res->fetch_assoc();
$total_pages=(int)$num['total'];
$res->free();

Open in new window


>>Should I include the new $sql1 and $sql2 inside the if statement of each $sql condition?
Yes.  $sql1 should have your COUNT(*) query, and $sql2 should have your fields and LIMIT clause at the end.  "case 3" is included below.  Notice that every if-else if clause contains its own $sql1 and $sql2 variables:

		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;

Open in new window

@Hielo, thanks for the reply.

I got the right number of results per page that has been set up in $limit , but the pagination itself is not coming up. I mean this,

<< < 1 2 3 4 5 6 7 8 > >>

Do you know what might be?

Cheers!
Comment deleted - I can no longer find the earlier comment this was applicable to!
Hi Ray,

I edited the comment because I found the error, but instead I have this new question. Somehow is not appearing the pagination that should be at the bottom right after the result list.
new question

?

I got the right number of results per page that has been set up in $limit , but the pagination itself is not coming up. I mean this,

<< < 1 2 3 4 5 6 7 8 > >>
How can we help?  Is there some code or data we can look at?  Have you created the SSCCE so we don't waste your time chasing red herrings?
Ray, here is the last code I have for the pagination. As I mentioned before, it´s showing properly the number of results I set up per page, but not the links of the pagination to go to the other pages

<?php  ///// PAGINATION CODE STARTS
	

	// How many adjacent pages should be shown on each side?
	$adjacents = 3;
	
	/* Setup vars for query. */
	$targetpage = "search.php"; 	//your file name  (the name of this file)
	$limit = 50; 								//how many items to show per page
	$page = $_GET['page'];
	if($page) 
		$start = ($page - 1) * $limit; 			//first item to display on this page
	else
		$start = 0;								//if no page var is given, set start to 0
	
	/* Get data. */
	
	
	/* Setup page vars for display. */
	if ($page == 0) $page = 1;					//if no page var is given, default to 1.
	$prev = $page - 1;							//previous page is page - 1
	$next = $page + 1;							//next page is page + 1
	$lastpage = ceil($total_pages/$limit);		//lastpage is = total pages / items per page, rounded up.
	$lpm1 = $lastpage - 1;						//last page minus 1
	
	
	$res= $mysqli->query($sql2 .$start.','. $limit); // LINE MARKED WITH SYNTAX ERROR

	/* 
		Now we apply our rules and draw the pagination object. 
		We're actually saving the code to a variable in case we want to draw it more than once.
	*/
	$pagination = "";
	if($lastpage > 1)
	{	
		$pagination .= "<div class=\"pagination\">";
		//previous button
		if ($page > 1) 
			$pagination.= "<a href=\"$targetpage?page=$prev\">« previous</a>";
		else
			$pagination.= "<span class=\"disabled\">« previous</span>";	
		
		//pages	
		if ($lastpage < 7 + ($adjacents * 2))	//not enough pages to bother breaking it up
		{	
			for ($counter = 1; $counter <= $lastpage; $counter++)
			{
				if ($counter == $page)
					$pagination.= "<span class=\"current\">$counter</span>";
				else
					$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
			}
		}
		elseif($lastpage > 5 + ($adjacents * 2))	//enough pages to hide some
		{
			//close to beginning; only hide later pages
			if($page < 1 + ($adjacents * 2))		
			{
				for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
				{
					if ($counter == $page)
						$pagination.= "<span class=\"current\">$counter</span>";
					else
						$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
				}
				$pagination.= "...";
				$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
				$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";		
			}
			//in middle; hide some front and some back
			elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
			{
				$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
				$pagination.= "<a href=\"$targetpage?page=2\">2</a>";
				$pagination.= "...";
				for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
				{
					if ($counter == $page)
						$pagination.= "<span class=\"current\">$counter</span>";
					else
						$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
				}
				$pagination.= "...";
				$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
				$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";		
			}
			//close to end; only hide early pages
			else
			{
				$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
				$pagination.= "<a href=\"$targetpage?page=2\">2</a>";
				$pagination.= "...";
				for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
				{
					if ($counter == $page)
						$pagination.= "<span class=\"current\">$counter</span>";
					else
						$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
				}
			}
		}
		
		//next button
		if ($page < $counter - 1) 
			$pagination.= "<a href=\"$targetpage?page=$next\">next »</a>";
		else
			$pagination.= "<span class=\"disabled\">next »</span>";
		$pagination.= "</div>\n";		
	}


	
		while ($row = $res->fetch_array()) {
   //get the publisher name
 
     $organizerSql = $mysqli->query("SELECT nazwa FROM baza_obiektow_inne WHERE id=" . $row['organizer_id'] . " LIMIT 1");
   $organizer = mysqli_fetch_assoc($organizerSql);
   $pretty_date = date('F Y', strtotime($row['publication_date']));
   echo "<tr>\n";
   $simbolos_reemplazar = array (" ","%","/");
   printf("<td><a href='http://embs-group.com/%s,%s'>%s</a></td>", $row['id'], str_replace( $simbolos_reemplazar, "_", $row['name']), $row['name']);
   printf("<td>%s</td>", $organizer['nazwa']);
   printf("<td>%s</td>", $row['no_pages']);
 printf("<td>%s</td>", $pretty_date);
   printf("<td>%s</td>", $row['price']);
   printf("<td>%s</td>", $row['currency']);
   	  
   echo "</tr>\n";

}
	

$pagination?>
<?

echo PHP_EOL;

// END OF ACTION SCRIPT
}



?>

Open in new window

Line 133... What is contained in the $pagination variable?  You may want to use var_dump() to inspect the variable.  If you find it contains the information you want, the next step might be to add this information into the HTML document with echo.

From the code smell, you seem to be cultivating some bad habits that will eventually cause your script to fail, or make it the subject of a hacker attack.  You can avoid these latent mistakes if you follow a structured learning process.  A couple of months of structured study will put you a couple of years ahead of trial-and-error learning.

Some structured learning guidance is available in this article.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

Some things to avoid are pointed out in this article.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12293-AntiPHPatterns-and-AntiPHPractices.html

I think the first thing you should do would be to add error_reporting(E_ALL) to the top of your script so that you can be sure that the script never relies on an undefined variable.  Then the next thing would be to learn how to test for errors in the MySQLi extension.  MySQL is not a black box; it can and will fail for reasons that are outside of your control, and if you do not test for errors, you're leaving a time-bomb in the code.  You might also want to learn about how to handle external data in PHP.  The request variables ($_GET and $_POST) are obviously external data, but what you may not have thought about are the values in $_SESSION, $_COOKIE and the data base.
http://php.net/manual/en/language.variables.external.php
http://php.net/manual/en/security.php

As you begin to develop a sense of programming and the principles of computer science, you may want to refer back to this excellent article.
http://www.codinghorror.com/blog/2006/05/code-smells.html

...and also to this hilariously funny article!
http://thc.org/root/phun/unmaintain.html
Hey Ray, thanks for info! I just printed the $pagination through echo  and now the pagination appears!

The only one topic is that the pagination links (page 1, 2, 3 etc) are not properly constructed.

I have it in the form search.php?page=2

And should be, for example, if I search for construction germany:

search.php?keyword=construction+germany&categories=any&regions=any&radio=3&page=2

I tried it manually in the address bar and it works!
Can ayou give me a hand  building the link structure properly?

Thanks in advance

UPDATE:

I´m trying to create the link structure through this variable:

$valorpaginacion = "keyword=$keyword&categories=$id_cat_paginacion&regions=$id_reg_paginacion&radio=$radio" ;

But at the moment I have 2 problems:

- If I search contruction germany, in the link strcture should be construction+germany
but actually the variable $keyword recognize only the second word.
- This part (&re) from $ valorpaginacion structure is being recognize as a symbol by HTML
No offense meant, but this question was answered at the top, and it seems to have morphed into a long and meandering quest for help writing your application.  You should consider hiring a professional developer to write the application for you, or at least stop what you're doing and take some time to learn the principles of computer science and PHP programming.  You're trying to do too many different things at once.  If you can focus on the parts of the application, one at a time, you will have a better chance of success.

Best of luck with the project, ~Ray
I totally agree with what you say Ray. In general I´m very thankful for your constant support and I know I  need to take a time and start learning more about PHP programming.
But sometimes things goes different as we want, and this search is something I must to do, and finish as soon as possible. Fortunately I´m actually in the last stage of it.

By the way, I also found solution to the last question I wrote here.

Thanks one again and have a nice day!
@Hielo, I hope you can see this message. I need your help to include your code within the other 2 cases. I'm trying but at the moment is only working the option you gave me as example. Please help.