Kristoff1875 Posted August 12, 2011 Share Posted August 12, 2011 Hi, i've progressed from the topic here: http://www.phpfreaks.com/forums/index.php?topic=341230.0 And now have 3 tables set up: -- phpMyAdmin SQL Dump -- version 3.4.3.2 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Aug 12, 2011 at 09:01 PM -- Server version: 5.0.91 -- PHP Version: 5.2.9 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Database: `gdesignz_Giftlist` -- -- -------------------------------------------------------- -- -- Table structure for table `GiftList` -- CREATE TABLE IF NOT EXISTS `GiftList` ( `ID_GiftList` int(11) NOT NULL auto_increment, `Owner` int(11) NOT NULL, PRIMARY KEY (`ID_GiftList`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; -- -------------------------------------------------------- -- -- Table structure for table `GiftListItems` -- CREATE TABLE IF NOT EXISTS `GiftListItems` ( `ID_GiftListItem` int(11) NOT NULL auto_increment, `Name` text NOT NULL, `Price` decimal(10,2) NOT NULL, `Link` text NOT NULL, `Availability` enum('Available','Reserved') NOT NULL, PRIMARY KEY (`ID_GiftListItem`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=50002 ; -- -- Dumping data for table `GiftListItems` -- INSERT INTO `GiftListItems` (`ID_GiftListItem`, `Name`, `Price`, `Link`, `Availability`) VALUES (50001, 'Morphy Richards Red Accents slow cooker', 34.99, 'http://www.houseoffraser.co.uk/Morphy+Richards+Red+Accents+slow+cooker+48728/146808112,default,pd.html', 'Available'); -- -------------------------------------------------------- -- -- Table structure for table `Users` -- CREATE TABLE IF NOT EXISTS `Users` ( `ID_Users` int(11) NOT NULL auto_increment, `Username` text NOT NULL, `Password` text NOT NULL, PRIMARY KEY (`ID_Users`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; -- -- Dumping data for table `Users` -- INSERT INTO `Users` (`ID_Users`, `Username`, `Password`) VALUES (1, 'Admin', 'password'); /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; How do I now go about setting up a list in the GiftList and getting it to display just that list on the page? Thanks in advance. Quote Link to comment https://forums.phpfreaks.com/topic/244643-gathering-and-displaying-data-from-3-tables/ Share on other sites More sharing options...
Boxerman Posted August 12, 2011 Share Posted August 12, 2011 Hi, Im not the best coder in the world, and i know someone will come up with a better reply, but until then, maybe take a look at http://www.w3schools.com/php/php_mysql_select.asp Maybe create var's and call them from config.php? Like i said it may not be the best but it should work. Quote Link to comment https://forums.phpfreaks.com/topic/244643-gathering-and-displaying-data-from-3-tables/#findComment-1256624 Share on other sites More sharing options...
Kristoff1875 Posted August 12, 2011 Author Share Posted August 12, 2011 Hi, thanks for the reply buddy. I am able to get the results to output, but what I actually need is to link the fields from the different tables together. I've noticed I missed a field which tells the giftlistitem which list it is from in the giftlist table, I'm just not sure how i'm meant to tell it what to do. Quote Link to comment https://forums.phpfreaks.com/topic/244643-gathering-and-displaying-data-from-3-tables/#findComment-1256628 Share on other sites More sharing options...
Boxerman Posted August 12, 2011 Share Posted August 12, 2011 Maybe it would help others if you posted the code you got now? Quote Link to comment https://forums.phpfreaks.com/topic/244643-gathering-and-displaying-data-from-3-tables/#findComment-1256629 Share on other sites More sharing options...
Kristoff1875 Posted August 12, 2011 Author Share Posted August 12, 2011 Ok here goes... Here is my SQL: -- phpMyAdmin SQL Dump -- version 3.4.3.2 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Aug 12, 2011 at 11:44 PM -- Server version: 5.0.91 -- PHP Version: 5.2.9 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Database: `gdesignz_Giftlist` -- -- -------------------------------------------------------- -- -- Table structure for table `GiftList` -- CREATE TABLE IF NOT EXISTS `GiftList` ( `ID_GiftList` int(11) NOT NULL auto_increment, `Owner` varchar(100) NOT NULL default '', PRIMARY KEY (`ID_GiftList`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=50002 ; -- -- Dumping data for table `GiftList` -- INSERT INTO `GiftList` (`ID_GiftList`, `Owner`) VALUES (1, 'Admin'); -- -------------------------------------------------------- -- -- Table structure for table `GiftListItems` -- CREATE TABLE IF NOT EXISTS `GiftListItems` ( `ID_GiftListItem` int(11) NOT NULL auto_increment, `ID_GiftList` varchar(100) NOT NULL default '', `Name` text NOT NULL, `Price` decimal(10,2) NOT NULL, `Link` text NOT NULL, `Availability` enum('Available','Reserved') NOT NULL, PRIMARY KEY (`ID_GiftListItem`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; -- -- Dumping data for table `GiftListItems` -- INSERT INTO `GiftListItems` (`ID_GiftListItem`, `ID_GiftList`, `Name`, `Price`, `Link`, `Availability`) VALUES (1, '1', 'Morphy Richards Red Accents slow cooker', 34.99, 'http://www.houseoffraser.co.uk/Morphy+Richards+Red+Accents+slow+cooker+48728/146808112,default,pd.html', 'Available'); -- -------------------------------------------------------- -- -- Table structure for table `Users` -- CREATE TABLE IF NOT EXISTS `Users` ( `ID_Users` int(11) NOT NULL auto_increment, `Username` varchar(100) NOT NULL default '', `Password` text NOT NULL, PRIMARY KEY (`ID_Users`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; -- -- Dumping data for table `Users` -- INSERT INTO `Users` (`ID_Users`, `Username`, `Password`) VALUES (1, 'Admin', 'password'); /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; And on the giftlist page: <?php //Include database connection details require_once('list/config.php'); //Array to store validation errors $errmsg_arr = array(); //Validation error flag $errflag = false; //Connect to mysql server $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD); if(!$link) { die('Failed to connect to server: ' . mysql_error()); } //Select database $db = mysql_select_db(DB_DATABASE); if(!$db) { die("Unable to select database"); } $query="select * from GiftList where ID_GiftList='1'"; // query string stored in a variable $rt=mysql_query($query); // query executed echo mysql_error(); // if any error is there that will be printed to the screen while($nt=mysql_fetch_array($rt)){ echo "$nt[Owner] $nt[Name] $nt[Price] $nt[Availability]<br>"; // name class and mark will be printed with one line break } ?> I added the $nt[Owner] part to see if anything was showing up. I am currently getting just "Admin" showing up, so it's showing the list, just not the stuff to go on the list, which is where i'm missing how it calls the data from the GiftListItems table to the GiftList table. Cheers Quote Link to comment https://forums.phpfreaks.com/topic/244643-gathering-and-displaying-data-from-3-tables/#findComment-1256634 Share on other sites More sharing options...
Boxerman Posted August 12, 2011 Share Posted August 12, 2011 Sorry, its must be my dumbness(not long finished 12 hours shift) What do you want it to do? grab data from database? Quote Link to comment https://forums.phpfreaks.com/topic/244643-gathering-and-displaying-data-from-3-tables/#findComment-1256640 Share on other sites More sharing options...
Kristoff1875 Posted August 12, 2011 Author Share Posted August 12, 2011 Don't worry, it's probably me not you! Basically the way I understand it as it was explained to me is the GiftList table will have the ID of the list and the username of who's list it is. Then the GiftListItems table will store all of the items that will be used on each list. So for example if the list with ID 1 (in GiftList table) would have items with the ID 3, 4, 7, 11 (from GiftListItems) in the list. For each item ID there is Name, Price, Availability - these are the details that I want to display. Quote Link to comment https://forums.phpfreaks.com/topic/244643-gathering-and-displaying-data-from-3-tables/#findComment-1256644 Share on other sites More sharing options...
Boxerman Posted August 12, 2011 Share Posted August 12, 2011 I see, create a page, i.e product.php then get it to grab from the database the id and display what it is, so product.php?id=1 so it only displays information on id 1 etc... Make sense? Quote Link to comment https://forums.phpfreaks.com/topic/244643-gathering-and-displaying-data-from-3-tables/#findComment-1256649 Share on other sites More sharing options...
Kristoff1875 Posted August 12, 2011 Author Share Posted August 12, 2011 So create a page for each item? Can't I just get it to call in to: while($nt=mysql_fetch_array($rt)){ echo "$nt[Owner] $nt[Name] $nt[Price] $nt[Availability]<br>"; // name class and mark will be printed with one line break } ? I only want to list the items rather than having a page for each one. At the moment the list is showing up, in that it's showing the correct list number, but it's not showing any of the items on the list. Quote Link to comment https://forums.phpfreaks.com/topic/244643-gathering-and-displaying-data-from-3-tables/#findComment-1256652 Share on other sites More sharing options...
Boxerman Posted August 12, 2011 Share Posted August 12, 2011 Take a look at this, and edit it to your liking... <?PHP $con = mysql_connect("localhost","root",""); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("news", $con); /* function to return the first N words of a string */ function shorten_string($string, $wordsreturned) { $retval = $string; $array = explode(" ", $string); if (count($array)<=$wordsreturned) { $retval = $string; }else{ array_splice($array, $wordsreturned); $retval = implode(" ", $array)." ..."; } return $retval; } /* check to see if an article has been selected */ if(!$_GET['id']) { $id = 0; }else{ $id = (int) $_GET['id']; } /* set the number of words for the brief */ $N = 15; /* if id is set display the news */ if($id>0) { $query = "SELECT * FROM news WHERE id='$id' ORDER BY id DESC"; $result = mysql_query($query); $row = mysql_fetch_array($result); $title = $row['title']; $posted_by = $row['postedby']; ?><p> <b><font size="5"><?PHP echo $title; ?></font> </b><br /> <font size="2">Posted By: <?PHP echo $posted_by; ?></font> <br /> Story: <?PHP echo $row['text'] ?></p> <br> Posted At: <?php echo $row['timestamp'] ?> <br><br> <a href="/news/">Back to news listings</a> <?PHP }else{ /* create query */ $query = "SELECT * FROM news ORDER BY id DESC"; /* execute the query */ $result = mysql_query($query); while($row=mysql_fetch_array($result)) { $title = $row['title']; $posted_by = $row['postedby']; $brief = shorten_string($row['text'], $N); ?><p> <b><font size="5"><?PHP echo $title; ?></font></b> <br> <font size="2">Posted By: <?PHP echo $posted_by; ?></font> <br> </p><p><?PHP echo $brief; ?></p><p> <a href="news.php?id=<?PHP echo $row['id']; ?>">Read More</a></p> <?PHP } } Quote Link to comment https://forums.phpfreaks.com/topic/244643-gathering-and-displaying-data-from-3-tables/#findComment-1256655 Share on other sites More sharing options...
Kristoff1875 Posted August 12, 2011 Author Share Posted August 12, 2011 I may be misunderstanding the point completely, however: $query = "SELECT * FROM news WHERE id='$id' ORDER BY id DESC"; $title = $row['title']; $posted_by = $row['postedby']; Aren't those values in the news table? Quote Link to comment https://forums.phpfreaks.com/topic/244643-gathering-and-displaying-data-from-3-tables/#findComment-1256656 Share on other sites More sharing options...
Boxerman Posted August 12, 2011 Share Posted August 12, 2011 yeah, as i said buddy, edit it, i.e it would be $query = "SELECT * FROM GiftList WHERE ID_GiftList='$id' ORDER BY id DESC"; Quote Link to comment https://forums.phpfreaks.com/topic/244643-gathering-and-displaying-data-from-3-tables/#findComment-1256658 Share on other sites More sharing options...
Kristoff1875 Posted August 12, 2011 Author Share Posted August 12, 2011 That's what I mean though, my items I want to display are in the GiftListItems table. Quote Link to comment https://forums.phpfreaks.com/topic/244643-gathering-and-displaying-data-from-3-tables/#findComment-1256660 Share on other sites More sharing options...
Boxerman Posted August 12, 2011 Share Posted August 12, 2011 point it towards your table, thats the script i use for news... so take the news out and add giftlistitems in replacement for it. Add your code into that Quote Link to comment https://forums.phpfreaks.com/topic/244643-gathering-and-displaying-data-from-3-tables/#findComment-1256662 Share on other sites More sharing options...
Kristoff1875 Posted August 12, 2011 Author Share Posted August 12, 2011 Right, don't know if i've done it the right way, but i've now replaced the query, so instead of: $query="select * from GiftList where ID_GiftList='1'"; I'm calling: $query="select * from GiftListItems where ID_GiftList='1'"; This is displaying my list as I want it to, and only from the correct list too. I still think there's something that won't be right! Quote Link to comment https://forums.phpfreaks.com/topic/244643-gathering-and-displaying-data-from-3-tables/#findComment-1256663 Share on other sites More sharing options...
Kristoff1875 Posted August 12, 2011 Author Share Posted August 12, 2011 point it towards your table, thats the script i use for news... so take the news out and add giftlistitems in replacement for it. Add your code into that Just done that I was calling the wrong table, using GiftList instead of GiftListItems. I have the feeling that this is just the start of my quest. I need to make a page to add and edit these lists and also a login page. Oh dear! Quote Link to comment https://forums.phpfreaks.com/topic/244643-gathering-and-displaying-data-from-3-tables/#findComment-1256664 Share on other sites More sharing options...
Boxerman Posted August 12, 2011 Share Posted August 12, 2011 So its calling fine now? everything works? if so please mark topic solved, and start a new topic for other questions Quote Link to comment https://forums.phpfreaks.com/topic/244643-gathering-and-displaying-data-from-3-tables/#findComment-1256665 Share on other sites More sharing options...
Kristoff1875 Posted August 12, 2011 Author Share Posted August 12, 2011 It's displaying properly so far. Should I make a new topic if I run in to problems regarding the same code? For example logging in and editing etc? Thanks Quote Link to comment https://forums.phpfreaks.com/topic/244643-gathering-and-displaying-data-from-3-tables/#findComment-1256667 Share on other sites More sharing options...
Boxerman Posted August 12, 2011 Share Posted August 12, 2011 Yes, as there is no logging in and editing in this script, this just 'selects the data and displays it' Thanks Quote Link to comment https://forums.phpfreaks.com/topic/244643-gathering-and-displaying-data-from-3-tables/#findComment-1256669 Share on other sites More sharing options...
Kristoff1875 Posted August 12, 2011 Author Share Posted August 12, 2011 It is kind of related, hence the Username and Password fields, but i'll post again. Thanks again Quote Link to comment https://forums.phpfreaks.com/topic/244643-gathering-and-displaying-data-from-3-tables/#findComment-1256673 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.