Hi guys, you've helped me out before and I'm hoping you'll be able to help me out again.
This is actually a two-part question.

I have a gallery which I'm trying to create, and while the current code I have works with one table only, but after doing union selects, it messes up.
Here's my code (I've omitted the unnecessary parts and simplified the table entries)

<?php
// database connection stuff here

$x = $_GET["year"];
$cat = $_GET["year"];
$i=1;

if ($cat=="")
{ $query .= "select * from table1 union select * from table2 order by datetime desc "; }
else
{ $query .= "select * from table1 union select * from table2 where year='$cat' order by datetime desc "; }

$result = mysql_query($query, $connection) or die
("Could not execute query : $query ." . mysql_error());

$rows = mysql_num_rows($result);
if ($rows=="0") { echo "Nothing found."; }

$db_query .= "select * from table1 union select * from table2 order by datetime desc ";

if($x!="")
$db_query .= "where year='".$x."' ";

// Start paging variables
$screen = $_GET['screen'];
$PHP_SELF = $_SERVER['PHP_SELF'];
$rows_per_page=16; // number of records per page
$total_records=mysql_num_rows($result);
$pages = ceil($total_records / $rows_per_page); // calculate number of pages required

if (!isset($screen))
$screen=0;
$start = $screen * $rows_per_page; // determine start record
$query .= "LIMIT $start, $rows_per_page";
$result= mysql_query($query) or die
("Could not execute query : $query ." . mysql_error());
while ($row=mysql_fetch_array($result))
{

$id=$row["id"];
$name=$row["name"];
$type=$row["type"];
$year=$row["year"];

include "(template.php)";

if ($i==4) {
echo "<div class=\"clear\"></div>";
$i=0;
}
$i++;
}

echo "<div class=\"clear\"></div>
<div class=\"wardrobe-pagination\">";

// create the links
if ($screen > 0) {
$j = $screen - 1;
$url = "{$_SERVER['PHP_SELF']}?year=$cat&screen=$j";
echo "<a href=\"$url\">Previous</a>"; // generate the prev text link so long as page is not first page
}

// page numbering links now
$p = 5; // number of links to display per page
$lower = $p; // set the lower limit to $p
$upper = $screen+$p; // set the upper limit to current page + number of links per page

while($upper>$pages){
$p = $p-1;
$upper = $screen+$p;
}
if($p<$lower){
$y = $lower-$p;
$to = $screen-$y;
while($to<0){
$to++;
}
}
if(!empty($to))
{ for ($i=$to;$i<$screen;$i++){
$url = "{$_SERVER['PHP_SELF']}?year=$cat&screen=" . $i;
$j = $i + 1;
echo "<a href=\"$url\">$j</a>";
}
}
for ($i=$screen;$i<$upper;$i++) {
$url = "{$_SERVER['PHP_SELF']}?year=$cat&screen=" . $i;
$j = $i + 1;
echo "<a href=\"$url\">$j</a>";
}

if ($screen < $pages-1) {
$j = $screen + 1;
$url = "{$_SERVER['PHP_SELF']}?year=$cat&screen=$j";
echo "<a href=\"$url\">Next</a>";
}
 ?>

Here is what I have in my tables:

table1
(id) (name) (year) (type)
1     straw  2010  dress
2     orange 2010  dress
3     blue   2009  dress
(it goes on but this is the general idea)

table2
(id) (name) (year) (type)
1    exile  2003   skirt
2    poof   2005   skirt
(etc...)

Now, as for my actual problem. When I have one table selected (ie. table1), the above code will display my results and I have the ability to categorize the year by using ".php?year=(year)". However, once I union select table2, when I attempt to categorize by year, the page will display ALL results (regardless of the year) on its corresponding page and will only display the selected year on top. (ie. instead of only displaying "2003" results, on page 1 it will display "2003, 2010, 2009, 2008, 2007" and on page 2 it will display "2003, 2010, 2010, 2010, 2009", when I want it to ONLY display 2003 results.)

I can show you the code in action, if it is any help.

As for the second part of my question, I want to be able to categorize by other categories (other than year), such as "type". How would I be able to do that?

try parenthesizing your selects:

...
else
{ $query .= "(select * from `table1` WHERE `year`='$cat') union (select * from `table2` where `year`='$cat') order by `year` desc "; }
...

As for the second part of your question, you could put a dropdown with the list of available categories to sort by. Whenever the user makes a selection it would submit a request to the server with that category.

Thank you so much hielo you are a lifesaver! The parenthesizes corrected the year problem.

I currently have the dropdowns, and while it works, it's not what I'm looking for.
If you look at my code above, to select the year the url becomes ".php?year=(year)".
What I'm looking to do is something like .php?brand(brand)=&type(type)=&year=(year)" like I've seen several shopping sites do, but I don't know how one would do that.

I'm assuming that somewhere you have an initial page where the person is specifying the year. Assuming you have:

<input type='text' name='year' value=''/>

similarly, you can provide the other search criteria:

<input type='text' name='year' value=''/>
<input type='text' name='brand' value=''/>
<input type='text' name='type' value=''/>

Now, if you want the results to meet ALL the search criteria, your selects will now need to be ANDed:

(select * from `table1` WHERE `year`='$cat' AND `type`='$type' AND `brand`='$brand')

as for your links, if you are able to generate: ".php?year=(year)" then generating .php?brand(brand)=&type(type)=&year=(year) should be trivial as long as you provide the initial search parameters on the INITIAL page, just like you have been doing with year.

Thank you again for your response.
I had to look up what you said, and I'm not using (HTML?) search parameters.

I'm using a PHP query string, and what I wanted to achieve was multiple variables over the query string. I've tried using the $_GET function but I've only been able to get it to work once (in the code I originally posted) for 'year' ($cat). I want it to be able to also work if I use the $_GET function with type/brand/whatever else I may need.

Try saving the following as hielo.php. Be sure to read comments in code. If problem persists, post a link to hielo.php:

<?php
// database connection stuff here

$searchType='AND';
$condition='';
$condition .= (isset($_GET['year']) && !empty($_GET['year']) ? $searchType.' `year`='.mysql_real_escape_string($_GET['year']): '');
$condition .= (isset($_GET['brand']) && !empty($_GET['brand']) ? $searchType.' `brand`='.mysql_real_escape_string($_GET['brand']): '');
$condition .= (isset($_GET['type']) && !empty($_GET['type']) ? $searchType.' `type`='.mysql_real_escape_string($_GET['type']): '');


if ($condition=="")
{
	$query = "(SELECT * FROM `table1`) UNION (SELECT * FROM `table2`) ORDER BY `year` DESC ";
}
else
{ 
	$condition=substr($condition,strlen($searchType));
	$query = "(SELECT * FROM `table1` WHERE $condition) UNION (SELECT * FROM `table2` WHERE $condition) ORDER BY `year` DESC ";
}

$totalQuery="SELECT COUNT(*) as `total` FROM ($query) as t";
$result = mysql_query($totalQuery, $connection) or die("Could not execute query : $totalQuery ." . mysql_error());

$rows = mysql_fetch_assoc($result);

if ($rows['total']==0) { echo "Nothing found."; }

// Start paging variables
$screen = intval($_GET['screen']);
$PHP_SELF = $_SERVER['PHP_SELF'];
$rows_per_page=16; // number of records per page
$total_records=$rows['total'];
$pages = ceil($total_records / $rows_per_page); // calculate number of pages required

if ($screen < 0)
	$screen=0;
$start = $screen * $rows_per_page; // determine start record
$query .= " LIMIT $start, $rows_per_page";

$result= mysql_query($query) or die("Could not execute query : $query ." . mysql_error());

$i=1;
while ($row=mysql_fetch_assoc($result))
{

	$id=$row["id"];
	$name=$row["name"];
	$type=$row["type"];
	$year=$row["year"];

	include "(template.php)";

	if ($i==4)
	{
		echo "<div class=\"clear\"></div>";
		$i=0;
	}
	$i++;
}

echo "<div class=\"clear\"></div>
<div class=\"wardrobe-pagination\">";

$year=rawurlencode($_GET['year']);
$brand=rawurlencode($_GET['brand']);
$type=rawurlencode($_GET['type']);

// create the links
//the only parameter that keeps changing in the links below is 'screen', so initialized $url
//outside any of your constructs, then within you just need to append the value for 'screen'
//at the end
$url = "{$_SERVER['PHP_SELF']}?year=$year&brand=$brand&type=$type&screen=";
if ($screen > 0) {
	$j = $screen - 1;
	echo '<a href="'.$url.$j.'">Previous</a>'; // generate the prev text link so long as page is not first page
}

// page numbering links now
$p = 5; // number of links to display per page
$lower = $p; // set the lower limit to $p
$upper = $screen+$p; // set the upper limit to current page + number of links per page

while($upper>$pages){
	$p = $p-1;
	$upper = $screen+$p;
}
if($p<$lower){
	$y = $lower-$p;
	$to = $screen-$y;
	while($to<0){
		$to++;
	}
}

//the following two constructs seem to generate the same set of links
//Perhaps you meant if-else instead of if-for???
if(!empty($to))
{ 
	for ($i=$to;$i<$screen;$i++){
		$j = $i + 1;
		echo '<a href="'.$url.$i.'">'.$j.'</a>';
	}
}
for ($i=$screen;$i<$upper;$i++) {
	$j = $i + 1;
	echo '<a href="'.$url.$i.'">'.$j.'</a>';
}

if ($screen < $pages-1) {
	$j = $screen + 1;
	echo '<a href="'.$url.$j.'">Next</a>';
}
?>

Sorry for the late response.

Hielo, I tried out your code, but it errors. I'm unable to select by brand or type, but year works just fine.

Here is the page I was working on, as you requested.

Replace the previous hielo.php with the following:

<?php
// database connection stuff here

$searchType='AND';
$condition ='';
$condition .= (isset($_GET['year'])  && !empty($_GET['year'])  ? $searchType."  `year`='" . mysql_real_escape_string($_GET['year'])  . "' " : '');
$condition .= (isset($_GET['brand']) && !empty($_GET['brand']) ? $searchType." `brand`='" . mysql_real_escape_string($_GET['brand']) . "' " : '');
$condition .= (isset($_GET['type'])  && !empty($_GET['type'])  ? $searchType."  `type`='" . mysql_real_escape_string($_GET['type'])  . "' " : '');


if(trim($condition)=="")
{
	$query = "(SELECT * FROM `table1`) UNION (SELECT * FROM `table2`) ORDER BY `year` DESC ";
}
else
{ 
	$condition=substr($condition,strlen($searchType));
	$query = "(SELECT * FROM `table1` WHERE $condition) UNION (SELECT * FROM `table2` WHERE $condition) ORDER BY `year` DESC ";
}

$totalQuery="SELECT COUNT(*) as `total` FROM ($query) as t";
echo sprintf(PHP_EOL.'<!-- %s: %s -->',__LINE__,$totalQuery);
$result = mysql_query($totalQuery, $connection) or die("Could not execute query : $totalQuery ." . mysql_error());

$rows = mysql_fetch_assoc($result);

if ($rows['total']==0) { echo "Nothing found."; }

// Start paging variables
$screen = intval($_GET['screen']);
$PHP_SELF = $_SERVER['PHP_SELF'];
$rows_per_page=16; // number of records per page
$total_records=$rows['total'];
$pages = ceil($total_records / $rows_per_page); // calculate number of pages required

if ($screen < 0)
	$screen=0;
$start = $screen * $rows_per_page; // determine start record
$query .= " LIMIT $start, $rows_per_page";
echo sprintf(PHP_EOL.'<!-- %s: %s -->',__LINE__,$query);
$result= mysql_query($query) or die("Could not execute query : $query ." . mysql_error());

$i=1;
while ($row=mysql_fetch_assoc($result))
{

	$id=$row["id"];
	$name=$row["name"];
	$type=$row["type"];
	$year=$row["year"];

	include "(template.php)";

	if ($i==4)
	{
		echo '<div class="clear"></div>';
		$i=0;
	}
	$i++;
}

echo '<div class="clear"></div>
<div class="wardrobe-pagination">';

$year=rawurlencode($_GET['year']);
$brand=rawurlencode($_GET['brand']);
$type=rawurlencode($_GET['type']);

// create the links
//the only parameter that keeps changing in the links below is 'screen', so initialized $url
//outside any of your constructs, then within you just need to append the value for 'screen'
//at the end
$url = "{$_SERVER['PHP_SELF']}?year=$year&brand=$brand&type=$type&screen=";
if ($screen > 0) {
	$j = $screen - 1;
	echo ' <a href="'.$url.$j.'">Previous</a>'; // generate the prev text link so long as page is not first page
}

// page numbering links now
$p = 5; // number of links to display per page
$lower = $p; // set the lower limit to $p
$upper = $screen+$p; // set the upper limit to current page + number of links per page

while($upper>$pages){
	$p = $p-1;
	$upper = $screen+$p;
}
if($p<$lower){
	$y = $lower-$p;
	$to = $screen-$y;
	while($to<0){
		$to++;
	}
}

//the following two constructs seem to generate the same set of links
//Perhaps you meant if-else instead of if-for???
if(!empty($to))
{ 
	for ($i=$to;$i<$screen;$i++){
		$j = $i + 1;
		echo ' <a href="'.$url.$i.'">'.$j.'</a>';
	}
}
for ($i=$screen;$i<$upper;$i++) {
	$j = $i + 1;
	echo ' <a href="'.$url.$i.'">'.$j.'</a>';
}

if ($screen < $pages-1) {
	$j = $screen + 1;
	echo ' <a href="'.$url.$j.'">Next</a>';
}
?>

Once again, thank you so much Hielo!
The code works perfectly.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.