[SOLVED] Problem displaying JOIN query information

[bajangerry]

Hi Guys,

I have created a JOIN query from two tables both of which have columns named "time". My question is how do I distinguish between these two columns when it comes to displaying the result on a HTML page?

See code below:

 

<?php

include("lib/config.php");

top();

 

$result = mysql_query("SELECT * FROM unlime LEFT JOIN import ON unlime.number=import.calledno AND TIMEDIFF(unlime.time, import.time) BETWEEN -2 and 2");

 

while ($result = mysql_fetch_object($result)){

?>

<p>

<?

echo ($row->number);

echo " ";

echo ($row->destination);

echo " ";

echo ($row->calledno);

echo " ";

echo ($row->time);

echo " ";

echo ($row->mins);

echo " ";

echo ($row->amt);

echo " ";

}

?>

</p>

[TeNDoLLA]

You will probably have to use aliases for the field names in your select.

 

SELECT unlime.time AS time1, import.time AS time2 FROM ...

[bajangerry]

TeNDoLLA,

Thanks for your reply. There are a quite number of fields in the tables so I was hoping that would not be the answer but I guess I will have to put in each one. I will try it out now and let you know if I need any more help.

 

 

 

 

 

 

 

 

 

 

[TeNDoLLA]

You could also do this but I dont know if its very good approach

 

SELECT *, unlime.time AS time1, import.time AS time2 FROM ...

[bajangerry]

I made a slight change and got the result I was looking for as it was only this time column that I needed specifying. What are the problems with this way of doing this rather than specifying each column for unlime.xxx?

 

$result = mysql_query("SELECT *,unlime.time AS time1 FROM unlime LEFT JOIN import ON unlime.number=import.calledno AND unlime.month = import.month AND unlime.day = import.day AND TIMEDIFF(unlime.time, import.time) BETWEEN -5 and 5");

 

By the way, looking at the above code, do you agree that the "TIMEDIFF(unlime.time, import.time) BETWEEN -5 and 5" section will allow the query to search based on time accepting a difference of 5 mins between them? I am still a little skeptical that this is working correctly as well, tha tis why I want to display both table's times.

[TeNDoLLA]

See this http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff , both expressions must be same type and must be either DATE or DATETIME type to work correctly. Also the return value will be something like '48:23:12' style. So I doubt that works like you expect it to do.

 

And dunno if there is anything major using the star and aliases together, just a few more fields selected that are not neede to select. But I think its quite minor.

[bajangerry]

Both times are the same type of TIME. If the result is in hh:mm:ss then "BETWEEN -5 AND 5" would relate to 5 seconds while I want it to be 5 mins... hmm... Gotta think about this now.

[bajangerry]

ok, figured out that the values would be in seconds, hence 120 is 2 minutes so I can use "TIMEDIFF(unlime.time, import.time) BETWEEN -120 and 120" to test for results within a minute of each other. I am seeing light at the end of the tunnel, thanks for your help TeNDoLLA.