[SOLVED] query 2 tables - for defined function in include file

[gordo2dope]

i have a functions.inc.php file with some definitions in it.  I have a problem getting the query to select from 2 tables.  I have a parts table and a clothing table.  I cant get it to selectr from both  Here is the code below.  this is just a small part of it, the part thats the problem is the first line.

 

$sql = "SELECT * FROM mczippo1 WHERE PartNum = '{$PartNum}' ";
$result = $db->query($sql);
$row = $result->fetch();
extract($row);

 

i tried it like the usual way but it didnt work.  I tried a couple different things and still no luck.  mczippo1 and partsitems are the 2 different tables i need to get PartNum from.

$sql = "SELECT * FROM mczippo1, partsitems WHERE PartNum = '{$PartNum}' ";

 

anybody got any help?  I love this place usually Somebody has the answer or helps figure it out.  THANKS FOR ANY HELP!

 

[Iceman512]

Hi there,

 

Can u give us the columns or fields in your tables?

 

Thanks,

Iceman

[gordo2dope]

well the field it needs to get is PartNum .  During the query,

'{$PartNum}' ";

is the submitted variable.  the code works, I just cant get it to select from both tables.

 

From there it will take care of the rest.  Every PartNum is unique so there are never 2 the same.  The problem is this cart needs to be shared be shared between 2 departments, each with a seperate TABLE.  Both Tables first main unique field is PartNum.  I see where you're going with this though. something like this ...

"SELECT * FROM mczippo1.PartNum,partsitems.PartNum WHERE PartNum = '{$PartNum}' ";

 

but I dont know how to do it

[Iceman512]

Hi again,

 

Yup, that's what I was thinking. Why don't you try a JOIN, like this:

<?php
$select = ("SELECT field1, field2 FROM table1 WHERE id='1'
JOIN SELECT field1, field2 FROM table2 WHERE id='1'");
?>

 

Hope it works!

Regards

Iceman

[gordo2dope]

nope... 

 

"SELECT PartNum, Description, Active, Size FROM mczippo1 WHERE PartNum='{$PartNum}' 
JOIN SELECT PartNum, Description, Active FROM partsitems WHERE PartNum='{$PartNum}'"

 

i thought it worked but nope...  I still get the error. All the info is correct, it just wont join though.  I guess it is right about SYNTAX, maybe it just has to be worded different.

 

Notice: Query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN SELECT PartNum, Description, Active FROM partsitems WHERE PartNum='97028-05' at line 1 SQL: SELECT PartNum, Description, Active, Size FROM mczippo1 WHERE PartNum='97028-05VW/002S' JOIN SELECT PartNum, Description, Active FROM partsitems WHERE PartNum='97028-05VW/002S' in C:\Program Files\Apache2\htdocs\inc\mysql.class.php on line 109

[Iceman512]

Ok, well let's try UNION:

 

<?php
$sql = ("SELECT PartNum, Description, Active, Size FROM mczippo1 WHERE PartNum='{$PartNum}' 
UNION SELECT PartNum, Description, Active FROM partsitems WHERE PartNum='{$PartNum}'");
?>

 

Let's see if that works?

Iceman

[gordo2dope]

well it seemd like it would have worked but I get an error that the 2 tables have different # of columns so it can't.  =(

[Iceman512]

Man, I'm learning something here too!

 

Try JOIN LEFT:

 

<?php
$sql = ("SELECT PartNum, Description, Active, Size FROM mczippo1 WHERE PartNum='{$PartNum}' 
JOIN LEFT SELECT PartNum, Description, Active FROM partsitems WHERE PartNum='{$PartNum}'");
?>

 

Let's see?

Iceman

[gordo2dope]

i still get an error.  I think because it cant find that PartNum in PARTSITEMS table.  Its funny the Partsitems TABLE comes first in the error, because in the script i have it selecting from mczippo1 first. you can see here it actually cut the PartNum in half see the BOLD LETTERS BELOW

 

Notice: Query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version

for the right syntax to use near 'JOIN LEFT SELECT PartNum, Description, Active FROM partsitems WHERE PartNum='968' at

line 1 SQL: SELECT PartNum, Description, Active, Size FROM mczippo1 WHERE PartNum='96826-06V' JOIN LEFT SELECT PartNum,

Description, Active FROM partsitems WHERE PartNum='96826-06V' in C:\Program Files\Apache2\htdocs\inc\mysql.class.php on line 109

[Iceman512]

Hi again,

 

By default, the error message returned will only be 80 characters in length. So that's why it appears as though it's 'cutting off' the partnum. The reason the error only starts from JOIN LEFT and onwards is because the query executes successfully until that point.

 

That being said, I don't know how to solve the problem apart from what we've tried... although I ain't no genius 

 

You might want to google it and see what you get. I'm gonna do the same and we'll see what we come up with. Sorry I can't be of any more help at the moment.

 

Regards,

Iceman

 

 

P.S: Dumb question... Do the rows in the database appear exactly as you have written them, with the first letter caps?

[gordo2dope]

to answer, yes, I try and make everything exactly match all the time, just to rule stuff like that out.  Also.  Yeah i have googled and search, but come up with nothing, especially when you arent sure what to search for.  I looked at everyting about querying 2 tables, and i even have 3 books u reference all day. This was my last resort coming here to ask.  Thats usually what i do if I cant figure it out from books or the net.   

 

HERE, let me paste the whole junk of code for the hell of it, its just one function from a functions.inc.php file.  so the other functions wont matter.  Maybe how its shown here will shed some more light.

 

as you can see the problem line is the $sql QUERY LINE there.  Like I said, it works like that, but i cant get it to select from two tables.

 

[pre]

function showCart() {
global $db;
$cart = $_SESSION['cart'];
if ($cart) {
	$items = explode(',',$cart);
	$contents = array();
	foreach ($items as $item) {
		$contents[$item] = (isset($contents[$item])) ? $contents[$item] + 1 : 1;
	}
	$output[] = '<form action="cart.php?action=update" method="post" id="cart">';
	$output[] = '<table>';
	foreach ($contents as $PartNum=>$qty) {
		$sql = "SELECT * FROM mczippo1 WHERE PartNum='{$PartNum}'";
		$result = $db->query($sql);
		$row = $result->fetch();
		extract($row);
		$output[] = '<tr>';
		$output[] = '<td width="65"  align="center"><a href="cart.php?action=delete&PartNum='.$PartNum.'" class="r">Remove</a></td>';
		$output[] = '<td width="230" align="center">'.$PartNum.' - '.$Description.'</td>';
		$output[] = '<td width="70" align="center">$'.$Active.'</td>';
		$output[] = '<td width="70" align="center">'.$Size.'</td>';
		$output[] = '<td width="65" align="center"><input type="text" name="qty'.$PartNum.'" value="'.$qty.'" size="3" maxlength="3" /></td>';
		$itemtotal = ($Active * $qty);
		$output[] = '<td>$'.$english_format_number = number_format($itemtotal, 2, '.', '').'</td>';
		$total += $Active * $qty;
		$output[] = '</tr>';
	}
	$output[] = '</table>';
	$output[] = '<p>Subtotal: <strong>$'.$english_format_number = number_format($total, 2, '.', '').'</strong></p>';
	$output[] = '<div><button type="submit">Update cart</button></div>';
	$output[] = '</form>';
} else {
	$output[] = '<p>You shopping cart is empty.</p>';
}
return join('',$output);
}

[/pre]

[gordo2dope]

well i thought i had a sol.ution,  it seems like it might work but it cant finish the job.  it has an ERROR AFTER the query.  what i did was make a smaller function for each query and then mad SQL as the both together. I dont know if thats allowed though or not.

 

this is the problem area, it has a error at  extract($row);

 

[pre]

$sqlmc = "SELECT * FROM mczippo1 WHERE PartNum='{$PartNum}'";
		$sqlparts = "SELECT * FROM partsitems WHERE PartNum='{$PartNum}'";
		$sql = $sqlmc AND $sqlparts;
		$result = $db->query($sql);
		$row = $result->fetch();
		extract($row);

[/pre]

here is the whole script

[pre]

function showCart() {
global $db;
$cart = $_SESSION['cart'];
if ($cart) {
	$items = explode(',',$cart);
	$contents = array();
	foreach ($items as $item) {
		$contents[$item] = (isset($contents[$item])) ? $contents[$item] + 1 : 1;
	}
	$output[] = '<form action="cart.php?action=update" method="post" id="cart">';
	$output[] = '<table>';
	$output[] = '<tr>';
	$output[] = '<td></td>';
	$output[] = '<td align="center"><b><font size="1">ITEM</b></font></td>';
	$output[] = '<td align="center"><b><font size="1">PRICE</b></font></td>';
	$output[] = '<td align="center"><b><font size="1">SIZE</b></font></td>';
	$output[] = '<td align="center"><b><font size="1">QTY</b></font></td>';
	$output[] = '<td align="center"><b><font size="1">EXT</b></font></td>';
	foreach ($contents as $PartNum=>$qty) {
		$sqlmc = "SELECT * FROM mczippo1 WHERE PartNum='{$PartNum}'";
		$sqlparts = "SELECT * FROM partsitems WHERE PartNum='{$PartNum}'";
		$sql = $sqlmc AND $sqlparts;
		$result = $db->query($sql);
		$row = $result->fetch();
		extract($row);
		$output[] = '<tr>';
		$output[] = '<td width="65"  align="center"><a href="cart.php?action=delete&PartNum='.$PartNum.'" class="r">Remove</a></td>';
		$output[] = '<td width="230" align="center"><b>'.$PartNum.'</b><br>'.$Description.'</td>';
		$output[] = '<td width="70" align="center">$'.$Active.'</td>';
		$output[] = '<td width="70" align="center">'.$Size.'</td>';
		$output[] = '<td width="65" align="center"><input type="text" name="qty'.$PartNum.'" value="'.$qty.'" size="3" maxlength="3" /></td>';
		$itemtotal = ($Active * $qty);
		$output[] = '<td align="center">$'.$english_format_number = number_format($itemtotal, 2, '.', '').'</td>';
		$total += $Active * $qty;
		$output[] = '</tr>';
	}
	$output[] = '</table>';
	$output[] = '<p>Subtotal: <strong>$'.$english_format_number = number_format($total, 2, '.', '').'</strong></p>';
	$output[] = '<div><button type="submit">Update cart</button></div>';
	$output[] = '</form>';
} else {
	$output[] = '<p>You shopping cart is empty.</p>';
}
return join('',$output);
}

[/pre]

[fenway]

How are these two tables related?

[gordo2dope]

the first field of both tables is PartNum and is unique.  The tables are of items from same DATABASE, but different departments.  One is clothing and other other is hard parts.  Two seperate tables are needed to assign extra fields the the PartNum's.  For example:  CLOTHING has description, onhand, price, color, size, etc.  PARTS has description, onhand,  price, universal (fitment), aftermarket, etc.

 

I split them into department tables so the HARD PARTS wont have extrta fields like color, size since they dont need them.  Also the Parts has stuff that clothing doesnt need.  PartNums are very similar too. EXAMPLE: 96852-05v could be a HAT while 84524-12A could be a MUFFLER.  Like I said, they are in the SAME DATABASE, just different TABLES.  And I need them to be able to share the same shopping car, which means the function that calls the cart needs to be able to pull from both tables.

[fenway]

So these two tables are not related at all.... then you need a UNION not a JOIN.

[gordo2dope]

well we tried Union earlier but no luck.

but i thought of something else.  It only seems to work for one table though.  here is the code. It gives me an error code on the last line with the EXTRACT.  If i add something to the cart from mczippo1 it works, but if I try and add from partsitems i get the error.

 

[pre]

$sqlmc = "SELECT * FROM mczippo1 WHERE PartNum='{$PartNum}'";
$sqlparts = "SELECT * FROM partsitems WHERE PartNum='{$PartNum}'";
$sql = $sqlmc AND $sqlparts;
$result = $db->query($sql);
$row = $result->fetch();
extract($row);

[/pre]

 

[gordo2dope]

by the way, my partner here managed to get it to work.  you was a UNION, syntax just required more () and {} and what not..

 

[pre]

$sql = "(SELECT PartNum, Description, Active, Size FROM mczippo1 WHERE PartNum='{$PartNum}') 
         UNION 
        (SELECT PartNum, Description, Active, Size FROM partsitems WHERE PartNum='{$PartNum}')"

[/pre]

 

THANKS TO ANY HELPERS! PROBLEM SOLVED!