Won't Show Any Data From A Database

[Krux20]

Hi,

 

I'm having trouble with showing the BikeCode and the Price from my database. Can anyone look at the cold below and help me find what the error is?

 

Thanks

 

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 $id=>$qty) {
$sql = 'SELECT BikeCode,Model,Price FROM Bike WHERE BikeCode = '.$id;
$result = $db->query($sql);
$row = $result->fetch();
extract($row);


$output[] = '<tr>';
$output[] = '<td><a href="cart.php?action=delete&id='.$id.'" class="r">Remove</a></td>';
$output[] = '<td>'.$BikeCode.'</td>';    // This is  not showing
$output[] = '<td>£'.$Price.'</td>'; // And this also 
$output[] = '<td><input type="text" name="qty'.$id.'" value="'.$qty.'" size="3" maxlength="3" /></td>';
$output[] = '<td>£'.($Price * $qty).'</td>'; 
$total += $Price * $qty;
$output[] = '</tr>';
}
$output[] = '</table>';
$output[] = '<p>Grand total: <strong>£'.$total.'</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);
}
?>

[MDCode]

$sql = 'SELECT BikeCode,Model,Price FROM Bike WHERE BikeCode = '.$id;

 

Your query has errors

[Krux20]

Hi,

 

I seem to not be having any error with my query.

[MDCode]

You would if you ever put echo mysql_error(); or had error reporting on

 

Your query is closed too early and by using single quotes variables will not be parsed

[White_Lily]

Try:

 

$sql = 'SELECT `BikeCode`, `Model`, `Price` FROM `Bike` WHERE `BikeCode` = '.$id;
$result = $db->query($sql) or die("Select query does not work!<br><br><br>".mysql_error());

[mrMarcus]

The query is (structurally) fine assuming the `BikeCode` field is numeric. I would recommend you echo the query to ensure you're receiving desired results; however, since you are looping the query, that will make it more difficult.

 

 

First thing that stuck out to me (that does not necessarily have anything to do with your issue at hand) is that you are looping a query. This should be avoided:

 

foreach ($contents as $id=>$qty) {
$sql = 'SELECT BikeCode,Model,Price FROM Bike WHERE BikeCode = '.$id;

 

Revise your code to avoid this. Look into MySQL's "IN" clause, and look to move your query outside of the foreach() loop:

 

$sql = 'SELECT BikeCode,Model,Price FROM Bike WHERE BikeCode IN ('. $several_ids_separated_by_commas .')';

[Krux20]

Hi,

 

I tried the code and It still doesn't show any error, it seems to be working, but it won't show the BikeCode from the database.

[Krux20]

Hi, BikeCode is not numeric is text.

[mrMarcus]

  On 12/4/2012 at 4:32 PM, Krux20 said:

Hi, BikeCode is not numeric is text.

 

Then it must be wrapped in single quotes:

 

$sql = "SELECT BikeCode,Model,Price FROM Bike WHERE BikeCode = '". $id ."'";

[MDCode]

deleted ^^ quick typing there

[Krux20]

Omg Thank you, Marcus and you too SocialCloud!

[Psycho]

Are you even sure that the query is being run? You have the query in a foreach() loop (which is very poor practice: see updated code below that will get ALL the records with one query) but what if there are no items in the array? You also have if() conditions in your code. Have you validated that what you think the variables contain they actually contain? Add debugging code into your scripts so you can see exactly what is happening rather than running code and scratching your head when you don't get the output you expect!

 

Also, why are you storing your cart info as a comma separated string? It looks like you are repeating each item id based upon the quantity. That's way too complicated. You are storing it in the session array, just make a sub array using the id as the index and the value as the quantity (which is what you are trying to convert it into anyway).

 

Give the following a try - not tested so there may be some minor typos

 

function showCart()
{
   global $db;

   //Prepare cart data from session variable
   $itemsAry = array();
   if (isset($_SESSION['cart']))
   {
    //Convert cart data to an array
    $itemsAry = explode(',', $_SESSION['cart']);
    //Convert items to integers
    $itemsAry = array_map('mysql_real_escape_string', $items);
    //Remove false/empty values
    $itemsAry = array_filter($items);

    //Create array using IDs as key and qty as value
    $cartAry = array_count_values($itemsAry);
   }

   //Create comma separated string of just the item IDs (enclosed in quotes)
   $cartIDs = "'" . implode("', '", array_keys($cartAry)) . "'";

   //Create and run query
   $query = "SELECT BikeCode, Model, Price
		  FROM Bike
		  WHERE BikeCode IN ({$cartIDs})";
   $result = $db->query($query);

   if(!$result)
   {
    //Query failed
    $output = "<p>There was a problem getting your cart.</p>";
    //Uncomment next line for debugging
    // $output .= "<br>Query: {$query}<br>Error: " . mysql_error();
   }
   elseif(!mysql_num_rows($result))
   {
    //Cart was empty
    $output = "<p>Your shopping cart is empty.</p>";
   }

   else
   {
    //Start form
    $output  = "<form action=\"cart.php?action=update\" method=\"post\" id=\"cart\">\n";
    $output .= "<table>\n";

    $grand_total = 0;
    while($row = $result->fetch())
    {
	    $quantity = $cartAry[$row['BikeCode']];
	    $sub_total = $quantity * $row['Price'];
	    $grand_total += $sub_total;
	    $output .= "<tr>\n";
	    $output .= "<td><a href=\"cart.php?action=delete&id={$id}\" class=\"r\">Remove</a></td>\n";
	    $output .= "<td>{$row['BikeCode']}</td>\n";
	    $output .= "<td>£ {$row['Price']}</td>\n";
	    $output .= "<td><input type=\"text\" name=\"qty[{$row['BikeCode']}] value=\"{$quantity}\" size=\"3\" maxlength=\"3\" /></td>\n";
	    $output .= "<td>£{$sub_total}</td>\n";
	    $output .= "</tr>\n";
    }

    //Close form
    $output .= "</table>\n";
    $output .= "<p>Grand total: <strong>£{$total}</strong></p>\n";
    $output .= "<div><button type=\"submit\">Update cart</button></div>\n";
    $output .= "</form>\n";
   }

   return $output;
}