visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Languages :: PHP :: table match
By: queryguy U.S.A.  Date: 18/09/2003 00:00:00  English  Points: 80 Status: Answered
Quality : Excellent
I need some matching from my tablea ,tableb and the fields. After that fetch arrays?

2 tables and fields need to match

Table name : tablea
field: subcon

matching -----------------

Table name: tableb
field: subcon




$query="SELECT (HOW TO QUERY HERE?) FROM (HOW TO QUERY HERE?) where project='new'";
$result = mysql_query($query) ;


while($q1 = mysql_fetch_array($result) ){

echo "$q1[names]"; // can I fetch names from the 2 table?

}



Thx

qm
By: RichardAllsebrook Date: 18/09/2003 23:44:00 English  Type : Comment
If I understand you correctly, you are trying to fetch back fields from two tables with the same name?

If this is the case, you can alias your field names to give them distict names:

SELECT table1.field1 AS t1f1, table1.field2 AS t1f2, table2.field1 AS t2f1, table2.field2 AS t2f2

this will give you a result set with four fields called t1f1, t1f2 etc.

If I have got completely the wrong end of the stick, please rephrase your question.
By: VGR Date: 18/09/2003 23:47:00 English  Type : Answer
1a) select * from tablea, tableb where tablea.subcon=tableb.subcon;

or

1b) select tablea.[unique field names from tablea] , tableb.[unique field names from tableb] from tablea, tableb where tablea.subcon=tableb.subcon;


2) yes you can if they've unique names. You can use aliases ("AS x") to simplify the retrieval, or you can use this technique to see what are the firlds to get from $q1[] :

while($q1 = mysql_fetch_array($result) ){
echo '<pre>';
print_r($q1);
echo '</pre>';
}
By: queryguy Date: 18/09/2003 23:47:00 English  Type : Comment
Does this query match table1 and table2?

What operator 'AS' means?
By: sumotimor Date: 18/09/2003 23:50:00 English  Type : Assist
Richard's code looks OK, it's definitely good practice to specify exactly which fields you want from the query, instead of getting lazy and doing "SELECT * "

It does look like you need a WHERE clause in there for the join, though.  Otherwise, you'll get a result for every combination of tableA and tableB (not what you want):

SELECT tablea.subcon AS subconA, tableB.subcon AS subconB FROM tableA, tableB WHERE tableA.someField=tableB.someField

the WHERE clause is specifying the relationship between tableA and tableB.  If you want additional filtering, do something like this:

SELECT tablea.subcon AS subconA, tableB.subcon AS subconB FROM tableA, tableB WHERE tableA.someField=tableB.someField AND subconB='foo'
By: RichardAllsebrook Date: 19/09/2003 00:00:00 English  Type : Assist
just to clarify...

SELECT table1.field1 AS t1f1 ...

will rename the field table1.field1 in the result set to t1f1

Each field in your result set (the select part of your query) MUST be returned as unique name

As for the join, shmerts post is the alternative syntax for a natural or inner join and can also be written as:

SELECT tablea.subcon AS subconA, tableB.subcon AS subconB
FROM tableA
INNER JOIN tableB
ON tableA.someField=tableB.someField

(ie.
return subconA and subconB
from tableA
where the value in tableA.somefield also exists in tableB.somefield
)
By: queryguy Date: 19/09/2003 00:34:00 English  Type : Comment
I tested the simple way step first from VGR, result is ok but I can't fetch 2 names from the 2 table.field




select * from tablea, tableb where tablea.subcon=tableb.subcon and tablea.project=new;


while($q1 = mysql_fetch_array($result) ){
echo '<pre>';
print_r($q1[name]); //this only fetch the [name] from tableb, I need to fetch the [name] from tablea also, possible?
echo '</pre>';
}



RichardAllsebrook, shmert

I have problem query the code, maybe I am quite confused
By: queryguy Date: 19/09/2003 00:48:00 English  Type : Comment
VGR,

Oh I got what you mean "yes you can if they've unique names"

I changed tablea 'name' field to 'namea' and tableb 'name' field remain.


while($q1 = mysql_fetch_array($result) ){
echo "q1[namea] matched q1[name]";


}


this get the results of what I want.


Thanks for the ideal


Thanks for the other helps from shmert, RichardAllsebrook.
By: sumotimor Date: 19/09/2003 00:56:00 English  Type : Comment
You don't need to change the structure of your SQL tables, either.  That's what the "AS" SQL command is for.  It's called column aliasing, and it lets you rename a column or table on the fly for a single query.

So for this query:
SELECT tablea.subcon AS subconA, tableB.subcon AS subconB FROM tableA, tableB WHERE tableA.someField=tableB.someField AND subconB='foo'          

You're fetching 2 fields, one from tableA and one from tableB.  Even though they're both called 'subcon' in the SQL table, I've used column aliasing to rename them subconA and subconB.

Cheers,
-S
By: VGR Date: 19/09/2003 01:18:00 English  Type : Comment
yes, "AS" is the aliasing mechanism I mentioned. The drawback is that you'mll have to enumerate all fields, and the two "name" will be aliased with "AS namea" and "AS nameb" respectively

this would work.

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 505.922790 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page