PHP - Moved: Which Join To Use For 3 Tables

This topic has been moved to MySQL Help.

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

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

I need to get the friends password and insert it into the table so the friend can delete it later can some one help me do a table join
<?php
//include the connect script
include "../../../../connect.php";

//Post variables from flash
$username = $_POST['username'];
$password = $_POST['password'];
$status = $_POST['status'];
$friend = $_POST['friend'];

$username = stripslashes($username);
$password = stripslashes($password);
$status = stripslashes($status);
$friend = stripslashes($friend);
$username = mysql_real_escape_string($username);
$password = mysql_real_escape_string($password);
$status = mysql_real_escape_string($status);
$friend = mysql_real_escape_string($friend);

$sql = mysql_query("SELECT * FROM user_declined_list  WHERE username = '$username' and password = '$password' and friend = '$friend' and status ='$status'");
$rows = mysql_num_rows($sql);
$your_username=$rows['username'];



if($rows > 0)
{
echo "&msgTextFriendShipRejected= RESEND NEW FRIEND REQUEST SUCCESSFULLY!";

// ok they are now our friend but we are not thier friend so lets insert the friendship in reverse and set the status to 1 on both ends
// Problem i see is this if the user wishs to remove the friend later he will neen his password set to delete from this table.
// so i need to do a join table again where friend password gets inserted here..
$insertnewfriend = mysql_query("INSERT INTO user_friends_list (username,password,friend,status) VALUES ('$friend','$password','$username','0')") or die(mysql_error());
//ok lets update the request and set the friend to be a friend becuase we said yes be my friend.
$deleteuserfriend = mysql_query("DELETE FROM user_declined_list  WHERE username = '$username' and password = '$password' and friend = '$friend' and status ='0'");
return;
}


?>

hi i created a page where users can reply to a post and i saved there reply in a table i called comment.when new visitors click on a topic they can see the topic and the people that comment on the topic..Now my problem is this when i click on any topic it display the comment on that topic whch is good but also display comments from other topics wchich is not supposed to be.i have tried joining the tables but i keep getting syntax errors.is joining the comment table to the topics table the solution?? if so can anyone give me the proper way of joining a table but if there is another way kindly tell me.Thanks 

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 />";
}
?>

I have two tables book and category,    book

id   title           author   category          isbn
1    fun times       Joe         1              16161514 (new record)
2    fishing trip    Jim      Juvenile Science  88771615 (old record) 
3    beach hunt      Sam         3              81009991 (new record)
4    day out         John        3              81009991 (new record)
5    farm fun        Jim      Classic Kids      88771615 (old record)

category

id   cat_name
1    horror 
2    science
3    kids 

I use the join query below to display a list of all book info, works fine.

SELECT b.id, b.title, b.category, b.author, b.isbn, b.publicationYear
        c.cat_name
        FROM book AS b
        INNER JOIN category AS c ON b.category = c.id

The problem I am facing is that all some of the books (1000's actually) in the db have been imported from a previous db, and instead of containing an integer for the category they contain a string such as 'General Fiction' or 'Popular Classics' etc.    Must I import all of these categories into the 'category' table and assign an integer to them, or is there a way of simply displaying the string that is contained within the 'book' table as it stands?   I use the code below to display the html table of results;

while($row = $res->fetch_array()) {
    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>';    
};

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?

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

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

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"}." ";
}
}

I want to display all the records of table 1.  I want to inner join with table 2 so that IF there is a match between tables, table 2 data gets displayed. 

HOWEVER, if there is no match in table 2, then the corresponding record in table 1 does not even get displayed. 

Code: [Select]
$query = "select * from table1
inner join table2 on table1.id = table2.table1_id
How do I display all the records of table 2, and if that id does not match with anything on table 2, STILL display that record?

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

This topic has been moved to MySQL Help.

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