help with foreach loop that builds a recordset access database

**Dormilich** · Mar 12 '11, 06:31 AM

your database code definitely won’t work.

- foreach(): I don’t know either what you want there
- line #20: inserts the string "Array". not sure what that’s supposed to do.
- mysql_connect() : a) wrong function (outdated), b) wrong parameter set, please refer to the manual.

**Magda Balise** · Mar 12 '11, 02:56 PM

thank you for your reply
what I need is if you go on the amenities page and check breakfast, pickup, tv and child
The ampage will bring up the inns that have those amenities
so the harrison house, trumpeter inn and argyle house return as a result
does this make sense

**Magda Balise** · Mar 12 '11, 03:52 PM

web site is at

404 Not Found

http://mossgraphics.net/TestSite/amenities/amenities.php

ampage is not up yet
I am trying to create it with the mySQL
so all the information will show up on the page

**Dormilich** · Mar 13 '11, 09:40 AM

what you need for your query is a WHERE clause, e.g.

Code:

// find all amenities with internet
$select_query = "SELECT `am_id` FROM `amenities` WHERE `internet` = 1";

additionally, I’d use the ENUM type for those fields (either they have it or they don’t) and you can move all of the descriptive fields (fax, mail, phone, address) to a new table.

**Magda Balise** · Mar 15 '11, 12:22 AM

Thank you soooooo much I will get on it right away. This weekend I was in a remote area but I downloaded the php manual and read some of it. Thanks again for all the help.

**Magda Balise** · Mar 15 '11, 11:24 PM

oh boy I thought I was thinking like you but I got lost again.
For the good news I did separate the tables and was able to populate the fields of name, fax, mail, phone, address. I am lost with the amenities again, I created a new table, not sure which one to use now. I feel like an idiot. I am an artist and this does not compute. My right brain is getting a workout.

FOR THE GOOD NEWS

Code:

<?php 
//Connect
$link = mysql_connect('localhost', '***', '***');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
$db_selected = mysql_select_db('***', $link);
if (!$db_selected) {
    die('Could not select database: ' . mysql_error());
}
$query = 'SELECT * FROM inns';
$result = mysql_query($query);
if (!$result) {
    die('Query failed: ' . mysql_error());
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>checkboxes</title>
<link href="scripts/main.css" rel="stylesheet" type="text/css" />
<style type="text/css"></style>
</head>

<body>
<div id="mainArticle">
	<div id="mainArticleInn3">
	<?php
		//populates information fields
		for ($i = 0; $i <= mysql_num_rows($result) -1;  $i++) {
   		 if (!mysql_data_seek($result, $i)) {
       	 	echo "Cannot seek to row $i: " . mysql_error() . "\n";
       	 	continue;
   		 	}

    		if (!($row = mysql_fetch_assoc($result))) {
       			 continue;
   			 }

   
		}

		mysql_free_result($result);

?>
				<div id="inn3a"><a href = "<?php echo $row['url']; ?>" target="_blank"><img src="innpg/<?php echo $row['image1']; ?>" width="200" height="150" /></a></div>
				<div class="lightText" id="innText3"> 
					<p class="innTitleLight"><a href="<?php echo $row['url']; ?>" target = "_blank"> <?php echo $row['bbName']; ?></a></p>
					<p class="innPhoneLight"><?php echo $row['phone']; ?></p>
					<p class="innPhoneLight"><?php echo $row['tollFree']; ?></p>
					<p class="innPhoneLight"><?php echo $row['fax']; ?></p>
					<p class="innWebLight"><a href = "<?php echo $row['url']; ?>" target="_blank"><?php echo $row['web'] ?></a></p>
					<p class="innWebLight"><a href="<?php echo $row['emailL']; ?>" target="_blank"><?php echo $row['email'] ?></a></p>
<div id="dateMap3"><a href="http://bytes.com/topic/php/calendar/calendar.html"><img src="logoMB /checkDateW.gif" alt="Check Date" width="60" height="40" align="left" /></a><a href="<?php echo $row['map']; ?>"><img src="logoMB /showOnMapW.gif" alt="Show on Map" width="60" height="40" align="right" /></a></div>
				</div>
				<div id="inn3b"><a href="<?php echo $row['url']; ?>" target="_blank"><img src="innpg/<?php echo $row['image2']; ?>" width="200" height="150" /></a></div>
				<div id="innRating3"><img src="logoMB /<?php echo $row['logo1']; ?>" width="60" height="55" /><img src="logoMB /<?php echo $row['logo2']; ?>" width="60" height="55" /><img src="logoMB /<?php echo $row['logo3']; ?>" width="60" height="55" /><img src="logoMB /<?php echo $row['logo4']; ?>" width="60" height="55" /></div>
		<div class="InformationTextLight" id="innMainText3"><?php echo $row['description']; ?>
		</div>
				<p class="lightText">&nbsp;</p>
	</div>

<form id="form1" name="form1" method="post" action="amenities.php">
	<input type="submit" name="start" id="start" value="Start Over" />
</form>
</div>
</body>
</html>

HERE IS TABLE 1 where I played with ENUM and it didn't quite make sense, so I created table 2 down below. I am still working with it, but not sure which one to use

Code:

-- phpMyAdmin SQL Dump
-- version 3.2.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 15, 2011 at 04:12 PM
-- Server version: 5.1.44
-- PHP Version: 5.3.2

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `sjibb_test`
--

-- --------------------------------------------------------

--
-- Table structure for table `amenities`
--

CREATE TABLE `amenitites` (
  `am_id` int(11) NOT NULL,
  `bbName` enum('1','2','3','4','5','6','7','8','9','10','11','12') NOT NULL,
  `breakfast` varchar(4) NOT NULL,
  `bbq` varchar(4) NOT NULL,
  `kitchenette` varchar(4) NOT NULL,
  `fireplace` varchar(4) NOT NULL,
  `internet` varchar(4) NOT NULL,
  `tv` varchar(4) NOT NULL,
  `dvd` varchar(4) NOT NULL,
  `video` varchar(4) NOT NULL,
  `bath` varchar(4) NOT NULL,
  `jacuzzi` varchar(4) NOT NULL,
  `hotub` varchar(4) NOT NULL,
  `massage` varchar(4) NOT NULL,
  `handicap` varchar(4) NOT NULL,
  `child` varchar(4) NOT NULL,
  `pickup` varchar(4) NOT NULL,
  `pet` varchar(4) NOT NULL,
  PRIMARY KEY (`am_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `amenity_test`
--

INSERT INTO `amenity_test` VALUES(1, '1', 'yes', 'yes', 'no', 'no', 'yes', 'yes', 'yes', 'yes', 'yes', 'no', 'yes', 'yes', 'no', 'yes', 'yes', 'yes');
INSERT INTO `amenity_test` VALUES(2, '2', 'yes', 'no', 'no', 'no', 'yes', 'yes', 'yes', 'no', 'yes', 'yes', 'no', 'no', 'no', 'no', 'no', 'no');
INSERT INTO `amenity_test` VALUES(3, '3', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'no', 'yes', 'yes', 'yes');
INSERT INTO `amenity_test` VALUES(4, '4', 'yes', 'yes', 'no', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'no', 'no', 'no', 'no', 'yes', 'no', 'no');
INSERT INTO `amenity_test` VALUES(5, '5', 'yes', 'yes', 'no', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'no', 'no', 'no', 'no', 'no', 'yes', 'no');
INSERT INTO `amenity_test` VALUES(6, '6', 'yes', 'yes', 'no', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'no', 'no', 'no', 'no', 'no', 'no');
INSERT INTO `amenity_test` VALUES(7, '7', 'yes', 'no', 'no', 'yes', 'no', 'no', 'no', 'no', 'yes', 'no', 'no', 'no', 'no', 'no', 'no', 'no');
INSERT INTO `amenity_test` VALUES(8, '8', 'yes', 'no', 'no', 'no', 'no', 'no', 'no', 'no', 'yes', 'no', 'no', 'no', 'no', 'yes', 'no', 'no');
INSERT INTO `amenity_test` VALUES(9, '9', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'no', 'yes', 'no', 'no', 'no', 'yes', 'yes', 'yes', 'no');
INSERT INTO `amenity_test` VALUES(10, '10', 'yes', 'no', 'no', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'no', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes');
INSERT INTO `amenity_test` VALUES(11, '11', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes');
INSERT INTO `amenity_test` VALUES(12, '12', 'yes', 'no', 'no', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'no', 'no', 'yes', 'no', 'yes', 'no', 'no');

THEN I THOUGHT OF CHANGING IT TO THIS is this wrong?
I made a row of inns and the columns are the amenities. Is this what you meant?

Code:

-- phpMyAdmin SQL Dump
-- version 3.2.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 15, 2011 at 04:00 PM
-- Server version: 5.1.44
-- PHP Version: 5.3.2

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `sjibb_test`
--

-- --------------------------------------------------------

--
-- Table structure for table `amenities_test`
--

CREATE TABLE `amenities_test` (
  `am_id` int(11) NOT NULL AUTO_INCREMENT,
  `argyle` varchar(20) NOT NULL,
  `dragonfly` varchar(20) NOT NULL,
  `harrison` varchar(20) NOT NULL,
  `hillside` varchar(20) NOT NULL,
  `intothewoods` varchar(20) NOT NULL,
  `kirk` varchar(20) NOT NULL,
  `olympic` varchar(20) NOT NULL,
  `warfside` varchar(20) NOT NULL,
  `states` varchar(20) NOT NULL,
  `trumpeter` varchar(20) NOT NULL,
  `tucker` varchar(20) NOT NULL,
  `wildwood` varchar(20) NOT NULL,
  PRIMARY KEY (`am_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=54 ;

--
-- Dumping data for table `amenities`
--

INSERT INTO `amenities` VALUES(1, 'breakfast', 'breakfast', 'breakfast', 'breakfast', 'breakfast', 'breakfast', 'breakfast', 'breakfast', 'breakfast', 'breakfast', 'breakfast', 'breakfast');
INSERT INTO `amenities` VALUES(2, 'bbq', '', 'bbq', 'bbq', '', 'bbq', '', '', 'bbq', '', 'bbq', '');
INSERT INTO `amenities` VALUES(3, '', '', 'kitchenette', '', '', '', '', '', 'kitchenette', '', 'kitchenette', '');
INSERT INTO `amenities` VALUES(4, '', '', 'fireplace', 'fireplace', '', 'fireplace', 'fireplace', '', 'fireplace', 'fireplace', 'fireplace', 'fireplace');
INSERT INTO `amenities` VALUES(5, 'internet', '', '', '', '', '', '', '', '', '', '', '');
INSERT INTO `amenities` VALUES(6, 'tv', 'tv', 'tv', 'tv', '', 'tv', '', '', 'tv', 'tv', 'tv', 'tv');
INSERT INTO `amenities` VALUES(7, 'dvd', 'dvd', 'dvd', 'dvd', '', 'dvd', '', '', 'dvd', 'dvd', 'dvd', 'dvd');
INSERT INTO `amenities` VALUES(8, 'video', '', 'video', 'video', '', 'video', '', '', '', 'video', 'video', 'video');
INSERT INTO `amenities` VALUES(9, 'bath', 'bath', 'bath', 'bath', 'bath', 'bath', 'bath', 'bath', 'bath', 'bath', 'bath', 'bath');
INSERT INTO `amenities` VALUES(10, '', 'jacuzzi', 'jacuzzi', 'jacuzzi', '', 'jacuzzi', '', '', '', '', 'jacuzzi', '');
INSERT INTO `amenities` VALUES(11, 'hotub', '', 'hotub', '', '', '', '', '', '', 'hotub', 'hotub', '');
INSERT INTO `amenities` VALUES(12, 'massage', '', 'massage', '', '', '', '', '', '', 'massage', 'massage', 'massage');
INSERT INTO `amenities` VALUES(13, '', '', '', '', '', '', '', '', 'handicap', 'handicap', 'handicap', '');
INSERT INTO `amenities` VALUES(14, 'child', '', 'child', 'child', '', '', '', 'child', 'child', 'child', 'child', 'child');
INSERT INTO `amenities` VALUES(15, 'pickup', '', 'pickup', '', '', 'pickup', '', '', 'pickup', 'pickup', 'pickup', '');
INSERT INTO `amenities` VALUES(16, 'pet', '', 'pet', '', '', '', '', '', '', 'pet', 'pet', '');

**Dormilich** · Mar 16 '11, 07:02 AM

you should use neither of these dumps (as they are now). it may be a typo, but you create `amenities` and insert into `amenity_test` (and vice versa) … and in your PHP file, you query `inns`

**dgreenhouse** · Mar 16 '11, 11:31 AM

NOTE: Fixed... I think

This might be a start assuming you setup the amenities table as follows:

bnb_id int - the B&B id (foreign key to bnb table's id field)
amenity varchar(length? ) - the amenity name

Here's the amenities table populated with some records:

Code:

bnb_id, amenity
   1,   "bbq"
   1,   "kitchenette"
   1,   "child"
   1,   "pickup"
   1,   "pet"
   1,   "tv"
   2,   "child"
   2,   "pet"
   2,   "tv"

Code:

<?php

$am_count = count($_POST['amenity']);

if (!$am_count) {

  $sql = "Select name FROM bnb";

} else {
  
  $in_clause = "";
  
  for ($i = 0; $i < $am_count; $i++ ) {
    $in_clause .= '"' . $_POST['amenity'][$i] . ($i < $am_count -1 ? '",' : '"');
  }

  $sql = 
    sprintf(
      "SELECT name "
     . "FROM bnb "
     .  "WHERE id IN "
     .   "(SELECT bnb_id FROM amenities "
     .     "WHERE amenity IN (%s) "
     .   "GROUP BY bnb_id "
     .   "HAVING COUNT(amenity) = %d)",
     $in_clause, $am_count
	 );
}

print $sql

/*
SELECT name FROM bnb WHERE id IN 
 (SELECT bnb_id FROM amenities 
   WHERE amenity 
    IN (
       "bbq","kitchenette","child","pickup","pet"
     ) 
     GROUP BY bnb_id 
     HAVING COUNT(amenity) = 5
   )

*** Only B&B with an id of 1 will get selected based on the amenities selected ***
*/
?>

**Magda Balise** · Mar 24 '11, 10:09 PM

Hi dgreenhouse I hope you are still out there. I have been playing with the code and it is not working I keep getting an error undefined index: amenity in/.....
I have simplified everything as much as I could
INN TABLE

Code:

-- Database: `bnbtest`
--
-- -----------------------------------------------------
--
-- Table structure for table `bnb`
--

CREATE TABLE `bnb` (
  `bnb_id` int(11) unsigned NOT NULL,
  `image1` varchar(50) NOT NULL,
  `bbName` varchar(50) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `tollFree` varchar(20) NOT NULL,
  `fax` varchar(20) NOT NULL,
  `web` varchar(50) NOT NULL,
  `url` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `emailL` varchar(50) NOT NULL,
  `map` varchar(50) NOT NULL,
  `image2` varchar(50) NOT NULL,
  `logo1` varchar(50) NOT NULL,
  `logo2` varchar(50) NOT NULL,
  `logo3` varchar(50) NOT NULL,
  `logo4` varchar(50) NOT NULL,
  `description` varchar(4096) NOT NULL,
  PRIMARY KEY (`bnb_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `bnb`
--
INSERT INTO `bnb` VALUES(1, '../image/inn/argyle.jpg', 'ARGYLE HOUSE', '360-378-4084', '800-624-3459', '', 'www.argylehouse.net', 'http://www.argylehouse.net', 'info@argylehouse.net', 'info@argylehouse.net', '../maps/Argyle.html', '../image/inn/argyle1.jpg', '../image/logo/threedoor.gif', '', '', '', 'Enjoy the charm and character of this 1910-vintage Craftsman located only two blocks from downtown and a short walk from the ferry.');
INSERT INTO `bnb` VALUES(2, '../image/dragonfly.jpg', 'DRAGONFLY INN', '360-378-4280', '877-378-4280', '360-378-1392', 'www.thedragonflyinn.com', 'http://www.thedragonflyinn.com', 'host@thedragonflyinn.com', 'host@thedragonflyinn.com', '../maps/dragonfly.html', '../image/dragonfly1.jpg', '../image/logo/aaa3D.gif', '../image/logo/threedoor.gif', '', '', 'A unique,Japanese-inspired bed and breakfast.');
INSERT INTO `bnb` VALUES(3, '../image/inn/hillside.jpg', 'HILLSIDE HOUSE', '360-378-4730', '800-232-4730', '360-378-4715 FAX', 'www.hillsidehouse.com', 'www.hillsidehouse.com', 'info@argylehouse.net', 'info@argylehouse.net', '../map/hillside.html', '../image/inn/hillside.jpg', '../image/logo/tripleA', '../image/logo/mobile', '../image/logo/3doors', '../image/logo/bellhop', 'Enjoy views of snowcapped Mt. Baker');

TABLE AMENITYTEST

Code:

-- Database: `bnbtest`
--
-- ----------------------------------------------------
--
-- Table structure for table `amenitytest`
--

CREATE TABLE `amenitytest` (
  `bnb_id` int(11) unsigned NOT NULL,
  `amenity` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `amenitytest`
--

INSERT INTO `amenitytest` VALUES(3, 'internet');
INSERT INTO `amenitytest` VALUES(2, 'fireplace');
INSERT INTO `amenitytest` VALUES(1, 'kitchenette');
INSERT INTO `amenitytest` VALUES(2, 'bbq');
INSERT INTO `amenitytest` VALUES(3, 'breakfast');

and here is the process page

Code:

<html>
<head>
<title>checkboxes</title>
</head>

<body>
<?php 
//connect
mysql_connect("localhost", "root", "root") or die(mysql_error());
mysql_select_db("bnbtest") or die(mysql_error());

//selects all columns from bnb
$query = 'SELECT * FROM bnb';

//retrieves the records
$result = mysql_query($query);
if (!$result) {
    die('Query failed: ' . mysql_error());
}

		$am_count = count($_POST['amenity']);
		
		if (!$am_count) {
			
			$sql = "SELECT * FROM bnb";
			
		} else {
			$in_clause = "";
			
			for ($i = 0; $i < $am_count; $i++) {
				
				$in_clause .= ' " ' . $_POST['amenity'][$i] . ($i < $am_count -1 ? '",' : '"');
			}
			
			$sql=
			sprintf(
			"SELECT *"
			. "FROM bnb"
			.  "WHERE id IN"
			.   "(SELECT bnb_id FROM amenitytest"
			.     "WHERE amenity IN (%s)"
			.	"GROUP by bnb_id"
			.	"HAVING COUNT (amenity) = %d)",
			$in_clause, $am_count);
		
				
// Print out the contents of the entry 
echo $row['bbName'] . "<br />";
echo $row['url'] . "<br />";
echo $row['phone'] . "<br />";
echo $row['tollFree'] . "<br />";
echo $row['fax'];
echo $row['web'] . "<br />";
echo $row['email'] . "<br />";
echo $row['description'] . "<br />". "<br />";
}
mysql_close()
?>
</body>
</html>

**Dormilich** · Mar 24 '11, 11:06 PM

you never check, whether $_POST['amenity'] actually exists (isset()).

**Magda Balise** · Mar 24 '11, 11:30 PM

I placed

Code:

if(isset($_POST['amenity']))
//connect
mysql_connect("localhost", "root", "root") or die(mysql_error());
mysql_select_db("bnbtest") or die(mysql_error());

and my error now is Warning: mysql_select_db () [function.mysql-select-db]: Access denied for user........

I know that the connection is correct because it works for all my other simple test. for instance I just finished creating this example below and all the inns that have breakfast (WHERE amenity.breakfa st ='y') come up. the table is an older one I used before with y and n.

Code:

<?php

// Make a MySQL Connection
mysql_connect("localhost", "root", "root") or die(mysql_error());
mysql_select_db("bnbtest") or die(mysql_error());


// Retrieve all the data from the "example" table

$result = mysql_query("SELECT bnb.* 
						FROM amenity, bnb 
						WHERE amenity.breakfast = 'y' 
						AND amenity.bnb_id = bnb.bnb_id
						LIMIT 0,30")
or die(mysql_error()); 


//store the record of the bnb and amenity table into $row
while($row = mysql_fetch_array($result)) {
	
// Print out the contents of the entry 
echo $row['bbName'] . "<br />";
echo $row['url'] . "<br />";
echo $row['phone'] . "<br />";
echo $row['tollFree'] . "<br />";
echo $row['fax']. "<br />";
echo $row['web'] . "<br />";
echo $row['email'] . "<br />";
echo $row['description'] . "<br />". "<br />";
}
?>

**Dormilich** · Mar 24 '11, 11:46 PM

you are aware that an if() condition without {} only works on the next line (the mysql_connect() )? thus when calling mysql_select_db () I think PHP tries to use the defaults from php.ini, which may cause that error.

one more reason to use MySQLi (or even better: PDO) instead of the outdated mysql functions.

**Magda Balise** · Mar 26 '11, 02:32 AM

thanks I did not understand the brackets,

Code:

if(isset($_POST['amenity']))
{
//connect..............
//all the same code

echo $row['description'] . "<br />". "<br />";
}
}//thanks for the bracket info

mysql_close()//now it says this is a problem

I am getting

Warning: mysql_close(): no MySQL-link resource supplied in........

**Magda Balise** · Mar 26 '11, 02:40 AM

Also I started to read about MySQLi and PDO. I am not sure but MySQLi seems simpler because it works with MySQL.
With PDO there is a warning that some MySQL table do not support transactions.
I am new at programing and I would like to go in the right direction
thanks for all your help

help with foreach loop that builds a recordset access database

help with foreach loop that builds a recordset access database

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment