Prev | Next Linking - PHPBuilder Forums

Prev | Next Linking

cyronuts

Hi, attempting to set prev | next links in a cart display if items returned from the db are over 5. Any ideas, or pointers on how to code this??? thank you all!



<?

include("db.php");
require_once("sc_fns.php");

 db_connct();

 mysql_select_db("icart");

 $query = "SELECT * FROM html_pages WHERE html_page_name = 'site_header'";

 $result = mysql_query($query);

 $num_results = mysql_num_rows($result);

for ($i=0; $i <$num_results; $i++)

{

$row = mysql_fetch_array($result); 



echo unhtmlentities($row["html_page_code"]);

echo "<BR>";

}

?>
<?php

// This page will list all of the items
// from the items table. Each item will have
// a link to add it to the cart


// Get a connection to the database
$cxn = @ConnectToDb($dbServer, $dbUser, $dbPass, $dbName);
$result = mysql_query("select * from items order by itemName asc");

 ?>
	<html>
	<head>
	<title> Product List </title>
	</head>


	<body bgcolor="#ffffff">

	<table width="555" cellspacing="0" cellpadding="0" border="0">
		<tr>
			<td width="30%" height="25" bgcolor="red">
				<font face="verdana" size="1" color="white">
					&nbsp;&nbsp;<b>Product</b>
				</font>
			</td>
			<td width="10%" height="25" bgcolor="red">
				<font face="verdana" size="1" color="white">
					<b>Price</b>
				</font>
			</td>
			<td width="50%" height="25" bgcolor="red">
				<font face="verdana" size="1" color="white">
					<b>Description</b>
				</font>
			</td>
			<td width="10%" height="25" bgcolor="red">
				<font face="verdana" size="1" color="white">
					<b>Add</b>
				</font>
			</td>
		</tr>
		<?php
		while($row = mysql_fetch_array($result))
		{
		?>
			<tr>
				<td width="30%" height="25">
					<font face="verdana" size="1" color="black">
						<?php echo $row["itemName"]; ?>
					</font>
				</td>
				<td width="10%" height="25">
					<font face="verdana" size="1" color="black">
						$<?php echo $row["itemPrice"]; ?>
					</font>
				</td>
				<td width="50%" height="25">
					<font face="verdana" size="1" color="black">
						<?php echo $row["itemDesc"]; ?>
					</font>
				</td>
				<td width="10%" height="25">
					<font face="verdana" size="1" color="black">
						<a href="products.php?action=add_item&id=<?php echo $row["itemId"]; ?>&qty=1">Add Item</a>
					</font>
				</td>
			</tr>
			<tr>
				<td width="100%" colspan="4">
					<hr size="1" color="red" NOSHADE>
				</td>
			</tr>
		<?php
		}
	?>
		<tr>
			<td width="100%" colspan="4">
				<font face="verdana" size="1" color="black">
					<a href="products.php">Your Shopping Cart &gt;&gt;</a>
				</font>
			</td>
		</tr>
	</table>

	<BR><BR><BR>

<?php



	// Get a connection to the database

	$cxn = @ConnectToDb($dbServer, $dbUser, $dbPass, $dbName);



switch($_GET["action"])

{

	case "add_item":

	{

		AddItem($_GET["id"], $_GET["qty"]);

		ShowCart();

		break;

	}

	case "update_item":

	{

		UpdateItem($_GET["id"], $_GET["qty"]);

		ShowCart();

		break;

	}

	case "remove_item":

	{

		RemoveItem($_GET["id"]);

		ShowCart();

		break;

	}

	default:

	{

		ShowCart();

	}

}



function AddItem($itemId, $qty)

{

	// Will check whether or not this item

	// already exists in the cart table.

	// If it does, the UpdateItem function

	// will be called instead



	global $dbServer, $dbUser, $dbPass, $dbName;







	// Check if this item already exists in the users cart table

	$result = mysql_query("select count(*) from cart where cookieId = '" . GetCartId() . "' and itemId = $itemId");

	$row = mysql_fetch_row($result);

	$numRows = $row[0];



	if($numRows == 0)

	{

		// This item doesn't exist in the users cart,

		// we will add it with an insert query



		@mysql_query("insert into cart(cookieId, itemId, qty) values('" . GetCartId() . "', $itemId, $qty)");

	}

	else

	{

		// This item already exists in the users cart,

		// we will update it instead



		UpdateItem($itemId, $qty);

	}

}



function UpdateItem($itemId, $qty)

{

	// Updates the quantity of an item in the users cart.

	// If the qutnaity is zero, then RemoveItem will be

	// called instead



	global $dbServer, $dbUser, $dbPass, $dbName;



	// Get a connection to the database

	$cxn = @ConnectToDb($dbServer, $dbUser, $dbPass, $dbName);



	if($qty == 0)

	{

		// Remove the item from the users cart

		RemoveItem($itemId);

	}

	else

	{

		mysql_query("update cart set qty = $qty where cookieId = '" . GetCartId() . "' and itemId = $itemId");

	}

}



function RemoveItem($itemId)

{

	// Uses an SQL delete statement to remove an item from

	// the users cart



	global $dbServer, $dbUser, $dbPass, $dbName;



	// Get a connection to the database

	$cxn = @ConnectToDb($dbServer, $dbUser, $dbPass, $dbName);



	mysql_query("delete from cart where cookieId = '" . GetCartId() . "' and itemId = $itemId");

}



function ShowCart()

{

	// Gets each item from the cart table and display them in

	// a tabulated format, as well as a final total for the cart



	global $dbServer, $dbUser, $dbPass, $dbName;



	// Get a connection to the database

	$cxn = @ConnectToDb($dbServer, $dbUser, $dbPass, $dbName);



	$totalCost = 0;

	$result = mysql_query("select * from cart inner join items on cart.itemId = items.itemId where cart.cookieId = '" . GetCartId() . "' order by items.itemName asc");

	?>

	<html>

	<head>

	<title> Your Shopping Cart </title>

	<script language="JavaScript">



		function UpdateQty(item)

		{

			itemId = item.name;

			newQty = item.options[item.selectedIndex].text;



			document.location.href = 'products.php?action=update_item&id='+itemId+'&qty='+newQty;

		}



	</script>

	</head>

	<body bgcolor="#ffffff">

	<h5>Your Shopping Cart</h5>

	<form name="frmCart" method="get">

	<table width="100%" cellspacing="0" cellpadding="0" border="0">

		<tr>

			<td width="15%" height="25" bgcolor="red">

				<font face="verdana" size="1" color="white">

					&nbsp;&nbsp;<b>Qty</b>

				</font>

			</td>

			<td width="55%" height="25" bgcolor="red">

				<font face="verdana" size="1" color="white">

					<b>Product</b>

				</font>

			</td>

			<td width="20%" height="25" bgcolor="red">

				<font face="verdana" size="1" color="white">

					<b>Price Each</b>

				</font>

			</td>

			<td width="10%" height="25" bgcolor="red">

				<font face="verdana" size="1" color="white">

					<b>Remove?</b>

				</font>

			</td>

		</tr>

		<?php



		while($row = mysql_fetch_array($result))

		{

			// Increment the total cost of all items

			$totalCost += ($row["qty"] * $row["itemPrice"]);

			?>

				<tr>

					<td width="15%" height="25">

						<font face="verdana" size="1" color="black">

							<select name="<?php echo $row["itemId"]; ?>" onChange="UpdateQty(this)">

							<?php



								for($i = 1; $i <= 20; $i++)

								{

									echo "<option ";

									if($row["qty"] == $i)

									{

										echo " SELECTED ";

									}

									echo ">" . $i . "</option>";

								}

							?>

							</select>

						</font>

					</td>

					<td width="55%" height="25">

						<font face="verdana" size="1" color="black">

							<?php echo $row["itemName"]; ?>

						</font>

					</td>

					<td width="20%" height="25">

						<font face="verdana" size="1" color="black">

							$<?php echo number_format($row["itemPrice"], 2, ".", ","); ?>

						</font>

					</td>

					<td width="10%" height="25">

						<font face="verdana" size="1" color="black">

							<a href="products.php?action=remove_item&id=<?php echo $row["itemId"]; ?>">Remove</a>

						</font>

					</td>

				</tr>

			<?php

		}



		// Display the total

		?>

				<tr>

					<td width="100%" colspan="4">

						<hr size="1" color="red" NOSHADE>

					</td>

				</tr>

				<tr>

					<td width="70%" colspan="2">

						<font face="verdana" size="1" color="black">

							<a href="products.php">&lt;&lt; Keep Shopping</a>

						</font>

					</td>

					<td width="30%" colspan="2">

						<font face="verdana" size="2" color="black">

							<b>Total: $<?php echo number_format($totalCost, 2, ".", ","); ?></b>

						</font>

					</td>

				</tr>

			</table>

			</form>


		<?php

}



?>


</body>
</html>

juschillinnow

I have this code on my site and it works fine. You may have to change it some to fit your needs. Have fun scramblin through🙂

See it in action:

https://www2.serve.com/ttt/php/store.php?categoryID=8&categoryName=African%20American&current=1

[/code]
<?
print("OUT OF STOCK Items will be available for shipping in 2 - 6 weeks from order date. Your account will NOT be charged for any items ordered that are OUT OF STOCK until they come in. ");
$limit=10; // rows to return
$numresults=mysql_query("select * from storeProduct where storeProduct.productCategory = '$categoryID' order by productName");
$numrows=mysql_num_rows($numresults);

// next determine if offset has been passed to script, if not use 0
if (empty($offset)) {
$offset=0;
}
$query = "select * from storeProduct where storeProduct.productCategory = '$categoryID' order by productName limit $offset, $limit";
$mysql_result = mysql_query($query, $mysql_link);
$num_rows = mysql_num_rows($mysql_result);
printf("%s \n", $categoryName);
echo "$numrows Items ";
if ($offset==0) { // bypass PREV link if offset is 0
print(" ");
}
else
{
$prevoffset=$offset-10;
printf("<a href=\"$PHP_SELF?categoryID=%s&categoryName=%s&offset=%s&current=%s&i=%s\">PREV</a>   \n", $categoryID, $categoryName, $prevoffset, $current-1, $i-1);
}

// calculate number of pages needing links
$pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division
if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}
for ($i=1;$i<=$pages;$i++) { // loop thru
$newoffset=$limit*($i-1);
if($current==$i)
{
print("$i ");
}
else
{
printf("<a href=\"$PHP_SELF?categoryID=%s&categoryName=%s&offset=%s&current=%s\">$i</a>   \n", $categoryID, $categoryName, $newoffset, $i);
}
}
// check to see if last page
if (!(($offset/$limit)==$pages) && $pages!=1) {
$newoffset=$offset+$limit;
}
if($newoffset >= $numrows)
{
print(" ");
}
else
{
printf("<a href=\"$PHP_SELF?categoryID=%s&categoryName=%s&offset=%s&current=%s&i=%s\">NEXT</a>", $categoryID, $categoryName, $newoffset, $current+1, $i+1);
}
print("<table width=\"500\" align=\"center\" border=\"0\" cellpadding=\"0\" cellspacing=\"3\">");
print("<tr><td align=\"center\" width=\"70\">Order</td>
<td align=\"center\" width=\"90\">Image</td><td align=\"center\" width=\"150\">Description</td>
<td align=\"center\" width=\"63\">Price</td><td align=\"center\" width=\"63\">Case Price</td><td align=\"center\" width=\"63\">Case Pack<td></tr>");
print("</table>");
while($row = mysql_fetch_row($mysql_result))
{
print("<table width=\"500\" align=\"center\" border=\"0\" cellpadding=\"0\" cellspacing=\"3\">");
print("<tr>\n");
printf("<td width=\"70\" valign=\"center\" align=\"center\"><a href=\"store.php?categoryID=%s&categoryName=%s&productID=%s\"><img src =\"https://www2.serve.com/ttt/pf/order.gif\" border=\"0\"></a> see more info...</td><td width=\"90\" align=\"center\"><img border=\"0\" src=\"https://www2.serve.com/ttt/pic/%s\" width=\"85\" height=\"85\"></td>\n", $row[2], $categoryName, $row[0], $row[4]);
printf("<td alidn=\"center\" width=\"150\">%s %s</td>", $row[1], $row[3]);
printf("<td align=\"center\" width=\"63\">%s</td>", $row[5]);
printf("<td align=\"center\" width=\"63\">%s</td>", $row[7]);
printf("<td align=\"center\" width=\"63\">%s</td>", $row[8]);
print("</tr>");
print("</table>\n");
print("<hr>\n");
}
if ($offset==0) { // bypass PREV link if offset is 0
print(" ");
}
else
{
$prevoffset=$offset-10;
printf("<a href=\"$PHP_SELF?categoryID=%s&categoryName=%s&offset=%s&current=%s&i=%s\">PREV</a>   \n", $categoryID, $categoryName, $prevoffset, $current-1, $i-1);
}

// calculate number of pages needing links
$pages=intval($numrows/$limit);

juschillinnow

Oh yeah, and i think you have to pass the string current = 0; or something like that from the referring url.

I forget, It's been a long time since writing that.

cyronuts

cool, thanks, I'll check this out, what did you mean by the 'current string = 0' ??

juschillinnow

I'm not sure. If you look in the code, you will see something about current or offset.

Well in order to set the initial value for one of those, i think you have to pass it in from the referring URL like:

or something like that. Again you'll have to kinda look at it and tear it apart yourself. This may not even be the case, I just vaguely remember something like that from with I wrote it.

cyronuts

Hi, tearing it apart right now, thanks.

One other question, where in the code can I pull back my data from my table under the cols you have set up? I seem to be missing something there...

Cyro

cyronuts

I've hacked it up a little bit, but can't seem to find out how to show my columm data from the db, it is showing nothing right now:


<?

include("db.php");

 require_once("sc_fns.php");
 db_connct();

 mysql_select_db("icart");


print("<font size=\"4\" color=\"#FF0000\">OUT OF STOCK</font> <b>Items will be available for shipping in 2 - 6 weeks from order date. Your account will NOT be charged for any items ordered that are </b><font size=\"4\" color=\"#FF0000\">OUT OF STOCK</font><b> until they come in.</b><br>");
$limit=10; // rows to return 
$numresults=mysql_query("select * from items order by itemName asc");
$numrows=mysql_num_rows($numresults); 

// next determine if offset has been passed to script, if not use 0 
if (empty($offset)) { 
$offset=0; 
} 
$query = "select * from items order by itemName asc limit $offset, $limit"; 
$mysql_result = mysql_query($query, $mysql_link); 
$num_rows = mysql_num_rows($mysql_result); 
printf("<font color=\"#24377E\" size=\"6\">%s</font><font color=\"#24377E\"><br>\n", $categoryName); 
echo "<b>$numrows Items</b></font><br>"; 
if ($offset==0) { // bypass PREV link if offset is 0 
print(" "); 
} 
else 
{ 
$prevoffset=$offset-10; 
printf("<a href=\"$PHP_SELF?categoryID=%s&categoryName=%s&offset=%s¤t=%s&i=%s\">PREV</a>   \n", $categoryID, $categoryName, $prevoffset, $current-1, $i-1); 
} 

// calculate number of pages needing links 
$pages=intval($numrows/$limit); 

// $pages now contains int of pages needed unless there is a remainder from division 
if ($numrows%$limit) { 
// has remainder so add one page 
$pages++; 
} 
for ($i=1;$i<=$pages;$i++) { // loop thru 
$newoffset=$limit*($i-1); 
if($current==$i) 
{ 
print("<font size=\"5\" color=\"#FF0000\"><b>$i </font></b>"); 
} 
else 
{ 
printf("<a href=\"$PHP_SELF?categoryID=%s&categoryName=%s&offset=%s¤t=%s\">$i</a>   \n", $categoryID, $categoryName, $newoffset, $i); 
} 
} 
// check to see if last page 
if (!(($offset/$limit)==$pages) && $pages!=1) { 
$newoffset=$offset+$limit; 
} 
if($newoffset >= $numrows) 
{ 
print(" "); 
} 
else 
{ 
printf("<a href=\"$PHP_SELF?categoryID=%s&categoryName=%s&offset=%s¤t=%s&i=%s\">NEXT</a><p>", $categoryID, $categoryName, $newoffset, $current+1, $i+1); 
} 
print("<table width=\"500\" align=\"center\" border=\"0\" cellpadding=\"0\" cellspacing=\"3\">"); 
print("<tr><td align=\"center\" width=\"70\"><b>Order</b></td> 
<td align=\"center\" width=\"90\"><b>Image</b></td><td align=\"center\" width=\"150\"><b>Description</b></td> 
<td align=\"center\" width=\"63\"><b>Price</b></td><td align=\"center\" width=\"63\"><b>Case Price</b></td><td align=\"center\" width=\"63\"><b>Case Pack</b><td></tr>"); 
print("</table>"); 
while($row = mysql_fetch_row($mysql_result)) 
{ 
print("<table width=\"500\" align=\"center\" border=\"0\" cellpadding=\"0\" cellspacing=\"3\">"); 
print("<tr>\n"); 
printf("<td width=\"70\" valign=\"center\" align=\"center\"><a href=\"store.php?categoryID=%s&categoryName=%s&productID=%s\"><img src =\"https://www2.serve.com/ttt/pf/order.gif\" border=\"0\"></a><font size=\"1\" color=\"#0000FF\"> see more info...</font></td><td width=\"90\" align=\"center\"><img border=\"0\" src=\"https://www2.serve.com/ttt/pic/%s\" width=\"85\" height=\"85\"></td>\n", $row[0], $row[1], $row[3], $row[4]); 
printf("<td align=\"center\" width=\"150\"><b>%s</b> %s</td>", $row[0], $row[3]); 
printf("<td align=\"center\" width=\"63\">%s</td>", $row[5]); 
printf("<td align=\"center\" width=\"63\">%s</td>", $row[7]); 
printf("<td align=\"center\" width=\"63\">%s</td>", $row[8]); 
print("</tr>"); 
print("</table>\n"); 
print("<hr>\n"); 
} 
if ($offset==0) { // bypass PREV link if offset is 0 
print(" "); 
} 
else 
{ 
$prevoffset=$offset-10; 
printf("<a href=\"$PHP_SELF?categoryID=%s&categoryName=%s&offset=%s¤t=%s&i=%s\">PREV</a>   \n", $categoryID, $categoryName, $prevoffset, $current-1, $i-1); 
} 

// calculate number of pages needing links 
$pages=intval($numrows/$limit); 

// $pages now contains int of pages needed unless there is a remainder from division 
if ($numrows%$limit) { 
// has remainder so add one page 
$pages++; 
} 
for ($i=1;$i<=$pages;$i++) { // loop thru 
$newoffset=$limit*($i-1); 
if($current==$i) 
{ 
print("<font size=\"5\" color=\"#FF0000\"><b>$i </font></b>"); 
} 
else 
{ 
printf("<a href=\"$PHP_SELF?categoryID=%s&categoryName=%s&offset=%s¤t=%s\">$i</a>   \n", $categoryID, $categoryName, $newoffset, $i); 
} 
} 
// check to see if last page 
if (!(($offset/$limit)==$pages) && $pages!=1) { 
$newoffset=$offset+$limit; 
} 
if($newoffset >= $numrows) 
{ 
print(" "); 
} 
else 
{ 
printf("<a href=\"$PHP_SELF?categoryID=%s&categoryName=%s&offset=%s¤t=%s&i=%s\">NEXT</a><p>", $categoryID, $categoryName, $newoffset, $current+1, $i+1); 
} 
?>

juschillinnow

Sorry I stepped out for a while.

I'm not quite sure what you meant?

can you re-phrase?

cyronuts

yes, sorry if I was vague....

where in the code does it pull the appropriate table data for each column in the html table?

thanks!

juschillinnow

Not sure if you have figured it out yet, I have been away from my computer all weekend.

But here's the part that takes data from the database


print("<table width=\"500\" align=\"center\" border=\"0\" cellpadding=\"0\" cellspacing=\"3\">");
print("<tr><td align=\"center\" width=\"70\"><b>Order</b></td>
<td align=\"center\" width=\"90\"><b>Image</b></td><td align=\"center\" width=\"150\"><b>Description</b></td>
<td align=\"center\" width=\"63\"><b>Price</b></td><td align=\"center\" width=\"63\"><b>Case Price</b></td><td align=\"center\" width=\"63\"><b>Case Pack</b><td></tr>");
print("</table>");
while($row = mysql_fetch_row($mysql_result))
{

if (preg_match("/<br><font size=\"4\" color=\"#FF0000\">GONE<\/font><br> /", $row[3])) {
//escape loop and do nothing;
} else {
print("<table width=\"500\" align=\"center\" border=\"0\" cellpadding=\"0\" cellspacing=\"3\">");
   print("<tr>\n");
   printf("<td width=\"70\" valign=\"center\" align=\"center\"><a href=\"store.php?categoryID=%s&categoryName=%s&productID=%s\"><img src =\"https://www2.serve.com/ttt/pf/order.gif\" border=\"0\"></a><font size=\"1\" color=\"#0000FF\"> see more info...</font></td><td width=\"90\" align=\"center\"><img border=\"0\" src=\"https://www2.serve.com/ttt/pic/%s\" width=\"85\" height=\"85\"></td>\n", $row[2], $categoryName, $row[0], $row[4]);
   printf("<td alidn=\"center\" width=\"150\"><b>%s</b>  %s</td>", $row[1], $row[3]);
   printf("<td align=\"center\" width=\"63\">%s</td>", $row[5]);
   printf("<td align=\"center\" width=\"63\">%s</td>", $row[7]);
   printf("<td align=\"center\" width=\"63\">%s</td>", $row[8]);
   print("</tr>");
print("</table>\n");
   print("<hr>\n");
   }

I just used the $row[5] etc. method. You can replace that with:

echo $your data;

or 

echo $row['columnName'];

This should really be the only part you have to change except for like the out of stock thing.

-juschillin

cyronuts

juschillin,

thanks! still pulling my hair out there, let me run the following by you:

What is this line doing:


printf("<td width=\"70\" valign=\"center\" align=\"center\"><a href=\"store.php?categoryID=%s&categoryName=%s&productID=%s\"><img src =\"https://www2.serve.com/ttt/pf/order.gif\" border=\"0\"></a><font size=\"1\" color=\"#0000FF\"> see more info...</font></td><td width=\"90\" align=\"center\"><img border=\"0\" src=\"https://www2.serve.com/ttt/pic/%s\" width=\"85\" height=\"85\"></td>\n", $row[0], $row[1], $row[3], $row[4]);

When I throw in the following two echo statements, under the mysql_fetch statement, something starts to happen, but it looks like it is picking up information from your site!?!? Here is the whole block:


print("<table width=\"500\" align=\"center\" border=\"0\" cellpadding=\"0\" cellspacing=\"3\">"); 
print("<tr><td align=\"center\" width=\"70\"><b>Order</b></td> 
<td align=\"center\" width=\"90\"><b>Image</b></td><td align=\"center\" width=\"150\"><b>Description</b></td> 
<td align=\"center\" width=\"63\"><b>Price</b></td><td align=\"center\" width=\"63\"><b>Case Price</b></td><td align=\"center\" width=\"63\"><b>Case Pack</b><td></tr>"); 
print("</table>"); 
while($row = mysql_fetch_row($mysql_result))

echo $row["itemName"];
echo $row["itemDesc"];

if (preg_match("/<br><font size=\"4\" color=\"#FF0000\">GONE<\/font><br> /", $row[3])) {
//escape loop and do nothing;
} else {

print("<table width=\"500\" align=\"center\" border=\"0\" cellpadding=\"0\" cellspacing=\"3\">");
print("<tr>\n"); 
printf("<td width=\"70\" valign=\"center\" align=\"center\"><a href=\"store.php?categoryID=%s&categoryName=%s&productID=%s\"><img src =\"https://www2.serve.com/ttt/pf/order.gif\" border=\"0\"></a><font size=\"1\" color=\"#0000FF\"> see more info...</font></td><td width=\"90\" align=\"center\"><img border=\"0\" src=\"https://www2.serve.com/ttt/pic/%s\" width=\"85\" height=\"85\"></td>\n", $row[0], $row[1], $row[3], $row[4]);
printf("<td align=\"center\" width=\"150\"><b>%s</b> %s</td>", $row[0]);
printf("<td align=\"center\" width=\"63\">%s</td>", $row[2]);
printf("<td align=\"center\" width=\"63\">%s</td>", $row[4]);
printf("<td align=\"center\" width=\"63\">%s</td>", $row[4]);
print("</tr>");
print("</table>\n");
print("<hr>\n");

Thanks for the help!!