combined qry tables

[wmguk]

Hey,

 

I've got a qry that is using two tables of information...

 

$eventssql = "SELECT * FROM events, shortlists_history WHERE events.shortlist_id={$short['shortlist_id']} && shortlists_history.shortlist_id={$short['shortlist_id']} ORDER BY shortlists_history.date DESC";

$eventsresult = mysql_query($eventssql) or die(mysql_error());

 

then I have the results displayed:

<?php 
while ($event = mysql_fetch_array($eventsresult)) {
echo "<tr><td>{$event['event']}</td><td>".date('d/m/y h:ia', strtotime($event['date']))."</td></tr>";
echo "<tr><td>{$event['status']}</td><td>".date('d/m/y h:ia', strtotime($event['date']))."</td></tr>"; //NEED OTHER DATE
} ?>

 

however obviously they are using the same date column, but i need the line with status displayed to show the date in the shortlists_history date field, not the events date field...

 

i tried

<?php 
while ($event = mysql_fetch_array($eventsresult)) {
$sdate = $events['shortlists_history.date'];
echo "<tr><td>{$event['event']}</td><td>".date('d/m/y h:ia', strtotime($event['date']))."</td></tr>";
echo "<tr><td>{$short['status']}</td><td>".date('d/m/y h:ia', strtotime($sdate))."</td></tr>";
} ?>

but it didnt work, how can i use the date column in the event database as $edate, and the date in shortlists_history as $sdate?

[sasa]

try

$eventssql = "SELECT *, shortlists_history.date As sdata FROM events, shortlists_history WHERE events.shortlist_id={$short['shortlist_id']} && shortlists_history.shortlist_id={$short['shortlist_id']} ORDER BY shortlists_history.date DESC";
$eventsresult = mysql_query($eventssql) or die(mysql_error());
...
$sdate = $events['sdate'];
...

[wmguk]

Hey,

 

I've just tried this but it still shows the wrong date..

$eventssql = "SELECT *, shortlists_history.date As sdate FROM events, shortlists_history WHERE events.shortlist_id={$short['shortlist_id']} && shortlists_history.shortlist_id={$short['shortlist_id']} ORDER BY shortlists_history.date DESC";
                              $eventsresult = mysql_query($eventssql) or die(mysql_error());
...

while ($event = mysql_fetch_array($eventsresult)) {
echo "<tr><td>{$event['status']}</td><td>".date('d/m/y h:ia', strtotime($event['sdate']))."</td></tr>";
echo "<tr><td>{$event['event']}</td><td>".date('d/m/y h:ia', strtotime($event['date']))."</td></tr>";
}

[wmguk]

hmmm, actually I'm sorry, it seems that it did work, however I'm unsure whats happening with the script now...

 

in events under ref:413 i have one record:

"cand App" 09/02/10 - 19:13

 

in shortlists_history I have 2 records for 413:

"cv sent" 09/03/10 - 15:56

"applied" 09/02/10 - 19:13

 

and in shortlists I have 1 record for 413:

"applied" 09/03/10 - 16:00

 

however my page shows:

 

Applied 10/03/09 - 16:00

Applied 10/03/09 - 15:56

Candidate Applied 10/03 09 - 16:00

Applied 10/03/09 - 16:00

Applied 10/02/09 - 19:13

Candidate Applied 10/03 09 - 16:00

 

my qry is:

$eventssql = "SELECT *, shortlists_history.date As sdate, shortlists.date As s_date, shortlists.status As s_status FROM events, shortlists_history, shortlists WHERE events.shortlist_id={$short['shortlist_id']} && shortlists_history.shortlist_id={$short['shortlist_id']} && shortlists.shortlist_id={$short['shortlist_id']} ORDER BY shortlists_history.date DESC";

$eventsresult = mysql_query($eventssql) or die(mysql_error());

 

and my results are:

<?php 
while ($event = mysql_fetch_array($eventsresult)) {
echo "<tr><td>{$event['s_status']}</td><td>".date('d/m/y - H:i', strtotime($event['s_date']))."</td></tr>";
echo "<tr><td>{$event['status']}</td><td>".date('d/m/y - H:i', strtotime($event['sdate']))."</td></tr>";
echo "<tr><td>{$event['event']}</td><td>".date('d/m y - H:i', strtotime($event['date']))."</td></tr>";
} ?>

 

its not displaying the correct names, and for some reason I should only have 4 results, but i get 6

I'd appriciate any help...

[sasa]

your query returns two rows, and each of this row output 3 table rows

 

2*3=6 rows total

[wmguk]

hmmm, but why does it return two rows not all 4?

[wmguk]

hmmm, I just checked on record and it says:

 

CV Sent 27/01/09 - 11:51

CV Sent 26/01/09 - 10:19

AW - STC 19/12/09 27/01 09 - 11:51

 

I added:

test info from drew 27/01 09 - 11:51

 

and now I have

 

CV Sent 27/01/09 - 11:51

CV Sent 26/01/09 - 10:19

AW - STC 19/12/09 27/01 09 - 11:51

CV Sent 27/01/09 - 11:51

CV Sent 26/01/09 - 10:19

test info from drew 27/01 09 - 11:51

 

but why is it repeating:

CV Sent 27/01/09 - 11:51

CV Sent 26/01/09 - 10:19

on each record found....?

[sasa]

but why is it repeating:

CV Sent 27/01/09 - 11:51

CV Sent 26/01/09 - 10:19

on each record found....?

and what do you want to repeat

[wmguk]

i dont want to repeat anything... I need to show all the results in the 3 different tables... there should only be 4 results not 6....

 

CV Sent 27/01/09 - 11:51

CV Sent 26/01/09 - 10:19

AW - STC 19/12/09 27/01 09 - 11:51

CV Sent 27/01/09 - 11:51 *******REPEATED

CV Sent 26/01/09 - 10:19 *******REPEATED

test info from drew 27/01 09 - 11:51

[sasa]

you have TWO results not 6

in while loop each time you echo 3 html table (<tr> tag) rows

are you want to while loop repeat 1.333 times? (for 4 html rows)

[kickstart]

Hi

 

You do not appear to be joining the tables conventionally, merely relying on specifying the key on both tables which could be risky. Try something like this normally.

 

$eventssql = "SELECT * FROM events a INNER JOIN shortlists_history b ON a.shortlist_id = b.shortlist_id WHERE a.shortlist_id={$short['shortlist_id']} ORDER BY shortlists_history.date DESC";

 

However looking at the results you appear to want it looks like what you might want it a join of events and shortlists and union that with a join of evenets and shortlists_hostory. Maybe something like:-

 

$eventssql = "SELECT *, b.date AS SortDate FROM events a INNER JOIN shortlists b ON a.shortlist_id = b.shortlist_id WHERE a.shortlist_id={$short['shortlist_id']} 
UNION
SELECT *, b.date AS SortDate FROM events a INNER JOIN shortlists_history b ON a.shortlist_id = b.shortlist_id WHERE a.shortlist_id={$short['shortlist_id']}
ORDER BY SortDate DESC";

 

 

All the best

 

Keith

[wmguk]

Hi Keith,

 

On using your qry i get the following error:

 

The used SELECT statements have a different number of columns

 

all i need to do is get all the results from table1, all the results from table2 and all the results from table3 where id=1 and display them in one ordered list...

[kickstart]

Hi

 

Trouble with joining them is that you will get all the columns back in one lump. You will then have to sort out which events go with which shortlist and shortlist_hostory. For example if you had (say) 1 entry for an event on shortlist and 20 entries on the shortlist_history table you would get 20 rows back, one for each of shortlist_history but every one of them having the same info for shortlist

 

Hence I suggested a union. One query to get all the shortlist entries for an event and one to get all the shortlist_history entries for that event.

 

Issue appears to be that shortlist and shortlist_history have different numbers of columns. Easy solution is to just specify the columns you are actually interested in. You only seem to use the date, status and possibly the id so something like :-

 

$eventssql = "SELECT a.shortlist_id, b.Status, b.date AS SortDate FROM events a INNER JOIN shortlists b ON a.shortlist_id = b.shortlist_id WHERE a.shortlist_id={$short['shortlist_id']} 
UNION
SELECT a.shortlist_id, b.Status, b.date AS SortDate FROM events a INNER JOIN shortlists_history b ON a.shortlist_id = b.shortlist_id WHERE a.shortlist_id={$short['shortlist_id']}
ORDER BY SortDate DESC";

 

All the best

 

Keith

[wmguk]

wow, this is confusing...

 

so in your example I simply change a.shortlist_idto shortlist.shortlist_id and b.status to shortlist_history.status?

[kickstart]

Hi

 

I have just used "a" and "b" as short tables names (eg, specified - FROM events a INNER JOIN shortlists b ) . Saves typing the full table names when specifying column names.

 

The big difference is that you had "*" to return ALL the columns (whether you need them or not) where I have reduced that to just returning a couple of specified columns which you actually need.

 

The UNION puts 2 (or more) sets of results from a couple of queries together and returns them as 1 set of results. However to do this both of the queries must return equivalent columns. You cannot have 1 returning (say) 5 columns and the other returning 6 columns.

 

All the best

 

Keith

[wmguk]

hi keith,

 

thanks for the continued help! I'm still learning this!

 

I simply need to show

 

shortlists.date

shortlists.shortlist_id

shortlists.status

 

shortlists_history.date

shortlists_history.shortlist_id

shortlists_history.status

 

events.date

events.shortlist_id

events.event

 

and show event/status | date | id

 

I'm really sorry and I know it seems like im asking you to do it for me, but i am totally lost on this join / union functions...

[kickstart]

Hi

 

No problems. I will try and talk you through it.

 

While you say you need event/status | date | id, I think that what you mean is that for all of event/status | date | id combined with all of event/historystatus | date | id. So your list would be something like:-

 

event/status | date | id

Applied | 10/03/09 - 16:00 | 413

Applied | 11/03/09 - 16:01 | 413

Applied | 12/03/09 - 16:02 | 413

Applied | 13/03/09 - 16:03 | 413

Applied | 14/03/09 - 16:04 | 413

Applied | 15/03/09 - 16:05 | 413

Applied | 16/03/09 - 16:06 | 413

Applied | 17/03/09 - 16:07 | 413

 

with the ones in purple being from the shortlists_history table and the ones in blue being from the shortlists table.

 

Now you could have 2 totally seperate selects, get the results from one against shortlists_history, loop round them and output them, then do the same for one against the shortlists table. For example, something like this:-

 

<?php
$eventssql = "SELECT a.shortlist_id AS IdField, a.event, b.date, b.status FROM events a INNER JOIN shortlists b ON a.shortlist_id = b.shortlist_id WHERE events.shortlist_id={$short['shortlist_id']} ORDER BY shortlists_history.date DESC";

$eventsresult = mysql_query($eventssql) or die(mysql_error());

while ($event = mysql_fetch_array($eventsresult))
{
echo "<tr><td>{$event['event']}</td><td>".date('d/m/y h:ia', strtotime($event['date']))."</td><td>".event['IdField']."</td></tr>";
}

$eventssql = "SELECT a.shortlist_id AS IdField, a.event, b.date, b.status FROM events a INNER JOIN shortlists_history b ON a.shortlist_id = b.shortlist_id WHERE events.shortlist_id={$short['shortlist_id']} ORDER BY shortlists_history.date DESC";

$eventsresult = mysql_query($eventssql) or die(mysql_error());

while ($event = mysql_fetch_array($eventsresult))
{
echo "<tr><td>{$event['event']}</td><td>".date('d/m/y h:ia', strtotime($event['date']))."</td><td>".event['IdField']."</td></tr>";
}

?>

 

However this relies on things being in the right order. That all your fields on the shortlist_history table are older than those on the shortlist for the same shortlist_id.

 

The solution to this is to use a UNION. What this does is get the 2 lots of results, put them together and then do the sort on them. For example if you had 2 selects, one that bought back rows A, G and Z, and the other brought back B, C and X, then a basic UNION of the 2 selects would bring back A, G, Z, B, C and X. A sort clause at the end would then affect all those results so bringing back A, B, C, G, X and Z.

 

Putting that idea into what you are trying to achieve I think you would do something like this:-

 

<?php
$eventssql = "SELECT a.shortlist_id AS IdField, a.event, b.date, b.status FROM events a INNER JOIN shortlists b ON a.shortlist_id = b.shortlist_id WHERE events.shortlist_id={$short['shortlist_id']} UNION SELECT a.shortlist_id AS IdField, a.event, b.date, b.status FROM events a INNER JOIN shortlists_history b ON a.shortlist_id = b.shortlist_id WHERE events.shortlist_id={$short['shortlist_id']} ORDER BY shortlists_history.date DESC";

$eventsresult = mysql_query($eventssql) or die(mysql_error());

while ($event = mysql_fetch_array($eventsresult))
{
echo "<tr><td>{$event['event']}</td><td>".date('d/m/y h:ia', strtotime($event['date']))."</td><td>".event['IdField']."</td></tr>";
}

?>

 

Does that make sense?

 

All the best

 

Keith