Selecting rows from db table

[Ian Rastall]

In a project mentioned in another thread, I'm trying to get some
dynamic data to work. In this discography, you can click on an
album name, and the title of the album and its tracks will appear
on the left. I have the beginnings worked out, like so:

***

<h3><?php echo $row_rsindiv['Album']; ?></h3>
<ol>
<?php do { ?>
<li><?php echo $row_rsindiv['Song']; ?> (<?php echo $row_rsindiv
['Time']; ?>)</li>
<?php } while ($row_rsindiv = mysql_fetch_ass oc($rsindiv)); ?>
</ol>

***

A sample of the database:

***

Electronic Meditation 3 Cold Smoke 10:48
Electronic Meditation 4 Ashes to Ashes 3:58
Electronic Meditation 5 Resurrection 3:21
Alpha Centauri 6 Sunrise in the Third System 4:20
Alpha Centauri 7 Fly and Collision of Comas Sola 13:23
Alpha Centauri 8 Alpha Centauri 22:04
Zeit 9 Birth of Liquid Plejades 19:52
Zeit 10 Nebulous Dawn 17:47

***

I want to tell PHP to write the album name and list the tracks in
an <li></li> based on whatever album title is clicked in the main
table.

The main table looks like:

***

<?php do { ?>
<?php
$class = ($class == 'odd') ? 'even' : 'odd';
?>
<tr class="<?php echo $class ?>">
<td><?php echo $row_rstdream['Album']; ?></td>
<td><?php echo $row_rstdream['Year']; ?></td>
<td><?php echo $row_rstdream['Era']; ?></td>
<td><?php echo $row_rstdream['Type']; ?></td>
</tr>
<?php } while ($row_rstdream = mysql_fetch_ass oc($rstdream)); ?>

***

I thought maybe of changing the first <td></td> to:

<td><a href="#" onclick="<?php $album = $row_rstdream['Album'] ?>">
<?php echo $row_rstdream['Album']; ?></td>

But that's as far as I can get with my limited knowledge. Does
anyone have any ideas on how to display only the rows in the second
database that correspond to the name of the album clicked on? Sorry
the examples are so long, and I hope this makes sense.

TIA
Ian
--

http://www.bookstacks.org/

[Ian Rastall]

Re: Selecting rows from db table

Well, this new thread appears to have been rather pointless, as
I've figured out the problem.

From the top of the page:

$album=$_GET['album'];

$query_rsindiv = "SELECT * FROM indiv_album WHERE
Album='$album' ORDER BY Number ASC";

This is grabbing the value of the $album variable, set when you
click on an album name in the dynamic table, like so:

<td><a href="<?php print $_SERVER['PHP_SELF']."?album=".
$row_rstdream['Album']; ?>"><?php echo $row_rstdream['Album']; ?>
</a></td>

The dynamic data comes from:

<?php if ($album) { ?>
<h3><?php echo $row_rsindiv['Album']; ?></h3>
<ol>
<?php do { ?>
<li><?php echo $row_rsindiv['Song']; ?> (<?php echo
$row_rsindiv['Time']; ?>)</li>
<?php } while ($row_rsindiv = mysql_fetch_ass oc($rsindiv)); ?>
</ol>
<?php } ?>

and it tests the $album variable to make sure it's got a value,
so that nothing shows up when the page is first loaded.

If that's helpful to anyone, I'm glad. Either way, it would be
rude not to post the answer, even if I asked the question. :-)

Ian
--

http://www.bookstacks.org/

[Geoff Berrow]

Re: Selecting rows from db table

I noticed that Message-ID: <Xns95EF69BB250 5loneliestmonk@ 130.133.1.4>
from Ian Rastall contained the following:
[color=blue]
><td><a href="#" onclick="<?php $album = $row_rstdream['Album'] ?>">
><?php echo $row_rstdream['Album']; ?></td>
>
>But that's as far as I can get with my limited knowledge. Does
>anyone have any ideas on how to display only the rows in the second
>database that correspond to the name of the album clicked on?[/color]

You really need to get your head around the fact that once the results
appear on your screen, all PHP scripts are finished. Onclick is just
for client side events. You'll only get new results if you pass
different variables to the script, and check for them before you do the
query.

In this case you need to pass the variable into the script via a query
string in the URL, like I showed you before.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Tim Van Wassenhove]

Re: Selecting rows from db table

On 2005-01-31, Ian Rastall <idrastall@gmai l.com> wrote:[color=blue]
> Well, this new thread appears to have been rather pointless, as
> I've figured out the problem.
>
> From the top of the page:
>
> $album=$_GET['album'];
>
> $query_rsindiv = "SELECT * FROM indiv_album WHERE
> Album='$album' ORDER BY Number ASC";[/color]

One should never trust input from the evil angry user.

As you are using MySQL, read http://www.php.net/mysql_real_escape_string
and change your code for use in the real world.



--
Met vriendelijke groeten,
Tim Van Wassenhove <http://www.timvw.info>

[Ian Rastall]

Re: Selecting rows from db table

In comp.lang.php Geoff Berrow wrote:
[color=blue]
> In this case you need to pass the variable into the script via
> a query string in the URL, like I showed you before.[/color]

Hey Geoff. That's what I ended up doing (taking out the onclick and
passing the variable in the URL). What really bugs me is that when
I had this page done before, in JavaScript, I was able to add text
beside the sortable table without reloading the page. I believe I
was messing with document.innerH tml, which I guess means I was
messing with the DOM. Before I had discovered that trick, I was
creating text input fields with no borders and writing to those.
Not that JavaScript is relevant to this group, but I had just
managed to get to that point, where I could alter the page without
having to ask for it again, and can't seem to reach the same point
in PHP. I guess it doesn't make sense that the document could re-
query the database without re-loading the page.

Ian
--

http://www.bookstacks.org/

[Ian Rastall]

Re: Selecting rows from db table

In comp.lang.php Tim Van Wassenhove wrote:
[color=blue]
> One should never trust input from the evil angry user.[/color]

I should really provide a URL, so you can see what I'm doing, but I
haven't yet figured out how to get the mySQL database off my own
computer and on to my hosting company's server. (I've been doing
this for about five days or so.) :-) My point is, there's nothing
for the user to enter. There's just links to click on. But I'll
read the document you provided.

Ian
--

http://www.bookstacks.org/

[Geoff Berrow]

Re: Selecting rows from db table

I noticed that Message-ID: <Xns95EF1E621EF 2Floneliestmonk @130.133.1.4>
from Ian Rastall contained the following:
[color=blue]
> My point is, there's nothing
>for the user to enter. There's just links to click on.[/color]

Right click the link.
Click copy shortcut.
Paste into browser address bar.
Edit maliciously.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Ian Rastall]

Re: Selecting rows from db table

In comp.lang.php Geoff Berrow wrote:
[color=blue]
> Right click the link.
> Click copy shortcut.
> Paste into browser address bar.
> Edit maliciously.[/color]

Okay. I'll have to work on this.

Ian
--

http://www.bookstacks.org/

[Sean]

Re: Selecting rows from db table

On 31 Jan 2005 07:58:10 GMT, Ian Rastall <idrastall@gmai l.com>
reverently intoned upon the aether:
[color=blue]
> In comp.lang.php Tim Van Wassenhove wrote:
>[color=green]
> > One should never trust input from the evil angry user.[/color]
>
> I should really provide a URL, so you can see what I'm doing, but I
> haven't yet figured out how to get the mySQL database off my own
> computer and on to my hosting company's server. (I've been doing
> this for about five days or so.) :-) My point is, there's nothing
> for the user to enter. There's just links to click on. But I'll
> read the document you provided.
>
> Ian[/color]

Hi Ian,

This is the same general topic as "SQL Injection" which I mentioned
earlier. Essentially it amounts to:

"Users are evil!"

And I admit to being one of them. Users could care less how your
coded it, they expect it to work. I am impatient, I hate waiting, and
did I mention I hate software that makes me do 5 things to do 1 thing?
;o)

And more seriously (the above is dead serious), even when a user is
not malicious, they still tend to f#$% things up and break stuff (see
impatience above). Actually, in my experience, users often cause more
grief with software than malicious hackers as they tend to blithely do
what they want the software to do rather than what the software wants
them to do.

And in truth, 90% of computer security (remember, you are on the web
with a website visible to the whole world [or at least most of the
industrialized world and climbers with laptops, solar power, and
satellite uplinks on the middle slopes of Mt. Everest {the sleazy
white guy name, not the real name it had for the previous ten to
fifteen thousand years as I fear Chomolungma is spelled wrong}] ;o).

Social commentary aside, the issue here is input validation. A
non-malicious user will cause an error far more often than a malicious
hacker. Why? Sometimes it will be a transmission error. Sometimes
they do things in the wrong order (use the back button at the wrong
moment?). The cause matters little, the random chaos of a real user
often breaks things far more often than the organize malice of
hacker/cracker.

In short, taking a website dynamic opens a whole can of worms that
does not exist in a static website or some software written in C.
Remember, most mistakes written in C execute and break the users
computer, not your customer's website.

enjoy,

Sean


"In the End, we will remember not the words of our enemies,
but the silence of our friends."

- Martin Luther King Jr. (1929-1968)

Photo Archive @ http://www.tearnet.com/Sean
Last Updated 29 Sept. 2004

[Michael Fesser]

Re: Selecting rows from db table

.oO(Ian Rastall)
[color=blue]
>In comp.lang.php Geoff Berrow wrote:
>[color=green]
>> Right click the link.
>> Click copy shortcut.
>> Paste into browser address bar.
>> Edit maliciously.[/color]
>
>Okay. I'll have to work on this.[/color]

Just remember this: Everything(!) coming in from the client side can be
manipulated, even the content of hidden or read-only form fields. You
don't really have to think about how or where, but simply accept that
it's possible and then take care of that in your scripts. If you want to
process user-submitted data, validate it first. Always.

The problem is not the average visitor who simply uses your site, but
the more experienced evil guy who explicitly looks for security holes,
as seen recently in phpBB.

Micha

[Tim Van Wassenhove]

Re: Selecting rows from db table

On 2005-01-31, Ian Rastall <idrastall@gmai l.com> wrote:[color=blue]
> In comp.lang.php Tim Van Wassenhove wrote:
>[color=green]
>> One should never trust input from the evil angry user.[/color]
>
> I should really provide a URL, so you can see what I'm doing, but I
> haven't yet figured out how to get the mySQL database off my own
> computer and on to my hosting company's server. (I've been doing
> this for about five days or so.) :-) My point is, there's nothing
> for the user to enter. There's just links to click on. But I'll
> read the document you provided.[/color]

Your statement has already been debunked by others, so i won't do that
again...

But let me say this: If you consider security as a priority from the
start, it's usually not that difficult to come up with a relative secure
application. But if you start adding it afterwards, you'll probably
experience problems (just look at the problems ms has with windows :p)

--
Met vriendelijke groeten,
Tim Van Wassenhove <http://www.timvw.info>

[Ian Rastall]

Re: Selecting rows from db table

In comp.lang.php Tim Van Wassenhove wrote:
[color=blue]
> If you consider security as a priority from the
> start, it's usually not that difficult to come up with a
> relative secure application.[/color]

Believe me, I'm listening, it's just that this is a bit over my
head. Essentially, I got tired of reading about PHP and mySQL and
just dove right in, so it will take some time to learn good
security practices. I'll work on mysql_real_esca pe_string(), or,
rather, will work on figuring out how to use it. :-)

Thanks for everyone's comments.

Ian
--

http://www.bookstacks.org/