php - I want to fetch data from three tables every table is linked with id's -
this question exact duplicate of:
- mysql join multiple tables 4 answers
i have 3 tables name product, brand, product_info **product table** create table if not exists `product` ( `p_id` int(11) not null auto_increment, `product` varchar(50) not null, `flag` int(11) not null, primary key (`p_id`) ) engine=innodb default charset=latin1 auto_increment=5 ; insert `product` (`p_id`, `product`, `flag`) values (1, 'atta', 0), (2, 'oil', 0), (3, 'biscut', 0), (4, 'rice', 0); brand table create table if not exists `brand` ( `b_id` int(11) not null auto_increment, `p_id` int(11) not null, `brand` varchar(50) not null, `image` varchar(255) not null, primary key (`b_id`) ) engine=innodb default charset=latin1 auto_increment=8 ; insert `brand` (`b_id`, `p_id`, `brand`, `image`) values (1, 1, 'ashirvad', 'freevector-blue-squares-vector.jpg'), (2, 1, 'phillsberry', 'ilbagnoalessi_one_02.jpg'), (3, 2, 'sunflower', '001-bi-fold-corporate-brochure-template-vol-1-2.jpg'), (4, 3, 'good day', 'but-ok-bg.gif'), (5, 3, 'sunfeast', 'but_call_bg.gif'), (6, 4, 'mosura', '2.jpg'), (7, 4, 'samba', '3.jpg'); **product_info table** create table if not exists `product_info` ( `pi_id` int(11) not null auto_increment, `pro` int(11) not null, `b_id` int(11) not null, `quantity` varchar(20) not null, `measurement` varchar(20) not null, `mrp` varchar(20) not null, `our_price` varchar(20) not null, primary key (`pi_id`) ) engine=innodb default charset=latin1 auto_increment=12 ; insert `product_info` (`pi_id`, `pro`, `b_id`, `quantity`, `measurement`, `mrp`, `our_price`) values (1, 1, 1, '1', '1kg', '50', '48'), (2, 1, 2, '1', '1kg', '60', '59'), (3, 2, 3, '1', '1ltr', '90', '86'), (4, 1, 1, '500', '500gms', '25', '23'), (5, 2, 3, '500', '500ml', '45', '43'), (6, 3, 4, '1', 'pcs', '15', '14'), (7, 3, 5, '1', 'pcs', '10', '9'), (8, 4, 6, '', '1 kg', '45', '43'), (9, 4, 6, '', '500 gm', '23', '21'), (10, 4, 7, '', '1 kg', '48', '47'), (11, 4, 7, '', '500 gm', '24', '23'); used below code list items product table according p_id $result = mysql_query("select * product"); echo '<ul class="list-1 p2">'; while($row = mysql_fetch_array( $result )) { // print out contents of each row table echo '<li>'; echo "<a href='page.1.php?pid=$row[p_id]'>"; echo '<b>'; echo $row['product']; echo '</b>'; echo "</a>"; echo '</li>'; } echo '</ul>'; mysql_close($con); **my intention display in below code** mysql_select_db("mr_bazaar",$con); $pid=$_get['pid']; $result = mysql_query("select p.product,b.brand,pi.quantity,pi.mrp,pi.our_price,pi.measurement product p inner join brand b on p.p_id=b.p_id inner join product_info pi on pi.b_id=b.b_id p.p_id=$pid"); echo '<ul class="list-1 p2">'; while($row = mysql_fetch_array( $result )) { // print out contents of each row table echo '<tr>'; echo '<td>'; echo $row['brand']; echo '</td><td>'; echo '<select name= qty>'; echo '<option value= 1>1</option> <option value= 2>2</option> <option value= 3>3</option> <option value= 4>4</option> <option value= 5>5</option> <option value= 6>6</option> <option value= 7>7</option> <option value= 8>8</option> <option value= 9>9</option> <option value= 10>10</option>'; echo '</select>'; echo '</td><td>'; echo $row['mrp']; echo '</td><td>'; echo $row['our_price']; echo '</td><td>'; echo "<select name='measurement'>"; while ($row = mysql_fetch_array($result)) { $id = $row['p_id']; $measurement = $row['measurement']; echo "<option value=\"" . $row['p_id'] . "\">" . $row['measurement'] . "</option>"; } echo "</select>"; echo '</td><td>'; echo '</td>'; } echo '</ul>'; mysql_close($con); but want display list such brand qty price our price measurement(value in dropdown list per table). when use above code displays 1 item! per image displaying ashirvad, in table phillsberry there 
your first while loop end 1 iteration because have inner while loop iterating same variable that's being used in first while loop.
so in case given in example returning 3 records, first while loop iterate 1 shows 1 record , inner while loop iterate 2 more time showing measurements.
so in case straight forward solution not use same variable iteration in inner while loop , separate query measurement brand. please check below code, made corrections show different produts/brands listing. can change logic based on product display requirement.
$pid = $_get['pid']; $result = mysql_query("select p.product,b.brand,pi.quantity,pi.mrp,pi.our_price,group_concat(pi.measurement) measurement product p inner join brand b on p.p_id=b.p_id inner join product_info pi on pi.b_id=b.b_id p.p_id=$pid group b.b_id"); echo '<ul class="list-1 p2">'; if(mysql_num_rows($result)) { // if result found echo "<table border=1 cellspacing=0>"; while($row = mysql_fetch_array( $result )) { // print out contents of each row table echo '<tr>'; echo '<td>'; echo $row['brand']; echo '</td><td>'; echo '<select name= qty>'; echo '<option value= 1>1</option> <option value= 2>2</option> <option value= 3>3</option> <option value= 4>4</option> <option value= 5>5</option> <option value= 6>6</option> <option value= 7>7</option> <option value= 8>8</option> <option value= 9>9</option> <option value= 10>10</option>'; echo '</select>'; echo '</td><td>'; echo $row['mrp']; echo '</td><td>'; echo $row['our_price']; echo '</td><td>'; $measurement = explode(',', $row['measurement']); // different measurements brand echo "<select name='measurement'>"; foreach ($measurement $measure) { echo $measure."<br/>"; echo "<option value=\"" . $row['p_id'] . "\">" . $measure . "</option>"; } echo "</select>"; echo '</td><td>'; echo '</tr>'; } echo "</table>"; } else { echo "no result found"; } echo '</ul>';
Comments
Post a Comment