Select from

[Draucia]

I just started learning mysql (today) and I figured out how to insert data into a table and how I am learning how to echo the data.

Here is what I have so far:

$email = mysql_query("SELECT * FROM mail") or die (mysql_error());

$row = mysql_fetch_array($email);

echo $row['email'];

This does work, but only echos the first row. How can I echo the 2nd or 3rd? If I just repeat the echo statement it just echos the first row.

EDIT: Ugh this is confusing. I just figured out that if I do like:

$row = mysql_fetch_array($email);
$row = mysql_fetch_array($email);
$row = mysql_fetch_array($email);

3 times, it will display the 3rd array. Why is this?

[smantscheff]

The mysql_query function sets an internal pointer, the cursor, to record 0. Each subsequent call of mysql_fetch_array retrieves the current row and advances the cursor by 1 row. If you call mysql_fetch_array 3 times, the result of the last call will be the 3rd row.

[simplypixie]

You need to use a while loop, as such:

$email = mysql_query("SELECT * FROM mail") or die (mysql_error());

while ($row = mysql_fetch_array($email)) {

echo $row['email'].'<br>';

}

The <br> is just to break up your results for this example.

[Draucia]

The mysql_query function sets an internal pointer, the cursor, to record 0. Each subsequent call of mysql_fetch_array retrieves the current row and advances the cursor by 1 row. If you call mysql_fetch_array 3 times, the result of the last call will be the 3rd row.

Ohhh ok that clears it up for me. I have one question, though. I have made a table with id, email, and phone. I made it with:

id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

Now it works fine and all but whenever I insert a value into email and phone (which are varchar and int), ID increases by 1. This is what I want. But when I delete a row, and reinsert the data, the ID increases by 1 which it left off with. For example, I insert email and phone into the table, so it gets the id 1. Then I do the next.

--ID-----------EMAIL----------------Phone---
1 [e-mail] 555-555-555
2 [e-mail] 555-555-555

But when I delete row 1 & 2, and insert the new data, it starts off with id 3. Why is this?

[smantscheff]

MySQL auto_increment fields are counters which move upward only. With each new record the value for the next record is increased by 1 by the system. Thus you can be sure that in each table you have unique values in auto_increment fields even if you deleted some records in between.
To restart the numbering at zero you have to delete and re-create the table.