...

View Full Version : Referencing db when inner join used in query, php warning



Flic
01-01-2008, 06:57 PM
Hi,

I have a query which looks at the name of an item in my database from the item_id that is given to it.

I did have a simple sql query which just looked at everything from the table, this worked but I also needed info from another table.

I changed the sql query so that it now looks in both of the tables and uses a join to present the info. This then made an error crop up that didn't before.

The error I'm getting is Warning: extract() [function.extract]: First argument should be an array in functions.inc.php on line 36 which is [I]extract($row) but this didn't come up before!

This also means that the info that was showing isn't anymore.

Now as it does use two tables I tried to get to that info in two ways:
$row["item_id"] causes the error
$row["item.item_id"] doesn't cause the error but doesn't show the info either

It kind of makes it more annoying because it was working and now its not!
Any help would be very much appreciated!
Thanks! :thumbsup:

The php code:

<?php
function shortCart() {
$cart = $_SESSION['cart'];
if (!$cart) {
return '<p>You have no items in your shopping cart</p>';
} else {
// Parse the cart session variable
$items = explode(',',$cart);
$s = (count($items) > 1) ? 's':'';
return '<p>You have <a href="cart.php">'.count($items).' item'.$s.' in your shopping cart</a></p>';
}
}

function showCart() {
global $db;
$cart = $_SESSION['cart'];
$output[] = '<form action="cart.php?action=update" method="post" id="cart">';
$output[] = '<table border=1 style="text-align: center;">';
$output[] = '<td style="width: 100px; font-weight: bold;">ID</td>';
$output[] = '<td style="width: 300px; font-weight: bold;">Name</td>';
$output[] = '<td style="width: 100px; font-weight: bold;">Price</td>';
$output[] = '<td style="width: 100px; font-weight: bold;">Quan</td>';
$output[] = '<td style="width: 100px; font-weight: bold;">Subtotal</td>';
$output[] = '<td style="border-left-style: solid; border-right-style: none; border-bottom-style: none; border-top-style: none;"></td></tr>';
if ($cart) {
$empty = 1;
$items = explode(',',$cart);
$contents = array();
foreach ($items as $item) {$contents[$item] = (isset($contents[$item])) ? $contents[$item] + 1 : 1;}
foreach ($contents as $id=>$qty) {
$sql = 'SELECT item.item_id, item.item_name, packet.size, packet.price, item.weight, packet.package ';
$sql .= 'FROM item INNER JOIN packet ON item.item_id = packet.item_id ';
$sql .= 'WHERE packet.size LIKE "' .$size. '" AND packet.item_id LIKE "' .$id. '"';
$result = $db->query($sql);
$row = $result->fetch();
extract($row);
$output[] = '<td style="text-align: center;">'.$id.'</td>'; // Item id
$output[] = '<td style="text-align: center;">'.$row["item_name"].'</td>'; // Item name
$output[] = '<td>'.$row2["price"].'</td>'; // Item price
$output[] = '<td style="text-align: center;">'.$qty.'</td>'; // Item quan
$sub = $price * $qty;
$output[] = '<td>'.number_format($sub,2).'</td>';
$output[] = '<td style="border-left-style: solid; border-right-style: none; border-bottom-style: none; border-top-style: none;">';
$output[] = '<form method="post" action="cart.php?action=delete&id='.$row["item_id"].'">';
$output[] = '<input type="submit" style="position: relative; top: 10px;"name="remove" value="X"/></form></td></tr>';
$total += $price * $qty;
}
$output[] = '<tr><td colspan=5 style="text-size: 1px;">&nbsp;</td></tr>';
$output[] = '<tr><td colspan=2>&nbsp;</td><td colspan=2>';
$output[] = 'Sub total:</td><td>'.number_format($total,2).'</td><td></td></tr>';
$postage = 0.75;
$output[] = '<tr><td colspan=2>&nbsp;</td><td colspan=2>';
$output[] = 'VAT:</td><td>'.number_format($postage,2).'</td><td></td></tr>';
$gtotal = $total + $postage;
$output[] = '<tr><td colspan=2>&nbsp;</td><td colspan=2>';
$output[] = 'Grand total:</td><td>'.number_format($gtotal,2).'</td><td></td></tr>';
$output[] = '</table></form>';
} else {
$empty = 0;
$output[] = '<tr><td colspan=4 style="text-align: center;">- No items found in cart -</td></tr></table>';
}
return join('',$output);
}
?>

See it in action (with error) (http://beta.flics-jewellery.co.uk/cart.php?action=add&id=b-bb-6g&size=45&quan=1)

Fou-Lu
01-02-2008, 04:13 AM
Hi mate, without being able to test your code against any data I cannot provide with 100% certainty, but this here is your problem:


$sql = 'SELECT item.item_id, item.item_name, packet.size, packet.price, item.weight, packet.package ';
$sql .= 'FROM item INNER JOIN packet ON item.item_id = packet.item_id ';
$sql .= 'WHERE packet.size LIKE "' .$size. '" AND packet.item_id LIKE "' .$id. '"';

Best I can see, is there is no $size defined to this point, and that would be the same as nothing at all. So, if you get something where the id = val AND size LIKE '', you shouldn't get any results at all, assuming that size is a not null value.
Hopefully thats your problem, should be a simple fix! Good luck

Flic
01-02-2008, 09:17 PM
Hi,

Nope, $size isn't it, all variables are fine.

If you take a peek at the link in the OP it shows what the variables have stored in them.

hammer65
01-02-2008, 09:22 PM
When accessing a database, you are interacting with an external program. Any number of things can go wrong from the time you connect, till the time that you disconnect. All of the MySQL (and presumably that of the abstraction layer you appear to be using) functions return a value that evaluates to false if there is a problem. It's important to use that to control the flow of your application and provide proper feedback for what is going on.

Specifically, you execute a query, but never check to see if it succeeded. You then fetch a row without first determining that the query returned results. You then use extract before you know that the output of the fetch method is truly an array.

Use if constructs and direct your application to make the appropriate choice on the condition. For testing you can use die(), which so many script seem to do. I wouldn't recommend it for production however. Die() is a decidedly user-unfriendly and non-HTML compliant way to handle errors.

Fou-Lu
01-03-2008, 06:04 AM
Hi,

Nope, $size isn't it, all variables are fine.

If you take a peek at the link in the OP it shows what the variables have stored in them.

Ah ok gotcha. Don't rely on register globals btw, a lot of hosts are not using them anymore.
I guess the next question is what kind of database you are using. I would recommend that you shove that fetch() method into an object (whatever your db object is thats running the query) so that you can use it later with different DB types.
If you are using the mysqli extension, I believe you are running a prepared statement. If this is the case, make sure that you have an execute() command within the query. The next step would be to ensure that you have a result, and that the result is of the expected datatype. Using a var_dump($row) will tell you if thats the case. If this is a null value, you have no result set (which you should error check for as well though), so then make sure that your query executes with the desired effects. I would print that $sql directly out and copy that and execute it directly.
Try these, see if any of them solve your problems.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum