[Resolved] Selecting an entire column

Benny_Johnson

Hi folks!

I have a table with about 3 rows and 4 columns. Each column has a unique name.

I want to take all the data from one specific column; we'll call it "pet_names".

owner | pet_type | pet_names | pet_age

john bird penelope 2
barbara cat kittybop 6
tommy dog rover 3

You get the idea.

I want to get all of the pet names and put them in an array and then retrieve each item in the array, one after another, separated by break tags. Here's what I'm doing, minus all the database connection nonsense, which I have excluded:

$thisdb = "my_database";
$thistable = "pet_stuff";
$query = "SELECT pet_name FROM " . $thistable;
$query_result = mysql_db_query($thisdb, $query);
$pet_name_array = mysql_fetch_array($query_result, MYSQL_ASSOC);
foreach ($pet_name_array as $petname) {
echo "$petname <br />\n";
}

This works, but only for the first pet name, not all three. I consulted the SQL documentation and in the examples they give, this select query selects an entire column, not just the first entry in the column, which is what is happening when I use the above code.

Any ideas?

THanks!

pete_bisby

Create a for loop, which will count from the first row in your recordset to the last, and place the contents of each row in an array. For example,

$query = "SELECT `pet_name` FROM " . $thistable;
$query_result = mysql_db_query($thisdb, $query);
for ($i=0; $i < mysql_num_rows($query_result); $i++) {
  $row_result = mysql_fetch_array($query_result);
  echo $row_result["pet_name"] . " <br />";
}

Benny_Johnson

Wow, great, thanks so much, Pete. That works just the way I would like it to 🙂

I like to understand what's being done, so may I ask, what does the expression

for ($i=0; $i < mysql_num_rows($query_result); $i++)

mean? My guess is that it means something like, "set $i equal to zero; for whenever $i is less than the total number of rows in the table (where table is specified by the query string) add that data to an array; increment $i by 1 for each cycle of the loop."

Is that fairly close? Just not sure what the ++ operator is I suppose...such a noob.

Also, how do you color and tab your text like that? It really makes it a lot earier for me to read and I'd like to do it for other people.

Thanks!!

pete_bisby

Yep, pretty close there. $i is a variable within the for loop, so "$i=0" is the starting criteria.

For each successful pass through the loop, the criteria "$i < mysql_num_rows($query_result)" has to be met. So, when $i is equal to the number of rows in your query result, the loop ends.

The "$i++" does increment the variable by 1 each time a loop is successful. The "++" is C/C++ notation for increment. Similarly, "--" is to decrement the variable.

And to get the coloured text use the "PHP" button when creating your message - it's just underneath the SIZE/FONT/COLOR drop-down lists.

Have a look at the Docs section on this web-site for other loops that can be used.

Benny_Johnson

Great, thanks again, Pete. It makes perfect sense.

I know this is a little off topic and I'll stop bugging you after this question, but have you ever seen the SQL error "Unknown column '0x' in 'where clause'" ? It just started happening and I'm not sure what I changed to cause it. It happens whenever I go to edit or delete a row in my table. I think it started when I changed an INT column specifier to DATE and turned off the auto-incrementing. I think I might have botched it up but I'm not sure how. Basically, I want to have a date column at the very left of my table and have it serve as the index, but it doesn't seem to be working right.

Again, I really appreciate your insights.

Thanks,

Ben

Benny_Johnson

Appears that I just needed to update phpmyadmin to the latest version...piece of trash.