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 :: selecting results that doesn't match
By: roe1and Great Britain  Date: 04/11/2007 13:21:45  English  Points: 20 Status: Answered
Quality : Excellent
I am compiling a list of information that comes from 2 different tables in a mysql database using php. most of the information (name, address(line 1 and 2), telephone no, etc.) comes from Table 1. Table 2 contains the name, first line of the address(add_l1) and an id. I have to use the information in Table 1 to get the id from Table 2. That is easy my sql statement look like so: "SELECT id FROM marc_db1.tbl_nid WHERE name = '{$prac_nam}' AND add_l1 = '{$al1}'". This works. My problem is that in some cases the name in Table 1 looks like :Chadderton Health Centre and in Table 2 it looks like :Chadderton Health Cent, and then I get errors. Please help.
By: roe1and Date: 04/11/2007 14:53:27 English  Type : Comment
if i have the name as '$nam = Chadderton Health Centre' can i shorten it to 22 characters?
By: VGR Date: 04/11/2007 23:35:42 English  Type : Answer
ok. you may :
1) ALTER TABLE so that the columns do match
2) arraneg so that IDs in the 2 tables are synchronized (equal) so that you don't rely on the (textual) name
3) use SUBSTR(name,1,27) to get only the 27 first characters ; this is slooow because it's non indexable, so you may revert to solution (1) above, or create a "name27" column = substr(name,1,27) so that it can be indexed, and use it when querying for the id.
4) you should perhaps use a LEFT JOIN in stead of the cartesian product you anow use.

I also "smell" that you could reassemble all the data in one table only, but I may be wrong ;-)

happy coding, and come back for more explanations if need be .

regards
By: VGR Date: 04/11/2007 23:36:03 English  Type : Comment
read "22" in stead of "27" above ;-)
By: roe1and Date: 05/11/2007 09:08:40 English  Type : Comment
The SUBSTR is what I was looking for thanks. Could you perhaps explain a bit more about the LEFT JOIN, if it's not too much trouble? Thanks Again.
By: VGR Date: 05/11/2007 20:23:25 English  Type : Comment
yes. It is explained in details in the mysql online manual, and partly (gretly ;-) explains why MySql is so much more stable and faster than Oracle, for instance. Oracle knows only cartesian product using += and *= : on two 10 000 rows tables, it performs 10 000 * 10 000 combinations and examinations ; a proper database uses intelligently indices, lower-cardinality tables first, etc so that to reduce the dataset size and accelerate computation.

In your case, try to compare this :

use marc_db1; SELECT * FROM tbl_nid AS a, tbl_2 AS b WHERE b.name=a.name;



with that :

SELECT * FROM tbl_nid AS a LEFT JOIN tbl_2 AS b ON (b.name=a.name) WHERE b.name IS NOT NULL;



PS : ensure you've index the two tables on name, whatever solution you choose.
PPS : try to avoid qualifying your table names with the DB name, and use shorter aliases whenever feasible.

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 338.835000 milliseconds

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