[SOLVED] File EXISTS command with QUERY ? its for paging system ...

[gordo2dope]

ok well i have a file exists on my page.  It check to see if there is a picture of the product.  If it exists, then it displays the item.  It works just fine.  I recently got a paging system to work to split the query results into numerous pages.  Now here is how it normaly works

 

QUERY

IF FILE EXISTS

  - display item

 

now here is how the paging system works..

QUERY - AND COUNT # OF ROWS MATCHING DATA

QUERY - split rows of matching data by PAGE LIMIT, etc.

 

NOW, the problem is when you mix them, the file exists works, but on the page #'s on the bottom counts ALL ITEMS MATCHING QUERY, just doesnt display them because of the file exists.  My question is to know if there is a way do the FILE EXISTS in conjunction with the paging system. 

 

so for example. i have 40 items in database, 4 of them have pics.  Paging system shows 4 per page... so the query finds all 40 items that match criteria and divides them my page limit (4) so you get 20 pages.  So the first page has the 4 items with pictures, then the last 19 pages display nothing because the FILE EXISTS is over riding it. 

 

So again, is there a way to use the FILE EXISTS inside a query, or do the FIEL EXISTS and make a variable out of it and use that somehow? THANKS!

[gordo2dope]

NOBODY ?

 

When I first started, I had a field in the table called "PICTURE" and it was YES OR NO, and in the PHP in the Query i had "if picture = yes", etc.  Then i found out the file exists thing and that was way better than the old solution, but NOW, i could use that field again.  Is there some way to use the file exists in conjuction with a query, to only SELECT the stuff that had a successful FILE EXISTS command ?

[lemmin]

Could you show some code on how you are doing this?

 

You could go through the list looking for file exists, print them and remove them from the list. then print the rest of the ones you want.

[gordo2dope]

yeah sorry i didnt post code earlier, I was making a smaller simpler test page to go from, here is the code below.  The first query checks the results and counts them to figure out the pages.  Then the second query searches for the SAME thing, but limits it depending on the number of pages it determines.  I need to limit BOTH of these queries with a file exists.    You'll notice I already have a FILE EXIST in the middle of the page after the second query.  This is for the WHILE thats checked each result, i don't want it displaying if a pic dont exists, this works fine, i just need to have the other queries work somehow to NOT COUNT the results if i don't have a picture for it.

 

[pre]

<?
mysql_connect("xxxx", "xxxx", ""xxxx) or die(mysql_error());
mysql_select_db("mczippo") or die(mysql_error());

//if ((file_exists("images/motorclothes/" . $strSQL['Cat1'] . "/" . $strSQL['Cat2'] . "/" . $strSQL['Style'] . ".jpg")) AND (file_exists("images/motorclothes/" . $strSQL['Cat1'] . "/" . $strSQL['Cat2'] . "/" . $strSQL['Style'] . ".gif")))
//{
//	$picexists = 'yes';
//}

$pagelimit = "2";

// need to somehow get this query below to work with file exists!! FOR EXAMPLE: 40 results, but only 4 have pictures in varaibled folder, so i need it to only find the 4 results

$strSQL = mysql_query("SELECT * FROM `mczippo1` WHERE Cat2 ='zippo lighters' AND OnHand > MinQty GROUP BY Style ORDER BY OnHand Desc");

   $totalrows = mysql_num_rows($strSQL);
   $pagenums = ceil ($totalrows/$pagelimit);
    if ($page==''){
        $page='1';
    }
    $start = ($page-1) * $pagelimit;
$starting_no = $start;
if ($totalrows - $start < $pagelimit) {
   $end_count = $totalrows;
} elseif ($totalrows - $start >= $pagelimit) {
   $end_count = $start + $pagelimit;
}
echo "<div align='center'>Showing $starting_no to $end_count of " . $totalrows . " results<br>\n";

if ($totalrows - $end_count > $pagelimit) 
	{
   $var2 = $pagelimit;
	} 
	elseif ($totalrows - $end_count <= $pagelimit) 
		{
		   $var2 = $totalrows - $end_count;
		}
$space = " ";
if ($page > 1) 
{
        echo "« <a href='page4.php?page=".($page-1)."' class=main>Previous" . $space . $pagelimit . "</a>" . $space . $space . "";
    }
    for ($i=1; $i<=$pagenums; $i++) 
	{
        if ($i!=$page) 
				{
            echo " <a href='page4.php?page=$i' class=main>$i</a>";
        			}
       				 else 
					{
		            echo " <b class='red'>$i</b>";
		        	}
}
    if ($page < $pagenums) 
{
        echo "" . $space . $space . $space . $space . " <a href='page4.php?page=".($page+1)."' class=main>Next " . $var2 . "</a>»";
}

$strSQL = mysql_query("SELECT * FROM `mczippo1` WHERE Cat2='Zippo Lighters' AND OnHand > MinQty GROUP BY Style ORDER BY OnHand Desc LIMIT $start,$pagelimit");
while ($part = mysql_fetch_array($strSQL))
	{
		if ((file_exists("images/motorclothes/" . $part['Cat1'] . "/" . $part['Cat2'] . "/" . $part['Style'] . ".jpg")) AND (file_exists("images/motorclothes/" . $part['Cat1'] . "/" . $part['Cat2'] . "/" . $part['Style'] . ".gif")))
		{
			echo 	"<div align='center'><br>
					<form action='mcdisplaypart.php?Categ1=Shirt&Categ2=Tee&Style=" . $part['Style'] . "' METHOD='post' style='display:inline;'>
					<INPUT TYPE='image' src='images/motorclothes/" . $part['Cat1'] . "/" . $part['Cat2'] . "/" . $part['Style'] . ".gif' BORDER=0 ALT='" . $part['Style'] . "'>
					</form><br>
					";
		}
	}
	echo " 	</td>
			</tr>
			</table>";



?> 

[/pre]

[lemmin]

I don't think I fully understand what you are trying to do, but what is wrong with doing the file exists check after the first query?

[gordo2dope]

watch, here is the problem i took a screen shot.  ok, say thers 40 items that CATEGORY 2 = zippo lighters....  but only 4 have pictures.. the paging system counts EVERYTHING thats in Category 2 - Zippo Lighters, and adds it to the number of pages...  here is a screen shot of what im talking about.  For test purposes I set the page limit to 2 (to only show 2 items per page)  so the first 2 pages have lighters, and the rest have NOTHING,  this is the problem...

 

[gordo2dope]

ok here i change the PAGELIMIT to 5, so it shows 5 items per page, since these 4 are the only 1s with pics, the FILE EXISTS only shows those 4, however... the row counting QUERY does not have a way to check if FILE EXISTS, so as you can see, it only shows the 4, but still COUNTS all the other items in that category.

 

this is the query that finds the rows and counts them.  I need the WHERE clause to somehow know if the file exists and to count the row if it does.  So is there a way to query and if the file exists, then creata a variable out of it and add it to the query or something?

$strSQL = mysql_query("SELECT * FROM `mczippo1` WHERE Cat2 ='zippo lighters' AND OnHand > MinQty GROUP BY Style ORDER BY OnHand Desc");

 

Here is with page limit = 5, as you can see the number of pages went down because 40 results, 5 per page..  = 9 pages. if you go to page 2, the FILE EXISTS knows the picture does not exists, so it doesnt display the item, but again, the query still counts it in the results.

 

 

PS. sorry the code comes out so ugly above post, it doesnt look like that on my screen.  Yes mine is spaced for easy reading, but not like that. hahaha

[lemmin]

You should query for the number of items, check for file exists, and loop through only those ones that have pictures. If you needed to do it in a second query you could make a query string out of the ones that have pictures in a loop where you append a string with "WHERE item = '$OneThatHasAPicture' ", then print that query out.

[gordo2dope]

thats what it is doing... QUERY the number of items, and looping the print for the ones with pictures.  The problem isnt printing them, its COUNTING the ones with pictures, thats what i need to know how to do...  thanks for your help by the way.  Ive been stuck on this for a while.    I need to count ONLY the ones with pictures and use that final total to determine the number of pages.  Here I cleaned up my code a little and separated it into the 2 queries.

 

this first query finds the items in the table and counts them and displays the paging #'s at the top.

[pre]

$pagelimit = "5";

$strSQL = mysql_query("SELECT * FROM `mczippo1` WHERE Cat2 ='zippo lighters' AND OnHand > MinQty GROUP BY Style ORDER BY OnHand Desc");

$totalrows = mysql_num_rows($strSQL);
$pagenums = ceil ($totalrows/$pagelimit);
if ($page==''){
$page='1';
}
$start = ($page-1) * $pagelimit;
$starting_no = $start;
if ($totalrows - $start < $pagelimit) {
$end_count = $totalrows;
} elseif ($totalrows - $start >= $pagelimit) {
$end_count = $start + $pagelimit;
}
echo "<div align='center'>Showing $starting_no to $end_count of " . $totalrows . " results<br>\n";
if ($totalrows - $end_count > $pagelimit) 
{
$var2 = $pagelimit;
} 
elseif ($totalrows - $end_count <= $pagelimit) 
{
$var2 = $totalrows - $end_count;
}
$space = " ";
if ($page > 1) 
{
echo "« <a href='page4.php?page=".($page-1)."' class=main>Previous" . $space . $pagelimit . "</a>" . $space . $space . "";
}
for ($i=1; $i<=$pagenums; $i++) 
{
if ($i!=$page) 
{
echo " <a href='page4.php?page=$i' class=main>$i</a>";
}
else 
{
echo " <b class='red'>$i</b>";
}
}
    if ($page < $pagenums) 
{
        echo "" . $space . $space . $space . $space . " <a href='page4.php?page=".($page+1)."' class=main>Next " . $var2 . "</a>»";
}

[/pre]

 

this second query loops through them and prints them.  this has nothing to do with the pages though EXCEPT, the query has a limit on it as to how many to display per page.

 

[pre]

$strSQL = mysql_query("SELECT * FROM `mczippo1` WHERE Cat2='Zippo Lighters' AND OnHand > MinQty GROUP BY Style ORDER BY OnHand Desc LIMIT $start,$pagelimit");
while ($part = mysql_fetch_array($strSQL))
{
if ((file_exists("images/motorclothes/" . $part['Cat1'] . "/" . $part['Cat2'] . "/" . $part['Style'] . ".jpg")) AND (file_exists("images/motorclothes/" . $part['Cat1'] . "/" . $part['Cat2'] . "/" . $part['Style'] . ".gif")))
{
echo 	"<div align='center'><br>
<form action='mcdisplaypart.php?Categ1=Shirt&Categ2=Tee&Style=" . $part['Style'] . "' METHOD='post' style='display:inline;'>
<INPUT TYPE='image' src='images/motorclothes/" . $part['Cat1'] . "/" . $part['Cat2'] . "/" . $part['Style'] . ".gif' BORDER=0 ALT='" . $part['Style'] . "'>
</form><br>
";
}
}
echo " 	</td>
</tr>
</table>";
?>

[/pre]

[lemmin]

That is my point. Your code currently gets everything, whether it has a picture or not and goes straight into printing the number of pages. What you need to do is, before printing the number of pages, check if the file exists. if it does, make a couter increment and at the end print the number of pages as that counter divided by how ever many pictures you want on a page.

[gordo2dope]

well yeah i get thats what needs to happen, i just dont know how to do it =)  hahaha thats why im here.  ok say i add the file exists BEFORE it counts the pages.  here is what it would look like.  However, i don't know how to code a counter increment if  $picexists=yes.  I know its probably just 1 line, but I don't know how.  I dont know what to put AFTER the FILE EXISTS and BEFORE THE COUNT...

[pre]

$pagelimit = "5";
$strSQL = mysql_query("SELECT * FROM `mczippo1` WHERE Cat2 ='zippo lighters' AND OnHand > MinQty GROUP BY Style ORDER BY OnHand Desc");
if ((file_exists("images/motorclothes/" . $strSQL['Cat1'] . "/" . $strSQL['Cat2'] . "/" . $strSQL['Style'] . ".jpg")) AND (file_exists("images/motorclothes/" . $strSQL['Cat1'] . "/" . $strSQL['Cat2'] . "/" . $strSQL['Style'] . ".gif")))
{
$picexists = 'yes';
}

$totalrows = mysql_num_rows($strSQL);
$pagenums = ceil ($totalrows/$pagelimit);

[/pre]

 

 

[lemmin]

If you replace this line:

$totalrows = mysql_num_rows($strSQL);

 

with this:

while ($part = mysql_fetch_array($strSQL))
	{
		if ((file_exists("images/motorclothes/" . $part['Cat1'] . "/" . $part['Cat2'] . "/" . $part['Style'] . ".jpg")) AND (file_exists("images/motorclothes/" . $part['Cat1'] . "/" . $part['Cat2'] . "/" . $part['Style'] . ".gif")))
			$totalrows++;
	}

(Which is basically copied from lower in your code)

The total rows should be the number of pictures availabe.

[gordo2dope]

HAH! YES! it worked hahah.  i had to adapt it to my other page where it shows up a little different, compared to the test page, just change some variables but it works.  Man you r the shiznit.  i knew there had to be a way.  THANKS!!!!!