Search the Community

So I have been searching and doing trial and error for several days now, and Im turning to here for help. I have created a page on my website that pulls a schedule information from a table on a database and displays it to the webpage. I am trying to pull a color scheme from a second table in the same DB, when the name on the first table matches the name on the second table, only that name will be displayed in the different color assigned to it. MY tables look as follows: Schedule: id time timeframe Monday Tuesday Wednesday Thursday Friday Saturday Sunday 1 12a 00:00:00 Name1 Name2 Name3 Name2 Name5 Name6 Name7 2 1a 01:00:00 Name1 Name2 Name3 Name2 Name5 Name4 Name3 3 2a 02:00:00 Name1 Name2 Name4 Name2 Name2 Name6 Name2 4 3a 03:00:00 Name2 Name2 Name4 Name2 Name3 Name3 Name2 ect. dj_colors: TBL2: id name color 1 Name1 #hexcolor 2 Name2 #hexcolor2 3 Name3 #hexcolor3 ect $query = array('select' => "*", 'tbl' => "Schedule"); $name = "select t1.*, Monday.color, Tuesday.color, Wednesday.color, Thursday.color, Friday.color, Saturday.color, Sunday.color FROM Schedule t1 JOIN dj_colors Monday ON t1.Monday = Monday.name, JOIN dj_colors Tuesday ON t1.Tuesday = Tuesday.name, JOIN dj_colors Wednesday ON t1.Wednesday = Wednesday.name, JOIN dj_colors Thursday ON t1.Thursday = Thursday.name, JOIN dj_colors Friday ON t1.Friday = Friday.name, JOIN dj_colors Saturday ON t1.Saturday = Saturday.name, JOIN dj_colors Sunday ON t1.Sunday = Sunday.name"; $DB = new DB(); $result = $DB->select_multi($query); foreach ($result as $arrayLoop) { $printout .= "<tr>"; foreach ($arrayLoop as $field => $data) { if ($field == 'id' || $field == 'TimeFrame') continue; if ($data === $name) $printout .= "<td color=".$color.">".$data."</td>"; else $printout .= "<td class='schedule4'>".$data."</td>"; } $printout .= "</tr>"; } I am pulling names from the Schedule db and posting them to a website. That part works fine. I am also trying to make it to where if the name in the field from db Schedule matches the name on db dj_colors, that name will be displayed in the color assigned to that name in dj_colors. I am at a loss here (I am still new to php coding and just learning the JOIN function). I am not wanting anyone to do it for me, but point me in the right direction anf give me some helpful pointers on where I am going wrong, please.

MYSQL version: 5.5.27-cll - MySQL Community Server (GPL) Hey I have a simple articles and comments table. I want to display the articles along with their comments. I want to union with one select with the comments and another select without the comments on the same table. I have article number 1 with 1 comment and article number 2 with no comments and article number 3 with 2. I use the following query to get the results. SELECT articles.id AS article_id, comments.id AS comment_id, comment FROM articles LEFT JOIN comments ON comments.aid = articles.id UNION ALL SELECT articles.id AS article_id, NULL, NULL FROM articles GROUP BY article_id ORDER BY article_id DESC The result I get which is correct: article_id | comment_id | comment 3 | 3 | good 3 | 2 | very good 3 | NULL | NULL 2 | NULL | NULL 2 | NULL | NULL 1 | NULL | NULL 1 | 1 | | bad one Now if I want to count the comments also I add COUNT to the query and it becomes: SELECT articles.id AS article_id, comments.id AS comment_id, comment , COUNT(DISTINCT comments.id) AS count_comments FROM articles LEFT JOIN comments ON comments.aid = articles.id UNION ALL SELECT articles.id AS article_id, NULL, NULL , NULL FROM articles GROUP BY article_id ORDER BY article_id DESC Now the results change after adding the the count column and not all rows are outputted: article_id | comment_id | comment 3 | NULL | NULL 2 | NULL | NULL 1 | NULL | NULL 1 | 1 | bad one Now the comments aren't displayed except the comment of article 1, ID(2) should be displayed twice for the 2 select commands, and ID(3) should be displayed 3 times (1 for the second select command and 2 for the first select commands as there are 2 comments) I don't know why adding count leads to ths. Thanks

I have a table in MYSQL as follows Id-------NAME 1-------Black 2-------Red-- 3-------Green 4-------Black 5-------Green 6-------Yellow 7-------Orang 8-------White 9-------Black 10-------violet 11-------maroA 12-------indiA 13-------Blue 14-------Yellow 15-------Orane 16-------White 17-------White 18-------White I need to get the id of each color names where the condition matches unique names for each set of five rows and if any duplicate occurs then it has to be moved to end of the result. Note: I don’t want the count (max/min) as a result for each color names output: ID: 1 8 6 NAME: Black | white | yellow | violet | orange | - Black | white | yellow | violet | orange | - Blue| maroon | white| white | white | Kindly guide if this is possible to get MYSQL query

Hello, guys! I have a really big issue right now, I've been trying to solve this for weeks but I can't find the solution. I have a website (it's kind of a hosting site, for example www.randomhost.com) which has an administrator's section. So if someone creates an account they can post something on their own subpage (user.randomhost.com) via admin's section. The user also has to input their facebook page in the settings (not their personal page but something like a fan-page or a group-page) AND their personal page and of course all the data needed to log into facebook with both accounts (fan-page and personal page). If someone posts something to their own subpage (user.randomhost.com), I want that exact same post to be created on the given fan-page or group-page and seen posted by the user's given personal account. A tried to make it as clear as possible. I can't find the solution, I hope you guys can help. Thanks in advance. VC

Here is my dilemma. I have table 'mail'. I need to select all mail rows from the mail table based on two conditions: 1) There is only one message. There have not yet been any replies(orig_mess = 0). This would need to select the only message in the database in this condition. 2) There are one or more message related to the original message. This would be indicated by orig_mess = 'oringal message id'. If there are any responses to any of the original messages I need to retrieve them all and then order by their id. Here is a table example: mail.table id from_user to_user orig_mess message_body 1 37 54 0 Hello 2 54 37 1 Hello back 3 60 37 0 Test The task is that I need to display new mail messages. It could be a new conversation, or it could be a new message based off an older conversation. In either case I need to pull the most current(highest id) message and display that first. I then need to loop through any older messages(if applicable to the original) and display them from newest to oldest as well. I have tried and failed attempts to grab it all from one query, but that is what I want to do. If I can't do it from one, I would be fine with looping through a second query to get older messages. Any ideas on how this could be accomplished? Thanks. This is all I have at the moment: $result = mysql_query("SELECT Mail.id FROM Mail WHERE Mail.to_user='$_SESSION[user_id]' AND Mail.orig_mess='0' AND tbl.id2!=Mail.id UNION SELECT MAX(Mail.id) FROM Mail WHERE Mail.orig_mess!='0' ORDER BY id DESC")or die(mysql_error()); while($row = mysql_fetch_array($result)){

I am a beginner with MySQL and was wondering what method advanced users would recommend to perform the simple task of looking through two tables to find one row. I don't need code from you, just a general idea of what type of query is best for this situation. If you can offer an explanation of why you like the method, that's even better. Thanks!

I've been stuck on this for a couple of hours now & I'd really appreciate some help(!) I have 2 tables: entries +----+--------+------+-------+------------+ ¦ id ¦ status ¦ type ¦ order ¦ date ¦ +----+--------+------+-------+------------+ ¦ 1 ¦ 1 ¦ term ¦ 4 ¦ 0000-00-00 ¦ ¦ 2 ¦ 0 ¦ item ¦ 6 ¦ 0000-00-00 ¦ ¦ 3 ¦ 1 ¦ term ¦ 1 ¦ 0000-00-00 ¦ ¦ 4 ¦ 1 ¦ term ¦ 7 ¦ 0000-00-00 ¦ ¦ 5 ¦ 0 ¦ item ¦ 5 ¦ 0000-00-00 ¦ ¦ 6 ¦ 1 ¦ term ¦ 8 ¦ 0000-00-00 ¦ ¦ 7 ¦ 1 ¦ term ¦ 3 ¦ 0000-00-00 ¦ ¦ 8 ¦ 0 ¦ item ¦ 2 ¦ 0000-00-00 ¦ +----+--------+------+-------+------------+ entry_text +----+----------+----------+---------+---------+ ¦ id ¦ entry_id ¦ language ¦ name ¦ value ¦ +----+----------+----------+---------+---------+ ¦ 1 ¦ 7 ¦ en ¦ title ¦ varchar ¦ ¦ 2 ¦ 7 ¦ en ¦ content ¦ varchar ¦ ¦ 3 ¦ 7 ¦ fr ¦ title ¦ varchar ¦ ¦ 4 ¦ 7 ¦ fr ¦ content ¦ varchar ¦ ¦ 5 ¦ 3 ¦ en ¦ title ¦ varchar ¦ ¦ 6 ¦ 3 ¦ en ¦ content ¦ varchar ¦ ¦ 7 ¦ 3 ¦ fr ¦ title ¦ varchar ¦ ¦ 8 ¦ 3 ¦ fr ¦ content ¦ varchar ¦ ¦ 9 ¦ 6 ¦ en ¦ title ¦ varchar ¦ ¦ 10 ¦ 6 ¦ en ¦ content ¦ varchar ¦ ¦ 11 ¦ 6 ¦ fr ¦ title ¦ varchar ¦ ¦ 12 ¦ 6 ¦ fr ¦ content ¦ varchar ¦ ¦ 13 ¦ 2 ¦ en ¦ title ¦ varchar ¦ ¦ 14 ¦ 2 ¦ en ¦ content ¦ varchar ¦ ¦ 15 ¦ 2 ¦ fr ¦ title ¦ varchar ¦ ¦ 16 ¦ 2 ¦ fr ¦ content ¦ varchar ¦ +----+----------+----------+---------+---------+ What I'm trying to do is some sort of join statement like: select * from entries where status = 1 and type = term join name,value from entry_text where entry_id = entries.id and language = en order by entries.order, entries.date ASC where I end up with an associative result, indexed by entries.id with the result from entry_text being a name/val pair nested array: array( [3] => array( [id] => 3 [status] => 1 [type] => term [order] => 1 [date] => 0000-00-00 [text] => array( [title] => varchar [content] => varchar ) ) [7] => array( [id] => 7 [status] => 1 [type] => term [order] => 3 [date] => 0000-00-00 [text] => array( [title] => varchar [content] => varchar ) ) [1] => array( [id] => 1 [status] => 1 [type] => term [order] => 4 [date] => 0000-00-00 [text] => array( [title] => varchar [content] => varchar ) ) etc... ) Doable...? (sorry for the wonky formatting - I can't get the editor to preserve my spacing)

Hey guys, This is my first proper php app. so be nice I have a mysql_query that joins a two tables, it's working properly, but I want to add one extra variable with the results. $topRanked = mysql_query("SELECT * FROM news JOIN users ON news.Username = users.Username WHERE users.connected='$yourname' ORDER BY created DESC") or die(mysql_error()); How this works is, I have 2 tables, one containing news stories and one with users...the users can be linked with one another, therefor you see their story. This part works GREAT, but the user doesn't see his own stories. Currenly it's getting every news story, except the users news story...how can I modify the QUERY that it also includes the users news stories?

I have the following tables: USERS -id -fname -lname COMPANIES -id -name -type PRODUCTS -id -product -restaurant (linked to companies.id) -vendor (linked to companies.id) -user (linked to users.id) -transaction_date I have the following code to try to output data from the Products table but it isn't working: $sql = mysql_query("SELECT products.*, companies.name FROM products INNER JOIN companies ON products.vendor = companies.id ON products.restaurant = companies.id WHERE vendor='$id' AND companies.type='$type'") or die(mysql_error()); $num=mysql_num_rows($sql); <th><h5>Date</h5></th> <th><h5>Meal</h5></th> <th><h5>Restaurant</h5></th> <th><h5>Vendor</h5></th> $i=0; while ($i < $num) { $f1=mysql_result($result,$i,"transaction_date"); $f2=mysql_result($result,$i,"product"); $f3=mysql_result($result,$i,"restaurant"); $f4=mysql_result($result,$i,"vendor"); <td><?php echo $f1; ?></td> <td><?php echo $f2; ?></td> <td><?php echo $f3; ?></td> <td><?php echo $f4; ?></td> $i++; } I looked on another site and found this answer: http://stackoverflow...-keys-php-mysql but that doesn't help. The problem is that I can only get either the restaurant or vendor name to show but not both. The $id and $type are created with the session, fyi. Any ideas? Thank you

Heya I've been having a hard time getting my mysql query to do as I want. SELECT m.team, SUM(pc.points) AS total_points FROM members AS m JOIN pointscount AS pc ON pc.tournamentid = m.tournament JOIN persons AS p ON p.pid = m.person WHERE p.secret = 1 AND m.tournament = 5 AND pc.day < 8 GROUP BY m.team SELECT m.team, SUM(pc.points) AS total_points FROM members AS m JOIN pointscount AS pc ON pc.tournamentid = m.tournament JOIN persons AS p ON p.pid = m.person WHERE p.secret = 0 AND m.tournament = 5 GROUP BY m.team Yeah, this is what I've been writing. The table `members` contain information about what kind of tournament and team a certain person is in. The table `pointscount` contain information about how many points a person has got each day of a certain tournament, one row per person per day per tournament. So yes, they are grouped as being UNIQUE together. The table `persons` contains the id of a person, but it also contains a field called secret, which if is set to true is supposed to be used to keep the points they've obtained secret after a certain date. What I want the query to do: Get the team name and get the total of points this team has earned, but this with a little twist. I want the second query to only sum the amount of points earned by the team where it's members have had their secret field in the person table set to false. The first one wants to do almost the opposite, but instead of getting everyone who has set their secret field in the person table to true, it wants to only get the point they've earned before the 8th day. o.o' Hopefully this makes any sense. Thanks in advance.