hance2105 Posted June 30, 2013 Share Posted June 30, 2013 (edited) i have 2 tables tblproduct and tblretprod. product details are stored in tblproduct and product price is stored in tblretprod. i have below code and need to display the prod price of every product being displayed randomly. <?php //Create the connection and select the DB include('db_connect.php'); // Select records from the DB $query = "SELECT * FROM tblproduct ORDER BY Rand() LIMIT 6"; $result = mysql_query($query); // Display records from the table ?> <?php while ($row = mysql_fetch_array($result)): ?> <div class="prod_box"> <div class="top_prod_box"></div> <div class="center_prod_box"> <div class="product_title"><a href="#"><?= $row['prod_name']; ?></a></div> <div class="product_img"><a href=""><?= '<img height="100" width="100" src="Images/Products/'.$row['prod_photo'].'"/>'; ?></a></div> <div class="prod_price"><span class="price">Rs price to be displayed here </span> <!--<span class="reduce">%promo%</span>--></div> </div> <div class="bottom_prod_box"></div> <div class="prod_details_tab"> <a href="#" title="header=[Add to fav] body=[ ] fade=[on]"><img src="Images/add_to_fav.png" alt="" title="" border="0" class="left_bt" /></a> <a href="#" title="header=[Compare] body=[ ] fade=[on]"><img src="Images/compare-icon.png" alt="" title="" border="0" class="left_bt" /></a> <a href="#" class="prod_details">details</a> </div> </div> <?php endwhile; ?> Edited June 30, 2013 by hance2105 Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/ Share on other sites More sharing options...
Barand Posted June 30, 2013 Share Posted June 30, 2013 Use a table JOIN SELECT p.prod_name, p.prod_photo, pr.prod_price FROM tblproduct p INNER JOIN tblretprod pr ON p.prod_id = pr.prod_id ORDER BY RAND() LIMIT 6 Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1438732 Share on other sites More sharing options...
hance2105 Posted July 1, 2013 Author Share Posted July 1, 2013 thank you my friend i created and update price page with below codes but am having error below Notice: Undefined index: pr.prod_price in C:\wamp\www\buysmart_site\upd_prod.php on line 61 <!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> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Update product</title> <SCRIPT language=JavaScript> function reload(form) { var val=form.prod_id.options[form.prod_id.options.selectedIndex].value; //var description1=form.description.value; self.location='upd_prod.php?prod_id=' + val; //+'&description=' +description1; } </script> <link href="CSS/update.css" rel="stylesheet" type="text/css" /> </head> <body> <?php include('db_connect.php'); @$prod_name=$_GET['p.prod_name']; $query=mysql_query("select p.prod_name, pr.prod_price from tblproduct p inner join tblretprod pr on p.prod_id = pr.prod_id "); //$query1=mysql_query("select prod_price tblretprod where prod_id='$prod_id'"); $row=mysql_fetch_array($query); ?> <div id="stylized" class="myform"> <form id="form" name="upd_prod" method="post" action="updprod.php"> <h2 align="center"><b>- Update Product -</b></h2> <table width="1000" border="0"> <tr> <td><div align="right">Product Name</div></td> <td> <?PHP echo "<select name='p.prod_name' onchange=\"reload(this.form)\"><option value=''>select one</option>"; while($row1 = mysql_fetch_array ($query)) { if($row1['0']==@$prod_name){echo "<option selected value='".$row1['0']."'>".$row1['0']."</option>";} else{echo "<option value=\"".$row1['0']."\">".$row1['0']."</option>";} } echo "</select>"; ?> </td> </tr> <tr> <td><div align="right">Product Price (MRU)</div></td> <td><input type="text" name="prod_price" id="prod_price" value = "<?PHP print $row['pr.prod_price'] ?>"/></td> </tr> </table> <p align="center"> <input type="submit" class="button" name="update" id="update" value="<-- Update product -->" /> </p> </form> </div> </body> </html> line where error is occurring is <td><input type="text" name="prod_price" id="prod_price" value = "<?PHP print $row['pr.prod_price'] ?>"/></td> Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1438757 Share on other sites More sharing options...
Zane Posted July 1, 2013 Share Posted July 1, 2013 It should just be $row['prod_price'], tablenames are not needed when accessing the result of fetch_array() Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1438758 Share on other sites More sharing options...
hance2105 Posted July 1, 2013 Author Share Posted July 1, 2013 well that code is not selecting the first product name and on clicking on the product the price attached to it is not being displayed for editing btw thnx for the previous help Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1438759 Share on other sites More sharing options...
hance2105 Posted July 1, 2013 Author Share Posted July 1, 2013 the first product name in my table is missing and only the first price is being displayed in the text field Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1438791 Share on other sites More sharing options...
jcbones Posted July 2, 2013 Share Posted July 2, 2013 Remove this line: $row=mysql_fetch_array($query); There is no call to that line, and all it is doing is moving the internal pointer to the second row. So subsequent calls to mysql_fetch_array() will continue from the second row. Thereby making you think that the first row has been skipped. Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1438910 Share on other sites More sharing options...
hance2105 Posted July 2, 2013 Author Share Posted July 2, 2013 thanx for the reply but if i remove that line i will still have an error of undefined variable at this line <td><input type="text" name="prod_price" id="prod_price" value = "<?PHP print $row['pr.prod_price'] ?>"/></td> how i solve this one? Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1438946 Share on other sites More sharing options...
hance2105 Posted July 2, 2013 Author Share Posted July 2, 2013 i did the required amendment but now on selecting the product, its respective price is not being displayed Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1438952 Share on other sites More sharing options...
Barand Posted July 2, 2013 Share Posted July 2, 2013 It should just be $row['prod_price'], tablenames are not needed when accessing the result of fetch_array() Read the replies Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1439007 Share on other sites More sharing options...
hance2105 Posted July 2, 2013 Author Share Posted July 2, 2013 i removed that but still having the error message below Undefined variable: row in C:\wamp\www\buysmart_site\upd_prod.php on line 61 the price is not being displayed once the product name in the drop down is selected Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1439008 Share on other sites More sharing options...
Barand Posted July 2, 2013 Share Posted July 2, 2013 Is it because you are using $row1 in the loop? Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1439013 Share on other sites More sharing options...
hance2105 Posted July 2, 2013 Author Share Posted July 2, 2013 possibly but even if i use $row, its the same issue Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1439023 Share on other sites More sharing options...
trq Posted July 2, 2013 Share Posted July 2, 2013 possibly but even if i use $row, its the same issue I would suggest you have a better look. Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1439024 Share on other sites More sharing options...
hance2105 Posted July 2, 2013 Author Share Posted July 2, 2013 Have a better look where please? Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1439028 Share on other sites More sharing options...
hance2105 Posted July 2, 2013 Author Share Posted July 2, 2013 I tried everything so far but still this is a pain. I looked at the code over and over again but still can't know why the error message is being displayed and the price is not being populated. If it gets populated, i can proceed with price updates of the product being selected. Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1439041 Share on other sites More sharing options...
hance2105 Posted July 2, 2013 Author Share Posted July 2, 2013 any help is welcome to let me know where am erring. i looked at the code several times but still dnt know where the issue is...if i sort this out i can carry on with other works...this will unlock mostly everything in my project Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1439089 Share on other sites More sharing options...
Barand Posted July 2, 2013 Share Posted July 2, 2013 It doesn't matter if you use $row, $row1 or $slartybartfast. Just be consistent. You can't use use $row for some fields and $row1 for others in the same while loop. Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1439125 Share on other sites More sharing options...
hance2105 Posted July 3, 2013 Author Share Posted July 3, 2013 Yes i know that. I did the necessary changes and used $row1 for the prod_price. Still nothing is displayed upon selecting the product name. Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1439193 Share on other sites More sharing options...
trq Posted July 3, 2013 Share Posted July 3, 2013 Post your current code. Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1439195 Share on other sites More sharing options...
hance2105 Posted July 3, 2013 Author Share Posted July 3, 2013 <!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><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>Update product</title> <SCRIPT language=JavaScript>function reload(form){var val=form.prod_name.options[form.prod_name.options.selectedIndex].value;//var description1=form.description.value;self.location='upd_prod.php?prod_name=' + val;//+'&description=' +description1;} </script> <link href="CSS/update.css" rel="stylesheet" type="text/css" /></head> <body> <?php include('db_connect.php'); @$prod_name=$_GET['prod_name']; $query=mysql_query("select p.prod_name, pr.prod_price from tblproduct p INNER JOIN tblretprod pr ON p.prod_id = pr.prod_id"); //$query1=mysql_query("select prod_price from tblretprod where prod_id='$prod_id'"); //$row=mysql_num_rows($query); ?> <div id="stylized" class="myform"> <form id="form" name="upd_prod" method="post" action="updprod.php"> <h2 align="center"><b>- Update Product -</b></h2> <table width="1000" border="0"> <tr> <td><div align="right">Product Name</div></td> <td> <?PHP echo "<select name='prod_name' onchange=\"reload(this.form)\"><option value=''>select one</option>";while($row1 = mysql_fetch_array ($query)) { if($row1['0']==@$prod_name){echo "<option selected value='".$row1['0']."'>".$row1['0']."</option>";}else{echo "<option value=\"".$row1['0']."\">".$row1['0']."</option>";}}echo "</select>";?> </td> </tr> <tr> <td><div align="right">Product Price (MRU)</div></td> <td><input type="text" name="prod_price" id="prod_price" value = "<?PHP print $row1['prod_price'] ?>"/></td> </tr></table> <p align="center"> <input type="submit" class="button" name="update" id="update" value="<-- Update product -->" /></p> </form> </div> </body></html> Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1439198 Share on other sites More sharing options...
thara Posted July 3, 2013 Share Posted July 3, 2013 You are using $row1['prod_price'] from out of while loop Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1439222 Share on other sites More sharing options...
hance2105 Posted July 3, 2013 Author Share Posted July 3, 2013 how can i amend the codes to have the required display please? Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1439223 Share on other sites More sharing options...
Solution hance2105 Posted July 3, 2013 Author Solution Share Posted July 3, 2013 hello guys, i tried below code as well but this time the drop down is not being displayed and am having an undefined variable for selected_product it would be of great help if i knew where the issue is and how to sort it out...either with the code below or the previous one i provided thank you....am stuck with that since some days now and would like to sort this out please <!-- you beginning HTML --> <?php include('db_connect.php'); $prod_name = isset($_GET['prod_name'])?$_GET['prod_name']:""; $sql = "select p.prod_name, pr.prod_price from tblproduct p INNER JOIN tblretprod pr ON p.prod_id = pr.prod_id"; $result = mysql_query($sql); if(mysql_num_rows($result) < 1) {} //No records, do something (exit, die, return, whatever) ?> <!-- Continue your HTML --> <?php while($row = mysql_fetch_array($result){ if($row['prod_name'] == $prod_name) { $selected_product = $row; echo "<option selected value='".$row['0']."'>".$row['0']." </option>"; } else { echo "<option value=\"".$row['0']."\">".$row['0']."</option>"; } } ?> <!-- Continue your HTML --> <td><input type="text" name="prod_price" id="prod_price" value = "<?php print $selected_product['prod_price'] ?>"/></td> <!-- Rest of HTML --> Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1439251 Share on other sites More sharing options...
hance2105 Posted July 3, 2013 Author Share Posted July 3, 2013 well guys i managed to sort out the issue with code above. the <td> thing was not properly formatted. now working great. thanks to everyone who helped... Quote Link to comment https://forums.phpfreaks.com/topic/279728-how-to-select-from-2-tables-and-display-data/#findComment-1439301 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.