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...