Guber-X Posted November 5, 2011 Share Posted November 5, 2011 yeah im back again with yet another thing i need some help lol. what im trying to do is set up a "Feature" box that will pull data from multiple tables by date. as of right now i would like to pull data from my "news" table and my "event" table. both need to be displayed differently. table contents: News ------- id date header news edate Event ------- id eventdate cdate header description image location so now I would like to make the mysql_query to get the info from both tables and list them by recent date. "date" and "cdate" are the dates i want the query to read from and of course display from most recent. Quote Link to comment Share on other sites More sharing options...
jcbones Posted November 5, 2011 Share Posted November 5, 2011 So order the query by the date column descending. Should give you the rows you desire. Quote Link to comment Share on other sites More sharing options...
Guber-X Posted November 6, 2011 Author Share Posted November 6, 2011 well, i dont know if i explained what im trying to do the very well.. so ill try this again lol okay so what i need is to "Display" two different tables in the same section by date. So if i have a "News" post thats newer than an "Event" post the "News" post will be first in line to be displayed. But for laying out the details for each table needs to be different. Somethin like this if order or recent dates go News, Event, Event, News ====Featured============================================ || || || || || || News header || Event Header || Event Header || News Header || || Date || Date || Date || Date || || Details up to || image || image || Details up to || || 96 char || || || 96 char || || || || || || ====================================================== Quote Link to comment Share on other sites More sharing options...
xyph Posted November 6, 2011 Share Posted November 6, 2011 You could use a union, assuming both date columns are names the same. Keep in mind this can mess up expected column names. (SELECT `id`,`date`,`img` FROM `events`) UNION (SELECT `id`,`date`,`description` FROM `news`) ORDER BY `date` DESC will return +----+---------------------+--------------+ | id | date | img | +----+---------------------+--------------+ | 1 | 2011-11-07 10:29:38 | tomorrows | | 1 | 2011-11-06 10:29:11 | todays | | 2 | 2011-11-05 10:29:19 | yesterdays | | 2 | 2011-11-04 10:29:47 | two days ago | +----+---------------------+--------------+ 4 rows in set (0.00 sec) With the following set of data -- -- Table structure for table `events` -- CREATE TABLE IF NOT EXISTS `events` ( `id` int(11) NOT NULL AUTO_INCREMENT, `date` datetime NOT NULL, `img` varchar(50) NOT NULL, UNIQUE KEY `id` (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ; -- -- Dumping data for table `events` -- INSERT INTO `events` (`id`, `date`, `img`) VALUES (1, '2011-11-07 10:29:38', 'tomorrows'), (2, '2011-11-04 10:29:47', 'two days ago'); -- -------------------------------------------------------- -- -- Table structure for table `news` -- CREATE TABLE IF NOT EXISTS `news` ( `id` int(11) NOT NULL AUTO_INCREMENT, `date` datetime NOT NULL, `description` varchar(96) NOT NULL, UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ; -- -- Dumping data for table `news` -- INSERT INTO `news` (`id`, `date`, `description`) VALUES (1, '2011-11-06 10:29:11', 'todays'), (2, '2011-11-05 10:29:19', 'yesterdays'); Quote Link to comment Share on other sites More sharing options...
Guber-X Posted November 7, 2011 Author Share Posted November 7, 2011 for some reason, it does not like the UNION query... i have tried it many different ways after doing some research on the mysql_query union. am i doing something wrong? ill list the ways ive tried with the Union query $result = mysql_query("SELECT * FROM news UNION SELECT * FROM event ORDER by date"); if(!$result){ die("query failed: " . msql_error()); } $sql = "SELECT * FROM news UNION SELECT * FROM event"; $result = mysql_query($sql); if(!$result){ die("query failed: " . msql_error()); } $sql = "(SELECT * FROM 'news') UNION (SELECT * FROM 'event')"; $result = mysql_query($sql); if(!$result){ die("query failed: " . msql_error()); } Quote Link to comment Share on other sites More sharing options...
xyph Posted November 7, 2011 Share Posted November 7, 2011 You need the quotes around each SELECT for ORDER BY to work. I can help you further if you tell me what's going wrong. Quote Link to comment Share on other sites More sharing options...
Guber-X Posted November 7, 2011 Author Share Posted November 7, 2011 anything i try i get this error report Fatal error: Call to undefined function msql_error() in C:\xampp\htdocs\FFY\contact.php on line 29 and line 29 in my code is: 26|| <?php 27|| 28|| $result = mysql_query("'SELECT * FROM news' UNION 'SELECT * FROM event' ORDER by date") 29|| or die("query failed: " . msql_error()); Quote Link to comment Share on other sites More sharing options...
xyph Posted November 7, 2011 Share Posted November 7, 2011 Seems that function doesn't exist. Quote Link to comment Share on other sites More sharing options...
Guber-X Posted November 7, 2011 Author Share Posted November 7, 2011 is there a way to fix that? lol Quote Link to comment Share on other sites More sharing options...
xyph Posted November 7, 2011 Share Posted November 7, 2011 There sure is! Use the correct function name. http://php.net/manual/en/ref.mysql.php The manual is right there. Read the Language Reference. It's not a good idea to dive into database manipulation as your first PHP project, or without being fairly familiar with the PHP manual. The User Comments are generally a great learning (avoid direct copy+paste) resource as well. Quote Link to comment Share on other sites More sharing options...
Guber-X Posted November 7, 2011 Author Share Posted November 7, 2011 well, im not new to mysql and php... ive been using it for many years. but just simple uses... okay so i did some more research and adjusted my code to the code quote and now i get this error... Query Failed: The used SELECT statements have a different number of columns $query = "(SELECT * FROM news) UNION (SELECT * FROM event)"; $result = mysql_query($query) or die("Query Failed: ".mysql_error()); while ($row = mysql_fetch_array($result)) { print_r($row); } Quote Link to comment Share on other sites More sharing options...
xyph Posted November 7, 2011 Share Posted November 7, 2011 That means you're new to PHP/MySQL. Really, not being able to debug the errors your are getting says you are extremely new to the language. A union is multiple select statements merged into one. Both of those select statements must return the same number of columns. Your selects are returning all of the columns in both tables, which is obviously not the same. Quote Link to comment Share on other sites More sharing options...
Guber-X Posted November 7, 2011 Author Share Posted November 7, 2011 okay, well i think i got it figured out. i just made my SELECT grab from the same amount of fields for both and it finally displays my query. now just modify the layout $query = "(SELECT id, header, date, news FROM news) UNION ALL (SELECT eventdate, date, header, description FROM event) ORDER by date"; $result = mysql_query($query) or die("Query Failed: ".mysql_error()); while ($row = mysql_fetch_array($result)) { print_r($row); } Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.