Solved

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

Posted on 2011-09-25
7
457 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
ID: 36595119
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
ID: 36595182
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
ID: 36595208
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:h3rm1t9536
ID: 36595216
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
ID: 36595267
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
ID: 36595484
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
ID: 36595490
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to boost certain documents at query time in Solr. Query time boosting can be a powerful resource for finding the most relevant and "best" content. Of course the more information you index, the more fields you will be able to use for y…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

914 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

16 Experts available now in Live!

Get 1:1 Help Now