PHP - Problem With Joins
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? Similar TutorialsHi 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']; } i 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 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? This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=322128.0 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? 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 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=315743.0 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=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 Guys thanks for helping me solve the problem i had but now i have another problem and i am lost. i have included the code below for you to have overview.
This is the code:
<table style="width:100%; margin-left:auto; margin-right:auto"> Hi, I am trying to write in an existing excel file. But for some reason, I can't open the workbook (but I know I have made similar code work before). Here I am just pasting the code upto the error line $filename="C:\\apache\\htdocs\\GAC2\\dload\\template.xls"; $excel = new COM("excel.application") or die("Unable to instanciate excel"); $excel->Visible = 1; $excel->DisplayAlerts = 1; try{ $wkb = $excel->Workbooks->Open($filename); }catch (Exception $e) { echo 'Caught exception: ', $e->getMessage(), "\n"; } //........ Later part of the code This returns the following error Code: [Select] Caught exception: Source: Microsoft Office Excel Description: Unable to get the Open property of the Workbooks class For record, the I have already validated the file location. And I have tried the following line instead without any positive result. Code: [Select] $filename="C:\apache\htdocs\GAC2\dload\template.xls"; Looking for any help please. -Abd Hello everyone Just found this forum and really need some help. I'm trying to create a popup contact form for my product pages. I managed to get a php form from a random site and i cant get the form to pop up. http://www.e-sol.co.uk/printer-scanner-copier.php If someone could help i would be very grateful. You may see a lot of things wrong with the code below, but please bring it to my attention! I'm trying to advance my knowledge on OOP, but I'm trying my best to get the basics down. The below code, after selecting the two characters to fight, the page returns nothing, just blank. I'm pretty sure the error lyes within class_li.php, but I'm not exactly sure. Script is attached. The problem is whit the var $threadid . It dosent work here(it dosen't display the data from database): $dbh = "SELECT *FROM comments WHERE threadid = '".$threadid."' "; But if I assign a value before the that code the html page show correctly. Code: [Select] include("config.php"); $name = $_POST['name']; $comment = $_POST['comment']; $threadid = $_POST['threadid']; if($name & $comment) { $dbh="INSERT INTO comments (name,comment,threadid) VALUES ('$name','$comment','$threadid') "; mysql_query($dbh); } $dbh = "SELECT *FROM comments WHERE threadid = '".$threadid."' "; $req = mysql_query($dbh); while($row=mysql_fetch_array($req)) { echo "<li>"; echo "<br>"."<b>".$row['name']."</b></br>"."<br>".$row['comment']."</br>"; echo "</li>"; } Code: [Select] $('.submit').click(function(){ location.reload(); var name = $("#name").val(); var comment = $("#comment").val(); var threadid = $("#v").val(); var dat = 'name='+name+'&comment='+comment+'&threadid='+threadid; $.ajax({ type:"post", url:"comment.php", data:dat, success:function(){ console.log("dat"); } }); return false; }); so i want to call my php file with a query string to select a record out of a XML file... im close but cant get the variable i pass to be the record selected.. example... index.php?auction=1 i highlighted in red, where it doesnt pick up the proper row Code: [Select] <?php ini_set('display_errors', 1); error_reporting(E_ALL); $auction = $_GET{auction}; // parse the xml $xmlFileData = file_get_contents("items.xml"); $xmlData = new SimpleXMLElement($xmlFileData); [color=red]$location = $xmlData->item[$auction];[/color] //Retrieving the content from the selected item $title = $location->title; $value = $location->value; $itemnumber = $location->itemnumber; $description = $location->description; $donatedby = $location->donatedby; $image1 = $location->image1; $image2 = $location->image2; $image3 = $location->image3; $image4 = $location->image4; $image5 = $location->image5; $image6 = $location->image6; $image7 = $location->image7; $image8 = $location->image8; ?> <style> .itemimages img {width: 10px; height: 10px;} img.large {width: 100px; height: 100px;} </style> <script language="JavaScript" src="http://code.jquery.com/jquery-1.5.min.js" type="text/javascript"></script> <script> $().ready(function() { $(".itemimages img").click( $("#largepic").attr("src","this"); }); }); </script> <table border="1" cellpadding="10"> <tr> <td valign="top"> <img src="<?php echo $image1 ?>" alt="" border="0" class="large" id="largepic"><br> <div class="itemimages"> <?php // image 1 if (trim(''.$image1) != '') { echo "<img src='"; echo $image1; echo "' alt='' border='0'/> "; } // image 2 if (trim(''.$image2) != '') { echo "<img src='"; echo $image2; echo "' alt='' border='0'/> "; } // image 3 if (trim(''.$image3) != '') { echo "<img src='"; echo $image3; echo "' alt='' border='0'/> "; } // image 4 if (trim(''.$image4) != '') { echo "<img src='"; echo $image4; echo "' alt='' border='0'/> "; } // image 5 if (trim(''.$image5) != '') { echo "<img src='"; echo $image5; echo "' alt='' border='0'/> "; } // image 6 if (trim(''.$image6) != '') { echo "<img src='"; echo $image6; echo "' alt='' border='0'/> "; } // image 7 if (trim(''.$image7) != '') { echo "<img src='"; echo $image7; echo "' alt='' border='0'/> "; } // image 8 if (trim(''.$image8) != '') { echo "<img src='"; echo $image8; echo "' alt='' border='0'/> "; } ?> </div> </td> <td valign="top"> <?php echo $title ?><br> Estimated Value: <?php echo $value ?><br> Item Number: <?php echo $itemnumber ?><br><br> <?php echo $description ?><br><br> Donated by: <?php echo $donatedby ?><br> </td> </tr> </table> I am trying to use the following code snippet to return values from a database with a "," after each value apart form the last one. At the moment for testing I have 2 rows with values 1 and 3 $totalRows_Recordset3 equals 2 (I echoed this to check) $i=1; while($row_Recordset3 = mysql_fetch_assoc($Recordset3)) { if($i < $totalRows_Recordset3) { $str = $str.$row_Recordset3['reading'].","; $i=$i+1; } else { $str = $str.$row_Recordset3['reading']; $i=$i+1; } } I get the number 3 which is the last value in the database, but I can't see why I don't get "1,3" I am sure it is something simple but it just escapes me. Thanks in advance for any help Gordon |