Solved

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

Posted on 2011-09-25
7
467 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 110

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
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 …

736 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