PHP - Adding Data And Join Tables

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,

The query below works well. It pulls information from 3 MySQL tables: login, submission, and comment.

It creates a value called totalScore2 based on calculation of values pulled from these three tables.

The MySQL tables "comment" and "submission" both have the following fields:

Code: [Select]
loginid submissionid
In the table "submission," each "submissionid" has only one entry/row, and thus only one "loginid" associated with it.

In the table "comment," the field "submissionid" could have several entries/rows, and could be associated with multiple "loginid"s.

Each time one of the "submissionid"s in "comment" is associated with the same "loginid" that it has in the table "submission," I would like to add this as a factor to the equation below. I would like to multiple instances like this times (-10).

How could I do this?

Thanks in advance,

John

$sqlStr2 = "SELECT 
    l.loginid, 
    l.username, 
    l.created,
    DATEDIFF(NOW(), l.created) + COALESCE(s.total, 0) * 5 + COALESCE(scs.total, 0) * 10 + COALESCE(c.total, 0) AS totalScore2
FROM login l    
LEFT JOIN (
    SELECT loginid, COUNT(1) AS total 
    FROM submission 
    GROUP BY loginid
) s ON l.loginid = s.loginid
LEFT JOIN (
    SELECT loginid, COUNT(1) AS total 
    FROM comment 
    GROUP BY loginid
) c ON l.loginid = c.loginid
LEFT JOIN (
    SELECT S2.loginid, COUNT(1) AS total 
    FROM submission S2
    INNER JOIN comment C2
    ON C2.submissionid = S2.submissionid
    GROUP BY S2.loginid
) scs ON scs.loginid = l.loginid
GROUP BY l.loginid
ORDER BY totalScore2 DESC 
LIMIT 25";

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=352310.0

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=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`))

This topic has been moved to MySQL Help.

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

This topic has been escorted to MySQL Help.

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

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

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 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?

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=308347.0

I'm using an INNER JOIN to grab some data from 2 tables depending on if another's id matches an id within the other table (obviously..  ).

Is there anyway to continue on with this join but to filter even deeper by only taking data that not only has matching some_id but also does not have a row that equals a certain value?

Something along the lines of adding "WHERE some_row != 'value'" ?

"SELECT * FROM my_firstTable INNER JOIN my_secondTable ON my_firstTable.some_id = my_secondTable.some_id ORDER BY my_firstTable.some_id"