|
|
Posted by Jerry Stuckle on 07/07/06 11:19
Dodger wrote:
> Jerry Stuckle wrote:
>
>
>>You don't know the underlying database structure? Then how do you know
>>the data you want is even there? Or, if you only want specific data,
>>you shouldn't be doing SELECT * - just select the columns you want.
>
>
>>Or, if you insist on using the bind parameters, you can use
>>stmt->result_metadata to get the metadata for the statement, including
>>the number of columns returned and their names.
>
>
> No, you misunderstand me--
> I know certain things about the data structure, but other things will
> vary depending on which virtual domain is being served up -- and I
> shouldn't need to code the same basic sessioning system more than once.
>
They are two different tables - so typically they have two different
routines. In this case one size does NOT fit all.
> Okay, there's a session table for each virtual domain. The virtual
> domain (hostname) the request goes to determines which table to use.
> The table structure of the session have additional fields on top of the
> basic ones. I should be able to change this without needing to redo
> code at all, just by adding columns to the session system.
>
And why is that? You're accessing different data - so you need
different code.
> For instance, say we have foo.com and bar.com. foo.com is a
> subscription content site, and bar.com is a online store selling sporks
> in assorted varieties.
>
> foo.com therefore needs to know whether a user is subscribed. bar.com
> on the other hand doesn't even know the concept of a subscription, but
> it does need to know the shopping cart ID of a user. Therefore, we take
> the basic table structure:
>
> [generic_session]
> id varchar(32) // MD5 string
> member varchar(32) // or whatever
> handle varchar(16)
>
> and for foo.com's foo_session table it's different. It has one more
> column:
> [foo_session]
> id varchar(32)
> member varchar(32)
> handle varchar(16)
> subscribed enum('Yes','No')
>
> While bar.com doesn't even need a handle (perhaps email is used instead
> of a username to log in) but it does need a shopping cart id, thus
> [bar_session]
> id varchar(32)
> member varchar(32)
> cart_id varchar(32)
>
> Now, there's no reason on Earth that I should be forced to make up
> seperate session modules for foo and bar, when the statement:
>
> SELECT *
> FROM $table
> WHERE id = ?
>
> is plenty to get me id, member, handle, subscribed on foo, and to get
> me id, member, cart_id on bar.
>
Sure. But they're two different tables.
> All that said, things are still not working...
>
I'm not surprised. Short cuts rarely are.
> if ($sessionid) {
> $get_session_st = <<<EOF
> SELECT *
> FROM apache.xfx_session
> WHERE id = ?
> EOF;
> ?>
> <pre><?= $get_session_st ?></pre>
> <?php
> $get_session_q = $sql->prepare($get_session_st);
> if ($get_session_q->bind_param('s', $sessionid)) {
> ?><p>Bound params</p><?php
> $get_session_q->execute();
> $get_session_res = $get_session_q->result_metadata();
> if ($get_session_res) {
> ?><p>Session results returned fine.</p><?php
> $session_data =
> $get_session_res->fetch_array(MYSQLI_ASSOC);
>
> if ($session_data) {
> ?><p>Session data is true</p>
>
> <table border="1">
> <?php
>
> foreach ($session_data as $col => $val) {
> ?>
>
> <tr>
> <td><?= $col ?></td>
> <td><?= $val ?></td>
> </tr>
>
> <?php
> }
>
> ?>
> </table><br><br>
>
> <?php
> }
> else {
> ?><p>$session_data is false</p><?php
> if ($get_session_q->error) {
> ?><p>Errstr: <?= $get_session_q->error ?></p><?php
> }
> else {
> ?><p>No error string...?</p><?php
> }
> }
>
> $get_session_res->free_result();
> $get_session_q->free_result();
> }
> else {
> ?>
> <p>No session results...?</p>
>
> <?php
> }
> }
> else {
> ?><p>Could not bind params</p><?php
> }
>
> }
> else {
> $sessionid = 'No session ID';
> }
>
>
> Now I'm explicitly getting the get_session_res result object
> $get_session_res. And checking. And it's working fine. That far.
>
> Narrowing things down, the fetch_assoc() method is just supposed to be
> internally aliased to fetch_array(MYSQLI_ASSOC) from what the docs are
> indicating. A lot.
>
> But
> $session_data = $get_session_res->fetch_array(MYSQLI_ASSOC);
>
> Raises no errors. However it also doesn't give me back anything.
>
That's right, because you're trying to mix bind and non-bind functions.
It doesn't work.
> "
> SELECT *
> FROM apache.xfx_session
> WHERE id = ?
>
> Bound params
>
> Session results returned fine.
>
> $session_data is false
>
> No error string...?
> "
>
No, there's nothing wrong with the syntax. But there are no results to
return the way you're doing it. You used mysql_bind_param to bind the
parameters so you need to use mysql_bind_result to get the results back,
not mysql_fetch_assoc().
> Yes I can explicitly do:
> SELECT *
> FROM apache.xfx_session
> WHERE id = '29f0b7ee81407e58aa.snip.247f5897'
>
> and get back
>
> id member handle last_action login_dt access subscribed
> remember newsletter aeoncust nudity violence mature
> 29f0b7ee81407e58aa.snip247f5897 4d15bea711d36.snip.4768914c27129
> Dodger 2006-07-07 06:37:00 2006-07-07 06:36:58 ADMIN:9;SUPERUSER:9
> Yes Yes No No Yes Yes Yes
>
> when I query it in the database.
>
Yes, because you're not binding the parameters.
> Logged into the database as *root* right now in both cases to avoid any
> possible other things messing up the process. Perl and the MySQL
> monitor are giving me back things. The result object seems to be
> turning up dead -- and from everything I've been seeing it shouldn't
> be.
>
> So I am once again lost.
>
You're trying to mix apples and oranges. It doesn't work.
People have tried to create generic libraries before. Some of them,
like Pear::DB, are pretty decent. Others are not so good.
If you insist on using bound parameters, you *must* use bound results.
It doesn't work otherwise. And if you need to know the column names,
you need to fetch the metadata.
Now tell me - how long have you been fooling around trying to get this
to work - vs. how long it would have taken you to copy/past/modify the
code to work with different tables in the first place? It could have
easily been done by simply creating separate classes for each table,
reuse common code (or better yet, derive all from a common base) and add
code to handle the unique stuff.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|