|
PHP - Mysql Join Not Producing Any Results
I am trying to join two tables but I am producing no results. I'm convinced that I have entered everything correctly, but clearly I haven't? Can anyone spot my mistake?
<?php
require 'core/memberinit.php';
$member = new Member();
include 'timeago.php';
$memberid = $member->data() ->id;
if(isset($_GET['q'])) {
$q = html_entity_decode($_GET['q']);
$query2 = DB::getInstance()->query("SELECT c.id c.time as time, c.message as message, c.removed1, c.removed2,
m1.member_first_name as firstname1, m1.member_last_name as lastname1, m1.username as username1,
m2.member_first_name as firstname2, m2.member_last_name as lastname2, m2.username as username2
FROM conversation c
LEFT JOIN members m1 ON c.member1=m1.id
LEFT JOIN members m2 ON c.member2=m2.id
WHERE (c.id=$q OR c.originalid=$q)
AND ((c.member1=$memberid AND c.removed1='No')
OR (c.member2=$memberid AND c.removed2='No'))
ORDER BY c.id");
foreach ($query2->results() as $result2)
{
echo $result2->firstname1;
echo $result2->lastname1;
echo $result2->username1;
echo timeAgo(strtotime($result2->time));
echo $result2->message;
echo $result2->firstname2;
echo $result2->lastname2;
echo $result2->username2;
}
?>
Edited by MartynLearnsPHP, 23 November 2014 - 12:55 PM. Similar TutorialsI have the following four tables. The query below is supposed to retrieve all projects and related data in associated tables where the project has been archived (i.e., archive field in project_info table set to '1') and user ID = '599zxj'. Hi there i have 3 tables joined together and am outputting the results ok. Heres what the results look like: Fleet Name: Home One Detected: No Ship Name: Slave 1 Bobafett Hull: 3 / 2 Fleet Name: Home Two Detected: Yes Ship Name: Imperial Star Destroyer Hull: 2 / 4 Fleet Name: Home Two Detected: Yes Ship Name: Executer Hull: 10 / 10 The 3 tables join together by the following: Planet = parsed url parameter Fleets = fleets on the current planet Ships = current ships in the fleets Its all working fine except for one thing, the Fleet Name is repeated for each occurance of a ship. I want to make it so that Fleet Name only occurs once for each fleet so it would look something like: Fleet Name: Home One Detected: No Ship Name: Slave 1 Bobafett Hull: 3 / 2 Fleet Name: Home Two Ship Name: Imperial Star Destroyer Detected: Yes Hull: 2 / 4 Ship Name: Executer Detected: Yes Hull: 10 / 10 Is this possible??? Heres the code in full: Code: [Select] <?php require_once('Connections/swb.php'); ?> <?php if (!function_exists("GetSQLValueString")) { function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue); switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue; } } $colname_Planet = "-1"; if (isset($_GET['recordID'])) { $colname_Planet = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']); } mysql_select_db($database_swb, $swb); $query_Planet = sprintf("SELECT * FROM planet WHERE PlanetName = %s", GetSQLValueString($colname_Planet, "text")); $Planet = mysql_query($query_Planet, $swb) or die(mysql_error()); $row_Planet = mysql_fetch_assoc($Planet); $totalRows_Planet = mysql_num_rows($Planet); $colname_Fleet = "-1"; if (isset($_GET['recordID'])) { $colname_Fleet = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']); } mysql_select_db($database_swb, $swb); $query_Fleet = sprintf("SELECT p.PlanetName, p.PlayerName, f.FleetName, f.Detected, s.ShipName, s.HealthA, s.HealthB FROM Planet p LEFT JOIN Fleet f ON (p.PlanetName = f.PlanetName) LEFT JOIN Ships s ON (f.FleetName = s.FleetName) WHERE p.PlanetName = '$colname_Planet'"); $Fleet = mysql_query($query_Fleet, $swb) or die(mysql_error()); $row_Fleet = mysql_fetch_assoc($Fleet); $totalRows_Fleet = mysql_num_rows($Fleet); ?> <head> <title>Untitled Document</title> <style type="text/css"> <!-- body { background-color: #FFFFFF; } --> </style></head> <body> <table width="60%" border="0"> <tr> <td width="10%">Planet:</td> <td width="14%"><?php echo $row_Planet['PlanetName']; ?></td> <td width="7%" rowspan="8"> </td> <td colspan="2" rowspan="8"> <?php while ($row_Fleet = mysql_fetch_assoc($Fleet)) { echo '<table width="400" border="0"><tr><td width="200">'; echo 'Fleet Name: '; echo'</td><td>'; echo $row_Fleet['FleetName']; echo'</td></tr><tr><td>'; echo 'Detected: '; echo'</td><td>'; if ($row_Fleet['Detected'] == '1'){ echo 'Yes';} else echo 'No'; echo'</td></tr><tr><td>'; echo 'Ship Name: '; echo'</td>'; echo'<td>'; echo $row_Fleet['ShipName']; echo'</td></tr><tr><td>'; echo 'Hull: '; echo'</td><td>'; echo $row_Fleet['HealthA']; echo ' / '; echo $row_Fleet['HealthB']; echo '</td></tr><tr><td></td></tr>'; echo'</table>'; } ?> <tr> <td>Faction:</td> <td><?php echo $row_Planet['PlayerName']; ?></td> </tr> <tr> <td>Infastructure</td> <td> </td> </tr> <tr> <td>Class 1: </td> <td><?php echo $row_Planet['Class1']; ?></td> <tr> <td>Class 2: </td> <td><?php echo $row_Planet['Class2']; ?></td> </tr> <tr> <td>Class 3: </td> <td><?php echo $row_Planet['Class3']; ?></td> </tr> <tr> <td>Class 4: </td> <td><?php echo $row_Planet['Class4']; ?></td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td> <a href="index.php target="main2.php"">Back</a></td> <td> </td> <td> </td> <td width="43%"> </td> <td width="26%"> </td> </tr> </table> </body> </html> <?php mysql_free_result($Planet); mysql_free_result($Fleet); ?> Thanks I need to set up pagination. I've seen a tutorial online, and in a book I have. But they're both for basic queries. I've got a LEFT JOIN. The basic script doesn't seem to work. Is there a different way it has to be done for joins? I need to echo the "reward.id" Code: [Select] $sql = "select reward.id, reward.location, reward.industry, reward.title, reward.stamp, industry.id, industry.label, industry.location FROM reward LEFT JOIN industry ON industry.id = reward.industry where (industry.label = '".$_GET['ind']."' and industry.location = '".$_GET['loc']."')"; $result = mysql_query($sql) or die(mysql_error()); while($info = mysql_fetch_assoc($result)){ echo $info['HOW TO I PRINT reward.id']; }free($result ); Any thoughts? I am trying to join these 2 tables below, but its not working, any ideas where i am going wrong?? <?php require("../include/mysqldb.php"); $con = mysql_connect("$dbhost","$dbuser","$dbpass"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("$dbame", $con); $result = "SELECT * Reg_Profile_public.pref, Search_profiles_up.search_small_image FROM Search_profiles_up INNER JOIN Reg_Profile_public_Sex ON Search_profiles_up.UIN=Reg_Profile_public.UIN WHERE UIN='803272125132009'"; while ($row = mysql_fetch_array($result)) { echo $row['search_small_image']; echo $row['pref']; } ?> Hi, Been looking for a little while on this problem, but can't seem to find a solution. Ok, so I have two tables, gallery & users Gallery contains images, image info etc. Users contains usernames, emails etc. In users there is a 'photo' column, which references to an ID in the gallery table. This is so each user can have a 'profile' image from a bank of images stored in gallery. What i'm trying to do is have a query that will output all of the images, and then show the usernames of assigned to each photo if there is one. Heres what i've got: Code: [Select] $result = mysql_query("SELECT *,COUNT(photo) AS usercount FROM gallery LEFT JOIN users ON gallery.img_id=users.photo GROUP BY img_id ORDER BY date_uploaded ASC"); Its all fine, and displays the username, though if there are multiple users using the same photo, it only shows one username. However, when displaying 'usercount', it will show that there are 2+ or whatever. Short of nesting queries within other loops, is there an alternative solution?? Thanks! Edd This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=351625.0 Heyyzarrh, iv been having troubles trying to make it so my PHP script will echo out the username by corresponding the posters ID with the ID in the members table. Heres what iv got so far: Code: [Select] <?php if ($urlid == "") { } $sql = mysql_query("SELECT members.username, posts.mem_id, posts.post_date, posts.post FROM members JOIN posts ON members.id = posts.mem_id WHERE posts.mem_id='$urlid' ORDER BY post_date DESC LIMIT 20"); while ($row = mysql_fetch_array($sql)){ echo '<b>' .$row['$username']. ':</b> '.$row['post'].'<br />At: '.$row['post_date'].'<br /><br />'; } ?> Please help!! :S so far only the actual posts data comes out not the username from the table "members"... This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=348196.0 This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=306058.0 This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=321443.0 This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=321929.0 I'm outputting my DB to a table. I want to include a JOIN so the table shows name, email, and company (which is in a different table). Not sure where to put the join. 2 other things I'd like to do: Not all users have email and I want any NULL to output as N/A instead of NULL. Would like the rows to alternate background color between white and blue for each row. Any hints appreciated. $query= "select * from Managers"; $result=mysql_query($query); echo mysql_error(); echo '<table align="center" cellspacing="1" cellpadding="2"> <tr> <td align="left"><b>Name</b></td> <td align="left"><b>Email</b></td> <td align="left"><b>Company</b></td> </tr> '; while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { echo '<tr bgcolor="' . $bg . '"> <td align="left">' . $row['name'] . '</td> <td align="left">' . $row['email'] . '</td> <td align="left">' . $row['company.name'] . '</td> //Need JOIN here </tr> '; } This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=323935.0 Hello all
I have the need to join up two tables and display the output in a table. These tables are a 'Site Details' table and a 'Customer Details' table where the 'cust_id' (PK on the CD table and FK on the SD table) match.
The MySQL statement (within PHP) I have works a treat in as much as the join seems to do the trick and join the two up on the correct row:
<?php
if($letter == 'ALL')
{$query = mysqli_query($con,
SELECT site_details.site_id, site_details.sitename, site_details.sitecode,
site_details.postcode,customer_details.cust_number, customer_details.cust_fname
FROM site_details
LEFT JOIN customer_details ON site_details.cust_id = customer_details.cust_id
ORDER BY sitename ASC);
}
This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=313679.0 Here is what I am trying to accomplish: I have a students table with a studentID I also have a notes table and a sched table with studentID The sched table is working as planned where if the student is scheduled for more than one time he is displayed twice. However if there are multiple notes per student the student is display for each individual note where I would like only the most current note to be displayed Here is what the output is: 1- 10:30:00 - 10:50:00 student3 Three FST Teacher One Special1 One Writing Ratios 09/10 2- 10:30:00 - 10:50:00 student3 Three FST Teacher One Special1 One Needs to work on fractions and decimals 09/10 3- 13:00:00 - 14:00:00 student3 Three FST Teacher One Special1 One Writing Ratios 09/10 4- 13:00:00 - 14:00:00 student3 Three FST Teacher One Special1 One Needs to work on fractions and decimals 09/10 As you can see I have two notes and two schedule times for this student. what I want it to display is only line 1 & 3. which is the newest note in the system. Below is my code that I am using. Any help would be greatly appreciated. Code: [Select] <?php $result = mysql_query("SELECT * FROM students LEFT JOIN teachers ON students.teacherId = teachers.teacherId LEFT JOIN course ON students.courseId = course.courseId LEFT JOIN specialEd ON students.specialId = specialEd.specialId LEFT JOIN sched ON students.studentId = sched.studentId ORDER BY start "); echo "<table>"; while ($row = mysql_fetch_array($result)){ $id = "?id=" . $row['studentId']; echo "<tr>"; echo "<td>" . $row['start'] . " - " . $row['stop'] . "</td>"; echo "<td>" . "<a href='student.php$id'>" . $row['fName'] . " " . $row['lName'] . "</td>"; echo "<td>" . $row['courseName'] . "</td>"; echo "<td>" . $row['teachers_fName'] . " " . $row['teachers_lName'] . "</td>"; echo "<td>" . $row['special_fName'] . " " . $row['special_lName'] . "</td>"; echo "<td>" . $row['note'] . "</td>"; echo "<td>" . date("m/d", strtotime($row['started'])) . "</td>"; echo "</tr>"; } echo "</table>"; ?> This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=308347.0 This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=308855.0 This portion is kind of stumping me. Basically, I have a two tables in this DB: users and users_access_level (Separated for DB normalization) users: id / username / password / realname / access_level users_access_level: access_level / access_name What I'm trying to do, is echo the data onto an HTML table that displays users.username in one table data and then uses the users.access_level to find users_access_level.access_name and echo into the following table data, I would prefer not to use multiple queries if possible or nested queries. Example row for users: 1234 / tmac / password / tmac / 99 Example row for users_access_level: 99 / Admin Using the examples above, I would want the output to appear as such: Username: Access Name: Tmac Admin I am not 100% sure where to start with this, but I pick up quickly, I just need a nudge in the right direction. The code I attempted to create just shows my lack of knowledge of joining tables, but I'll post it if you want to see that I did at least make an effort to code this myself. Thanks for reading! |
|||||||