2 Queries become one

[thefollower]

Is there a way to merge this:

 

$Getreports = mysql_query("SELECT * FROM textmessages WHERE Status = '3' AND Staff='0' ORDER BY `SentTime` ASC")
or die(mysql_error());
$GetReports2 = mysql_query("SELECT * FROM phonecalls WHERE Status = '3' AND Staff='0' ORDER BY 'SentTime' ASC")
or die(mysql_error());

 

So that when i do:

 

while(mysql_fetch_assoc($Getboth)    < it will alternate between each query per row?

[revraz]

Just to confirm, you have a SentTime, Status and Staff column in both tables?

[thefollower]

Yeah

[emehrkay]

use a join

 

example

 

SELECT table1.fileld1, table.field2, table2.field1, table2.field2, FROM

table1

INNER JOIN

table2 ON table1.common_column = table2.common_column

WHERE

common_column = value

 

now if you want to return values from both tables that happen to be in columns of the same name, use an alias

 

SELECT table1.field1 AS t_field1, table2.field1 as tt_field1 ...

[revraz]

It's not very good practice to have repeating data in your database tables.  You should only need it in one table and then link them in the database.  I would recommend you consider looking into re-organizing your DB layout now before you get too deep.

[emehrkay]

It's not very good practice to have repeating data in your database tables.  You should only need it in one table and then link them in the database.  I would recommend you consider looking into re-organizing your DB layout now before you get too deep.

 

Just by looking at hsi table names I can see how it is necessary for him to repeat the same data in both tables...

[thefollower]

The two tables are completely different features how ever rather than two while loops i wanted to just put one to keep it less complex.

 

I could not see how the two could be together..

[thefollower]

use a join

 

example

 

SELECT table1.fileld1, table.field2, table2.field1, table2.field2, FROM

table1

INNER JOIN

table2 ON table1.common_column = table2.common_column

WHERE

common_column = value

 

now if you want to return values from both tables that happen to be in columns of the same name, use an alias

 

SELECT table1.field1 AS t_field1, table2.field1 as tt_field1 ...

 

 

So :

 

SELECT textmessages.Status, textmessages.Staff, phonecalls.Status, phonecalls.Staff FROM
textmessages
INNER JOIN
phonecalls ON 

// dont know what to put below:
textmessages.common_column = table2.common_column
WHERE
common_column = value

 

Dont quite get the last bit.. which column is the common one?

[emehrkay]

Well you need to use aliases becuase you are pulling Status and Staff from both tables

 

You can only make these two queries into one query if there is foreign key in one table.

 

How are the tables related?

[thefollower]

Well the only real connection is the fact that Status =  3

 

 

Because that means 3 = "reported" by a user.

 

Which is what im doing.. but rather than staff having to look through a table of reported text messages

And then a separate table for phonecalls I thought i shall interlink them.. so that staff don't keep prioritizing the first table on the page.

 

Before hand i have:

List of reported text messages:

rows etc

rows

rows

 

then i had:

List of repoted phone calls:

Rows

rows

rows

 

 

And staff kept ignoring the phone calls cos it was further down the page so i want them interlinked so staff don't know which one is which thus theres none being neglected from sheer laziness of scrolling down the page

[emehrkay]

Since the table has no relationship between keys, id say keep the queries seperate, when you loop through one of the datasets, just print out the data from both data sets