INNER JOIN

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
wHiTeHaT
Forum Newbie
Posts: 15
Joined: Thu Nov 17, 2005 2:48 pm

INNER JOIN

Post by wHiTeHaT »

Hello @all

I'm working on a kind of shoppingsystem where have 1 main product what can be purchased by selected dates.
not a date range but single day's , as i making this step-by-step and my php skils are limited i assume that i frequently will visit this site.
I hope whenever see some other posts i am able to help , and hope i help correctly. :oops:

Anyway here's my issue , i have this database call:

Code: Select all

$result = $db->sql_query("SELECT UNIX_TIMESTAMP(calendar_date) AS calendar_date, calendar_id, calendar_price, did FROM calendar WHERE MONTH(calendar_date)='$monthID'");
ofcourse this is for +coders understanding.

As you can see it load events as products (it took a lot of frustration when making this)
I now need to combine some main products to this page.
The database i think is already prepared for it
my db stucture is the folowing
calendar (holding al day's calendar_id , did(= the day id can be from 1 to 31) , calandar_date , calendar_price)
items (holding all main products , product_id , product_description, qty)
products_to_calendar(here i sayed product_id X belong to calendar_id x till xxx)

My plan is to also make a check that 1 date to 1 product cant be ordered more as 30 times.
I hope it is a bit clear.
Last edited by wHiTeHaT on Fri Oct 13, 2006 8:17 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

What's the question?
wHiTeHaT
Forum Newbie
Posts: 15
Joined: Thu Nov 17, 2005 2:48 pm

Post by wHiTeHaT »

Yes somehow i lost it in the post.


i also seeing i was to quick with asking , withouth making the basic structure first.
i have to excuse for this.
i will come back asp so i can go on to the main question , i think i'm a bit to excited. :oops:
Last edited by wHiTeHaT on Fri Oct 13, 2006 8:17 pm, edited 1 time in total.
wHiTeHaT
Forum Newbie
Posts: 15
Joined: Thu Nov 17, 2005 2:48 pm

Post by wHiTeHaT »

pff ok here we go:
Last edited by wHiTeHaT on Fri Oct 13, 2006 8:19 pm, edited 1 time in total.
wHiTeHaT
Forum Newbie
Posts: 15
Joined: Thu Nov 17, 2005 2:48 pm

Post by wHiTeHaT »

I think i make some mistake on how to make sure i use the correct combination between a product and its attribute.

please correct me if i'm wrong.

Should i let check first the table products_to_calendar and then inner join table items(what hold the main product) and table calendar(what hold the dates)????????? :roll:
wHiTeHaT
Forum Newbie
Posts: 15
Joined: Thu Nov 17, 2005 2:48 pm

Post by wHiTeHaT »

Comming back then to this tread again , sorry for the trouble in the other forum :oops:

this is my first failed attemp wich failed :roll: :

Code: Select all

$result = $db->sql_query("SELECT * FROM products_to_calendar LEFT JOIN items AS i ON products_to_calendar.products_id = i.productsmain_id LEFT JOIN calendar AS c ON products_to_calendar.calendar_id = c.calendar_id WHERE MONTH(c.calendar_date)='$monthID'");



OpenTable();
while ($row = $db->sql_fetchrow($result, MYSQL_BOTH)){
$products_id = $row["products_id"];
$calendar_price = $row['calendar_price'];

$mysqlDay=date("j",$row["calendar_date"]); // makes a day out of the database date
$mysqlMonth=date("n",$row["calendar_date"]); // makes a month out of the database date
$mysqlYear=date("Y",$row["calendar_date"]); // makes a year out of the database date
$mysqlContent= $row["did"]; // gets the event content
Any suggestions are more as apreciated
wHiTeHaT
Forum Newbie
Posts: 15
Joined: Thu Nov 17, 2005 2:48 pm

Post by wHiTeHaT »

Another attempt:

Code: Select all

CREATE TABLE `calendar` (
  `calendar_id` int(11) NOT NULL default '0',
  `did` char(250) NOT NULL default '0',
  `calendar_date` date NOT NULL default '0000-00-00',
  `calendar_price` decimal(4,2) NOT NULL default '1.00',
  PRIMARY KEY  (`calendar_id`)
) TYPE=MyISAM;

CREATE TABLE `items` (
  `productsmain_id` int(11) NOT NULL auto_increment,
  `products_name` varchar(64) NOT NULL default '',
  `products_description` text,
  `products_price` decimal(4,2) default NULL,
  `products_image` varchar(64) default NULL,
  PRIMARY KEY  (`productsmain_id`),
  UNIQUE KEY `id` (`productsmain_id`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;

CREATE TABLE `products_to_calendar` (
  `products_id` int(11) NOT NULL default '0',
  `calendar_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`products_id`,`calendar_id`)
) TYPE=MyISAM;

Code: Select all

$result = $db->sql_query("

Code: Select all

SELECT 
        items.productsmain_id, 
        items.products_name, 
        items.products_description,  
        products_to_calendar.products_id,
        products_to_calendar.calendar_id,        
        calendar.calendar_id,
        calendar.did,
        UNIX_TIMESTAMP(calendar.calendar_date) AS calendar.calendar_date, 
        calendar.calendar_price
FROM 
        items 
LEFT JOIN 
        products_to_calendar 
ON 
        items.productsmain_id = products_to_calendar.products_id 
LEFT JOIN 
        calendar
ON 
        products_to_calendar.calendar_id=calendar.calendar_id 
WHERE MONTH(calendar.calendar_date)='$monthID'

Code: Select all

");
Still no luck with it, who see the failure?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

The query appears to be okay, so its difficult to know where you're going sideways.
wHiTeHaT
Forum Newbie
Posts: 15
Joined: Thu Nov 17, 2005 2:48 pm

Post by wHiTeHaT »

maby this can help someone to help me out , i'm getting a beard at the moment:

Code: Select all

<?php
if (!defined('MODULE_FILE')) {
	die ("You can't access this file directly...");
}
define('INDEX_FILE', true);
require_once("mainfile.php");
require_once("source/activecalendar.php");

$module_name = basename(dirname(__FILE__));
get_lang($module_name);
include("header.php");
global $db;

$yearID=date("Y"); // current year
$monthID=date("n"); // current month
$dayID=date("j");

if (isset($_GET['yearID'])) $yearID=$_GET['yearID'];
if (isset($_GET['monthID'])) $monthID=$_GET['monthID'];
if (isset($_GET['dayID'])) $dayID=$_GET['dayID'];

$now = time();
$j = date("j", $now);
$m = date("n", $now);
$y = date("Y", $now);

//$myurl= 'modules.php?name=Fly_to_Basket&file=products'; // the links url is this page
$myurl= 'modules.php?name=Fly_to_Basket&file=productinfo&Pid=' . $_GET['Pid'] . ''; // the links url is this page

$test_id = $_GET['products_id']; 
$test_id = preg_replace("/[^\d]/si","",$products_id); 
$Pid = $_GET['Pid'];
$cal=new activeCalendar($yearID,$monthID,$dayID);

$eventID="event"; // sets the name of the generated HTML class on the event day (css layout)
//$result = $db->sql_query("SELECT UNIX_TIMESTAMP(calendar_date) AS calendar_date, calendar_id, calendar_price, did FROM calendar WHERE MONTH(calendar_date)='$monthID'");

$result = $db->sql_query("SELECT 
        items.productsmain_id, 
        items.products_name, 
        items.products_description,  
        products_to_calendar.products_id, 
        products_to_calendar.calendar_id,        
        calendar.calendar_id, 
        calendar.did, 
        UNIX_TIMESTAMP(calendar.calendar_date) AS calendar.calendar_date, 
        calendar.calendar_price 
FROM 
        items 
LEFT JOIN 
        products_to_calendar 
ON 
        items.productsmain_id = products_to_calendar.products_id 
LEFT JOIN 
        calendar 
ON 
        products_to_calendar.calendar_id=calendar.calendar_id 
WHERE MONTH(calendar.calendar_date)='$monthID' 
");


OpenTable();
while ($row = $db->sql_fetchrow($result, MYSQL_BOTH)){
$products_id = $row["calendar_id"];
$calendar_price = $row['calendar_price'];

$mysqlDay=date("j",$row["calendar_date"]); // makes a day out of the database date
$mysqlMonth=date("n",$row["calendar_date"]); // makes a month out of the database date
$mysqlYear=date("Y",$row["calendar_date"]); // makes a year out of the database date
$mysqlContent= $row["did"]; // gets the event content
//$mysqlLink=$row[$tblLink]; // gets the event link
$mysqlLink = "#\" onclick=\"addToBasket('$products_id','$Pid');return false;"; // the name of your MySQL Table Link Field

$cal->setEventContent($mysqlYear,$mysqlMonth,$mysqlDay,$mysqlContent,$mysqlLink); // set the event content and link

?>
<script type="text/javascript" src="modules/Fly_to_Basket/js/ajax.js"></script>
<script type="text/javascript" src="modules/Fly_to_Basket/js/fly_to_basket.js"></script>
<?php

		

echo"<div id=\"slidingProduct_".$products_id."\">";
	
	
echo "</div>";

}
Post Reply