[SOLVED] PHP query question

[182x]

Hey guys,

 

I am trying to create a query in order to dynamicly popular a listbox however I am trying to fill the listbox with years such as 2007 etc based on if they are stored in the data base. The problem is that two tables have to be checked that have no relation to eachother but this caues the problem of duplication so if both tables have 2007 it will appear in the list more than once.

 

I was just wondering if there is a way round this? Thanks.

 

$years= "SELECT * FROM table1;
$qyears = mysql_query($years, $link_id) or die(mysql_error());


$years2= "SELECT * FROM table2;
$qyears2 = mysql_query($years2, $link_id) or die(mysql_error());

if (mysql_num_rows($qyears2) > 0 || mysql_num_rows($qyears1) > 0)
{
?>
<select name="years">
<?php

//don't know what do do here
while($row = mysql_fetch_assoc($qyears))
{
?>
  <option value="<?php echo $row['year']; ?>"><?php echo $row['year']; ?></option>
<?php
}
}
?>
</select>

[nicelad_uk]

Just give this a try for me, dont know if it will work on MYSQL:

 

$years= "SELECT DISTINCT(year) FROM table1,table2";
$qyears = mysql_query($years) or die(mysql_error());

?>
<select name="years">
<?php

//don't know what do do here
while($row = mysql_fetch_assoc($qyears))
{
?>
  <option value="<?php echo $row[$qyears]; ?>"><?php echo $row[$qyears]; ?></option>
<?php
}
}
?>
</select>
[/quote]

[182x]

I get this error:

 

Column 'year' in field list is ambiguous

[182x]

Still can't figure this one out, anyone got anymore ideas? Thanks.

[dg]

it must be present in both the tables, give from which table u want.... alias

[dg]

$years= "(SELECT year FROM table1 t1) UNOIN ALL (SELECT year FROM table2 t1)"

 

 

[182x]

Thanks, if its done that way is there a method that can limit the result for example if 2007 appears 8 times can it be limited to just appearing once?

[dg]

instead of UNION ALL use  UNION

[182x]

does that still only produce one 2007 value if there are three 2007 values in table1 and four 2007 values in table2?