Solved

fetching rows from database that have been asked by end user through a search page

Posted on 2011-09-25
7
454 Views
Last Modified: 2012-08-13
Hi There!

I have 2 web pages  search.php and searchresults.php which handles the results of search.php.

I am getting the following errors when I run searchresults.php:

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\isd\searchresults.php on line 309

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\isd\searchresults.php on line 335

In the code line 309 is ...
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC))
      {

In the code line 335 is ...
      mysqli_free_result ($r);

I am not sure what I am doing wrong when calling the database and when closing the database...

Please could someone help ?

Thanks!!
Chelsea
0
Comment
Question by:h3rm1t9536
  • 4
  • 2
7 Comments
 
LVL 7

Accepted Solution

by:
kshna earned 250 total points
Comment Utility
can you let me know what is $r...

I hope its the result of the query execution i.e.

$r = mysqli_query($query);
where $query is the mysql query....


also can you please check if the query you are executing is a valid mysql query... ie. can you var_dump($r); can paste the output.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given ... almost always means the preceding query failed.  See the man page here for the return values.
http://php.net/manual/en/mysqli.query.php

We see this a lot when a PHP program has not tested the return values from mysqli_query(), but has attempted to use the return values in other functions.  If you read Example #1 on the man page and you follow the guidance there for error visualization you will be able to see what went wrong and how to correct it.
0
 

Author Comment

by:h3rm1t9536
Comment Utility
Thanks so much for your advise!

The exact code is:
<?php //Display th results from the search.php 
	
	$_pagetitle="Search Results"; 
	include('inc/headersr.inc.php'); 
	
	$CountryValue = $_POST['CountryValue'];
	$IndustrySectorValue = $_POST['IndustrySectorValue'];
	$IndustrySubSectorValue = $_POST['IndustrySubSectorValue'];
	$InsertStockValue = $_POST['InsertStockValue'];
	$InsertSizeValue = $_POST['InsertSizeValue'];
	$CoverFormatSizeValue = $_POST['CoverFormatSizeValue'];
	$CoverStockFinishValue = $_POST['CoverStockFinishValue'];
	$DistributionChannelValue = $_POST['DistributionChannelValue'];
	$StrategicApplicationValue = $_POST['StrategicApplicationValue'];
	
	$conditions = FALSE;
	
	if($CountryValue)
	{
		$conditions = "AND country.CountryValue ". $CountryValue;
	}
	if($IndustrySectorValue)
	{
		$conditions .= "AND industry_sector.IndustrySectorValue ". $IndustrySectorValue;
	}
	if($IndustrySubSectorValue)
	{
		$conditions .= "AND industry_sub_sector.IndustrySubSectorValue ". $IndustrySubSectorValue;
	}
	if($InsertStockValue)
	{
		$conditions .= "AND insert_stock.InsertStockValue ". $InsertStockValue;
	}
	if($InsertSizeValue)
	{
		$conditions .= "AND insert_size.InsertSizeValue ". $InsertSizeValue;
	}
	if($CoverFormatSizeValue)
	{
		$conditions .= "AND cover_format_size.CoverFormatSizeValue ". $CoverFormatSizeValue;
	}
	if($CoverStockFinishValue)
	{
		$conditions .= "AND cover_stock_finish.CoverStockFinishValue ". $CoverStockFinishValue;
	}
	if($DistributionChannelValue)
	{
		$conditions .= "AND distribution_channel.DistributionChannelValue ". $DistributionChannelValue;
	}
	if($StrategicApplicationValue)
	{
		$conditions .= "AND strategicApplication.StrategicApplicationValue ". $StrategicApplicationValue;
	}
	
	require_once ('C:xampp/mysqli_connect.php');

	// Number of records to show per page:
	$display = 10;
	
	// Determine how many pages there are...
	if (isset($_GET['p']) && is_numeric($_GET['p'])) 
	{ 	
		// Already been determined.
		$pages = $_GET['p'];
	} 
	else 
	{ 	
		// Need to determine.
		
		// Count the number of records:
		$q = "SELECT COUNT(OrdersID) 
		FROM 
			account,
			content
			country,
			cover_format_size,
			cover_stock_finish,
			distribution_channel,
			distribution_channel_details,
			industry_sector,
			industry_sub_sector,
			insert_size,
			insert_stock,
			local_order_number,
			orders,
			orders_bridge_account,
			strategic_application
		WHERE 
			orders.AccountID = orders_bridge_account.AccountID
			AND orders_bridge_account.OrdersID = orders.OrdersID
			AND orders_bridge_account.AccountID = account.AccountID
			AND orders.ContentID = content.ContentID
			AND orders.CountryID = country.CountryID
			AND orders.CoverFormatSizeID = cover_format_size.CoverFormatSizeID
			AND orders.CoverStockFinishID = cover_stock_finish.CoverStockFinishID
			AND orders.DistributionChannelID = distribution_channel.DistributionChannelID
			AND orders.DistributionChannelDetailsID = distribution_channel_details.DistributionChannelDetailsID
			AND orders.IndustrySectorID = industry_sector.IndustrySectorID
			AND orders.IndustrySubSectorID = industry_sub_sector.IndustrySubSectorID
			AND orders.InsertSizeID = insert_size.InsertSizeID
			AND orders.InsertStockID = insert_stock.InsertStockID
			AND orders.LocalOrderNumberID = local_order_number.LocalOrderNumberID
			AND orders.StrategicApplicationID = strategic_application.StrategicApplicationID
			$conditions";
		$r = @mysqli_query ($dbc, $q);
		$row = @mysqli_fetch_array ($r, MYSQLI_NUM);
		$records = $row[0];
		// Calculate the number of pages...
		if ($records > $display) 
		{ // More than 1 page.
			$pages = ceil ($records/$display);
		} 
		else 
		{
			$pages = 1;
		}
	} // End of p IF.
	
	// Determine where in the database to start returning results...
	if (isset($_GET['s']) && is_numeric($_GET['s'])) 
	{
		$start = $_GET['s'];
	} 
	else 
	{
		$start = 0;
	}
	
	// Determine the sort...
	// Default is by OrdersDate.
	$sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'OrdersDate';

	// Determine the sorting order:
	
	switch ($sort) {
		case 'OrdersDate':
			$order_by = 'OrdersDate ASC';
			break;
		case 'LocalOrderNumberValue':
			$order_by = 'LocalOrderNumberValue ASC';
			break;
		case 'OrdersName':
			$order_by = 'OrdersName ASC';
			break;
		case 'OrdersQuantity':
			$order_by = 'OrdersQuantity ASC';
			break;
		case 'InsertSizeValue':
			$order_by = 'InsertSizeValue ASC';
			break;
		case 'InsertStockValue':
			$order_by = 'InsertStockValue ASC';
			break;
		case 'CoverStockFinishValue':
			$order_by = 'CoverStockFinishValue ASC';
			break;
		case 'CoverFormatSizeValue':
			$order_by = 'CoverFormatSizeValue ASC';
			break;	
		case 'Content':
			$order_by = 'Content ASC';
			break;
		case 'StrategicApplicationValue':
			$order_by = 'StrategicApplicationValue ASC';
			break;
		case 'DistributionChannelValue':
			$order_by = 'DistributionChannelValue ASC';
			break;
		case 'DistributionChannelDetailsValue':
			$order_by = 'DistributionChannelDetailsValue ASC';
			break;
		case 'Account':
			$order_by = 'AccountValue ASC';
			break;	
		case 'Country':
			$order_by = 'Country ASC';
			break;	
		case 'IndustrySectorValue':
			$order_by = 'IndustrySectorValue ASC';
			break;
		case 'IndustrySubSectorValue':
			$order_by = 'IndustrySubSectorValue ASC';
			break;
		default:
			$order_by = 'OrdersDate ASC';
			$sort = 'OrdersDate';
			break;
	}
		
	// Make the query:
	$q = "
		SELECT 
			DATE_FORMAT(OrdersDate, '%M %d, %Y'), 
			LocalOrderNumberValue, 
			OrdersName, 
			OrdersQuantity, 
			InsertSizeValue, 
			InsertStockValue, 
			CoverStockFinishValue, 
			CoverFormatSizeValue, 
			Content, 
			StrategicApplicationValue, 
			DistributionChannelValue, 
			DistributionChannelDetailsValue, 
			AccountValue, 
			Country, 
			IndustrySectorValue, 
			IndustrySubSectorValue
		FROM 
			account,
			content
			country,
			cover_format_size,
			cover_stock_finish,
			distribution_channel,
			distribution_channel_details,
			industry_sector,
			industry_sub_sector,
			insert_size,
			insert_stock,
			local_order_number,
			orders,
			orders_bridge_account,
			strategic_application
		WHERE 
			orders.AccountID = orders_bridge_account.AccountID
			AND orders_bridge_account.OrdersID = orders.OrdersID
			AND orders_bridge_account.AccountID = account.AccountID
			AND orders.ContentID = content.ContentID
			AND orders.CountryID = country.CountryID
			AND orders.CoverFormatSizeID = cover_format_size.CoverFormatSizeID
			AND orders.CoverStockFinishID = cover_stock_finish.CoverStockFinishID
			AND orders.DistributionChannelID = distribution_channel.DistributionChannelID
			AND orders.DistributionChannelDetailsID = distribution_channel_details.DistributionChannelDetailsID
			AND orders.IndustrySectorID = industry_sector.IndustrySectorID
			AND orders.IndustrySubSectorID = industry_sub_sector.IndustrySubSectorID
			AND orders.InsertSizeID = insert_size.InsertSizeID
			AND orders.InsertStockID = insert_stock.InsertStockID
			AND orders.LocalOrderNumberID = local_order_number.LocalOrderNumberID
			AND orders.StrategicApplicationID = strategic_application.StrategicApplicationID
		ORDER BY 
			$order_by 
		LIMIT 
			$start, $display";		
	$r = @mysqli_query ($dbc, $q); // Run the query.

	// RESULTS DISPLAYED IN TABLE
	
	//Table markup
	echo '<table id="displayresults">';
	  
		// Table header 
			echo '<thead>  
				<tr>  
					<th scope="col" id="...">Date</th>
					<th scope="col" id="...">Local Order Number</th>
					<th scope="col" id="...">Opportunity</th>';
					//<th scope="col" id="...">Company</th>
					echo '<th scope="col" id="...">Country</th>
					<th scope="col" id="...">Industry Sector</th>
					<th scope="col" id="...">Industry Sub Sector</th>
					<th scope="col" id="...">Quantity</th>
					<th scope="col" id="...">Insert Size</th>
					<th scope="col" id="...">Insert Stock</th>
					<th scope="col" id="...">Cover Format & Size</th>
					<th scope="col" id="...">Cover Stock & Finish</th>';
					//<th scope="col" id="...">Content</th>
					echo '<th scope="col" id="...">Strategic Application</th>
					<th scope="col" id="...">Distribution Channel</th>';
					//<th scope="col" id="...">Distribution Channel Details</th>
				echo '</tr>  
			</thead>';  
	  
		//Table footer   
			echo '<tfoot>  
				<tr>  
					  <td colspan="14">...</td>  
				</tr>  
			</tfoot>';
	  
		//Table body
			echo '<tbody>'; 

	// Fetch and print all the records....
	$bg = 'odd'; 
	while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) 
	{
		$bg = ($bg=='odd' ? 'even' : 'odd');
		//you can take out table. for all the values below...
		echo '
			<tr class="' . $bg . '">
				<td class="date" width="100">' . $row['orders.OrdersDate'] . '</td>
				<td class="local_order_number">' . $row['LocalOrderNumber.LocalOrderNumberValue'] . '</td>
				<td class="opportunity">' . $row['orders.OrderName'] . '</td>
				<td class="quantity">' . $row['orders.OrderQuantity'] . '</td>
				<td class="insert_size">' . $row['insert_size.InsertSizeValue'] . '</td>
				<td class="insert_stock">' . $row['insert_stock.InsertStockValue'] . '</td>
				<td class="cover_format_size">' . $row['cover_format_size.CoverFormatSizeValue'] . '</td>
				<td class="cover_stock_finish"><a href="edit_user.php?id=' . $row['cover_stock_finish.CoverStockFinishValue'] . '">Edit</a></td>
				<!--<td class="content"><a href="delete_user.php?id=' . $row['content.ContentValue'] . '">Delete</a></td>-->
				<td class="strategic_application">' . $row['strategic_application.StrategicApplicationValue'] . '</td>
				<td class="distribution_channel">' . $row['distribution_channel.DistributionChannelValue'] . '</td>
				<!--<td class="distribution_channel_details">' . $row['distribution_channel_details.DistributionChannelDetailsValue'] . '</td>-->  
				<!--<td class="account">' . $row['account.AccountValue'] . '</td>-->
				<td class="country">' . $row['country.CountryValue'] . '</td>
				<td class="industry_sector">' . $row['industry_sector.IndustrySectorValue'] . '</td>
				<td class="industry_sub_sector">' . $row['industry_sub_sector.IndustrySubSectorValue'] . '</td>				
			</tr>';
	} // End of WHILE loop.
	 
	echo '</tbody></table>';
	mysqli_free_result ($r);
	mysqli_close($dbc);

	// Make the links to other pages, if necessary.
	if ($pages > 1) 
	{
		echo '<br /><p>';
		$current_page = ($start/$display) + 1;
		
		// If it's not the first page, make a Previous button:
		if ($current_page != 1) {
			echo '<a href="searchresults.php?s=' . ($start - $display) . '&p=' . $pages . '&sort=' . $sort . '">Previous</a> ';
		}
		
		// Make all the numbered pages:
		for ($i = 1; $i <= $pages; $i++) 
		{
			if ($i != $current_page) 
			{
				echo '<a href="searchresults.php?s=' . (($display * ($i - 1))) . '&p=' . $pages . '&sort=' . $sort . '">' . $i . '</a> ';
			} 
			else 
			{
				echo $i . ' ';
			}
		} // End of FOR loop.
		
		// If it's not the last page, make a Next button:
		if ($current_page != $pages) 
		{
			echo '<a href="searchresults.php?s=' . ($start + $display) . '&p=' . $pages . '&sort=' . $sort . '">Next</a>';
		}
		
		echo '</p>'; // Close the paragraph.
		
	} // End of links section.
?>

</div>
<?php include('inc/footer.inc'); ?>

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:h3rm1t9536
Comment Utility
Ray and kshna you two were right - the sql query isn't correct :( Do you have any suggestions as to what would be the correct select query?

I hope so!
Chelsea
0
 
LVL 7

Expert Comment

by:kshna
Comment Utility
it would be much easier to correct the query if you paste the error you get when you execute the query... can you try running the query and paste the error output...
0
 

Author Comment

by:h3rm1t9536
Comment Utility
The query:

SELECT 
			DATE_FORMAT(OrdersDate, '%M %d, %Y'), 
			LocalOrderNumberValue, 
			OrdersName, 
			OrdersQuantity, 
			InsertSizeValue, 
			InsertStockValue, 
			CoverStockFinishValue, 
			CoverFormatSizeValue, 
			ContentValue, 
			StrategicApplicationValue, 
			DistributionChannelValue, 
			DistributionChannelDetailsValue, 
			AccountValue, 
			CountryValue, 
			IndustrySectorValue, 
			IndustrySubSectorValue
		FROM 
			account,
			content
			country,
			cover_format_size,
			cover_stock_finish,
			distribution_channel,
			distribution_channel_details,
			industry_sector,
			industry_sub_sector,
			insert_size,
			insert_stock,
			local_order_number,
			orders,
			orders_bridge_account,
			strategic_application
		WHERE 
			orders_bridge_account.OrdersID = orders.OrderID
			AND orders_bridge_account.AccountID = account.AccountID
			AND orders.ContentID = content.ContentID
			AND orders.CountryID = country.CountryID
			AND orders.CoverFormatSizeID = cover_format_size.CoverFormatSizeID
			AND orders.CoverStockFinishID = cover_stock_finish.CoverStockFinishID
			AND orders.DistributionChannelID = distribution_channel.DistributionChannelID
			AND orders.DistributionChannelDetailsID = distribution_channel_details.DistributionChannelDetailsID
			AND orders.IndustrySectorID = industry_sector.IndustrySectorID
			AND orders.IndustrySubSectorID = industry_sub_sector.IndustrySubSectorID
			AND orders.InsertSizeID = insert_size.InsertSizeID
			AND orders.InsertStockID = insert_stock.InsertStockID
			AND orders.LocalOrderNumberID = local_order_number.LocalOrderNumberID
			AND orders.StrategicApplicationID = strategic_application.StrategicApplicationID

Open in new window

0
 

Author Comment

by:h3rm1t9536
Comment Utility
And the error: #1054 - Unknown column 'CountryValue' in 'field list'

However in the table country there are two columns CountryID and CountryValue. I made a reference with the following line AND orders.CountryID = country.CountryID

is this the correct way to join multiple table with foreign keys in a select query?
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

If you've heard about htaccess and it sounds like it does what you want, but you're not sure how it works... well, you're in the right place. Read on. Some Basics #1. It's a file and its filename is .htaccess (yes, with a dot in the front). #…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now