understanding mysql_fetch_array

spookztar

Hi guys,

I'm having a bit of an issue getting the desired results from a round of mysql_fetch_array.

I have a parameter table with these fields:

id
name
param

and I'm trying to get two specific values from "param" by focusing on the names of the parameters in "name"

$adminquery = mysql_query("SELECT param FROM parameter WHERE name='siteuser' OR name='siteuserpasswd'");
                      if (!$adminquery) {
                         die('<h3 class="warning">fetching admin credentials failed.</3>  '. mysql_error());                 

                         } 
                         while ($row = mysql_fetch_array($adminquery)) {
                         echo $row['siteuser'];
                         echo $row['siteuserpasswd'];
                         }

The above gives me four instances of notice: undefined index: two for each field.

What do I need to do to make $row['siteuser'] and $row['siteuserpasswd']
Assume their proper values?

bradgrafelman

You need to select the siteuser and siteuserpasswd columns in your query. If you only SELECT the param column, only the param column will be returned by the MySQL server.

EDIT: Wait... I don't understand your question. "siteuser" and "siteuserpasswd" aren't columns in your database, so... where are these values supposed to come from?

spookztar

The two values are supposed to come from the "name" and "parameter" columns. I need to recieve the value from the "param" field in the row which has "siteuser" in it's "name" field and the value from the "param" field in the row that has "siteuserpassword" in it's "name" field.

Edit: Wait.. <selfmutilation>Damn, I seem to have made a mistake when I wrote the post:</selfmutilation>

The "parameter" table contains the COLUMNS "id", "name" and "param" :o

bpat1434

You'd select name, param in your query. Then, in mysql_fetch_array, they'd be available as keys (param and name) in the array.

$adminquery = "SELECT name, param FROM `parameter` WHERE name='$siteuser' OR name='$siteuserpasswd'";

$result = mysql_query($adminquery) or die('Unable to query database.');

while($row = mysql_fetch_array($result))
{
    echo $row['name'] . ' || ';
    echo $row['param'];
}

That's just an example.

spookztar

The example isn't any good here, as the column names in the WHERE clause are not variable, and fiddling around with it still doesn't get me anywhere.

The following query:

SELECT param FROM parameter WHERE name='siteuser' OR name='siteuserpasswd'

gives me exactly what I need

(The query result won't display right when posted. so I hope this is ok🙂

param:

2kjk58f297a57a5a743894a0e4gt654433h

09d1d29f3mf5912edh76jhg8ui510440d6

I just need these two values from the "param" column, found by looking for the rows with "siteuser" and "siteuserpassword" in it's "name" field, parked in two suitably named variables ($siteuser and $sitepassword) so I can work with them.

or do I have to query for each separately?

bpat1434

Okay, so instead of echoing them out, just do $var = $row['param'];

spookztar

Ok, that gives me: 2kjk58f297a57a5a743894a0e4gt654433h09d1d29f3mf5912edh76jhg8ui510440d6

I need to end up with:

$siteuser = "21232f297a57a5a743894a0e4a801fc3"; and:
$siteuserpassword = "09d1d29f3mf5912edh76jhg8ui510440d6";

I need to know, that the given variable contains the right information. I can't rely on the order of the pull.

I really can't believe this has to be so difficult to achieve...

bpat1434

$siteuser = '';
$siteuserpassword = '';

while($row = mysql_fetch_array($result))
{
    if(empty($siteuser))
        $siteuser = $row['param'];
    else
        $siteuserpassword = $row['param'];
}

bradgrafelman

Wait a second. You're saying that for one user, their username and password are split into two separate rows?

You seriously need to rethink your database design here. Why would you put them all in the same row?

bpat1434

I noticed that too.... but there may be reasons .... but yeah... this would be much easier if it was a database like:

much easier....

spookztar

I agree with that. I just thought that I would limit the amount of tables in the database by creating a general "parameter" table, that would hold all sorts of parameters in a field of their own ("param") which could all be selected individually via their respective titles in the name field (a kind of title indexing if you will).

But apparently mysql_fetch_array() isn't exactly up for that way of doing it with the greatest of ease. :bemused:

How would you do it? Fetch a single parameter at a time? What's the best procedure for grabbing a single piece of information NOT as an array?

Thanx for responding by the way, this was a bit of a mindbender for someone still quite the newbie.🙂

bradgrafelman

spookztar wrote:
I just thought that I would limit the amount of tables in the database

Why?

spookztar wrote:
How would you do it?

The way bpat described above.

spookztar

As far as I know databases with a fairly large number of tables consumes a fairly large amount of memory, no? I would also like to bring down the number of needed joins.

edit: "Wait a second. You're saying that for one user, their username and password are split into two separate rows?"

No "username" is under "name" and the "password" is under "parameter". All in the same row.

Think of the table this way:

The username would go under "parameter_name" and the password under "parameter".

bradgrafelman

spookztar wrote:
As far as I know databases with a fairly large number of tables consumes a fairly large amount of memory, no?

Same amount as a database with one single table with a large amount of rows. With separate tables, however, the data becomes much easier to work with.

EDIT: For example, in your kitchen, you probably have a different cupboard for plates, one for bowls, one for large serving dishes, a drawer for silverware, etc. etc. Why don't you just have one large tub full of every utensil in the kitchen? Because separating them out into separate containers (according to what type of utensil they are) makes it much, much more efficient.

Why did I use this analogy? Who knows... it just came to mind. Perhaps because I'm hungry and am thinking about fixing lunch. :p

spookztar

I do get your point. I was merely trying to rationalize, as many pieces of information of different nature could easily just be seen as parameters, so why not toss them into a parameter table, slap a title on 'em and let MySQL sort the dishes for me, by looking at the "name" field whenever a specific parameter is needed? Fewer tables, fewer joins = less hassle.

Having 14+ tables for a really small application is just overkill.

spookztar

Conclusion:

Thinking that bpat1434's solution was a bit.. well on the alternative side, I decided to run two separate instances of mysql_fetch_array() instead.

I'm now doing this:

$adminquery = mysql_query("SELECT param FROM parameter WHERE name='siteuser'");
if (!$adminquery) die('<h3 class="warning">fetching admin name failed.</3>  '. mysql_error());
   $row = mysql_fetch_array($adminquery);
   extract($row);
   $admin = $row['param'];

$passwordquery = mysql_query("SELECT param FROM parameter WHERE name='siteuserpasswd'");
if (!$passwordquery) die('<h3 class="warning">fetching admin password failed.</3>  '. mysql_error());
   $row = mysql_fetch_array($passwordquery);
   extract($row);
   $password = $row['param'];

It's somewhat surprising (to me anyways) that this result is not achievable in a more scholastically "correct" way, with just one round of mysql_fetch_array()

But ok, I'm gonna mark this sucker "solved".

Once again, thanks you guys 🙂

bpat1434

It is actually....

$query = "SELECT name, param
FROM parameters
WHERE name = 'siteusername'
    OR name = 'siteuserpassword'";

$result = mysql_query($query) or die('Database Query Error');

while($row = mysql_fetch_array($result))
{
    $$row['name'] = $row['param'];
}

echo $siteusername . '<br />' . $siteuserpassword;

How difficult is that?

spookztar

I don't believe we've touched on the concept of variable variables? I've only looked briefly at it once some time ago, so I don't know if it's "gettable" by me at this stage as I had a hard time putting it into perspective back then, but I'm willing to give it a try...

bpat1434

Variable variables are an easy concept. The value of the "inner variable" becomes the name of the "outer" variable.

Example:

<?php

$foo = 'bar';

$$foo = 'baz';

echo $foo . ' is the name of the next variable (bar): ' . $bar;

That will output 'bar is the name of the next variable (bar): baz'

They really can be a good tool to grasp your head around. Self writing code is just an example of its use 😉 Did you try the above code (with minor changes to the query to get the proper columns and query the right table)?

spookztar

Not yet, as it's getting really late in my part of the world, but I'll have a look at it as soon as possible.

As far as I remember, I had problems detecting in what kind of situations they would be ideal to use.

else I would propably have done it in this one 😉