PHP - Mysql Join

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>
';
}

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.

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

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=313679.0

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);
}

I 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'. 

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!

Not sure if it is because it is too early in the morning but I am having a problem.

Code: [Select]
$mpid = #

$query = "SELECT table1.title, table2.* ".
 "FROM table1, table2 ".
"WHERE table1.mpid = table2.mpid";

How do I only show the info from the 2 tables where mpid=#

Hi

I want to know I can select which ID I get from an inner join?

Not sure If I should post here or in the MYSQL forum, but as it's using OOP I posted here.


Code: [Select]
$sql = "SELECT * FROM area_county INNER JOIN area_country ";
$sql .= "ON area_county.country_id = area_country.id ";
$sql .= "ORDER BY area_country.country, area_county.county ASC ";
$sql .= "LIMIT {$per_page} ";
$sql .= "OFFSET {$pagination->offset()}";
$list = Area_county::find_by_sql($sql);

foreach($list as $lists){
$ID = $lists->id;
$country_id = $lists->country_id;
$county = ucwords($lists->county);
        $country = ucwords($lists->country);

echo $ID;
}


I want it to show the ID of the county, NOT the id of the country.
both tables in the database have the ID column called id.

I know this can be done, but not to sure how.


Thanks

I'm an amateur PHP/mySQL person, trying to learn some more things, and I've hit a snag trying to do something I haven't done before.

Okay, here's what I'm trying to do. I have 3 tables:

Items (which includes `id` as the primary key) and a TON of information about each item.
Shop (which includes `id` as the primary key) and shop name and location.
Sale which has shop_id and item_id.

So, what I'm trying to do is have a form where someone can input in an item name and a shop name and have that item then added to the shop. This is where the Sale table comes into play too, as that's going to house each of the items and what shop it's in.

I currently have the following queries:


       $query5 = $db->execute("SELECT id FROM items LEFT JOIN sale ON items.id=sale.item_id");
       $query6 = $db->execute("SELECT id FROM shop LEFT JOIN sale ON shop.id=sale.shop_id");


I have the following form:


        if ($name == "") { $errors++; $errorlist .= "Name is required.<br />"; }
        if ($location == "" ) { $errors++; $errorlist .= "Location is required.<br />"; }
if (!isitem($itemname)) die("That item does not exist."); }

        if ($errors == 0) { 
            $query = doquery("INSERT INTO `sale` SET `item_id`=`$item`");
            admindisplay("New Item Added.","Add Items");

$page = <<<END

<b><u>Add Item</u></b><br /><br />
<form action="create_shop.php?do=additem" method="post">
<table width="90%">
<tr><td width="20%">Name:</td><td><input type="text" name="itemname" size="30" maxlength="255" value="" />**Be sure the item is already in the database and matches it IDENTICALLY.</td></tr>
<tr><td width="20%">Location:</td><td><input type="text" name="location" size="30" maxlength="55" value="" /><br /><span class="small">Where is the item obtained (unique shop name, please!)</span></td></tr>
</table>
<input type="submit" name="submit" value="Submit" /> <input type="reset" name="reset" value="Reset" />
</form>


What next steps do I need to take? Am I even on the right path?

Thanks for any and all help anyone provides!! I really appreciate it!