using a url parameter to define field in MySQL recordset

Report · Oct 25, 2006

I'd like to pass a url parameter from a matser page to detail page which will define the field used within a query. I have been trying but with no succes by adding the PHP code in the recorset within code view.
here's the code I end up with:

SELECT <?php echo $_GET['name']; ?>
FROM rota
WHERE <?php echo $_GET['name']; ?> = 'HOL'

Can anyone help me refine it so that it works.

Cheers

Dave

Report · Oct 25, 2006

On Wed, 25 Oct 2006 13:11:32 +0000 (UTC), "mac-in-the-mountains"
<webforumsuser@macromedia.com> wrote:

> WHERE <?php echo $_GET['name']; ?> = 'HOL'

WHERE <?php echo $_GET['name']; ?> == 'HOL'
--
Steve
steve at flyingtigerwebdesign dot com

Report · Oct 25, 2006

Steve wrote:
>
> WHERE <?php echo $_GET['name']; ?> == 'HOL'

No. SQL uses a single equals sign as a comparison operator. You don't
need two.

I think the problem lies in the original SQL. SELECT should be followed
by the column name or by *, not by <?php echo $_GET['name']; ?>

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/

Report · Oct 25, 2006

Sorry Steve,

That doesn't seem to work at all. I just get a MySQl error.

Thanks anyway. Any other suggestions?

Dave

Report · Oct 25, 2006

It's the column name that I was hoping to uset he URL parameter to dictate which will save me creating zillions of pages to deal with each column.

Is that possible or do I have to specify it exactly by name?

Dave

Report · Oct 25, 2006

On Wed, 25 Oct 2006 14:53:54 +0100, David Powers <david@example.com>
wrote:

>Steve wrote:
>>
>> WHERE <?php echo $_GET['name']; ?> == 'HOL'
>
>No. SQL uses a single equals sign as a comparison operator. You don't
>need two.

Ach! And I knew that. Time to call it a night I think.
--
Steve
steve at flyingtigerwebdesign dot com

Report · Oct 25, 2006

??? Do you have zillions of columns?

It looks like you're trying to match a name against your table:

SELECT <?php echo $_GET['name']; ?>
FROM rota
WHERE <?php echo $_GET['name']; ?> = 'HOL'

however, the statement above would mean you have a column name equal to
wahtever $_GET['name'] is - so if I entered HOL, then the COLUMN NAME would
need to be HOL, not the COLUMN VALUE.

You table should look something like this: (I made up some fields, but you
can get the gist)

table rota
id name password
1 HOL pass123
2 JCP 456pass

Then, if I want to find out my password, I would have an "enter name" box in
my form, and my sql qould look like this:

SELECT password WHERE name = <?php echo $_GET['name']?>

I don't think the echo statement will work, but don't know, i"ve just not
setup any statements exactly like that. Mine typically use a %s or I just
append the string.

Anyway, you have three sections to your SQL:

Select - This is the field you are wanting. Name has no place in here, as
you're not looking for a name, you already have that (it's what was
submitted on your form). What you're looking for is password. If you want
to bring in the rest of the records instead of just the password, use a *
for all fields in the table.

WHERE - this is your condition. In your statement, you said where HOL =
HOL. This is incorrect, b/c what you were wanting to say (it appears) is
Name=HOL. So in your where clause you need to list the field you're sorting
by (name), and then the value you wish to sort it with (your variable).

:O) And I guess you don't have a third. You can also SORT your results, but
that's not necessary here.

HTH<

Jon
"mac-in-the-mountains" <webforumsuser@macromedia.com> wrote in message
news:ehnt6k$hee$1@forums.macromedia.com...
> It's the column name that I was hoping to uset he URL parameter to dictate
> which will save me creating zillions of pages to deal with each column.
>
> Is that possible or do I have to specify it exactly by name?
>
>
> Dave
>

Report · Oct 25, 2006

Maybe I'm being a bit dumb, but I think it should be possible but probably not in the way I'm going about it:

It's an annual rota table in which every person has a field which holds a piece of text that says what they are doing that day.
eg 'OFF', 'SICK', 'WORKING', 'WORKING FROM HOME', HOLIDAY, etc
table = rota
field = dave (or any one of a hundred other names - each person has their own field)

what I'm trying to do is create a page which calculates the number of various entries in a field. for eaxmple how many times the word 'HOLIDAY' is listed throughout the year.

I thought I'd be able to do by using a combination of a dedcated recordset (created using the name variable passed over in the url) and the "display record count" in server behaviour in Dreamweaver".

Am I barking up the wrong tree?

Thanks

Dave

Report · Oct 25, 2006

That sounds pretty nasty. I don't know what a rota is (at least it's not
making sense right now), but you're trying to track time, yes?

Here is how your database should be setup (David, can you chime in?). You
don't need a field with everybody's name in it - you just need a field
called "name" that will hold everybody's name. Then, when you wish to view
what that person did on a particular day (or over the course of a year) then
you will simply search for that person's name and pu tthe appropriate where
clause in.

table: rota
fields: id, name, date, status

So, instead of hundreds of fields, you will only have 4.

Then, your database will look like this:

rota
ID Name Date Status
1 Holly 1/1/2006 HOME
2 Bob 1/1/2006 HOME
3 Jim 1/1/2006 WORKING
4 Tanya 1/1/2006 HOLIDAY
5 Holly 1/2/2006 WORKING
6 Bob 1/2/2006 WORKING
7 Jim 1/2/2006 HOME

So, if you wanted all the records for Holly, you would do this:

Select * WHERE name="Holly" (in the produced SQL, this is not code).

If you wanted to get a year's list of them

Select * WHERE name="Holly" and Date BETWEEN(1/1/2006 AND NOW())

See how it's working? Must smaller, much more simple to get the records you
want. Is it making snese to you?

Jon
"mac-in-the-mountains" <webforumsuser@macromedia.com> wrote in message
news:ehnvub$ktv$1@forums.macromedia.com...
> Maybe I'm being a bit dumb, but I think it should be possible but probably
> not
> in the way I'm going about it:
>
> It's an annual rota table in which every person has a field which holds a
> piece of text that says what they are doing that day.
> eg 'OFF', 'SICK', 'WORKING', 'WORKING FROM HOME', HOLIDAY, etc
> table = rota
> field = dave (or any one of a hundred other names - each person has their
> own
> field)
>
> what I'm trying to do is create a page which calculates the number of
> various
> entries in a field. for eaxmple how many times the word 'HOLIDAY' is
> listed
> throughout the year.
>
> I thought I'd be able to do by using a combination of a dedcated recordset
> (created using the name variable passed over in the url) and the "display
> record count" in server behaviour in Dreamweaver".
>
> Am I barking up the wrong tree?
>
> Thanks
>
>
> Dave
>

Report · Oct 25, 2006

mac-in-the-mountains wrote:
> It's the column name that I was hoping to uset he URL parameter to dictate
> which will save me creating zillions of pages to deal with each column.
>
> Is that possible or do I have to specify it exactly by name?

No, it's possible, if that's what you want/need to do. I suspect your
problem is that you're adding PHP tags in the middle of code that's
already being generated by PHP. You can't nest PHP tags.

Let's say your SQL query generated by DW looks like this:

$query_recordsetName = "SELECT columnName FROM rota WHERE columnName =
'HOL'";

You need to change it like this:

$columnName = mysql_real_escape_string($_GET['columnName']);
$query_recordsetName = "SELECT $columnName FROM rota WHERE $columnName =
'HOL'";

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/

Report · Oct 25, 2006

I'm afraid I'm no expert so although I'm highly intrigued, it doesn't make complete sense to me.

I created the table along the lines you suggested and it seems to leave me with nothing more than one row for each person, unless of course I create 356 rows for each person. Is that what you had in mind?