2 short mysql/php questions

Hi there,

I have two short questions:

1st:
I have a site with a mysql DB. There are 3 admins.
They're stored in a table called "admin".
Fields in there are 'id','name','passw'.
If i use the following query: "SELECT * FROM admin";
It returns three rows. How can i print out e.g. of the second
returned row the password. It has to be something like the following:
$query_results[2]['passw'];

2nd:
Is there a way to manipulate data in fields of a mysql DB. Now there is
data that is partially upercase, though all of it should be lowercase.
is there something like: LOWERCASE 'name' FROM 'members'
(i know this doesn't exist, butyou know what i mean..)

Thanks in advance.

re: 2 short mysql/php questions

*** frizzle escribió/wrote (24 Mar 2005 09:42:42 -0800):[color=blue]
> If i use the following query: "SELECT * FROM admin";
> It returns three rows. How can i print out e.g. of the second
> returned row the password. It has to be something like the following:
> $query_results[2]['passw'];[/color]

You need to use mysql_connect(), mysql_select_db(), mysql_query() and
mysql_fetch_assoc(). In their manual pages you can find usage examples.

[color=blue]
> Is there a way to manipulate data in fields of a mysql DB. Now there is
> data that is partially upercase, though all of it should be lowercase.
> is there something like: LOWERCASE 'name' FROM 'members'
> (i know this doesn't exist, butyou know what i mean..)[/color]

In www.mysql.com you have an online manual with all the operators and
functions. The string functions are here:

http://dev.mysql.com/doc/mysql/en/string-functions.html

However, the way you built your fictious example makes me thing that this
is your first contact with relational databases. I'd recommend that you
follow a tutorial to learn basic SQL. It doesn't need to be MySQL-specific.


--
-+ Álvaro G. Vicario - Burgos, Spain
+- http://www.demogracia.com (la web de humor barnizada para la intemperie)
++ No envíes tu dudas a mi correo, publícalas en el grupo
-+ Do not send me your questions, post them to the group
--

re: 2 short mysql/php questions

I'm sorry, but maybe i've been incomplete with providing information:
I know how to get the information from the database. Usually with e.g.
a guestbook i use

do{
blah
}while($result = mysql_fetch_array($get_results));

But instead of this, i'd like to replace this method with the above i
mentioned.

Both questions are not about the same example, if that's what you mean
with relational databses, I am quite new to mySQL, and also
(less but still) quite new to PHP as you've probably noticed.

If i've missed out something, i'm sorry, just want to make things more
clear...

Greetings!

re: 2 short mysql/php questions

*** frizzle escribió/wrote (24 Mar 2005 15:34:52 -0800):[color=blue]
> Usually with e.g. a guestbook i use
>
> do{
> blah
> }while($result = mysql_fetch_array($get_results));[/color]

What does the do{} block do in the first iteration, when $result still
doesn't have any value?

[color=blue]
> But instead of this, i'd like to replace this method with the above i
> mentioned.[/color]

Just use a counter variable. Give it an initial value and increment it in
each iteration. A simple if() can tell you if you reached the desired row.

Also, please note the order of rows is absolutely meaningless unless you
have an ORDER BY clause.



--
-+ Álvaro G. Vicario - Burgos, Spain
+- http://www.demogracia.com (la web de humor barnizada para la intemperie)
++ No envíes tu dudas a mi correo, publícalas en el grupo
-+ Do not send me your questions, post them to the group
--

re: 2 short mysql/php questions

I'm not sure if i understood it correctly, but i've solved it like
this:

$get_admins = mysql_query("SELECT id,username,email,website FROM admin
ORDER BY id",$db);
$admins = mysql_fetch_array($get_admins);

do{

if($admins['id']==1)
{
$admin1_username= $admins['username'];
$admin1_email = $admins['email'];
$admin1_website = $admins['website'];
}
else if($admins['id']==2)
{
$admin2_username= $admins['username'];
$admin2_email = $admins['email'];
$admin2_website = $admins['website'];
}
else if($admins['id']==3)
{
$admin3_username= $admins['username'];
$admin3_email = $admins['email'];
$admin3_website = $admins['website'];
};

}while($admins = mysql_fetch_array($get_admins));


It works, but somehow i feel like i'm missing out something. I wonder
how i'd do this if i had hundreds of admins: i can't imagine i'd have
to define the properties for each admin...

Well, anyway it works!

Thanks for that.

I can't figure out my second question though: i only find ways to
manipulate the output of a query, instead of manipulating the actual
raw data inside the DB..

Greetings frizzle.

re: 2 short mysql/php questions

frizzle wrote:[color=blue]
> I'm not sure if i understood it correctly, but i've solved it like
> this:
>
> $get_admins = mysql_query("SELECT id,username,email,website FROM admin
> ORDER BY id",$db);
> $admins = mysql_fetch_array($get_admins);
>
> do{
>
> if($admins['id']==1)
> {
> $admin1_username= $admins['username'];
> $admin1_email = $admins['email'];
> $admin1_website = $admins['website'];
> }
> else if($admins['id']==2)
> {
> $admin2_username= $admins['username'];
> $admin2_email = $admins['email'];
> $admin2_website = $admins['website'];
> }
> else if($admins['id']==3)
> {
> $admin3_username= $admins['username'];
> $admin3_email = $admins['email'];
> $admin3_website = $admins['website'];
> };
>
> }while($admins = mysql_fetch_array($get_admins));
>
>
> It works, but somehow i feel like i'm missing out something. I wonder
> how i'd do this if i had hundreds of admins: i can't imagine i'd have
> to define the properties for each admin...
>
> Well, anyway it works!
>
> Thanks for that.
>
> I can't figure out my second question though: i only find ways to
> manipulate the output of a query, instead of manipulating the actual
> raw data inside the DB..
>
> Greetings frizzle.
>[/color]

Why not something like (not tested):

$admins = array();
while ($data = mysql_fetch_array($get_admins)) {
$admins[$data['id']] = array('username'=>$data['username'],
'email'=>$data['email'],
'website'->$data['website']);
}

This will place the data in

$admins[1]['username']
$admins[1]['email']
$admins[1]['website']
$admins[2]['username']

etc.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

re: 2 short mysql/php questions

Great Jerry

That's just what i meant! Just great. I got it working immediatly.
I even belive that now the number corresponds with the id, instead of
it's position in the array... is that correct?
In that case, it's even greater than great!

You made my day!

re: 2 short mysql/php questions

frizzle wrote:[color=blue]
> Great Jerry
>
> That's just what i meant! Just great. I got it working immediatly.
> I even belive that now the number corresponds with the id, instead of
> it's position in the array... is that correct?
> In that case, it's even greater than great!
>
> You made my day!
>[/color]


Yes, the index of the item in the array matches the id.

Glad to be of help.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

re: 2 short mysql/php questions

Very very nice work! :D