|
PHP - Joins
I'm a newbie in PHP/MySQL. In MS-Access, I can set up a permanent relationship between tables. Does this not exist in MySQL? Do all the relationships need to be established/re-established with each query? Or is there a way to establish a permanent relationship?
Similar Tutorialsi have a recent topics box on my homepage which shows the five most recent topics. this is the query to display them: $query = $link->query("SELECT t.t_name, t.t_poster, t.t_time_posted, t.t_views, t.t_replies, t.t_last_poster, t.t_last_post_time, u.u_avatar, f.f_name FROM ".TBL_PREFIX."topics t JOIN ".TBL_PREFIX."users u ON (u.u_username = t.t_poster) JOIN ".TBL_PREFIX."forums as f ON (t.t_fid = f.f_fid) ORDER BY t_time_posted DESC LIMIT 5")or die(print_link_error()); $result = $query->fetchAll(); the problem is the url needs to be: Code: [Select] ./category/the_category/forum/the_forum/topic/the_topic but with this code i can only pull the topic name and the forum name due to the database. i dont have a seperate table for categories. instead i have forums with a pid of 0 to denote a category and all forums within the category have a pid which is equal to the fid of the forum category: Code: [Select] name fid pid category 1 0 forum 2 1 another 3 1 and so on. so how would i be able to get the category name aswell without running another query within my foreach loop? i tried joining the forums table again as c but that wouldnt help because the result would be looking for the name and it would appear twice within the query. Thanks Hi guys, I never learnt joins, and this is what my code looks like as a result, Can someone just show me how to convert the following from 2 queries to one? It is a blog post and the second query is getting an author I have shortened it for convenience.
Thanks in advance.
$getBlogs = mysql_query("SELECT * FROM blogs WHERE blogStatus='1'", $retreat);
while($row = mysql_fetch_array($getBlogs)){
$blogID = $row['blogID'];
$authurID = $row['authurID'];
}
$getAuthor = mysql_query("SELECT * FROM blog_authurs WHERE authurID='$blogAuthur' LIMIT 1", $retreat);
while($row = mysql_fetch_array($getAuthor)){
$authurName = $row['authurName'];
}
im trying to improve my code by using table joins but when i use the following code it just returns everything in the database rather than the results that equal $forum_id which is the get value 2. There should only be 2 results. $query = $db->query("SELECT ".DB_PREFIX."topics.topic_id, ".DB_PREFIX."topics.topic_name, ".DB_PREFIX."topics.topic_poster, ".DB_PREFIX."topics.topic_time_posted, ".DB_PREFIX."topics.topic_views, ".DB_PREFIX."topics.topic_replies, ".DB_PREFIX."topics.topic_last_poster, ".DB_PREFIX."topics.topic_last_post_time, ".DB_PREFIX."topics.topic_locked, ".DB_PREFIX."topics.topic_sticky, ".DB_PREFIX."parents.parent_id, ".DB_PREFIX."parents.parent_name, ".DB_PREFIX."forums.forum_name, ".DB_PREFIX."members.user_username, ".DB_PREFIX."members.user_group FROM ".DB_PREFIX."topics JOIN ".DB_PREFIX."members JOIN ".DB_PREFIX."parents JOIN ".DB_PREFIX."forums ON ".DB_PREFIX."topics.topic_poster = ".DB_PREFIX."members.user_username WHERE ".DB_PREFIX."forums.forum_id = ".$forum_id." ORDER BY ".DB_PREFIX."topics.topic_time_posted $max") or trigger_error("SQL", E_USER_ERROR); Check out my code below and you will see what I am trying to do. I cannot figure out what is going wrong. Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/mskordus/public_html/index.php on line 25 Code: [Select] <?php $result = mysql_query("SELECT * FROM students,teachers,specialEd,course,sched,notes WHERE students.studentId=notes.studentId AND students.studentId=sched.studentId AND students.teacherId=teachers.teacherId AND students.specialId=specialEd.specialId AND students.courseId=course.courseId AND "); echo "<table>"; while ($row = mysql_fetch_array($result)){ echo "<tr>"; echo "<td>" . $row['students.fName'] . "</td>"; echo "<td>" . $row['teachers.fName'] . "</td>"; echo "</tr>"; } echo "</table>"; ?> Hi, I'm trying to a query to check whether a record has a reference in two other tables, I have the following: Explain SELECT COUNT(*) AS new FROM entity_details LEFT JOIN entity_turned_prospect ON entity_details.id = entity_turned_prospect.entityRef LEFT JOIN entity_turned_customer ON entity_details.id = entity_turned_customer.entityRef WHERE entity_details.ownerRef = 41 AND entity_details.typeRef = 4 AND entity_details.dateCreated = MONTH(NOW()) AND entity_turned_prospect.id = NULL AND entity_turned_customer.id = NULL But I get 0 results returned when I know for a fact there should be some. If I change the count to just select * I then get an error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS new FROM entity_details, LEFT JOIN entity_turned_prospect ON' at line 1 Could someone show me how to fix this? I have created a file with this basic structure in the referals table: refREFER = the referer, person who sent the referal link out refREFED = the refered person, the one who signed up with the link refREFERIP = the referer's IP refREFEDIP = the refered person's IP I'm trying to create a code that will list any user that has signed up on the same IP with a referal link. Everything works except the Refered column, which I can't seem to figure out how to do. Basically I have made the refREFER = the userid in the users table, so it can be output as a username. However I want to do the same with the refREFED but don't know how. Here's my code so far: <?php include "globals.php"; if($ir['user_level'] != 2 && $ir['user_level'] != 3) //User level check { die("You can't access this page"); } $q=mysql_query("SELECT u.userid, u.username, u.laston, r.* FROM users u LEFT JOIN referals r ON r.refREFER=u.userid WHERE r.refREFERIP=r.refREFEDIP ORDER BY userid ASC",$c); //Selecting everything that's needed print "<center><br /><b><font color=white><h1>Referral Multis</h1></b><br /> <table width=75% border=1> <tr style='background:black'> <th><font color=grey>Referer</font></th> <th><font color=grey>Refered</font></th> <th><font color=grey>IP</font></th> <th><font color=grey>Time</font></th> </tr>"; while(($r=mysql_fetch_array($q) or die(mysql_error()))) //Making all of the select stuff into $r { $reftime = date('F j, Y g:i:s a', $r['refTIME']); if($r['laston'] >= time()-60*60) { $on="<font color=green><b>Online</b></font>"; } else { $on="<font color=red><b>Offline</b></font>"; } print " <tr> <td><a href='viewuser.php?u={$r['userid']}'><font color=brown><b>{$r['username']}</b></font></a> [{$r['refREFER']}]</td> <td> {$r['username']} - {$r['refREFED']}</td> <td>{$r['refREFERIP']}</td> <td>$reftime</td> </tr>"; } print "</table>"; $h->endpage(); ?> At the moment under the Refered column it shows the wrong username, but the correct ID. Any ideas? I've got a region-type select form where a user chooses his/her continent, and depending on the selection, a second select form is filled with country values relating to that continent. From there, depending on the country selection, a third select form is dynamically filled with regions relating to that country and continent. See the code attached: <form action="" method="POST"> <select id="continent" name="continent" style="min-width: 170px;"> <option value="">Select Continent</option> <?php $q = "SELECT * FROM table_areaContinent"; $r = mysql_query($q) or die(mysql_error()); while($row = mysql_fetch_array($r)){ $continent_id = $row['cont_id']; $continent = $row['continent']; print '<option value="' . $continent_id . '">' . $continent . '</option>' . "\n"; } ?> </select> <br /><br /> <select id="country" name="country" style="min-width: 170px;"> <option value="">Select Country</option> <?php $q = "SELECT * FROM table_areaCountry JOIN table_areaContinent USING (cont_id)"; $r = mysql_query($q) or die(mysql_error()); while($row = mysql_fetch_array($r)){ $country_id = $row['country_id']; $country = $row['country']; $continent_id = $row['cont_id']; print '<option value="' . $country_id . '" class="' . $continent_id . '">' . $country . '</option>' . "\n"; } ?> </select> <br /><br /> <select id="region" name="region" style="min-width: 170px;"> <option value="">Select Region</option> <?php $q = "SELECT * FROM table_areaRegion JOIN table_areaCountry USING (country_id)"; $r = mysql_query($q) or die(mysql_error()); while($row = mysql_fetch_array($r)){ $region_id = $row['region_id']; $region = $row['region']; $country_id = $row['country_id']; print '<option value="' . $region_id . '" class="' . $country_id . '">' . $region . '</option>' . "\n"; } ?> </select> <br /><br /> <input type="submit" value="Submit" name="submit" class="submit" /> </form> Now, I simply just want to echo out the Continent, Country and Region that the user selected after the form is submitted. I currently have this : <?php if(!empty($_POST['submit'])){ $continent = $_POST['continent']; $country = $_POST['country']; $region = $_POST['region']; print ' <b>Continent: ' . $continent . '<br /> <b>Country: ' . $country . '<br /> <b>Region: ' . $region . '<br /> '; } ?> Which echoes out the ID numbers for each variable. What would be the best direction to take when pulling data from multiple tables to echo out the actual Names that the IDs are assigned to? This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=322128.0 I have this code which should get all the results from forum_forums and list them in their respective parents using forum_forums.parent_id. It does work sort of but it repeats one of them and doesnt include all of them. Can anyone see where i am going wrong? $parent_info_query = $db->query("SELECT parent_id, parent_name FROM ".DB_PREFIX."parents") or die(mysql_error()); while ($parent_info = mysql_fetch_object($parent_info_query)) { // Add parent_id into variable for later query $parent_id = $parent_info->parent_id; echo '<table class="forum_table" onclick="expandCollapseTable(this)"> <tr id="tr1"> <th class="forum_left_corner"></th> <th class="forum_parent_name">'.$parent_info->parent_name.'</th> <th class="empty"></th> <th class="empty"></th> <th class="forum_last_post_header">'.LAST_POST.'</th> </tr>'; // Get Forum information from DB to show all forums // including who the last post was posted by $forum_info_query = $db->query("SELECT ".DB_PREFIX."forums.forum_id, ".DB_PREFIX."forums.forum_name, ".DB_PREFIX."forums.forum_description, ".DB_PREFIX."forums.forum_topics, ".DB_PREFIX."forums.forum_posts, ".DB_PREFIX."forums.forum_last_poster, ".DB_PREFIX."forums.forum_last_post_time, ".DB_PREFIX."forums.forum_last_post, ".DB_PREFIX."members.user_id, ".DB_PREFIX."members.user_username, ".DB_PREFIX."members.user_group, ".DB_PREFIX."topics.topic_id, ".DB_PREFIX."topics.topic_name FROM ".DB_PREFIX."forums JOIN ".DB_PREFIX."members ON ".DB_PREFIX."forums.forum_last_poster = ".DB_PREFIX."members.user_id JOIN ".DB_PREFIX."topics ON ".DB_PREFIX."forums.forum_id = ".DB_PREFIX."topics.forum_id WHERE ".DB_PREFIX."forums.parent_id = $parent_id") or trigger_error("SQL", E_USER_ERROR); while ($forum_info = mysql_fetch_object($forum_info_query)) { This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=313836.0 This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=315743.0 This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=320409.0 Given the below 3 database tables I am trying to construct a SQL query that will give me the following result: customer_favourites.cust_id customer_favourites.prod_id OR product.id product.code product.product_name product.hidden product_ section.section_id (MUST BE ONLY THE ROW WITH THE LOWEST SECTION ID, I.E. ROW ID #44108) product_ section.catpage (MUST BE ONLY THE ROW WITH THE LOWEST SECTION ID, I.E. ROW ID #44108) product_ section.relative_order (MUST BE ONLY THE ROW WITH THE LOWEST SECTION ID, I.E. ROW ID #44108) I currently have.... SELECT customer_favourites.cust_id, customer_favourites.prod_id, product.code, product.product_name, product.hidden, product_section.section_id, product_section.relative_order, product_section.catpage FROM customer _favourites INNER JOIN product ON customer_favourites.prod_id = product.id INNER JOIN product_section ON product_section.product_code = product.code WHERE `cust_id` = '17' AND `hidden` = '0' GROUP BY `code` ORDER BY `section_id` ASC, `relative_order` ASC, `catpage` ASC LIMIT 0,30 This gives me what I want but only sometimes, at other times it randomly selects any row from the product_section table. I was hoping that by having the row I want as the last row (most recent added) in the product_section table then it would select that row by default but it is not consistent. Somehow, I need to be able to specify which row to return in the product_section table, it needs to be the row with the lowest section_id value or it should by the last row (most recent). Pulling my hair out so any help is gratefully received. customer_favourites id cust_id prod_id 70 4 469 product id code product_name hidden 469 ABC123 My Product 0 product_section id section_id catpage product_code relative_order recommended 44105 19 232 ABC123 260 1 44106 3 125 ABC123 87 1 44107 2 98 ABC123 128 1 44108 1 156 ABC123 58 0 |
|||||||