Automatic count of items currently defined by select criteria

jonki

This is an idea I have just come up with and due to my limited php knowledge I have no idea if this is even possible. If anyone can point me in the right direction that would be great.

I would like my page to display the number of items available in the data base that are currently defined in the selection criteria.

For example - my database sells lets say, T-shirts and you have the choice of material, sizes and colour.

A number in the top right hand corner tells you that there are currently 76 T-shirts in my database (as the initial value of all pull down select options are "Select all")

The user then selects, from the material, pull down, "Cotton" - the number in the top right hand corner instantly changes to 47 to show that there are 47 different cotton T-shirts.
The user decides that this is still too many to choose from so he selects the colour, say, blue - which drops the number down to 23 - which he then decides is a reasonable quantity to search through and there for clicks on submit form.

It would be fantastic if some sort of code already exists and please don't assume that I am asking anyone to do the work for me - all I would like to know is that would this be possible and any ideas where I should start?

Thanks a lot.

Jon

LordShryku

Based on your query, just use [man]mysql_num_rows[/man], assuming you're using MySQL

jonki

Thanks for your help - but I know enough about php to have worked out that for myself.

I read your bit about how to post threads and I assume that I am being catagorised among these brainless people that you seem to have some sort of dislike to.

I appologise if you think that I am acting in this way but I think you have mis-read my question. If I had one word to go on maybe I could search Google or the php manual (which I have done many times in the past) but this particular question I have is beyond anything I am currently aware of. I don't even know if such a thing exists.

Again my appologies for any misunderstandings.

LordShryku

The link is part of my signature, and is not directed at you or anyone in particular.

And if mysql_num_rows wouldn't work, explain why. This sounds like a fairly simple thing to do, so we just need to figure out your circumstances.

jonki

No worries - it is quite harshly written that text and I didn't want you to assume that I was lazily asking someone to do my work for me.

What I am looking for specificaly is a way of perhaps refreshing the page (or better still - a small frame somewhere) with the results of a MySQL query (in this case count() ) - without the user clicking on "submit".
That it somehow gets activated just by selcting something from a pull down menu - a bit like <select name="choice" onChange="jump(this.form)"> in html.

Is there anything like this that would perhaps send the MySQL query, without altering the form and just pull back the count() and stick it somewhere, just by clicking in an option in a pull-down?

Just point me in the right direction - I'd really appreciate that.

Cheers.

Jon

kellog

I think it would be harder to find a script that did something like this, and then modify if to fit, than it would be to just write it. It's not that complicated if you build it a step at a time.

Basic concept, when the user changes the select box, the page submits itself, and passes some values to itself via POST, that you'll use to modify your sql string.

Would go something like this:

<?
\\<test.php>
\\Dynamicly build a query string depending on what info has been tossed to the page
$SQL = "SELECT * FROM database ";
if ($_POST['SelectBox1') \\if a meterial has been selected, then include it in the query
{
  $SQL .= "WHERE material = $_POST['SelectBoxMaterial'] ";
  $Where = True;
}
elseif ($_POST['SelectBox2')
{
	if (!$Where)
	{
		$SQL .= "WHERE color = $_POST['SelectBoxColor'] ";
	}
	else
	{
		$SQL .= "AND color = $_POST['SelectBoxColor'] ";
	}
}
elseif ($_POST['SelectBox2')
{
	if (!$Where)
	{
		$SQL .= "WHERE size = $_POST['SelectBoxSize'] ";
	}
	else
	{
		$SQL .= "AND size = $_POST['SelectBoxSize'] ";
	}
}

$NumShirts = number of rows returned
?>

<form action="test.php" method="post" name="ShirtForm">
# of shirts: <?=$NumShirts?>
<select name="SelectBoxMaterial" onchange="document.ShirtForm.submit();">
<option value="0">None
\\Note, since you want the previous selected values to be auto selected, you want all your option tags to look like the next one
<option <? if ($_POST['SelectBoxMaterial'] == "Cotton" { echo "selected"; } ?>>Cotton
<option>Polyester
<option>House Cat
</select>

<select name="SelectBoxColor" onchange="document.ShirtForm.submit();">
<option value="0">None
<option>Red
<option>Blue
<option>Transparent
</select>

<select name="SelectBoxSize" onchange="document.ShirtForm.submit();">
<option value="0">None
<option>S
<option>L
<option>XL
</select>
</form>

jonki

That is fantastic! I haven't had a chance to put it into my site yet but it seems to be exactly what I wanted. Thank you very much!

I thought I was going to be weeks on this one!

Cheers.

Jon

kellog

Already a bug fix:

elseif ($_POST['SelectBox2') 
{ 
    if (!$Where) 
    { 
        $SQL .= "WHERE color = $_POST['SelectBoxColor'] "; 
    } 
    else 
    { 
        $SQL .= "AND color = $_POST['SelectBoxColor'] "; 
    } 
}

should be:

elseif ($_POST['SelectBox2') 
{ 
    if (!$Where) 
    { 
        $SQL .= "WHERE color = $_POST['SelectBoxColor'] "; 
        $Where = True;
    } 
    else 
    { 
        $SQL .= "AND color = $_POST['SelectBoxColor'] "; 
    } 
}

jonki

Thanks again.

Moonglobe

and you should take the single quotes out from the _POST indices if they're inside a double-quoted string

add curly brackets around the variable

Weedpacket

Some rationalising of the if/elseif's to avoid multiple lines of repeated code
is also feasible (I also changed the names of the form fields being tested.
Also, checking to make sure the form fields actually exist as expected).

$search_criteria = array();
$select_boxes = array(
	'material' => 'SelectBoxMaterial',
	'color'    => 'SelectBoxColor',
	'size'     => 'SelectBoxSize'
);

foreach($select_boxes as $db_field=>$form_field)
{
	if(isset($_POST[$form_field]))
	{
		$search_criteria[]=$db_field."=".$_POST[$form_field];
	}
}

if(count($search_criteria)!=0)
{
	$SQL .= 'WHERE '.join(' AND ',$search_criteria);
}

Assuming the values sent back are numeric.

Check out also this page on Javascript-based RPC