Re: [PHP] Decide witch table within a union
On Oct 30, 2006, at 9:20 PM, Børge Holen wrote: On Tuesday 31 October 2006 02:27, Ed Lazor wrote: Someone asked what it was to be used for. It's for combining 4 forums into one witch shows the latest movement among them. More like an introduction sort of... Sounds cool. Are you using a pre-made forum package? No Maby it'd been easier, I did that at first... But not much to learn using other ppls tools. True, a good challenge is always fun :) -Ed -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Decide witch table within a union
On Monday 30 October 2006 05:46, Richard Lynch wrote: On Sun, October 29, 2006 3:34 pm, Børge Holen wrote: Hi. I got this working (almost) How do I decide (inside?) the whileloop the table_nr, 1 to 4 witch the link is echo'ed from. I could add another field in each table telling witch table it is. Easy solution, but feels like polluting the db. Also I could do another loop to check another of the printed fields up agains witch table is used, but that adding to the slowness of this union stuff, it would take forever, if the union in itself is slow or the fact that I'm cutting the message field after 100 characters I dunno. This is anyway the main code wherein the problem lies: $sql= (select * from table_1 WHERE pid='0' order by id desc limit 10) select *, 'table_1' from table_1 ... union (select * from table_2 WHERE pid='0' order by id desc limit 10) select *, 'table_2' from table_2 ... This gives you an extra field with the table name in it. You may not be able to use * any more, and have to name all the fields you actually want. Which is a better practice anyway. Yes, I never used *, I just inserted it to get rid of all the convertings of fields from the db. The amount of information wasn't useful to represent my problem. I've learned something very useful through this though... How to both NAME and INSERT information into an virtual field at an instance... THANKS to all of you=D. Someone asked what it was to be used for. It's for combining 4 forums into one witch shows the latest movement among them. More like an introduction sort of... -- Some people have a gift link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- --- Børge Kennel Arivene http://www.arivene.net --- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Decide witch table within a union
Someone asked what it was to be used for. It's for combining 4 forums into one witch shows the latest movement among them. More like an introduction sort of... Sounds cool. Are you using a pre-made forum package? -Ed -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Decide witch table within a union
On Tuesday 31 October 2006 02:27, Ed Lazor wrote: Someone asked what it was to be used for. It's for combining 4 forums into one witch shows the latest movement among them. More like an introduction sort of... Sounds cool. Are you using a pre-made forum package? No Maby it'd been easier, I did that at first... But not much to learn using other ppls tools. -Ed -- --- Børge Kennel Arivene http://www.arivene.net --- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Decide witch table within a union
PHP can't tell what table the data came from.
On Oct 29, 2006, at 1:34 PM, Børge Holen wrote:
Hi.
I got this working (almost)
How do I decide (inside?) the whileloop the table_nr, 1 to 4 witch
the link is
echo'ed from.
I could add another field in each table telling witch table it is.
Easy
solution, but feels like polluting the db.
Also I could do another loop to check another of the printed fields
up agains
witch table is used, but that adding to the slowness of this union
stuff, it
would take forever, if the union in itself is slow or the fact that
I'm
cutting the message field after 100 characters I dunno.
This is anyway the main code wherein the problem lies:
$sql= (select * from table_1 WHERE pid='0' order by id desc limit
10) union
(select * from table_2 WHERE pid='0' order by id desc limit 10)
union
(select * from table_3 WHERE pid='0' order by id desc limit 10)
union
(select * from table_4 WHERE pid='0' order by id desc limit 10)
order by date desc limit 10 ;
$result = mysql_query($sql) or die('Error, query failed');
while($myrow = mysql_fetch_array($result)){
$id = $myrow[id];
$pid = $myrow[pid];
echo a href=\index.php?table=$table_nrpage/a;
}
--
---
Børge
Kennel Arivene
http://www.arivene.net
---
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Decide witch table within a union
Ok.
Would you suggest to use a extra field to hold the table name as default?
On Sunday 29 October 2006 22:59, Ed Lazor wrote:
PHP can't tell what table the data came from.
On Oct 29, 2006, at 1:34 PM, Børge Holen wrote:
Hi.
I got this working (almost)
How do I decide (inside?) the whileloop the table_nr, 1 to 4 witch
the link is
echo'ed from.
I could add another field in each table telling witch table it is.
Easy
solution, but feels like polluting the db.
Also I could do another loop to check another of the printed fields
up agains
witch table is used, but that adding to the slowness of this union
stuff, it
would take forever, if the union in itself is slow or the fact that
I'm
cutting the message field after 100 characters I dunno.
This is anyway the main code wherein the problem lies:
$sql= (select * from table_1 WHERE pid='0' order by id desc limit
10) union
(select * from table_2 WHERE pid='0' order by id desc limit 10)
union
(select * from table_3 WHERE pid='0' order by id desc limit 10)
union
(select * from table_4 WHERE pid='0' order by id desc limit 10)
order by date desc limit 10 ;
$result = mysql_query($sql) or die('Error, query failed');
while($myrow = mysql_fetch_array($result)){
$id = $myrow[id];
$pid = $myrow[pid];
echo a href=\index.php?table=$table_nrpage/a;
}
--
---
Børge
Kennel Arivene
http://www.arivene.net
---
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
---
Børge
Kennel Arivene
http://www.arivene.net
---
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Decide witch table within a union
Børge Holen wrote: Ok. Would you suggest to use a extra field to hold the table name as default? Why do you need to know which table it's in? What are you going to use it for in the next step? I see you're passing it in: echo a href=\index.php?table=$table_nrpage/a; but what are you going to do with it? I think if you explain what you're trying to achieve as an end result, people might be able to give you better options. Basically php can't give you extra information than the query itself does.. whatever the query does in mysql, is exactly what it will do when run through php. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Decide witch table within a union
Definitely not. Review your design. If you need to know which table data comes from, then perform table specific queries. If you need to combine data from more than one table, code your application to respond accordingly. You may also need to review your database schema design in order that it best meet your needs. On Oct 29, 2006, at 2:00 PM, Børge Holen wrote: Would you suggest to use a extra field to hold the table name as default? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Decide witch table within a union
On Oct 29, 2006, at 2:00 PM, Børge Holen wrote: Would you suggest to use a extra field to hold the table name as default? At 10/29/2006 04:16 PM, Ed Lazor wrote: Definitely not. Review your design. If you need to know which table data comes from, then perform table specific queries. If you need to combine data from more than one table, code your application to respond accordingly. You may also need to review your database schema design in order that it best meet your needs. That seems unreasonably harsh. What in your view is wrong with a union query that preserves an indicator of which component table a particular record comes from? I can easily imagine a circumstance in which this could be valuable, say the union of several mailing lists that are in separate tables simply because they originate from different sources. We union them to print a single alphabetical list, for example, but we want an indicator as to the source of each record. I can imagine modifying the OP's query to read: $sql= (select '1' as tableId, * from table_1 WHERE pid='0' order by id desc limit 10) union (select '2' as tableId, * from table_2 WHERE pid='0' order by id desc limit 10) union (select '3' as tableId, * from table_3 WHERE pid='0' order by id desc limit 10) union (select '4' as tableId, * from table_4 WHERE pid='0' order by id desc limit 10) order by date desc limit 10 ; Would this be so egregious? and if so why? You say, If you need to combine data from more than one table, code your application to respond accordingly. What does this mean, exactly? Surely you're not suggesting that we code an application to somehow divine the source of a record in a union query when the query itself could simply supply that datum easily. Curiously, Paul -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Decide witch table within a union
That seems unreasonably harsh. I can see what you mean, but don't take it that way. I was trying to help. What in your view is wrong with a union query that preserves an indicator of which component table a particular record comes from? Read earlier in the thread. He's talking about adding a field to the table and that the value of this field in every single record will be the name of the table the record belongs to. I said I would definitely not recommend doing that. I can easily imagine a circumstance in which this could be valuable, say the union of several mailing lists that are in separate tables simply because they originate from different sources. We union them to print a single alphabetical list, for example, but we want an indicator as to the source of each record. I can imagine modifying the OP's query to read: $sql= (select '1' as tableId, * from table_1 WHERE pid='0' order by id desc limit 10) union (select '2' as tableId, * from table_2 WHERE pid='0' order by id desc limit 10) union (select '3' as tableId, * from table_3 WHERE pid='0' order by id desc limit 10) union (select '4' as tableId, * from table_4 WHERE pid='0' order by id desc limit 10) order by date desc limit 10 ; Would this be so egregious? and if so why? I think this is a great solution, wish I'd thought of it. =) You say, If you need to combine data from more than one table, code your application to respond accordingly. What does this mean, exactly? I'm just talking about intelligent programming. Surely you're not suggesting that we code an application to somehow divine the source of a record in a union query when the query itself could simply supply that datum easily. Of course not. Honestly, I think you're just being critical of what I said, because you thought I was being harsh and unfair when I wasn't actually trying to be. -Ed -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Decide witch table within a union
On Sun, October 29, 2006 3:34 pm, Børge Holen wrote: Hi. I got this working (almost) How do I decide (inside?) the whileloop the table_nr, 1 to 4 witch the link is echo'ed from. I could add another field in each table telling witch table it is. Easy solution, but feels like polluting the db. Also I could do another loop to check another of the printed fields up agains witch table is used, but that adding to the slowness of this union stuff, it would take forever, if the union in itself is slow or the fact that I'm cutting the message field after 100 characters I dunno. This is anyway the main code wherein the problem lies: $sql=(select * from table_1 WHERE pid='0' order by id desc limit 10) select *, 'table_1' from table_1 ... union (select * from table_2 WHERE pid='0' order by id desc limit 10) select *, 'table_2' from table_2 ... This gives you an extra field with the table name in it. You may not be able to use * any more, and have to name all the fields you actually want. Which is a better practice anyway. -- Some people have a gift link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Decide witch table within a union
At 10/29/2006 08:04 PM, Ed Lazor wrote: That seems unreasonably harsh. I can see what you mean, but don't take it that way. I was trying to help. Sorry, Ed, I had intended my reply to be friendly as well. I'm allergic to smiley-face icons, but I should have tried harder to convey my tone. Dang this poker-faced email! What in your view is wrong with a union query that preserves an indicator of which component table a particular record comes from? Read earlier in the thread. He's talking about adding a field to the table and that the value of this field in every single record will be the name of the table the record belongs to. I said I would definitely not recommend doing that. Me neither. You're right. I was only referencing his next email in which he said, Would you suggest to use a extra field to hold the table name as default? which I thought was a great idea, but only because I thought he meant the query. Warm regards, Paul -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Decide witch table within a union
Sorry, Ed, I had intended my reply to be friendly as well. I'm allergic to smiley-face icons, but I should have tried harder to convey my tone. Dang this poker-faced email! No worries. It's all good :) -Ed -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
