Need an example of mysql_fetch_array with same column name PLEASE

[masteroleary]

As you can see in this select statment I am requesting values from multiple tables and columns. Every table has the column 'id' and 'name'. How do I access the value from one table rather than the other. I thought I was supposed to use the mysql 'AS' keyword to create an alias "shoot.id AS shootid" but I cant echo the value.
[code]$query = 'SELECT shoot.id AS shootid, shoot.name AS shootname, site.id AS siteid, site.name AS sitename, model.id AS modelid, model.name AS modelname FROM shoot, site, model';
$result = mysql_query($query);
while($row = mysql_fetch_array($result)){
echo $row['shootid'];
}[/code]

Although I have found the same example on php.net on more than 5 other websites I just cannot find a good example of a while loop that accesses the values.

[masteroleary]

First of all let me say that my initial statement said nothing was being echoed and I learned that was because I hadnt created any sample rows in the site table, thus Im an idiot.

Secondly I have a new problem. In the first code box I am trying to fill a select form element with rows from the models table with the values being the id and the visible text being the models name. Now there are 2 example models in the database. When using the while loop to generate each option in the select form element I see each model four times like in the second code box. I know this is related to selecting many rows from many tables. My question is how can I do it right where the model is only listed once?


[code]$query = 'SELECT shoot.id AS shootid, shoot.name AS shootname, site.id AS siteid, site.name AS sitename, model.id AS modelid, model.name AS modelname FROM shoot, site, model';
$result = mysql_query($query);
$selectModel = '';
$selectShoot = '';

while($row = mysql_fetch_array($result)){
//
$selectModel .= "<option value=\"";
$selectModel .= $row['modelid'];
$selectModel .= '">';
$selectModel .= $row['modelname'];
$selectModel .= "</option>\n";
}

//var $page = <<<PAGE form will go here eventually PAGE;
?>[/code]

[code]<select name="shoot" id="shoot">
          <option value="1">Matthew O'Leary</option>
<option value="1">Matthew O'Leary</option>
<option value="1">Matthew O'Leary</option>
<option value="1">Matthew O'Leary</option>

<option value="2">Tiffany Amato</option>
<option value="2">Tiffany Amato</option>
<option value="2">Tiffany Amato</option>
<option value="2">Tiffany Amato</option>
        </select>[/code]

[masteroleary]

Damn I hate being the only one responding to my question. So Im thinking the solution lies somewhere in this new code:
[code]$query = 'SELECT shoot.id AS shootid, shoot.name AS shootname, COUNT(shoot.id) AS shootcount, site.id AS siteid, site.name AS sitename, model.id AS modelid, model.name AS modelname FROM shoot, site, model';
$result = mysql_query($query);
$selectModel = '';
$selectShoot = '';
$row = mysql_fetch_array($result);
for(var $i = 1 ; i <= $row['shootcount'] ; i++){
//
$selectModel .= "<option value=\"";
$selectModel .= $row['shootid'];
$selectModel .= '">';
$selectModel .= $row['shootname'];
$selectModel .= "</option>\n";
}[/code]

Im trying to count the rows in the table 'shoot' and use that in a for loop but i get this error: unexpected T_VAR, expecting ';' ...

[emehrkay]

inside of your for loop just do print_r($row) to see your array and the names of the keys

[masteroleary]

Wow thats a handy function! Well it doesnt like my for loop and after rewriting the code from box1 (which gave me the error: mysql_fetch_array(): supplied argument is not a valid MySQL result resource) to box2 (removing 'COUNT(shoot.id) AS shootcount,') from the select statement i was able to see all the values. So obviously I am using the COUNT function in the select statement incorrectly. Im just not sure how. If i could use the count function correctly the for loop would work, solving my problem (hopefully).

[code]$query = 'SELECT shoot.id AS shootid, shoot.name AS shootname, COUNT(shoot.id) AS shootcount, site.id AS siteid, site.name AS sitename, model.id AS modelid, model.name AS modelname FROM shoot, site, model';
$result = mysql_query($query);
$selectModel = '';
$selectShoot = '';
$row = mysql_fetch_array($result);
print_r($row);[/code]

[code]$query = 'SELECT shoot.id AS shootid, shoot.name AS shootname,  site.id AS siteid, site.name AS sitename, model.id AS modelid, model.name AS modelname FROM shoot, site, model';
$result = mysql_query($query);
$selectModel = '';
$selectShoot = '';
$row = mysql_fetch_array($result);
print_r($row);[/code]

[masteroleary]

This causes an error
[code]$query = 'SELECT COUNT(shoot.id) AS shootcount, shoot.name AS shootname FROM shoot';[/code]

This returns the count amount as it should
[code]$query = 'SELECT COUNT(shoot.id) AS shootcount FROM shoot';[/code]

Any ideas?