PHP - Join 2 Tables To Compare Values

Hey Guys,

I'm struggling with some logic of how to do something wondering if anyone can point me in the right direction.

Details:
I am making a PHP/MySQL game to learn more PHP, I've got my login/logout system created. However I am planning on the game been map based on a 100x100 grid. I have two tables:

USERS
Username, Password, x, y
Test, Test, 1, 1


MAP
X, Y, type
0,1,1
0,2,1
0,3,2
0,4,3
0,5,2
etc

I am stuck on how to get the user's logged in X and Y coordinates and then compare them with the map table X and Y, to see what type of field to show, 1,2 or 3.

Is there a name for this type of function so I can do some research on it? Or could someone give me an example? Would be fantastic if anyone has any advice.

Thanks in advance
NewcastleFan

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 

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


?>

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

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?

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?

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=312429.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

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