opel Posted September 27, 2006 Share Posted September 27, 2006 I am working on a displaying a list of links in my admin system that I have entered into the Database. Each link is realted to a category that is in a separate table. I have written out an SQL statement that joins the 2 tables together however the variable information isn't writting out onto my page?Can anyone see my error I'm determined to learn how to hand code PHP and stay away from Dreamweavers toolkit.thanks for any help...[code]<table><tr ><th>Item Description</th><th>Category</th><th>Edit</th><th>Delete </th></tr><?phpinclude ('connections/config.php');include ('connections/dbconn.php');$query = "SELECT tbl_links.link_id, tbl_links.link_title, tbl_links.link_desc, tbl_links.category_id, tbl_links.link_display, categories.category_name FROM tbl_links INNER JOIN categories ON tbl_links.category_ID = categories.category_name ORDER BY link_title";$result = mysql_query($query) or die("error querying database");$i = 0;while($result_ar = mysql_fetch_assoc($result)){?><tr <?php if($i%2 == 1){ echo "class='tablerow2'"; }else{echo "class='tablerow1'";}?>><td><?php echo $result_ar['link_title']; ?></td><td><?php echo $result_ar['category_name']; ?></td><td class="button"><a href="edit_link_detail.php?ID=<?php echo $result_ar['link_id']; ?>" title="Edit This Link">Edit</a></td><td class="button"><a href="delete_link.php?ID=<?php echo $result_ar['link_id']; ?>" title="Delete This Link">Delete</a></td></tr><?php$i+=1; }?></table>[/code] Quote Link to comment Share on other sites More sharing options...
Barand Posted September 27, 2006 Share Posted September 27, 2006 I can see[code]</table>[/code]but not[code]<table>[/code]EDIT: Scratch that, found it at the top Quote Link to comment Share on other sites More sharing options...
opel Posted September 27, 2006 Author Share Posted September 27, 2006 Its at the very top above the PHP script, should it be within the PHP script? I didnt think it would matter as it is only the rows I wish to loop out Quote Link to comment Share on other sites More sharing options...
Barand Posted September 27, 2006 Share Posted September 27, 2006 Is your join correct?[code]...ON tbl_links.category_ID = categories.category_name[/code]only it's joining an id with a name. Quote Link to comment Share on other sites More sharing options...
wildteen88 Posted September 27, 2006 Share Posted September 27, 2006 Try this:[code]<table><tr><th>Item Description</th><th>Category</th><th>Edit</th><th>Delete</th></tr><?phpinclude 'connections/config.php';include 'connections/dbconn.php';$sql = "SELECT tl.link_id, tl.link_title, tl.link_desc, tl.category_id, tl.link_display, cats.category_name FROM tbl_links tl, categories cats WHERE tl.category_id = cats.category_name ORDER BY tl.link_title";$result = mysql_query($sql) or die("Uable perform query at this time<br />" . mysql_error());if(mysql_num_rows($result) > 1){ $i = 0; while($result_ar = mysql_fetch_assoc($result)) {?><tr <?php echo (($i%2 == 1) ? "class='tablerow2'" : "class='tablerow1'"); ?>><td><?php echo $result_ar['link_title']; ?></td><td><?php echo $result_ar['category_name']; ?></td><td class="button"><a href="edit_link_detail.php?ID=<?php echo $result_ar['link_id']; ?>" title="Edit This Link">Edit</a></td><td class="button"><a href="delete_link.php?ID=<?php echo $result_ar['link_id']; ?>" title="Delete This Link">Delete</a></td></tr><?php $i++; }}else{ echo <<<HTML<tr class="tablerow2"><td style="color: red; font-weight: bold; text-align: center;">Sorry no results found</td></tr>HTML;}?></table>[/code] Quote Link to comment Share on other sites More sharing options...
opel Posted September 27, 2006 Author Share Posted September 27, 2006 thanks for the example I changed my sql statement to match up to the my table values and got this result: "sorry no results found"here is my SQL statement :"SELECT tbl_links.link_id, tbl_links.link_title, tbl_links.link_desc, tbl_links.category_id, tbl_links.link_display, categories.category_name FROM tbl_links, categories WHERE tbl_links.category_id = categories.category_name ORDER BY tbl_links.link_title";have attached my tables SQL to and I have added further links to my DB version since then so there is definately content.[attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
Barand Posted September 27, 2006 Share Posted September 27, 2006 Read my previous post Quote Link to comment Share on other sites More sharing options...
opel Posted September 27, 2006 Author Share Posted September 27, 2006 Sorry I just saw your post just now change it to category id and some of wild teens code and it worked. here is correct SQL[code]"SELECT tbl_links.link_id, tbl_links.link_title, tbl_links.link_desc, tbl_links.category_id, tbl_links.link_display, categories.category_name FROM tbl_links, categories WHERE tbl_links.category_id = categories.category_id ORDER BY tbl_links.link_title";[/code] Quote Link to comment Share on other sites More sharing options...
wildteen88 Posted September 27, 2006 Share Posted September 27, 2006 Umm, I see you got rid of my aliases :) I did that so you dont have to type the table name out all the time. Glad you got it all working now. Quote Link to comment Share on other sites More sharing options...
opel Posted September 27, 2006 Author Share Posted September 27, 2006 I'm new to PHP but eager to learn so if you have time to explain the alias or can post a URL to explanation I would be grateful. ;D Quote Link to comment Share on other sites More sharing options...
wildteen88 Posted September 27, 2006 Share Posted September 27, 2006 Aliases isnt to do with PHP, but with SQL queries. This is where I learnt [url=http://www.w3schools.com/sql/sql_alias.asp]aliases[/url]. Aliases allow you to setup a sort of shortcut for the table name or column you're returning. Quote Link to comment Share on other sites More sharing options...
Ninjakreborn Posted September 27, 2006 Share Posted September 27, 2006 I just happened across this post, I was going to try and help, but I ended up learning something, thanks for the info about aliases. Quote Link to comment 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.