Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-09-25
7
Medium Priority
?
479 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 1000 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 111

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
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 …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

618 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