php and mysql GET VARS

[BEFOR]

This topic split off from the end of another thread, but I think it seperate enough to begin another thread on.

My question is, I have a large reporting table and on this table I need to place variables with php that can be detected by the mysql select query to echo values.

Each of the different values I intend to place in a seperate html table cell.

Roughly (in ea. cell)

<?php

$MID = 6;
echo $row_category['MName'];?> //

note: the query does work, however I cannot seem to get it to pull from a variable in this way.

have tried: WHERE MID = '" . $MID . "'";

or using filter by 'colname' where this is set to $HTTP_GET_VARS

Is all of this possible with php/mysql?

[trexx]

I'm not sure if i understand your problem: all you want to do is to have a GET-variable in your SQL statement, right?

if the url is: /table.php?mid=6&colname=salary

then your query in php should look like:

$q = 'select * from employee where mid=' . intval($GET['mid']) . ' order by ' . $GET['colname'];

note: you should check the $_GET parameters before using them in the query to avoid that someone injects malicious code in your statement.

btw: anything is possible with php/mysql 😉

[BEFOR]

trexx,

I'm not passing the variable in the url, but I don't think that that matters. This is an admin table so only admin has access. Tried POST method but can't get that to go either.

basic problem: Placing code like this within each cell of an html table on an admin page. It is reports pages (sales across all regions of the country) Each $MID value will be manually placed and will be different for each cell in the table.

<?php
$MID = 6;
echo $row_category['MName'];?>

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

[Weedpacket]

If you're using $_GET then you are passing the variable in the URL. GET and POST are two types of HTTP request. POST has a body (where the form information goes), GET doesn't (it's just a bog-standard request for a page).

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

Unclear: MySQL is a database and doesn't know anything about HTML - "on the same page" (as what?) or otherwise. And what do you mean "placed within an html table"? Do you mean you have an HTML table with stuff in it, and you're passing that HTML and trying to extract the contents of its cells?

If you think I'm sounding dumb, remember that you're programming, and the computer really is dumb.

[BEFOR]

a) there is a mysql query on a page.
b) there is an html table that is on that same page
(so as not to confuse the terms 'database table' with 'html table')
c) there are many rows and columns on this html table. within the cells of this table, I want to place variables using php in order to echo values from the mysql query.
d) can this be done with this or simliar fashion with php/mysql, where $MID value will be manually placed within each of the table cells:

<?php
$MID = 6;
echo $row_category['MName'];?>

note: there is only (1) page being referred to.

[trexx]

Maybe you should post the structure of your database table and describe how the output should look like in HTML. I'm still a bit confused =)

Usually you have some data in a database. You do a select that gives you a result set with all the rows and columns you have selected. Then you loop through that result set creating the HTML output.

[BEFOR]

No offense, but I really don't know how else to more clearly put this.

This is all I'm asking. Can this be done ?:

<td width="25"><?php
$MID = 6;
echo $row_category['MName'];?>
td>

This will give a different MName or Count or whatever you want to use for a given $MID as defined in the query.

I need to ask:

Can a different $MID (manually placed by me) pull different individual values from the query?There are many different $MID's on the page.

Does this require stored procedures?

I do appreciate your help.

[trexx]

No offense too, but i think you try do something simple the wrong way =) That's why i've asked for the data and the output. I really believe that it can not be done the way you've posted it...

[BEFOR]

Can you go with the recordset/query alone -- there are 6 tables, 3 inner joins/ select max, etc/ and search on two variables. I've actually been trying to keep it simple by asking more of a "theory question". I've heard this may require a "pivot table" or stored procedure but can't get it verified.

[trexx]

There's nothing a stored procedure could do that you couldn't do with a simple PHP function that executes a SQL statement (As long as your database and php runs on the same machine). Anyway... a SP may be faster, but when your PHP/MySQL code runs fast enough you don't need it.

[BEFOR]

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]

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]

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]

1. your query always return just one single row. you don't need the numrows function here. instead return the count that you've selected.

2. 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]

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:

<table width="50" height="40" border="1" bordercolor="#666666">
<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>

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

[trexx]

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

[BEFOR]

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

<?php error_reporting(E_ALL);
?>

Both give nothing -- what else try?

[cretaceous]

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]

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]

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']);
}

<table width="50" height="40" border="1" bordercolor="#666666">
<tr>
<td><?= getItemCount($colname_XLA6, 71) ?></td>
<td><?= getItemCount($colname_XLA6, 20) ?></td>
</tr>
<tr>
<td><?= getItemCount($colname_XLA6, 40) ?></td>
<td><?= getItemCount($colname_XLA6, 63) ?></td>
</tr>
</table>