Gathering and displaying data from 3 tables?

Kristoff1875 · 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.

Boxerman · 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.

Kristoff1875 · 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.

Boxerman · August 12, 2011

Maybe it would help others if you posted the code you got now?

Kristoff1875 · 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

Boxerman · 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?

Kristoff1875 · 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.

Boxerman · 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?

Kristoff1875 · 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.

Boxerman · 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
} }

Kristoff1875 · 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?

Boxerman · 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";

Kristoff1875 · August 12, 2011

That's what I mean though, my items I want to display are in the GiftListItems table.

Boxerman · 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

Kristoff1875 · 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!

Kristoff1875 · 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!

Boxerman · August 12, 2011

So its calling fine now? everything works? if so please mark topic solved, and start a new topic for other questions

Kristoff1875 · 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

Boxerman · August 12, 2011

Yes, as there is no logging in and editing in this script, this just 'selects the data and displays it'

Thanks

Kristoff1875 · August 12, 2011

It is kind of related, hence the Username and Password fields, but i'll post again.

Thanks again

Sign In

Gathering and displaying data from 3 tables?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information