I need help with understanding how DBs work.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
pilau
Forum Regular
Posts: 594
Joined: Sat Jul 09, 2005 10:22 am
Location: Israel

I need help with understanding how DBs work.

Post by pilau »

Hello again.

I am working for 4 days on the subject of databases and still can't get the real hang of it.
How does mysql_fetch_assoc/array work?

Biggest problem is, how do I compare some variable on my PHP document to a value on a database table?
This seems to be a very basic thing, so I am quite frustrated now.
Please explain to me how this whole fetching data from databases work.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

First you might want to learn SQL... http://www.w3schools.com/sql/default.asp (A bit of database normalization will be welcome too..)


http://www.php.net/mysql_fetch_assoc
http://www.php.net/mysql_fetch_array

For example if you do select col1, col2 from table order by col1

Code: Select all

col1    | col2
--------------------
foo1       | bar
foo2       | bar
foo3       | bar
foo4       | bar
Now with mysql_fetch_assoc you can retrieve the rows as an associative array, this means that if you $row = mysql_fetch_assoc($rs) you will recieve an array $row ( 'col1' => 'foo1', 'col2' => 'bar'). The next row you retrieve will be $row ( 'col1' => 'foo2', 'col2' => 'bar') and so on...

If you use $row = mysql_fetch_array($rs) you will recieve an array $row ('col1' => 'foo1', 0 => 'foo1', 'col2' => 'bar', 1 => 'bar')...

Or you can decide you only want numeric or associative indices with mysql_fetch_array by passing MYSQL_NUM or MYSQL_ASSOC as the second parameter..
pilau
Forum Regular
Posts: 594
Joined: Sat Jul 09, 2005 10:22 am
Location: Israel

Post by pilau »

So that means that $row["col1"] = "foo1"? Wait. That can't be. See, I don't understand how I can get the values. There are 2 collumns, means there are two arrays inside the $row array?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Well, the resultset can have multiple rows... You retrieve them one-by-one with a call to mysql_fetch_*.

So, you retrieve 1 row.. And that rows has the columns..

Now you can choose how to represent those columns in the rows...

If you choose MYSQL_ASSOC, each column will be a "key" in the $row.
So $row has 2 keys, 'col1' and 'col2'. You can get the values of them via $row['col1'] and $row['col2'].

If you choose MYSQL_NUMERIC, each column will be a "number" (actually a numeric key) in the $row. So $row has 2 keys, 0 and 1. You can get the values via $row[0] and $row[1]. (It's possible that the keys are 1 and 2)


If you choose MYSQL_BOTH, each column will have a "number" and a "key" that gives access to the value. So you can get the value for col1 by $row[0] or by $row['col1']. And the value for col2 you can get by $row[1] or $row['col2'].


Consider mysql_fetch_assoc as a shortcut to mysql_fetch_array( .. , MYSQL_ASSOC)
pilau
Forum Regular
Posts: 594
Joined: Sat Jul 09, 2005 10:22 am
Location: Israel

Post by pilau »

Ok, so, col1 and col2 are indexes for the array, but both columns have 4 rows in them. how do I collect the data of the 2nd row for example, from col1, using fetch_assoc?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

It's the other way round ;)


You have to retrieve rows one at a time.. Each row(array) has columns (either numeric, either names or both).

So if you want to know col1 from the 2nd row..

Code: Select all

$rs = mysql_query(......);
$row = mysql_fetch_assoc($rs); // row contains info from 1st row
$row = mysql_fetch_assoc($rs); // row contains info from 2nd row
echo $row['col1'];
pilau
Forum Regular
Posts: 594
Joined: Sat Jul 09, 2005 10:22 am
Location: Israel

Post by pilau »

Oh! each time I want to move on to the next row I must run fetch_assoc again!
Just another question, what if I am on row 2, and I want to go back to row #1?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

With http://www.php.net/mysql_data_seek you can navigate through the resultset...
Post Reply