Solved

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

Posted on 2011-09-25
7
464 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 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
java mysql insert application 14 41
Concat multiple records into one line 3 40
Increase counter and attr inside a while loop 15 34
PHP SMTP authentication 6 23
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…
This article discusses how to create an extensible mechanism for linked drop downs.
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…
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.

685 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