I have multiple tables in a db, and they all have the same structure. It keeps track of jobs, and who they are assigned to. I want to make a list of all the people that have a job assigned to them. In other words, I want to get all the assigned_to data from MULTIPLE TABLES, BUT I don't want a bunch of duplicates (like if one guy has 10 jobs he's working on). I have to use 3.23.56 because my hosting co won't upgrade to 4.x.x, so the UNION command doesn't work.i don't get your table structure!
but try the group by
select assign_to from table1, table2 group by assign_toOk, I have tables named by the states that the data affects (az, nv, tx, nm, etc) and the structures are:
ID, contact, phone, cell, assigned_to, notes, etc.
I need to get the assigned_to's out of all the tables, but I don't want to list a bunch of duplicates.
Also, your example doesn't seem to work for me. I get an error that says that the query is no valid.
EDIT: Is there a way in php to add a value to an array only if the array doesn't currently have that value in it?I worked out somewhat of a solution...let me know if there is something better.
I start by creating an empty array to later fill with all the unique "assigned_to" variables. and a variable called $no-match that is set to TRUE.
I already have the tables listed in a txt file as tblname*table description. I use the file() command to read the tables into an array. Then I use preg_split inside a for loop to seperate out just the table names, and put them into another array called tables.
Then I use 2 nested for loops. The first is used to work through all the tables, and read the data. the second is used to run through the array that holds the unique assigned_to data, and checks to see if the current assigned_to equals any of the data in the array. If so, it sets the $no_match variable to FALSE. Back in the outside for loop, I check to see if no_match is true. if so, I add the current assigned_to to the array, and if not I set no_match back to true.thats the long way but ok.
you could have used distinct on the category that you want back were you don't want duplicates.put everything in an array and do array_unique()Originally posted by scoutt
thats the long way but ok.
you could have used distinct on the category that you want back were you don't want duplicates.
The problem with that was that since mysql is too old to use union, I have to pull them in from different select statements, and so I could still get the same entry from 2 different tables.
illogique - Thanks for that command...I didn't know such a thing existed...that's perfect, it will replace an entire for loop.you can select as many tables as you want in the same query. you can also use LEFT JOIN as well. don't have to use union.Could you give me an example of how to use left join? Say I have 1 database (employees) and 2 tables (az for the arizona employees, and nv for the nevada employees), and I want to pull all their employee IDs, names, and their salary (ID, name, salary).Say I have 1 database (employees) and 2 tables (az for the arizona employees, and nv for the nevada employees), and I want to pull all their employee IDs, names, and their salary (ID, name, salary).
1 database and 2 tables? did you mean 3 seperate tables?
select ID,name,salary from employees
LEFT JOIN az ON (az.column=employees.column)
but the az table and the employees table have to have a column that links the 2.yeah...that doesn't really work I don't think, because the 2 tables are the az, and nv tables, and they are formatted exactly the same...ID, name, salary. There isn't really a column to link them on. I just want to list ALL the employees, instead of just the arizona employees, and I wanted to list them alphabetically. The problem is, when I read them in through 2 select statements, with ORDER BY, I get the az employees in alphabetic order, and then I get the nv employees in alphabetic order.
How can I get them re-formatted into 1 list that is sorted properly?well since they are 2 different tables which don't have anything linked to each other is the clincher.
and you have done this
select ID,name,salary from az,nv order by name
but that will do some weird results as both tables have the same column names.ok, here is what I ended up doing...
I read in from one table, and put the info from all the variables into corresponding arrays
$result = mysql_query("SELECT ID, name, salary FROM $state");
while ($row_info = mysql_fetch_array($result))
{
//read needed info into variables
$ID[sizeof($ID)] = $row_info["ID"];
$name[sizeof($ID)] = $row_info["name"];
$salary[sizeof($ID)] = $row_info["salary"];
}
and I loop through for each $state (the different tables in the database).
Then I sort them with array_multisort (found it on php.net...nice little tool)
array_multisort($name, $ID, $salary);
Then I loop through the arrays, outputting the data to a table.
for ($j=0;$j<sizeof($ID);$j++)
{
//output info to table
echo " <tr style=\"background-color:$bg_color;\">\r\n";
echo " <td>&name[$j]</td>\r\n";
echo " <td>$salary[$j]</td>\r\n";
}that seems like the only way with the setup you have.
but try the group by
select assign_to from table1, table2 group by assign_toOk, I have tables named by the states that the data affects (az, nv, tx, nm, etc) and the structures are:
ID, contact, phone, cell, assigned_to, notes, etc.
I need to get the assigned_to's out of all the tables, but I don't want to list a bunch of duplicates.
Also, your example doesn't seem to work for me. I get an error that says that the query is no valid.
EDIT: Is there a way in php to add a value to an array only if the array doesn't currently have that value in it?I worked out somewhat of a solution...let me know if there is something better.
I start by creating an empty array to later fill with all the unique "assigned_to" variables. and a variable called $no-match that is set to TRUE.
I already have the tables listed in a txt file as tblname*table description. I use the file() command to read the tables into an array. Then I use preg_split inside a for loop to seperate out just the table names, and put them into another array called tables.
Then I use 2 nested for loops. The first is used to work through all the tables, and read the data. the second is used to run through the array that holds the unique assigned_to data, and checks to see if the current assigned_to equals any of the data in the array. If so, it sets the $no_match variable to FALSE. Back in the outside for loop, I check to see if no_match is true. if so, I add the current assigned_to to the array, and if not I set no_match back to true.thats the long way but ok.
you could have used distinct on the category that you want back were you don't want duplicates.put everything in an array and do array_unique()Originally posted by scoutt
thats the long way but ok.
you could have used distinct on the category that you want back were you don't want duplicates.
The problem with that was that since mysql is too old to use union, I have to pull them in from different select statements, and so I could still get the same entry from 2 different tables.
illogique - Thanks for that command...I didn't know such a thing existed...that's perfect, it will replace an entire for loop.you can select as many tables as you want in the same query. you can also use LEFT JOIN as well. don't have to use union.Could you give me an example of how to use left join? Say I have 1 database (employees) and 2 tables (az for the arizona employees, and nv for the nevada employees), and I want to pull all their employee IDs, names, and their salary (ID, name, salary).Say I have 1 database (employees) and 2 tables (az for the arizona employees, and nv for the nevada employees), and I want to pull all their employee IDs, names, and their salary (ID, name, salary).
1 database and 2 tables? did you mean 3 seperate tables?
select ID,name,salary from employees
LEFT JOIN az ON (az.column=employees.column)
but the az table and the employees table have to have a column that links the 2.yeah...that doesn't really work I don't think, because the 2 tables are the az, and nv tables, and they are formatted exactly the same...ID, name, salary. There isn't really a column to link them on. I just want to list ALL the employees, instead of just the arizona employees, and I wanted to list them alphabetically. The problem is, when I read them in through 2 select statements, with ORDER BY, I get the az employees in alphabetic order, and then I get the nv employees in alphabetic order.
How can I get them re-formatted into 1 list that is sorted properly?well since they are 2 different tables which don't have anything linked to each other is the clincher.
and you have done this
select ID,name,salary from az,nv order by name
but that will do some weird results as both tables have the same column names.ok, here is what I ended up doing...
I read in from one table, and put the info from all the variables into corresponding arrays
$result = mysql_query("SELECT ID, name, salary FROM $state");
while ($row_info = mysql_fetch_array($result))
{
//read needed info into variables
$ID[sizeof($ID)] = $row_info["ID"];
$name[sizeof($ID)] = $row_info["name"];
$salary[sizeof($ID)] = $row_info["salary"];
}
and I loop through for each $state (the different tables in the database).
Then I sort them with array_multisort (found it on php.net...nice little tool)
array_multisort($name, $ID, $salary);
Then I loop through the arrays, outputting the data to a table.
for ($j=0;$j<sizeof($ID);$j++)
{
//output info to table
echo " <tr style=\"background-color:$bg_color;\">\r\n";
echo " <td>&name[$j]</td>\r\n";
echo " <td>$salary[$j]</td>\r\n";
}that seems like the only way with the setup you have.