php and mysql GET VARS

BEFOR · Sep 27, 2005

If we can forget prior examples: this joins on CountyFIPS to bring up the regions.

What I need is to manually plug into the page various values for Mcateg that are filtered via colname2. There are approximately 25 to 30 diffferent values for this (products or services) that will occur across approxiamately 20-30 CountyFIPS values (regions)

XMem
XmemID – primary // note that all ID’s in these tables are joinable on this.

Mcateg – the “categories”

XMwcat – categores per “subscriber” // composite key table

XMCounty – CountyFIPS per “subscriber” // composite key table and FIPS # is representative of counties

commercial9 - the main lookup table for County/FIPS

$colname_XLA5 = "6037";
if (isset($HTTP_GET_VARS['XLAFIPS'])) {
  $colname_XLA5 = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['XLAFIPS'] : addslashes($HTTP_GET_VARS['XLAFIPS']);
}
$colname2_XLA5 = "0";
if (isset( $HTTP_POST_VARS['MCatID'])) {
  $colname2_XLA5 = (get_magic_quotes_gpc()) ?  $HTTP_POST_VARS['MCatID'] : addslashes( $HTTP_POST_VARS['MCatID']);
}
mysql_select_db($database_removed, $removed);
$query_XLA5 = sprintf("SELECT COUNT(*) as theCount FROM XMem, Mcateg INNER JOIN XMcounty ON XMem.XMemID = XMcounty.XMcXMemID INNER JOIN commercial9 ON commercial9.CountyFIPS =XMcounty.XMcountyFIPS AND commercial9.CommID = (SELECT  max( commercial9.CommID )  FROM commercial9 WHERE CountyFIPS = '%s' )   INNER JOIN XMwcat ON XMem.XMemID = XMwcat.XMwXMemID WHERE XMwcat.XMwCatID = Mcateg.MCatID  AND Mcateg.MCatID = '%s' GROUP BY NULL ", $colname_XLA5,$colname2_XLA5);
$XLA5 = mysql_query($query_XLA5, $removed) or die(mysql_error());
$row_XLA5 = mysql_fetch_assoc($XLA5);
$totalRows_XLA5 = mysql_num_rows($XLA5);

trexx · Sep 27, 2005

Ok, i think we're coming closer So you're page displays a table with the count of all items that belong to a category, right? Well, the simplest approach would be to make a function out of your code above that takes xlaFips and mcatId as arguments.

Your code could look something like that...

<?
function getItemCount($xlaFips, $mcatId) {
    [your code here]
    return mysql_num_rows($rs);
}
?>
<table>
    <tr>
        <td><?= getItemCount($xlaFips, 10) ?></td>
        <td><?= getItemCount($xlaFips, 20) ?></td>
    </tr>
    <tr>
        <td><?= getItemCount($xlaFips, 30) ?></td>
        <td><?= getItemCount($xlaFips, 40) ?></td>
    </tr>
</table>

but it really depends on how much data you have and how your database is organized. just try... if it's fast enough this way we don't have to look further...

BEFOR · Sep 27, 2005

tansferring my code above both with and without brackets, and $xlaFips, $mcatId both upper and lower case and my page blanks out.

<?php error_reporting(E_ALL);
?>

reveals nothing. when placed below other code, it shows but the new doesn't
should be using GET or POST with $MCatID //?

Basically: // also tried removing 1st count within recordset ($totalRows) and COUNT(*) within query:

<?
function getItemCount($XLAFIPS, $MCatID) {
    $colname_XLA6 = "6037";
if (isset($HTTP_GET_VARS['XLAFIPS'])) {
  $colname_XLA6 = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['XLAFIPS'] : addslashes($HTTP_GET_VARS['XLAFIPS']);
}
$colname2_XLA6 = "0";
if (isset( $HTTP_POST_VARS['MCatID'])) {
  $colname2_XLA6 = (get_magic_quotes_gpc()) ?  $HTTP_POST_VARS['MCatID'] : addslashes( $HTTP_POST_VARS['MCatID']);
}
mysql_select_db($database_removed, $removed);
$query_XLA6 = sprintf("SELECT COUNT(*) as theCount FROM XMem, Mcateg INNER JOIN XMcounty ON XMem.XMemID = XMcounty.XMcXMemID INNER JOIN commercial9 ON commercial9.CountyFIPS =XMcounty.XMcountyFIPS AND commercial9.CommID = (SELECT  max( commercial9.CommID )  FROM commercial9 WHERE CountyFIPS = '%s' )   INNER JOIN XMwcat ON XMem.XMemID = XMwcat.XMwXMemID WHERE XMwcat.XMwCatID = Mcateg.MCatID  AND Mcateg.MCatID = '%s' GROUP BY NULL ", $colname_XLA6,$colname2_XLA6);
$XLA6 = mysql_query($query_XLA6, $removed) or die(mysql_error());
$row_XLA6 = mysql_fetch_assoc($XLA6);
$totalRows_XLA6 = mysql_num_rows($XLA6);

return mysql_num_rows($XLA6);
}
?>
<table>
    <tr>
        <td><?= getItemCount($XLAFIPS, 71) ?></td>
        <td><?= getItemCount($XLAFIPS, 20) ?></td>
    </tr>
    <tr>
        <td><?= getItemCount($XLAFIPS, 30) ?></td>
        <td><?= getItemCount($XLAFIPS, 40) ?></td>
    </tr>
</table>

trexx · Sep 27, 2005

your query always return just one single row. you don't need the numrows function here. instead return the count that you've selected.
i've assumed that you display all categories for a certain country (identified by the FIPS code). The FIPS code is submitted via GET (e.g. mypage.php?XLAFIPS=6037) and is always the same for the whole page. the mcatId on the other side is what you set manually (the second argument in the function call), so you don't have to receive it by request-values (that's why i've commented it out)

<?
function getItemCount($XLAFIPS, $MCatID) {
    mysql_select_db($database_removed, $removed);
    $query_XLA6 = sprintf("SELECT COUNT(*) as theCount FROM XMem, Mcateg 
        INNER JOIN XMcounty ON XMem.XMemID = XMcounty.XMcXMemID 
        INNER JOIN commercial9 ON commercial9.CountyFIPS =XMcounty.XMcountyFIPS 
            AND commercial9.CommID = (
                SELECT  max( commercial9.CommID )  

                FROM commercial9 WHERE CountyFIPS = '%s' )   

        INNER JOIN XMwcat ON XMem.XMemID = XMwcat.XMwXMemID 
        WHERE XMwcat.XMwCatID = Mcateg.MCatID  

            AND Mcateg.MCatID = '%s' GROUP BY NULL ", $XLAFIPS, $MCatID);
    $XLA6 = mysql_query($query_XLA6, $removed) or die(mysql_error());
    $row = mysql_fetch_assoc($XLA6);
    return $row['theCount'];
}

$colname_XLA6 = "6037";
if (isset($HTTP_GET_VARS['XLAFIPS'])) {
  $colname_XLA6 = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['XLAFIPS'] : addslashes($HTTP_GET_VARS['XLAFIPS']);
}

/* You don't need that here, since you set the values manually
   when you call the getItemCount function
$colname2_XLA6 = "0";
if (isset( $HTTP_POST_VARS['MCatID'])) {
  $colname2_XLA6 = (get_magic_quotes_gpc()) ?  $HTTP_POST_VARS['MCatID'] : addslashes( $HTTP_POST_VARS['MCatID']);
}
*/

?>
<table>
    <tr>
        <td><?= getItemCount($colname_XLA6, 71) ?></td>
        <td><?= getItemCount($colname_XLA6, 20) ?></td>
    </tr>
    <tr>
        <td><?= getItemCount($colname_XLA6, 30) ?></td>
        <td><?= getItemCount($colname_XLA6, 40) ?></td>
    </tr>
</table>

BEFOR · Sep 27, 2005

i've assumed that you display all categories for a certain country (identified by the FIPS code).

that's County, but I can create a recordset query for each county if need be. (about 25 counties)

unfortnately page is still blanking out.
narrowed down to something in this section:

// tried placing "$colname_XLA6 " before SELECT, no luck

trexx · Sep 27, 2005

we need an error output... =) without that it's hard to debug anything

BEFOR · Sep 27, 2005

<?php
ini_set(display_errors, "1");
?>

<?php error_reporting(E_ALL);
?>

Both give nothing -- what else try?

cretaceous · Sep 27, 2005

could be way off here but have you looked at "variable variables" ?
so as to give dynamically generated names to vars (or html fieldnames)

re this question:
Can mysql query pick up on individual variable values placed within an html table on the same page?

yes - I was shown a method on this very board..
if you still need it I should be able to find it tomorrow

BEFOR · Sep 27, 2005

removed these sections to prod errors:

$colname_XLA6 = "6037";
if (isset($HTTP_GET_VARS['XLAFIPS'])) {
$colname_XLA6 = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['XLAFIPS'] : addslashes($HTTP_GET_VARS['XLAFIPS']);
}

//and $XLAFIPS here:

GROUP BY NULL ", $XLAFIPS, $MCatID);

//along with setting directly to that value

WHERE CountyFIPS = '6037'

to give errors//

Notice: Undefined variable: colname_XLA6 in on line 230
//understand once definition is removed

Notice: Undefined variable: database_removed in on line 203
// which is mysql_select_db($database_removed, $removed);

Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource on line 203
// same as above

Notice: Undefined variable: 'removed' on line 213
// $XLA6 = mysql_query($query_XLA6, $removed) or die(mysql_error());

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource on line 213

// same as above

note that recordset XLA6 is not being recognized in the bindings panel. Wondering if its being cloaked by that 1st begining {

BEFOR · Sep 28, 2005

alright,have it down to two error messages. Any help appreciated:
note:
raised 'mysql_select_db' to eliminate other errors.
created AND MCatID = ' . $MCatID . ' // instead of '%s'

Notice: Undefined variable: $removed // blue line

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource // same line

<?
mysql_select_db($database_removed, $removed);
function getItemCount($XLAFIPS, $MCatID) {
$colname_XLA6 = "6037";
if (isset($HTTP_GET_VARS['XLAFIPS'])) {
$colname_XLA6 = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['XLAFIPS'] : addslashes($HTTP_GET_VARS['XLAFIPS']);
}
$query_XLA6 = sprintf("SELECT COUNT(*) as theCount FROM XMem, Mcateg
INNER JOIN XMcounty ON XMem.XMemID = XMcounty.XMcXMemID
INNER JOIN commercial9 ON commercial9.CountyFIPS =XMcounty.XMcountyFIPS
AND commercial9.CommID = (
SELECT max( commercial9.CommID )

FROM commercial9 WHERE CountyFIPS = '%s' )

INNER JOIN XMwcat ON XMem.XMemID = XMwcat.XMwXMemID
WHERE XMwcat.XMwCatID = Mcateg.MCatID

AND MCatID = ' . $MCatID . ' GROUP BY NULL ", $colname_XLA6, $MCatID);
$XLA6 = mysql_query($query_XLA6, $removed) or die(mysql_error());
$row = mysql_fetch_assoc($rs);
return $row['theCount']; }

$colname_XLA6 = "6037";
if (isset($HTTP_GET_VARS['XLAFIPS'])) {
$colname_XLA6 = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['XLAFIPS'] : addslashes($HTTP_GET_VARS['XLAFIPS']);
}

Weedpacket · Sep 28, 2005

$removed doesn't have a value (it's inside a function, so it's not the same variable as the $removed that's outside the function). That's the reason for both errors. I guess the easiest fix for that would be to just declare $removed as global.

Also in that function, you're not using $XLAFIPS for anything. That's because you're just picking it up again from the $_GET variable anyway. So you might as well drop $XLAFIPS from the function declaration, and not bother with passing it $colname_XLA6 every time.

And just on a stylistic note, I think a better name for the function would be "countForMCatID()".

Then I'd wrap all those queries into a single one: "Select Count() As theCount, MCatID From..... Group By MCatID" and put all the records into a single array that can be looked up.

$query_XLA6 = 'Select Count(*) as theCount, MCatID .... Group BY MCatID';
$XLA6 = mysql_query($query_XLA6, $removed) or die(mysql_error()); // XXX lose the die() before deployment!
$counts_by_MCatID = array();
while($row = mysql_fetch_assoc($XLA6))
{
	$counts_by_MCatID[$row['MCatID']] = $row['theCount'];
}

(And doesn't using syntax highlighting make code so much easier to read?)
And then using the array in the table is a matter of

<tr>
<td><?php echo $counts_by_MCatID[71]?></td>
<td><?php echo $counts_by_MCatID[20]?></td>
</tr><tr>
<td><?php echo $counts_by_MCatID[40]?></td>
<td><?php echo $counts_by_MCatID[60]?></td>
</tr>

BEFOR · Sep 28, 2005

Getting errors for each of the four:
Undefined variable: counts_by_MCatID

What am I missing?

<? 
function countForMCatID($MCatID)
  {
    mysql_select_db($database_removed, $removed); 
  global $removed;  

	$query_XLA6 = sprintf("SELECT COUNT(*) as theCount FROM XMem, Mcateg 
        INNER JOIN XMcounty ON XMem.XMemID = XMcounty.XMcXMemID 
        INNER JOIN commercial9 ON commercial9.CountyFIPS =XMcounty.XMcountyFIPS 
            AND commercial9.CommID = ( 
                SELECT  max( commercial9.CommID )   

                FROM commercial9 WHERE CountyFIPS = '6037' )    

        INNER JOIN XMwcat ON XMem.XMemID = XMwcat.XMwXMemID 
        WHERE XMwcat.XMwCatID = Mcateg.MCatID   

            AND `MCatID` =  ' . $MCatID . ' GROUP BY MCatID ");
   $XLA6 = mysql_query($query_XLA6, $removed); 
$counts_by_MCatID = array(); 
while($row = mysql_fetch_assoc($XLA6)) 
{ 
    $counts_by_MCatID[$row['MCatID']] = $row['theCount']; 
}  

 }

?> 
<table width="50" height="40" border="1" bordercolor="#666666"> 
<tr> 
<td><?php echo $counts_by_MCatID[71]?></td> 
<td><?php echo $counts_by_MCatID[20]?></td> 
</tr><tr> 
<td><?php echo $counts_by_MCatID[40]?></td> 
<td><?php echo $counts_by_MCatID[60]?></td> 
</tr> 
</table>

BTW - where is syntax highlighting?

Weedpacket · Sep 28, 2005

You build the array in the function but:

You never call the function
You don't return the array you built.

You should read up on variable scope: www.php.net/variables.scope

BEFOR · Sep 28, 2005

You must mean:

<?php countForMCatID()
{
echo $counts_by_MCatID[71];
}?>

or

<?php countForMCatID()
{
echo ($counts_by_MCatID);
}?>

However getting blankout.

Tried setting connection to global within.
Tried quotations on echo.
Tired setting array variable to global

What intricacy am I missing?

Weedpacket · Sep 28, 2005

Last two lines of countForMCatID():

return $counts_by_MCatID;
}

(Look up [man]return[/man]
Before the table:

$counts_byMCatID = countForMCatID();

In the table:

echo $counts_by_MCatID[71];

And of course you want to do "global $removed;" in the function before you use $removed.

BEFOR · Sep 28, 2005

Weedpacket,

I want to thank you for the help you've given and the patience you've extended.
This forms a main portion of administering my project.

While, at the price of sounding what may be overly obvious to you at this point, viewing how clearly you've put this especially of late, there is one section that is still causing trouble. It is in blue (blanking the page out) and I've reworked it a number of ways without success. Why this is a stickler I don't know.

That and getting undefined variable (green) for MCatID which may or may not be on the following line, from the error mess.

And then (red) mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

This is how I've interpreted your latest suggestions.

<?
function countForMCatID()
{
mysql_select_db($database_removed, $removed);
global $removed;

$query_XLA6 = sprintf("SELECT COUNT(*) as theCount FROM XMem, Mcateg
INNER JOIN XMcounty ON XMem.XMemID = XMcounty.XMcXMemID
INNER JOIN commercial9 ON commercial9.CountyFIPS =XMcounty.XMcountyFIPS
AND commercial9.CommID = (
SELECT max( commercial9.CommID )

FROM commercial9 WHERE CountyFIPS = '6037' )

INNER JOIN XMwcat ON XMem.XMemID = XMwcat.XMwXMemID
WHERE XMwcat.XMwCatID = Mcateg.MCatID
AND MCatID = ' . $MCatID . ' GROUP BY MCatID ");
$XLA6 = mysql_query($query_XLA6, $removed);
$counts_by_MCatID = array();
while($row = mysql_fetch_assoc($XLA6))
{
$counts_by_MCatID[$row['MCatID']] = $row['theCount'];
$counts_by_MCatID = countForMCatID();

}
}
?>

Weedpacket · Sep 29, 2005

Read my previous post, and note where I said those lines should go.

Weedpacket wrote:
Last two lines of countForMCatID():

Means the last two lines where of the definition of function countForMCatID(). The last line is the closing brace that matches the opening brace right after the line that says "function countForMCatID()". I put it there so you'd be sure to put the return line inside the function and not outside.

Weedpacket wrote:
Before the table:

In other words, before the <table> tag.

Weedpacket wrote:
And of course you want to do "global $removed;" in the function

Again, "the function" refers to the bit that starts with "function countForMCatID() {" and goes on down to the matching "}".

In future, instead of just shovelling stuff in at random and wondering why it doesn't work, try and work out what the effect of each change must be - there's a manual to help you with this that lays out all the syntax. The computer just tries to run whatever program you give it - understanding the program is your job.