[SOLVED] Here's a tough one

[tqla]

I need help  ???

 

I have two tables in a database. One is called 'CUSTOMER' and the other 'COMPANY'

 

'CUSTOMER' has address info. 'COMPANY' has info pertaining to whether the customer is active or not.

 

A number in an 'ID' field in 'CUSTOMER' matches the number in a 'CusID' field in 'COMPANY'.

 

I want to pull all the files from 'COMPANY' whose 'SIGNUP' field equals 2007 and whose 'ACTIVE' field equals 'YES'.

 

Then, I want to pull all the files in the 'CUSTOMER' table that match the 'CusID' number in the result of the 'COMPANY' query (above).

 

I hope this makes sense. Thanks!

[pocobueno1388]

Hmmm...I'm not the greatest at SQL queries, but try something like this'

 

SELECT
    col1, col2
FROM
    customers, company
WHERE
    company.ID = customer.ID AND
    customer.signup = '2007' AND
    customer.active = 'yes'

 

You must think I live on this site or something lmao

[teng84]

i feel bad using where or normal join

its hard to debug like when you join 10 tables in 3 database it better for me to use inner join

[tqla]

Hi pocobueno1388 and teng84!

 

This is a join, right? What are the col1 and col2 entries? Am I to rename them? when I use it as is I get "Parse error: parse error, unexpected T_STRING"

 

 

You must think I live on this site or something

LOL It's awesome that you're here all the time. You've helped me a lot!

 

[tqla]

Nevermind, I got it now! Well at least I fixed the parse error (I was making a noob syntax mistake)  :-\

[Barand]

I agree with Teng.

 

Far better to separate the join conditions from the selection criteria

 

SELECT
    cu.col1, cu.col2
FROM
    customers cu 
    INNER JOIN company co ON co.cusID = cu.ID
WHERE
    co.signup = '2007' 
    AND
    co.active = 'yes'

[tqla]

Thanks Barand. I'm trying to understand this.

 

What does 'co' and 'cu' stand for?

 

What does col1 and col2 stand for?

 

 

[lightningstrike]

cu stands for the table customers, and co table company

 

col1 and col2 are for you to change into the appropriate column names i believe or if you need all the row data you could remove both of them and put a * instead.

[tqla]

Thanks lightningstrike.

 

So should I abbreviate it like Barand has, or am I to type:

 

company.col1, customers.col2  ?

 

 

Also, this part confuses me:

 

FROM
    customers cu 
    INNER JOIN company co ON co.cusID = cu.ID

 

He wrote "customers cu" and "company co". How does php know what cu and co are. Does it know becuse of the co.col1 and cu.col2 part?

 

???

 

Sorry but I just don't get it.

[lightningstrike]

Don't worry about that MySQL handles that automatically. e.g. "company co" tells MySQL to abbreviate table company as co.

You should type it as Barand did it with cu.col1, cu.col2 etc.

[tqla]

Thanks lightningstrike.

 

At first I got an error stating that 'cu.col1' did not exist so I removed both and put a '*' in there and it works.

 

So about the 'cu.col1', you're saying that I need to rename the 'col1' part to whatever column I need. Like 'cu.Address'. Right?

 

I think I'm getting it now.

[pocobueno1388]

So about the 'cu.col1', you're saying that I need to rename the 'col1' part to whatever column I need. Like 'cu.Address'. Right?

 

Yep, thats all you have to do =] Gosh, I need to learn how to do that Inner Join better, I'm slacking with mysql -laughs-

[tqla]

Thanks pocobueno.  You're suggestion also worked.  I've got both versions up and running. Man I learned a lot today. I'm trying real hard to understand this stuff.

 

Do you know how to remove duplicates? I am printing the results of the above code and there seems to be duplicates. Some of the files are printing 2 times, some 3, some 1. Weird. There must be some code to look at the result and de-dup it. Is there?

[pocobueno1388]

Are you sure you don't have duplicates in the database?

[tqla]

Nope. No dups in the db. There are 2 tables - one has addresses and the other has info about being active, etc, but that's about it.

 

Here's the whole script. It's a function that prints the addresses to a PDF file in Avery 5160 label format.  It's pretty cool. Only problem is I get duplicate labels.

 

<?php
include_once("../classes/dbconnect.php");
include_once("../classes/fpdf/fpdf.php");


// Prints to an Avery 5160 label sheet which is a label
// 2 5/8" wide by 1" tall, they are 3 accross on a page
// and 10 rows per page. (30 per page). The upper left
// corner is label(0,0) The X co-ord goes horizontally
// accross the page and Y goes vertically down the page
// Left/Right page margins are 4.2 MM (1/6 inch)
// Top/Botton page margines are 12.7 MM (.5 inch)
// Horizontal gap between labels is 4.2 MM (1/6 inch)
// There is no vertial gap between labels
// Labels are 66.6 MM (2 5/8") Wide
// Labels are 25.4 MM (1" ) Tall
//function Avery5160($x, // X co-ord of label (0-2)$y, // Y co-ord of label (0-9)&$pdf,$Data) // String w/ line breaks to print
function Avery5160($x, $y, &$pdf, $Data){
$LeftMargin = 3.5;
$TopMargin = 12.7;
$LabelWidth = 66.6;
$LabelHeight = 28.25;
// Create Co-Ords of Upper left of the Label
$AbsX = $LeftMargin + (($LabelWidth + 4.22) * $x);
$AbsY = $TopMargin + ($LabelHeight * $y);

// Fudge the Start 3mm inside the label to avoid alignment errors
$pdf->SetXY($AbsX+3,$AbsY+3);
$pdf->MultiCell($LabelWidth-8,4.5,$Data);

return;
}

function PrintAddressLabels($SelectStmt){
global $conn, $Tables;

$rs=$conn->Execute (

"SELECT
    *
FROM
    customers cu 
    INNER JOIN company co ON co.cusID = cu.ID
WHERE
    co.signup = '2007' 
    AND
    co.active = 'yes'
");


$pdf=new FPDF();
$pdf->Open();
$pdf->AddPage();
$pdf->SetFont('Arial','B',10);
$pdf->SetMargins(0,0);
$pdf->SetAutoPageBreak(false);

$x = 0;
$y = 0;
	//while (TRUE) {
	while(!$rs->EOF){

			if($rs->Fields(MAddress) <> '' && $rs->Fields(MCity) <> '' && $rs->Fields(MState) <> ''){
				$LabelText = sprintf("%s\n%s\n%s\n%s, %s %s\n", $rs->Fields(FName) . " " . $rs->Fields(LName), $rs->Fields(CompanyName),$rs->Fields(MAddress) . " " . $rs->Fields(MAddress2), $rs->Fields(MCity),$rs->Fields(MState),$rs->Fields(MZip));
			Avery5160($x,$y,$pdf,$LabelText);

			$y++; // next row

				if ($y == 10 ) { // end of page wrap to next column
					$x++;
					$y = 0;
					if ($x == 3 ) { // end of page
						$x = 0;
						$y = 0;
						$pdf->AddPage();
					}
				}
			}
		//} else {
			// Error quit printing
			//break;
		//}
		$rs->MoveNext();
	}
$pdf->Output();
}
echo PrintAddressLabels($SelectStmt);
?>