Page 1 of 1

I need help with understanding how DBs work.

Posted: Tue Jul 26, 2005 6:08 am
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.

Posted: Tue Jul 26, 2005 6:27 am
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..

Posted: Tue Jul 26, 2005 6:31 am
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?

Posted: Tue Jul 26, 2005 7:26 am
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)

Posted: Tue Jul 26, 2005 11:50 pm
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?

Posted: Wed Jul 27, 2005 8:23 am
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'];

Posted: Wed Jul 27, 2005 8:32 am
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?

Posted: Wed Jul 27, 2005 8:37 am
by timvw
With http://www.php.net/mysql_data_seek you can navigate through the resultset...