[RESOLVED] MySQL Query Issues

RyanMinor

I have a query that works perfectly in PHPMyAdmin yet does not work in my website. Here is the query:

// get active video purchases for customer
$query_purchases = mysql_query("SELECT video_title, purchase_expire FROM (customer INNER JOIN (video_purchase INNER JOIN purchase ON video_purchase_purchase_id = purchase_id) ON customer_id = purchase_customer_id) INNER JOIN video ON video_purchase_video_id = video_id WHERE customer_id = $customer_id AND purchase_expire > NOW()", $connect) or die(mysql_error());
$data_purchases = mysql_fetch_assoc($query_purchases);
$rows_purchases = mysql_num_rows($query_purchases);
$row_count = 0;

It should return all of the active video purchases for a particular customer. When I run the query in PHPMyAdmin I get the right values, but when I run it in my website code, I get a repeat of the first video that is repeated for as many total active video purchases for the customer. Here is my php page code:

<?php
// initiate session
session_start();
// restrict unauthorized access
if (!isset($_SESSION['customer_id'])) {
	header("location:login.php");
}
// connect to database
require_once('includes/connect.php');

// get customer information
$customer_id = $_SESSION['customer_id'];
$query_customer = mysql_query("SELECT customer_username FROM customer WHERE customer_id = '$customer_id'", $connect) or die(mysql_error());
$data_customer = mysql_fetch_assoc($query_customer);

// get active video purchases for customer
$query_purchases = mysql_query("SELECT video_title, purchase_expire FROM (customer INNER JOIN (video_purchase INNER JOIN purchase ON video_purchase_purchase_id = purchase_id) ON customer_id = purchase_customer_id) INNER JOIN video ON video_purchase_video_id = video_id WHERE customer_id = $customer_id AND purchase_expire > NOW()", $connect) or die(mysql_error());
$data_purchases = mysql_fetch_assoc($query_purchases);
$rows_purchases = mysql_num_rows($query_purchases);
$row_count = 0;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta name="Description" content="Information architecture, Web Design, Web Standards." />
<meta name="Keywords" content="your, keywords" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link rel="stylesheet" href="includes/global.css" type="text/css" />
<title></title>
</head>
<body>

<!--wrap starts here-->
<div id="wrap">

<?php include('includes/header.php');

if(isset($_SESSION['customer_id'])) {
	include('includes/menu_customer.php');
} else {
	include('includes/menu.php'); 
} ?>					

<!--content-wrap starts here-->
<div id="content-wrap" class="two-col">	

<?php include('includes/sidebar.php'); ?>

<!--main starts here-->
<div id="main">
<h1>My Videos</h1>

<?php if ($rows_purchases == 0) { ?>
	<p align="center">You have no videos to view.</p>
    <p align="center"><a href="search.php">Purchase Videos</a></p>
	<p align="center"><a href="past_purchases.php">View My Past Purchases</a></p>
<?php } else { ?>

	<p>Welcome back, <?php echo $data_customer['customer_username']; ?>. The videos you have purchased are listed below.</p>

	<table width="75%" border="0" align="center" cellpadding="5" cellspacing="0">  
		<tr>
			<td width="6%" align="left" bgcolor="#000000" class="shopping_cart">#</td>
			<td width="53%" align="left" bgcolor="#000000" class="shopping_cart">Video Title</td>
			<td width="40%" align="center" bgcolor="#000000" class="shopping_cart">Expires</td>
		</tr>
		<tr>
		<?php do {
			$row_count++; ?>
			<td width="7%" align="left"><?php echo $row_count; ?></td>
			<td width="53%" align="left">
            <a href="view_video.php?video=<?php echo $data_purchases['video_title']; ?>"><?php echo $data_purchases['video_title']; ?></a>
            </td>
			<td width="40%" align="center">
				<?php echo date("D, M jS @ g:i:s a", strtotime($data_purchases['purchase_expire'])); ?>
            </td>
		</tr>
		<?php } while ($data_purchasess = mysql_fetch_assoc($query_purchases)); ?>
	</table>

	<p align="center"><a href="search.php">Purchase More Videos</a></p>
	<p align="center"><a href="past_purchases.php">View My Past Purchases</a></p>
<?php } ?>

<?php include('includes/banner_ads.php'); ?>

<!--main ends here-->
</div>

<!--content-wrap ends here-->
</div>

<?php if(isset($_SESSION['customer_id'])) {
	include('includes/footer_customer.php');
} else {
	include('includes/footer.php'); 
} ?>	

<!--wrap ends here-->
</div>

</body>
</html>

<?php mysql_free_result($query_customer); ?>
<?php mysql_free_result($query_purchases); ?>
<?php require_once('includes/disconnect.php'); ?>

Can anybody see why my web page will not display each video and instead just displays the first video for as many total purchases as the customer has?

dagon

since php is gouing to process the string that is your query before passing it to the db, you should echo it, then you can compare it with the version you say works.

RyanMinor

dagon;10985733 wrote:
since php is gouing to process the string that is your query before passing it to the db, you should echo it, then you can compare it with the version you say works.

I just did what you suggested by putting the query in a variable called $sql and then echoing that since echoing $query_purchases gave me a resource id#6. I then copy and pasted what was echoed from $sql into PHPMyAdmin, ran it, and it worked. My php page still does not work though. Any thoughts?

leatherback

Hm.. There are a lot of joins which may screw up the results from being as you expect them. I think you might be making life dificult by including a query to the user table (You do a user query beforehand, so why complicate things by including the table here). Also, I have a hard time understanding the need for a table purchase and a table video_purchase. I would say they should / could be merged, and in your purchase table have a colum puchase_type. Then for all type of purchases you can re-use your code.

If you rework the code a little, I would come to something like:


$q = "
SELECT video_title, purchase_expire 
  FROM purchase 
    INNER JOIN video_purchase
     ON purchase_id= video_purchase_purchase_id
    INNER JOIN video 
     ON video_purchase_video_id = video_id 
WHERE customer_id = ".$customer_id." 
AND purchase_expire > NOW()
";

RyanMinor

leatherback;10985760 wrote:
Hm.. There are a lot of joins which may screw up the results from being as you expect them. I think you might be making life dificult by including a query to the user table (You do a user query beforehand, so why complicate things by including the table here). Also, I have a hard time understanding the need for a table purchase and a table video_purchase. I would say they should / could be merged, and in your purchase table have a colum puchase_type. Then for all type of purchases you can re-use your code.

If you rework the code a little, I would come to something like:
$q = "
SELECT video_title, purchase_expire 
  FROM purchase 
    INNER JOIN video_purchase
     ON purchase_id= video_purchase_purchase_id
    INNER JOIN video 
     ON video_purchase_video_id = video_id 
WHERE customer_id = ".$customer_id." 
AND purchase_expire > NOW()
";

What I don't understand is why it works in PHPMyAdmin and not when I render the page. I will look at the DB structure and see if I can re-work the query as you said.

Derokorian

Found your problem:

			<?php } while ($data_purchasess = mysql_fetch_assoc($query_purchases)); ?>

Has a typo (extra s on the variable name), and should instead be:

			<?php } while ($data_purchases = mysql_fetch_assoc($query_purchases)); ?>

HTH

Derokorian

So while it looped thru assigning each row to a variable, until it runs out of rows. you were using the original row assigned each and every time thru. Fixing the typo should cause each row to be displayed properly =D

RyanMinor

Derokorian;10985873 wrote:
So while it looped thru assigning each row to a variable, until it runs out of rows. you were using the original row assigned each and every time thru. Fixing the typo should cause each row to be displayed properly =D

Wow, I should've caught it. For as many spelling errors as I make in coding you would think I invented the "spelling error"! Thank you, I will make the change when I get home from work and report back.

RyanMinor

That was it...thanks!