|
PHP - Select And Order By Date From 2 Different Tables
I have 2 tables I have settup called Users_Messages and Users_Message_Replies inside each of these tables I have a row called DateSent, I'm trying to select from both of these tables and only display the latest Sent item by ID and order them all by date. I can get it to display the items correctly using the below code, but I can't get them to order correctly by the latest date in both of the Tables.
Code: [Select] $page_query = mysql_query(" SELECT MessageID, DateSent FROM Users_Messages WHERE ToID = '$user_ID' UNION SELECT MessageID, DateSent FROM Users_Messages WHERE FromID = '$user_ID' ORDER BY DateSent DESC "); while ($replycheck = mysql_fetch_assoc($page_query)){ $message_idmainnew = $replycheck['MessageID']; $date = $replycheck['DateSent']; $sql2 = "SELECT MainMessageID FROM Users_Message_Replies WHERE MainMessageID = '$message_idmainnew '"; $sql_result2 = mysql_query($sql2); $replycheck2 = mysql_fetch_assoc($sql_result2); $newreplyID = $replycheck2['MainMessageID']; $sql4 = "SELECT MessageID FROM Users_Messages WHERE MessageID= '$message_idmainnew'"; $sql_result4 = mysql_query($sql4); $messagecheck2 = mysql_fetch_assoc($sql_result4); $newmessageID = $messagecheck2['MessageID']; if ($newreplyID == NULL){ $sql2 = "SELECT * FROM Users_Messages WHERE MessageID= '$newmessageID' ORDER BY DateSent ASC"; $sql_result2 = mysql_query($sql2); $message_row = mysql_fetch_assoc($sql_result2); $message_01 = $reply_row['Message']; $date = $reply_row['DateSent']; $date1 = strtotime($date); $datemain = date('F j, Y, g:i a', $date1); }else{ $sql2 = "SELECT * FROM Users_Message_Replies WHERE MessageID= ' $newreplyID ' ORDER BY DateSent DESC"; $sql_result2 = mysql_query($sql2); $reply_row = mysql_fetch_assoc($sql_result2); $message_01 = $reply_row['Message']; $date = $reply_row['DateSent']; $date1 = strtotime($date); $datemain = date('F j, Y, g:i a', $date1); } { Is there an easier way to do this? And how would I get the dates to line up, with a Join? Thanks. Similar TutorialsHi ho, I am trying to assemble a table so that it shows article in order by id descending. The current one i am using is working but not displaying as i hoped, i need them aligned on the left and right, so id=1 align left, id=2 align right etc. Here's my current code Code: [Select] <table style='table-layout:fixed' align="center" cellpadding="2" cellspacing="0" colspan="" class="Main"><br> <? $select_paper=mysql_query("SELECT * FROM paper ORDER BY id DESC"); while($the=mysql_fetch_object($select_paper)){ ?> <tr> <td class="subtableheader" width="25%"><?php echo "$the->title"; ?></td> </tr> <tr> <td class="profilerow" width="100%"><?php echo "$the->news"; ?></td> </tr> <tr> <td class="subtableheader" width="25%"><?php echo "Article By $the->by - $the->date"; ?></td> </tr> Im doing a search system and Im having some problems.
I need to search in two tables (news and pages), I already had sucess doing my search system for just one table, but for two tables its not easy to do.
I already use a select statment with two tables using UNION because I want to show number of search results, that is number of returned rows of my first sql statment.
But now I need to do a select statment that allows me to acess all fields of my news table and all fields of my pages table.
I need to acess in my news table this fields: id, title, content, link, date, nViews
I need to acess in my pages table this fields: id, title, content, link
Im trying to do this also with UNION, but in this case Im not having any row returning.
Do you see what I have wrong in my code?
<?php //first I get my $search keyword $search = $url[1]; $pdo = connecting(); //then I want to show number of returned rows for keyword searched $readALL = $pdo->prepare("SELECT title,content FROM news WHERE title LIKE ? OR content LIKE ? UNION SELECT title,content FROM pages WHERE title LIKE ? OR content like ?"); $readALL->bindValue(1,"%$search%", PDO::PARAM_STR); $readALL->bindValue(2,"%$search%", PDO::PARAM_STR); $readALL->bindValue(3,"%$search%", PDO::PARAM_STR); $readALL->bindValue(4,"%$search%", PDO::PARAM_STR); $readALL->execute(); //I show number of returned rows echo '<p>Your search keyword returned <strong>'.$readALL->rowCount().'</strong> results!</p>'; //If dont return any rows I show a error message if($readALL->rowCount() <=0){ echo 'Sorry but we didnt found any result for your keyword search.'; } else{ //If return rows I want to show, if it is a page result I want to show title and link that I have in my page table //if it is a news result I want to show title and link that I have in my news table and also date of news echo '<ul class="searchlist">'; $readALL2 = $pdo->prepare("SELECT * FROM news WHERE status = ? AND title LIKE ? OR content LIKE ? LIMIT 0,4 UNION SELECT * FROM pages where title LIKE ? OR content LIKE ? LIMIT 0,4"); $readALL2->bindValue(1, '1'); $readALL2->bindValue(2, "%$search%", PDO::PARAM_STR); $readALL2->bindValue(3, "%$search%", PDO::PARAM_STR); $readALL2->bindValue(4, "%$search%", PDO::PARAM_STR); $readALL2->execute(); while ($result = $readALL2->fetch(PDO::FETCH_ASSOC)){ echo '<li>'; echo '<img src="'.BASE.'/uploads/news/'.$result['thumb'].'"/>'; echo '<a href="'.BASE.'/news/'.$result['id_news'].'">'.$result['title'].'</a>'; //if it is a news result I also want to show date on my list //echo '<span id="date">'.$result['date'].'</span>'; echo '</li>'; } echo ' </ul>'; //but how can I do my select statement to have access to my news table fields and my page table fields?? } ?> hirealimo.com.au/code1.php this works as i want it: Quote SELECT * FROM price INNER JOIN vehicle USING (vehicleID) WHERE vehicle.passengers >= 1 AND price.townID = 1 AND price.eventID = 1 but apparelty selecting * is not a good thing???? but if I do this: Quote SELECT priceID, price FROM price INNER JOIN vehicle....etc it works but i lose the info from the vehicle table. but how do i make this work: Quote SELECT priceID, price, type, description, passengers FROM price INNER JOIN vehicle....etc so that i am specifiying which colums from which tables to query?? thanks Greetings! I am really stock on this situation. 1. I have a variable $uc_order_created. 2. I have a code like this: <b><?php echo t('Due Date:'); ?></b> </td> <td width="98%"> <?php $duedate = mktime(0,0,0,date("m"),date("d")+30,date("Y")); echo "".date("F jS Y", $duedate);?> 3. How do I code if I want the result of $uc_order_created get displayed as Due Date using the code above? Or other modifications? 4. The situation now, the code is working when printing the invoice but its not based on $uc_order_created. So, every time I will edit the Order because of the change of items among others the Due Date will also update. All I want that when Order is edited the value of Due Date will still remain based on the $uc_order_created. Thank you so much in advance for your help. I teach 6 different class groups, named "A" to "F", respectively. Hello, I would like to order by date that contains day and month, like this: 14-3 Is this possible? Quote $sql = mysql_query("SELECT * FROM date WHERE online='1'"); This topic's house has been seized by the bank and it now lives in MySQL Help until it can get back on its feet. http://www.phpfreaks.com/forums/index.php?topic=358035.0 Hello.. I'm a somewhat beginner in php, but i decided to create my own basic forums system with a content managing system. Right now I'm trying to figure out how to make a thread link be at the top of the list for the forum display page, when someone posts a response or creates a new thread. HELP MUCH APPRECIATED How can i select the last 3 rows in a table and order it by ASC. When i use DESC it displays the results in the wrong order. $query24 = mysql_query("SELECT * FROM notifications WHERE to_id='$session' AND state='1' ORDER BY id DESC LIMIT 3 "); This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=345631.0 This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=321906.0 Hey guys, I am having trouble figuring this out. I currently have a database that automatically sorts itself by the id of the data. But, I am wanting the user to be able to change the sort order via a select box. I already have the select box programmed with the options, but I am not sure how to go about coding the options to change the sort order of the displayed data. What would the best way to go about programming this be? Thank you very much ahead of time!! I have a posting system worked out, as well as my database configured to my linking.. But as of right now, my posts are posted from oldest to newest, having users scroll down or go to the next page to see the most recent post.
How would I "flip" that around and make my posts start from newest to oldest by its date/time (Which is stored in the database)?
Thanks!
I have 2 queries that I want to join together to make one row
how do i grab and print on page in date order all .jpg files in a folder ? I have a calendar select date function for my form that returns the date in the calendar format for USA: 02/16/2012. I need to have this appear as is for the form and in the db for the 'record_date' column, but I need to format this date in mysql DATE format (2012-02-16) and submit it at the same time with another column name 'new_date' in the database in a hidden input field. Is there a way to do this possibly with a temporary table or something? Any ideas would be welcome. Doug Hi there I have 2 tables: Fleet FleetName PlanetName Status Planet PlanetName PlayerName Im trying to write a select query that will search through the fleet table and display Fleet table details Where the PlanetName from the fleet table is = to the PlanetName of the Planet table. Ive tried joining the tables but i dont think the logic quite works.. Code: [Select] mysql_select_db($database_swb, $swb); $query_Fleet = sprintf("SELECT fleet.FleetName, planet.PlanetName FROM fleet, planet WHERE fleet.PlanetName = planet.PlanetName"); $Fleet = mysql_query($query_Fleet, $swb) or die(mysql_error()); $row_Fleet = mysql_fetch_assoc($Fleet); $totalRows_Fleet = mysql_num_rows($Fleet); I want ONLY the Fleets from the planet i am looking at to be displayed not all of them. I have created another query that displays the planet name of the particular one I am looking at as it is parsed from a hyperlink on a previous page. So is there some way i can use both queries together? "SELECT FROM fleet, WHERE $fleet.PlanetName = $planet.PlanetName" Im a little bit confussed, please help hey, i'm trying to select information from 2 tables in one query... so i have 2 tables... members - (holds the members information) looks like this: Code: [Select] +---+--------+------------+------------+ | id | name | email | password | +---+--------+------------+------------+ | 1 | user1 | 1@g.com | **** | | 2 | user2 | 2@g.com | **** | | 3 | user3 | 3@g.com | **** | | 4 | user4 | 4@g.com | **** | | 5 | user5 | 5@g.com | **** | | 6 | user6 | 6@g.com | **** | +---+--------+------------+------------+ the second table is profile_views which stores the profile views... Code: [Select] +---+--------+-----------+---------------+ | id | userid | viewerid | time | +---+--------+-----------+---------------+ | 1 | 4 | 1 | 1287949172 | | 2 | 6 | 2 | 1287949172 | | 3 | 2 | 4 | 1287949172 | | 4 | 4 | 5 | 1287949172 | | 5 | 3 | 2 | 1287949172 | +---+--------+-----------+---------------+ userid - the profile (member) id that been watched viewerid - the id of the member who watched time - the time this happened im trying to select from profile_views the viewers ids' and select the info from the members according to the viewerid, but also i want to select the time from the profile_views, so it's should look like this: Code: [Select] +---+--------+------------+----------+------------+ | id | name | email | password | time +---+--------+------------+----------+------------+ | 1 | user1 | 1@g.com | **** | 1287949172 | 2 | user2 | 2@g.com | **** | 1287949172 | 3 | user3 | 3@g.com | **** | 1287949172 | 4 | user4 | 4@g.com | **** | 1287949172 | 5 | user5 | 5@g.com | **** | 1287949172 | 6 | user6 | 6@g.com | **** | 1287949172 +---+--------+------------+----------+------------+ this is my code: Code: [Select] $DB->query("SELECT viewerid FROM profile_views WHERE userid={$core->input['showuser']} ORDER BY time DESC LIMIT 5"); if ( $DB->get_num_rows() > 0 ) { while ( $all_viewers = $DB->fetch_row() ) { $viewers_ids[] = $all_viewers['viewerid']; } $viewers_ids = implode(",", $viewers_ids); $DB->query("SELECT m.*, v.* FROM members m LEFT JOIN profile_views v ON (v.userid=m.id) WHERE m.id IN ({$viewers_ids})"); while ( $viewers = $DB->fetch_row() ) { } } but it's just dont work! any ideas? Thanks in advance! hey, i trying to select * colums form one table and 1 colum from another table... this is my code: Code: [Select] $DB->query("SELECT m.*, v.time, v.viewerid FROM members m LEFT JOIN profile_views v ON (m.id=v.userid) WHERE m.id IN ({$viewers_ids})"); but sometimes this query return multiply rows... any ideas? Thanks! So I have this program I purchased and it allows me to create custom form fields. I have been trying to create a date select box but have been struggling because I am just learning php. I was wondering if someone could lend a hand. I will try and give as much info as i can. First I will show a picture of the custom field box and then i will give the description of both boxes that the company gave me. Next i will give you the php code i have been trying to work with. Please if my code is not the best then i will take any advice. I found the code for the date selector online. Parsed Default Value - You may specify a variable or a function as the default value; for example $_SERVER[HTTP_USER_AGENT] or mktime() Parsed PHP Code - You can specify actual PHP code that will be used to return a variable. // You should assign the value you wish to use to the variable $str. Code: [Select] <?PHP FUNCTION buildDate($name, $m, $d, $y) { $date = DATE("m-d-Y",STRTOTIME("now")); $date_array = EXPLODE("-",$date); $now_m = ""; $now_d = ""; $now_y = ""; $month = '<select class="small" name="'.$name.'_month">'; $month .= '<option value="" selected disabled>mm</option>'; FOR ($i=1;$i<=12;$i++) { IF ( $m != "" ) { IF ( $m == $i ) { $now_m = "selected"; } }ELSEIF ( $m == "0" ) { $now_m = ""; }ELSE{ IF ( $date_array[0] == $i ) { $now_m = "selected"; } } $month .= '<option value="'.$i.'" '.$now_m.'>'.$i.'</option>'; $now_m = ""; }//END month for loop $month .= '</select>'; $day = '<select class="small" name="'.$name.'_day">'; $day .= '<option value="" selected disabled>dd</option>'; FOR ($i=1;$i<=31;$i++) { IF ( $d != "" ) { IF ( $d == $i ) { $now_d = "selected"; } }ELSEIF ( $d == "0" ) { $now_d = ""; }ELSEIF ( $d == $i ) { $now_d = "selected"; }ELSE{ IF ( $date_array[1] == $i ) { $now_d = "selected"; } } $day .= '<option value="'.$i.'" '.$now_d.'>'.$i.'</option>'; $now_d = ""; }//END day for loop $day .= '</select>'; $year = '<select class="small" name="'.$name.'_year">'; $year .= '<option value="" selected disabled>yyyy</option>'; FOR ($i=GMDATE("Y"); $i <=SUBSTR(get330Date(),0,4); $i++) { IF ( $y != "" ) { IF ( $y == $i ) { $now_y = "selected"; } }ELSEIF ( $y == "0" ) { $now_y = ""; }ELSEIF ( $y == $i ) { $now_y = "selected"; }ELSE{ IF ( $date_array[2] == $i ) { $now_y = "selected"; } } $year .= '<option value="'.$i.'" '.$now_y.'>'.$i.'</option>'; $now_y = ""; }//END year for loop $year .= '</select>'; //------------------------------------------- ECHO $month." ".$day." ".$year; }//END buildDate function // ------------------------------------ FUNCTION get330Date() { RETURN DATE("Y-m-d", MKTIME(0, 0, 0, SUBSTR(GMDATE("Y m d"),5,2), SUBSTR(GMDATE("Y m d"),8,2) + 330, SUBSTR(GMDATE("Y m d"),0,4)))."<br />"; } ?> I might just be totaly wacked here with what i am trying to do but i am sure someone will tell me one way or the other. lol |
|||||||