|
PHP - Mysql Join For Four Tables Yielding Unexpected Results
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'.
Similar TutorialsThis 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! 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. 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=308347.0 I need to echo the "reward.id" Code: [Select] $sql = "select reward.id, reward.location, reward.industry, reward.title, reward.stamp, industry.id, industry.label, industry.location FROM reward LEFT JOIN industry ON industry.id = reward.industry where (industry.label = '".$_GET['ind']."' and industry.location = '".$_GET['loc']."')"; $result = mysql_query($sql) or die(mysql_error()); while($info = mysql_fetch_assoc($result)){ echo $info['HOW TO I PRINT reward.id']; }free($result ); Any thoughts? I need to set up pagination. I've seen a tutorial online, and in a book I have. But they're both for basic queries. I've got a LEFT JOIN. The basic script doesn't seem to work. Is there a different way it has to be done for joins? Hi there i have 3 tables joined together and am outputting the results ok. Heres what the results look like: Fleet Name: Home One Detected: No Ship Name: Slave 1 Bobafett Hull: 3 / 2 Fleet Name: Home Two Detected: Yes Ship Name: Imperial Star Destroyer Hull: 2 / 4 Fleet Name: Home Two Detected: Yes Ship Name: Executer Hull: 10 / 10 The 3 tables join together by the following: Planet = parsed url parameter Fleets = fleets on the current planet Ships = current ships in the fleets Its all working fine except for one thing, the Fleet Name is repeated for each occurance of a ship. I want to make it so that Fleet Name only occurs once for each fleet so it would look something like: Fleet Name: Home One Detected: No Ship Name: Slave 1 Bobafett Hull: 3 / 2 Fleet Name: Home Two Ship Name: Imperial Star Destroyer Detected: Yes Hull: 2 / 4 Ship Name: Executer Detected: Yes Hull: 10 / 10 Is this possible??? Heres the code in full: Code: [Select] <?php require_once('Connections/swb.php'); ?> <?php if (!function_exists("GetSQLValueString")) { function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue); switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue; } } $colname_Planet = "-1"; if (isset($_GET['recordID'])) { $colname_Planet = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']); } mysql_select_db($database_swb, $swb); $query_Planet = sprintf("SELECT * FROM planet WHERE PlanetName = %s", GetSQLValueString($colname_Planet, "text")); $Planet = mysql_query($query_Planet, $swb) or die(mysql_error()); $row_Planet = mysql_fetch_assoc($Planet); $totalRows_Planet = mysql_num_rows($Planet); $colname_Fleet = "-1"; if (isset($_GET['recordID'])) { $colname_Fleet = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']); } mysql_select_db($database_swb, $swb); $query_Fleet = sprintf("SELECT p.PlanetName, p.PlayerName, f.FleetName, f.Detected, s.ShipName, s.HealthA, s.HealthB FROM Planet p LEFT JOIN Fleet f ON (p.PlanetName = f.PlanetName) LEFT JOIN Ships s ON (f.FleetName = s.FleetName) WHERE p.PlanetName = '$colname_Planet'"); $Fleet = mysql_query($query_Fleet, $swb) or die(mysql_error()); $row_Fleet = mysql_fetch_assoc($Fleet); $totalRows_Fleet = mysql_num_rows($Fleet); ?> <head> <title>Untitled Document</title> <style type="text/css"> <!-- body { background-color: #FFFFFF; } --> </style></head> <body> <table width="60%" border="0"> <tr> <td width="10%">Planet:</td> <td width="14%"><?php echo $row_Planet['PlanetName']; ?></td> <td width="7%" rowspan="8"> </td> <td colspan="2" rowspan="8"> <?php while ($row_Fleet = mysql_fetch_assoc($Fleet)) { echo '<table width="400" border="0"><tr><td width="200">'; echo 'Fleet Name: '; echo'</td><td>'; echo $row_Fleet['FleetName']; echo'</td></tr><tr><td>'; echo 'Detected: '; echo'</td><td>'; if ($row_Fleet['Detected'] == '1'){ echo 'Yes';} else echo 'No'; echo'</td></tr><tr><td>'; echo 'Ship Name: '; echo'</td>'; echo'<td>'; echo $row_Fleet['ShipName']; echo'</td></tr><tr><td>'; echo 'Hull: '; echo'</td><td>'; echo $row_Fleet['HealthA']; echo ' / '; echo $row_Fleet['HealthB']; echo '</td></tr><tr><td></td></tr>'; echo'</table>'; } ?> <tr> <td>Faction:</td> <td><?php echo $row_Planet['PlayerName']; ?></td> </tr> <tr> <td>Infastructure</td> <td> </td> </tr> <tr> <td>Class 1: </td> <td><?php echo $row_Planet['Class1']; ?></td> <tr> <td>Class 2: </td> <td><?php echo $row_Planet['Class2']; ?></td> </tr> <tr> <td>Class 3: </td> <td><?php echo $row_Planet['Class3']; ?></td> </tr> <tr> <td>Class 4: </td> <td><?php echo $row_Planet['Class4']; ?></td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td> <a href="index.php target="main2.php"">Back</a></td> <td> </td> <td> </td> <td width="43%"> </td> <td width="26%"> </td> </tr> </table> </body> </html> <?php mysql_free_result($Planet); mysql_free_result($Fleet); ?> Thanks 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); 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 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; } ?> 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 kidsI 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.idThe 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>';
};
This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=312429.0 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`)) 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 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,
JThis topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=306274.0 |
|||||||