retrieving multiple tables from mysql database

[sjeggels]

need tutorial how to display multiple tables from database. i have table (price) that holds various pricing data related to table (acc_establishments). how do i link tables together to display data (price) that corresponds to (acc_establishments). need a simple example how to do this. thanks

[rajabhaskar525]

use joins in mysql. it will help you

[vaultdweller123]

you need to join two tables together
assuming you had these 2 table
ill make this example simple

/* tables */
price
price_id - int(autoincrement)
prod_price - double

acc_establishments
prod_id - int(autoincrement)
prod_name - varchar
price_id - int

/* query */
select ae.prod_name, p.prod_price from acc_establishments as ae join price as p where ae.price_id = p.price_id order by ae.prod_name

[diafol]

You should post your code here so that contributors can make solutions specific to your needs. However, good advice so far - use joins. Your example probably requires the INNER JOIN.

$recordset = mysql_query(" SELECT t1.field4, t2.field7 FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.id = t2.foreign_id WHERE t1.id = 'whatever')";

AS long as fieldnames in the list (field4 and field7) have unique names, they can be extracted from the recordset by their simple names:

$d = mysql_fetch_array($recordset);
$f4 = $d['field4'];

However, if you have duplicate fieldnames from more than one table - apply an alias via AS keyword, e.g.

$recordset = mysql_query("SELECT t1.field4 AS t1f4, t2.field4 AS t2f4 FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.id = t2.foreign_id WHERE t1.id = 'whatever'");
$d = mysql_fetch_array($recordset);
$t1f4 = $d['t1f4'];

[vaultdweller123]

inner join is the same as join

[sjeggels]

Thanks to those that responded to my last post: retrieving multiple mysql tables from a database.

However, I still cannot get the correct accommodation price to correspond to its associated accommodation establishment. As you know I am developing a website (CMS) to list accommodation establishments (acc descriptions, price charged, town location, etc.).

I am utilising Dreamweaver CS3 (recordsets) to develop this CMS and have successfully put together the admin and public (webpages) sections of the CMS.

The admin section consists of webpages (forms with php scripting) e.g. addaccommodation (add new establishment), listaccommodation (update), editaccommodation (update and delete) categories (tourism route, pricerange, accommodation estblishment). This section I have completed successfully and have no problems here.

In the public section of the CMS I have a web page: accommodation.php where I have successfully retrieved records from the database (acc_profiles) for accommodationname, contact, address, phone, fax, email. All this data is formated with CSS (DIVS, Text, Links, etc.) in accommodation.php.

My problem I have:
I want to retrieve the price as it corresponds to each accommodation establishment along with the other data from the database e.g. accommodationname, contact, phone, etc. in web page:accommodation.php

I also want to display records according to the towns where these accommodation establishments are located.

I need help with the retrieval of price to correspond correctly to its associated accommodation establishment in the datebase and to display (sort/filter) accommodation establishments to their locations (towns) and tourismroute in my webpage: accommodation.php.

Herewith the mysql code I am using:

CREATE TABLE `acc_info` (
`acc_ID` int(10) unsigned NOT NULL auto_increment,
`accommodationname` varchar(100) NOT NULL default '',
`contactperson` varchar(100) NOT NULL default '',
`address1` varchar(60) NOT NULL default '',
`address2` varchar(60) default NULL,
`towncity` varchar(40) NOT NULL default '',
`postcode` varchar(20) NOT NULL default '',
`phone` varchar(20) default NULL,
`fax` varchar(20) default NULL,
`email` varchar(100) default NULL,
`photo` varchar(30) default NULL,
`datejoined` date NOT NULL default '0000-00-00',
`category` int(10) unsigned NOT NULL default '0',
`tourismroute` int(10) unsigned default NULL,
`facilities` int(10) unsigned default NULL,
`pricerange` int(10) unsigned default NULL,
`profile` text,
`active` enum('Y','N') NOT NULL default 'Y',
PRIMARY KEY (`acc_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=30 ;

CREATE TABLE `pricerange` (
`price_ID` int(10) unsigned NOT NULL default '0',
`pricerange` varchar(100) NOT NULL default '',
PRIMARY KEY (`price_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `pricerange`
--

INSERT INTO `pricerange` VALUES (1, 'R0 - R350');
INSERT INTO `pricerange` VALUES (2, 'R350 - R600');
INSERT INTO `pricerange` VALUES (3, 'R600 - R1000');
INSERT INTO `pricerange` VALUES (5, 'R1000 - R2000');

CREATE TABLE `tourismroute` (
`route_ID` int(10) unsigned NOT NULL auto_increment,
`route_name` varchar(60) NOT NULL default '',
PRIMARY KEY (`route_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

-- Dumping data for table `tourismroute`
--

INSERT INTO `tourismroute` VALUES (1, 'Kouga Tourism Route');
INSERT INTO `tourismroute` VALUES (3, 'Tsitsikamma Tourism Route');

Any help in this regard will be appreciated.

Thanks

[diafol]

You don't need a join with your setup. Just dropdown like so:

<form ...>
<select id="range" name="range">
  <option value="1">R0 - R350</option>
  <option value="2">R350 - R600</option>
  <option value="3">R600 - R1000</option>
  <option value="5">R1000 - R2000</option>
</select>
</form>

When the form is sent - get the value of the select widget:

$range = mysql_real_escape_string($_POST['range']); //perhaps escaping not necessary
$r = mysql_query("SELECT * FROM acc_info WHERE pricerange='$range'");
(etc)

If you need the route as well, that's where you'd use the JOIN syntax - although I'd use LEFT JOIN as opposed to INNER JOIN so that all establishments are returned, regardless of whether they had a route or not.

[vaultdweller123]

your priceID should be set to primary key autoincrement. and you should add additional field, you can name it to acc_id, a foreign key which stores the acc_id of table acc_info. then you can query it to something like this

select p.price, a.accomodationname, a.contactperson ...... etc from acc_info as a join price as p where a.accID = b.priceID...

[vaultdweller123]

sorry i made typo error... here it is again

select p.pricerange, a.accomodationname, a.contactperson ...... etc. from acc_info as a join price as p where a.acc_ID = b.acc_ID

if you had trouble understanding the query, read it here
http://w3schools.com/sql/sql_join.asp

[vaultdweller123]

damn.... typo error again.. hehe

select p.pricerange, a.accomodationname, a.contactperson ...... etc. from acc_info as a join price as p where a.acc_ID = p.acc_ID