[RESOLVED] 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>

Piranha

You should defenetley not do what you try to do in your code. To be able to get all the years from a "select *"-query you have to go through all the posts in the table. What if you want to return dates, and have a table with millions of rows, then you will have a big problem. Instead you should only return the unique years, read up on distinct and group by to learn this.

You should use only one query instead of two, something simular to this:

select distinct(year) as year from (
select distinct(year) as year from table1
union
select distinct(year) as year from table2
)

It is possible that you should use group by instead of distinct.

182x

Thanks I was able to use just union and that seemed to work. 🙂

Is using union on its own the best method? as it seemed to make the values distinct on its own so I don't know if this is normal practice.

Piranha

Sorry, but I don't understand the question. Can you please use examples or explain the question in some other way?

I am looking on another of your questions. What comes to mind is that I don't understand why you are using 2 tables instead of 1. Can you please explain that, it is no use to solve the problems if the error is in the design.