PHP - Inner Join Only Includes Records That Match Both Tables

How do I join these queries and is it best practice?

$e_query = mysql_query("SELECT `employeeid`, `userid`, `companyid`, `function`, `timestamp` FROM `employees` WHERE `companyid` = '${c_row['companyid']}'");

$e_row = mysql_fetch_assoc($e_query);
         
$u_query = mysql_query("SELECT `id`, `firstname`, `lastname`, `username`, `accounttype` FROM `users` WHERE `id` = '${e_row['userid']}'");

$u_row = mysql_fetch_assoc($u_query);
         
$eQ = "SELECT `capitalrequested` FROM `eQuestions` WHERE `userid` = '${u_row['id']}'";
         
$eQRes = mysql_query($eQ);
         
$reQ = mysql_fetch_assoc($eQRes);
         
$iQ = "SELECT `capitalavailable` FROM `iQuestions` WHERE `userid` = '${u_row['id']}'";
         
$iQRes = mysql_query($iQ);
         
$riQ = mysql_fetch_assoc($iQRes);

Hello, I'm trying to get a join with 2 tables but cannot get it to work. It's a contentsystem with an ID for the author. Another table tells the name of the author. Now I want to join these things. This is what i've coded so far:

<?
$query = "SELECT rmnl_content.content_aid, COUNT(rmnl_content.content_id), rmnl_crew.crew_id, rmnl_crew.crew_name

FROM rmnl_content, rmnl_crew" "where rmnl_content.content_aid = rmnl_crew.crew_id";
   
$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
   echo " <b>". $row['COUNT(rmnl_content.content_id)'] ."</b> posted messages by ". $row

['crew_rmnl.crew_name'] ."</td> .";
   echo "<br />";
}
?>

This topic has been moved to MySQL Help.

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

I am creating a library app for personal development and would like to further my knowledge of MySql.   I currently have two tables 'book' and 'category'   book   id   title                    author      category     isbn
---- -------                 ----------   ----------  ------- 
1    Treasure Chest     Jim Jones     1           14252637 2    Pirates Boat         Sue Smith    2           88447737 3    Adventure Land   Harry Jo      3           01918273 4    Winter Week       Sam Dill      3           00999337   category   id       cat_name   
----     -------         
1        Horror 2        Kids 3        Fiction 4        Science   I am doing a simple search where I want to display all books (select * from book) and I would like to display all the books with their corresponding categories. This works but displays the category number instead of the category name.   How can I alter these tables in such a way that the cat_name and category are joined?   I tried to run a command in phpmyadmin as below however it returned an error;

ALTER TABLE book
ADD FOREIGN KEY (category) REFERENCES category(cat_name);

error

#1452 - Cannot add or update a child row: a foreign key constraint fails (`sslib`.`#sql-1ab4_1dae`, CONSTRAINT `#sql-1ab4_1dae_ibfk_1` FOREIGN KEY (`category`) REFERENCES `category` (`cat_name`))

Alright this is going to be a doozy:

I have 3 tables: Collectibles, CollectiblesMethods and CollectionsCollectibles

Collectibles contains: ID, Photo, Collectible, Value, Resale, Rarity
CollectiblesMethods contains: ID, Collectible, Method
CollectionsCollectibles contains: ID, Collectible, Collection, Amount

Needless to say, this is a many to many relationship model. (many methods to one collectible, many collectibles to one collection).

The page collectibles.php is contained inside collspec.php

collspec.php shows information relating to a specific collection called by GET.
collectibles.php shows all collectibles belonging to the same specfic collection in collpec.php, thus why it is contained inside it.

I want collectibles.php to output the following:
Photo, Collectible, Amount, Resale, Rarity, Method

I already have collectibles.php outputting:
Photo, Collectible, Amount, Resale, Rarity

Method is proving to be difficult. I can either get it to show the word None for every collectible with this code:
<table width="99%">
<tr>
<th colspan="6"><h2>Collectibles</h2></th>
</tr>
<tr>
<td>Photo</td>
<td>Name</td>
<td># Needed</td>
<td>Sells For</td>
<td>Rarity</td>
<td>Obtained</td>
</tr>
<?php 
$sql2 = "SELECT * FROM CollectionsCollectibles LEFT JOIN Collectibles ON CollectionsCollectibles.Collectible = Collectibles.Collectible WHERE Collection = '" . mysql_real_escape_string($item) . "'";
$collectible = mysql_query($sql2);
if (trim($row['Collectible']) != '') 
while($row2 = mysql_fetch_array($collectible)){
?>
<tr>
<td width="14%" valign="top"><img src="<?php echo "$img{$row2['Photo']}"; ?>"></td>
<td><?php echo $row2['Collectible']; ?></td>
<td><?php echo $row2['Amount']; ?></td>
<td><?php echo $row2['Sell']; ?></td>
<td><?php echo $row2['Rarity']; ?></td>
<td>
<?php
$sql3 = "SELECT CollectiblesMethods.Method FROM CollectiblesMethods WHERE Collectible = '" . $row2['Collectible'] . "'";
$obtain = mysql_query($sql3);
if (trim($row['Method']) != '') 
while($row3 = mysql_fetch_array($obtain)){
?>
<tr>
<td><?php echo $row3['Method']; ?></td>
</tr>
<?php
} else 
  echo 'None';
?>
</td>
<?php
} else 
  echo 'None';
?>
</tr>
</table>

or I can get it to show the Methods (of which certain collectibles have more than one method) using this code but with a catch; it repeats the photo, name, value and resale if there is more than one method for a given collectible:
<table width="99%">
<tr>
<th colspan="6"><h2>Collectibles</h2></th>
</tr>
<tr>
<td>Photo</td>
<td>Name</td>
<td># Needed</td>
<td>Sells For</td>
<td>Rarity</td>
<td>Obtained</td>
</tr>
<?php 
$sql2 = "SELECT * FROM CollectionsCollectibles LEFT JOIN Collectibles ON CollectionsCollectibles.Collectible = Collectibles.Collectible LEFT JOIN CollectiblesMethods ON Collectibles.Collectible = CollectiblesMethods.Collectible WHERE Collection = '" . mysql_real_escape_string($item) . "'";
$collectible = mysql_query($sql2);
if (trim($row['Collectible']) != '') 
while($row2 = mysql_fetch_array($collectible)){
?>
<tr>
<td width="14%" valign="top"><img src="<?php echo "$img{$row2['Photo']}"; ?>"></td>
<td><?php echo $row2['Collectible']; ?></td>
<td><?php echo $row2['Amount']; ?></td>
<td><?php echo $row2['Sell']; ?></td>
<td><?php echo $row2['Rarity']; ?></td>
<td><?php echo $row2['Method']; ?></td>
<?php
} else 
  echo 'None';
?>
</tr>
</table>

So Let's say I have a collection called Toys. This is how it outputs using my above codes:

1st Code example:
__________________________________________
|Photo|Name |Amount|Sells For   |Rarity       |Obtained|
|Pic1  |Marble| x5        | 20 cents | Common | None     |
|Pic2  |Train   | x1       | 5 $          | Rare       |  None    |

2nd Code example:
__________________________________________
|Photo|Name |Amount|Sells For   |Rarity       |Obtained|
|Pic1  |Marble| x5        | 20 cents | Common | Barter   |
|Pic1  |Marble| x5        | 20 cents | Common | Trade    |
|Pic2  |Train   | x1       | 5 $          | Rare       |  Gift      |

How it should output:
__________________________________________
|Photo|Name |Amount|Sells For   |Rarity       |Obtained|
|Pic1  |Marble| x5        | 20 cents | Common | Barter   |
|        |          |             |               |               | Trade    |
|Pic2  |Train   | x1       | 5 $          | Rare       |  Gift      |

As you can see, I want it to output all methods obtained in the same box, increasing the rowspan as necessary.

I don't want it to output None (since this is not true) as in the first example.
I don't want it to repeat the same information simply to add a line for the next method as in the second example.

Can someone help me figure out what I'm doing wrong?

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=352310.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"... 

Hi guys! Once again i require your endless talents!

I have 2 tables in my mysql DB. One table is a list of products a store offers, the other is the cart of the user. I need to find the most efficient way or retrieving the images and prices from the LIST OF PRODUCTS table based on the list of PRODUCT NUMBERS stored in the cart table of the user.

List of products offered

*************************
Prod # * Price *      img src     *
*************************
001     * 19.99* imgs/pic1.jpg *
002    * 29.99 * imgs/pic2.jpg *
*************************

User's Cart
*********************
Prod # * quantity * size *
*********************
001     *    10       *  m    *
002     *     4        *  s     *
********************

Basically i just want to retrieve ONLY the rows that i need (ones that the user has added to his/her cart) and ASSOCIATE the img src with the quantity and size so that i can lay it out in a table on a view_cart page...

If you want a good laugh just take a look at what ive done so far...

Code: [Select]
$cart = mysql_query("SELECT * FROM ".$session."");

$cart_row_count = mysql_fetch_assoc($cart);
$cart_row_count = mysql_num_rows($cart_row_count);

if($cart_row_count != 0){

$i = 1;

while($cart_result = mysql_fetch_assoc($cart)){
${"product$i"} = $cart_result['product'];
${"size$i"} = $cart_result['size'];
$i++;
}

$i2 = 1;

$query = "product = ".${"$product$i2"}." ";

while($i2 <= $i){
$i2++;
$query .= "AND ".${"$product$i2"}." ";
}
}

This topic has been escorted to MySQL Help.

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

This topic has been moved to MySQL Help.

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

I have two tables 'book' and 'category'. They look like the following in phpmyadmin;   book   id   title                    author      category     isbn
---- -------                 ----------   ----------  ------- 
1    Treasure Chest     Jim Jones     1           14252637 2    Pirates Boat         Sue Smith    2           88447737 3    Adventure Land   Harry Jo      3           01918273 4    Winter Week       Sam Dill      3           00999337 5    The Twite           Roald Dahl   Fiction   87873366   category   id       cat_name   
----     -------         
1        Horror 2        Kids 3        Fiction 4        Science   Users have the option of adding books into the library via an online form, or via a Google Booka api method (user enters isbn, searches, is presented with book info and then clicks 'add to library', done.). This is handled via ajax.   The online form works fine, and successfully adds the book info. However my problem is with the Google Books method, it successfully adds the data to the db however the category column is in text format (i.e 'Juvenile Science' or 'Scary Fiction') as opposed to the manual form which adds categories as 1, 2 or 3 (1 =Horror, 2 = Kids, 3 = Fiction).   Is there any way I can add the Google Book category data to my table and convert it to an integer or similar? Not sure what I need to do. Suggestions appreciated! Should I add the Google entries to another table (i.e googleCategory)?   My HTML only outputs the numbered category entries and ignored the text format entries.   my php

$sql = "SELECT b.id, b.title, b.author, b.isbn, b.publicationYear,
        c.cat_name
        FROM book AS b
        INNER JOIN category AS c ON b.category = c.id
        WHERE status != 'Archive' ORDER BY id DESC LIMIT $startrow, 15 ";


$res = $conn->query($sql) or trigger_error($conn->error."[$sql]");


while($row = $res->fetch_array()) {


    echo '<tbody>';
    echo '<tr>';
    echo '<td>' . $row['id'] . '</td>';
    echo '<td>' . $row['title'] . '</td>';
    echo '<td>' . $row['author'] . '</td>';
    echo '<td>' . $row['cat_name'] . '</td>';
    echo '<td>' . $row['isbn'] . '</td>';
    echo '<td>' . $row['publicationYear'] . '</td>';
    echo '</tr>';
    echo '</tbody>';
};

Apologies if this is all a bit confusing I am very new to php and mysql. Thanks,   J

I have a cart script, and I need to display the products the user has added.  This is a school assignment and I'm very stuck on this seemingly 'little' error.

Right now, I have two tables:

For time's sake, I am showing duplicate products with different prod_id's:
Table: products
prod_id category sub_category thumb_href image_href title desc summary manufacturer price 17 sofa leather red_leather_sofa_small.jpg red_leather_sofa_main.jpg Red Leather Sofa [BLOB - 134B] [BLOB - 59B] Balenty 999.99 18 sofa leather red_leather_sofa_small.jpg red_leather_sofa_main.jpg Red Leather Sofa [BLOB - 134B] [BLOB - 59B] Balenty 999.99 19 sofa leather red_leather_sofa_small.jpg red_leather_sofa_main.jpg Red Leather Sofa [BLOB - 134B] [BLOB - 59B] Balenty 999.99
And this is the cart table that holds temporary cart items (storing the product ID to pull later):
Table: cart
temp_cart_item cart_id (assigned by session_id() ) prod_id user_id 22 jier11u0e7cl2ghosjodpaark2 17 asdfasdf 23 jier11u0e7cl2ghosjodpaark2 17 asdfasdf 24 jier11u0e7cl2ghosjodpaark2 35 asdfasdf 25 jier11u0e7cl2ghosjodpaark2 5 asdfasdf 26 jier11u0e7cl2ghosjodpaark2 19 asdfasdf
SO, user 'asdfasdf' is logged in and has selected 5 items for his cart - two of which are the same (prod_id 17).  Basically,

I need to compare the prod_id's of each table and output all products (and quantities) matching the temporary cart prod_id.

What I have now to loop through and display the cart items are (bear with me):


echo "<h1>Cart</h1>";
}
// Display welcome and navigation links
displayWelcome();
displayNav();

// Getting the prod_id's from the table cart to compare with the product table
$cart_id = session_id();
$user_id = ($_SESSION['login_username']);
$query = "SELECT prod_id FROM cart WHERE cart_id='$cart_id' AND user_id='$user_id'";
$result = mysqli_query($cxn,$query) or die("<h2 class=\"warning\">Whoa! Problem with the cart script here!</h2>");
$nrows = mysqli_num_rows($result);
if($nrows < 1)
{
echo "<h2>Your cart is empty</h2><p><a href=\"catalog.php\">Go back to the catalog</a></p>";
}
else {  // Continue to gather products for all the product IDs in the products DB table...
echo "<h4>$nrows items in your cart</h4>";
while($row = mysqli_fetch_array( $result )) {
$user_id = ($_SESSION['login_username']);
$product = $row['prod_id'];

// Is the query my problem?
$query = "SELECT * FROM products WHERE prod_id='$product'";
$result = mysqli_query($cxn,$query) or die("<h2 class=\"warning\">Whoa! Problem with the cart script here!</h2>");
$nrows = mysqli_num_rows($result);
if($nrows < 1) { // If the item doesn't exist, display an error
echo "<h2>That is not a valid product, or any of the items you had in your cart are missing or have been deleted.</h2><p><a href=\"catalog.php\">Go back to the catalog</a></p>";
// Protect from continually displaying an error by deleting the invalid cart record
$deletequery = "DELETE FROM cart WHERE prod_id='$product'";
$deleteresult = mysqli_query($cxn,$deletequery) or die("<h2 class=\"warning\">Whoa! Problem with the cart script here!</h2>");
exit();
}
while($row = mysqli_fetch_array( $result )) {  //  Then disply them in a table
echo "<h2>Items in Your Cart:</h2>";
echo "
<table id=\"products\">
<thead>
<tr>
<th class=\"col1 headercol\" scope=\"col\">Thumbnail</th>
<th class=\"col2 headercol\" scope=\"col\">Summary</th>
<th class=\"col3 headercol\" scope=\"col\">Manufacturer</th>
<th class=\"col4 headercol\" scope=\"col\">Price</th>
</tr>
</thead>

<tbody>\n";
$query = "SELECT SUM(price) FROM products WHERE prod_id='$product' GROUP BY price";
$priceResult = mysqli_query($cxn,$query) or die("<h2 class=\"warning\">Whoa! Problem with the cart script here!</h2>");
// keeps getting the next row until there are no more to get
// Print out the contents of each row into a table
echo "<tr>
<td class=\"col1\">"; 
echo "<a class=\"imgHref\" href=\"detail.php?prod_id=".$row['prod_id']."\" title=\"".$row['title']."\"><img src=\"../images/".$row['thumb_href']."\" alt=\"".$row['title']."\" /></a>";
echo "</td>\n"; 
echo " <td class=\"col2\">"; 
echo "<h3 class=\"title\"><a href=\"detail.php?prod_id=".$row['prod_id']."\" title=\"".$row['title']."\">".$row['title']."</a> <span class=\"inlineh3\">by <a href=\"categories.php?manufacturer=".$row['manufacturer']."\" title=\"View all products by ".$row['manufacturer']."\">".$row['manufacturer']."</a></span></h3>";
echo "<p class=\"summary\">".$row['summary']."</p>
<p class=\"descHeading\">&#38;#9758; <em>Full Description:</em></p>
<p class=\"desc\">".$row['desc']."</p>";
echo "</td>\n"; 
echo " <td class=\"col3\">"; 
echo "<p class=\"manufacturer\"><a href=\"categories.php?manufacturer=".$row['manufacturer']."\" title=\"View all products by ".$row['manufacturer']."\">".$row['manufacturer']."</a></p>";
echo "</td>\n"; 
echo " <td class=\"col4\">"; 
echo "<p class=\"price\">$".$row['price']."</p>";
echo "</td>
</tr>\n"; 
}
while($priceRow = mysqli_fetch_array( $priceResult )) {
echo "<tr class=\"short\"><td class=\"col1\"><h3><a href=\"clear_cart.php\">Empty Cart</a></h3></td><td class=\"col2\"></td><td class=\"col3 nobackground\">Total:</td><td class=\"col4\">$".$priceRow[0]."</td></tr>";
}
echo "</tbody>
</table>";
echo "<p class=\"submitOrder\"><form action='submit_order.php' method='post' id='form'>
  <input type='submit' name='pButton' value='Submit Order'>
  <input name='order_total' type='hidden' id='order_total' value='".$priceRow[0]."' />
</form>
</p>";
}
}



And so the cart.php looks something like this (this example asdfasdf has 6 items in his cart):



So you can see that it only displays ONE item, even though asdfasdf DOES have 6 items in his cart (proven by checking database).  And the price is not accurate, either.

Can anyone help me in where my problem might be?  Is it my query?  Or my while clause?

Hi,

I want to restructure my database tables so that I can have one table (t_incidents) to hold foreign keys instead of holding foreign keys in the table "t_persons" because one person can commit more than one offense.

However, I need to know how the join should be implemented with MySQLi prepared statement.

I need some one to review the following statement for me and advise:

if ($stmt = $mysqli->prepare("Select t_persons.PersonID    
                                                                    
        ,t_persons.FamilyName
        ,t_persons.FirstName
        ,t_persons.OtherNames
        ,t_persons.Gender
        ,t_persons.CountryID
        ,t_persons.ImagePath    

        ,t_incidents.IncidentID
        ,t_incidents.Incident
        ,t_incidents.IncidentDate
        ,t_incidents.PersonID
        ,t_incidents.CountryID
        ,t_incidents.OffenceKeywordID
        ,t_incidents.StatusID
        ,t_incidents.AgencyID

        ,t_status.StatusID
        ,t_status.Status

        ,t_offencekeyword.KeywordID
        ,t_offencekeyword.Keyword

        ,t_countries.CountryID
        ,t_countries.Country

        ,t_agencies.AgencyID
        ,t_agencies.Agency

From t_persons
    Inner Join t_incidents On t_persons.PersonID = t_incidents.PersonID
    Inner Join t_incidents On t_countries.CountryID = t_incidents.CountryID
    Inner Join t_incidents On t_status.StatusID = t_incidents.StatusID
    Inner Join t_incidents On t_offenceskeyword.KeywordID = t_incidents.KeywordID
    Inner Join t_incidents On t_agencies.AgencyID = t_incidents.AgencyID

Where t_persons.PersonID = '$PersonID'")) {

Regards.

josephbupe

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

I have a db with 4 tables:
table_A
table_B
table_C
table_D


table_A contains personal info about users and has a primary id field called uid.
Each of tables B, C and D is linked to table_A via the foreign key f_uid.

table_A also has a column called signed_up which can contain a 1 or a 0.

I want to pull up lots of info from each of the 4 tables for every user, as long as the signed_up column in table_A contains a 1.

So, I've made a query like this:

Code: [Select]
$q = "SELECT
table_A . col_1 ,
table_A . col_2 ,
table_A . col_3 ,
table_B . col_1 ,
table_B . col_2 ,
table_B . col_3 ,
table_C . col_1 ,
table_C . col_2 ,
table_C . col_3 ,
table_D . col_1 ,
table_D . col_2 ,
table_D . col_3
FROM
table_A, table_B, table_C, table_D
WHERE
table_A . uid = table_B . f_uid
AND
table_A . uid = table_C . f_uid
AND
table_A . uid = table_D . f_uid
AND
table_A . signed_up = '1'
ORDER BY
table_A . some_col
";
$r = mysqli_query($dbc, $q);
The query is executing but it's also pulling up records where signed_up in table_A contains a '0'. Can anyone see a logical flaw in my query?...

Alternatively, is there an easier way I could build the query?

TIA