SitePoint Sponsor

User Tag List

Results 1 to 24 of 24
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query 1 table with values of another table

    Hello,

    I have 2 tables in 1 database. The table names are developer and developer_log. I want to use the keywords in developer_log to query developer. Then I want to display the results of this query.

    So here's how it should work:-
    developer_log.company should query developer.name
    developer_log.project should query developer.development
    developer_log.property should query developer.type

    Screen shots of my tables are attached.

    My half written code is below. I don't know how to modify it because I'm a newbie.

    Thank you very much for your help.
    PHP Code:
    <?php 
    $username
    ="abc123"
    $password="abc123"
    $database="abc123"
    $host="localhost"

    mysql_connect ("$host","$username","$password"); 
    mysql_select_db($database) or die( "Where's the database man?"); 

    $mktime date('Y-m-d'); 

    $query1=("SELECT * FROM THIS IS WHERE I NEED HELP, I GUESS"); 

    $result1=mysql_query($query1); 
    $num=mysql_num_rows($result1); 

    while (
    $row1 mysql_fetch_array($result1)) 

    echo
    "<b>Year Approved: </b> ".$row1['year']. 
    "<p><b>Name of Developer: </b> ".$row1['name']. 
    "<p><b>Address: </b> ".$row1['development']. 
    "<p><b>Type of Property: </b> ".$row1['type']. 
    "<p><b>Levels: </b> ".$row1['levels']. 
    "<p><b>Number of Units: </b> ".$row1['quantity']. 
    "<p>" 


    ?>
    Attached Files Attached Files

  2. #2
    SitePoint Enthusiast
    Join Date
    Sep 2005
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Could you post the DDL for your tables. (i.e. the SQL you need to create the tables.)

    It seems you only need an INNER JOIN between the tables.

    P.S: The uploaded zip file has not been approved yet. But is you simply copy and paste the DDL and post it here we will be able to help.
    www.SQLrecipes.com A free cookbook for SQL recipes.

    I didn't believe someone could make over $19,000 a month...
    ...with Google Adsense, until I read this.

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your reply wwms.
    Could you post the DDL for your tables
    What's a DDL?
    the SQL you need to create the tables
    The tables were created in PHPMyAdmin.
    It seems you only need an INNER JOIN between the tables
    I don't know how to do this. Your guidance is much appreciated.
    But is you simply copy and paste the DDL
    My apologies, I don't know what a DDL is.

  4. #4
    SitePoint Enthusiast
    Join Date
    Sep 2005
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In phpMyAdmin click onthe export tab.
    Then select the appropriate tables. You want to export the structure only so deselect the "data" checkbox. Click "go" and you should see the CREATE statement for the tables.
    Paste the info here.
    www.SQLrecipes.com A free cookbook for SQL recipes.

    I didn't believe someone could make over $19,000 a month...
    ...with Google Adsense, until I read this.

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the tutorial wwms. I think this is what you are looking for:-
    Code:
    -- phpMyAdmin SQL Dump
    -- version 2.8.0.2
    -- http://www.phpmyadmin.net
    -- 
    -- Host: localhost
    -- Generation Time: May 27, 2006 at 12:34 AM
    -- Server version: 4.1.10
    -- PHP Version: 4.4.2
    -- 
    -- Database: `transfield_property`
    -- 
    
    -- --------------------------------------------------------
    
    -- 
    -- Table structure for table `developer`
    -- 
    -- Creation: May 26, 2006 at 06:20 PM
    -- Last update: May 26, 2006 at 06:20 PM
    -- 
    
    DROP TABLE IF EXISTS `developer`;
    CREATE TABLE IF NOT EXISTS `developer` (
      `id` int(6) NOT NULL auto_increment,
      `date_updated` date NOT NULL default '0000-00-00',
      `year` year(4) NOT NULL default '0000',
      `name` varchar(255) NOT NULL default '',
      `development` varchar(255) NOT NULL default '',
      `type` varchar(50) NOT NULL default '',
      `levels` varchar(10) NOT NULL default '',
      `quantity` varchar(5) NOT NULL default '',
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=18437 ;
    
    -- --------------------------------------------------------
    
    -- 
    -- Table structure for table `developer_log`
    -- 
    -- Creation: May 25, 2006 at 12:54 AM
    -- Last update: May 25, 2006 at 09:56 PM
    -- 
    
    DROP TABLE IF EXISTS `developer_log`;
    CREATE TABLE IF NOT EXISTS `developer_log` (
      `id` int(6) NOT NULL auto_increment,
      `company` varchar(255) NOT NULL default '',
      `project` varchar(255) NOT NULL default '',
      `property` varchar(50) NOT NULL default '',
      `email` varchar(40) NOT NULL default '',
      `cc_email` varchar(40) NOT NULL default '',
      `date` datetime NOT NULL default '0000-00-00 00:00:00',
      `expiry` date NOT NULL default '0000-00-00',
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

  6. #6
    SitePoint Enthusiast
    Join Date
    Sep 2005
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, that is all we need.
    It looks like there is no relationship between the two tables though.
    I would have expected the 'developer_log' table to have a column named 'developer_id'.

    Is there a field that will always be the same between both tables?

    Could you please provide more details as to what you want to achieve...
    www.SQLrecipes.com A free cookbook for SQL recipes.

    I didn't believe someone could make over $19,000 a month...
    ...with Google Adsense, until I read this.

  7. #7
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for taking the time to look at all my stuff, wwms. Much appreciated.
    It looks like there is no relationship between the two tables though.
    You are right.
    Is there a field that will always be the same between both tables?
    No.
    Could you please provide more details as to what you want to achieve
    Okay. I plan to create a system where users can be notified automatically via email if any of their search criteria(which is stored in developer_log) matches any record in developer. developer will be updated daily by me. developer_log will be updated via a html form by the end users as and when they visit my site. The code I posted above will be activated by cron job daily to check whether there are any new records in developer that match the keywords in developer_log.

    I trust I'm making sense here?

    Thank you once again.

  8. #8
    SitePoint Enthusiast
    Join Date
    Sep 2005
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK it is starting to make more sense :-)

    Which field will be used to store the search criteria in the developer_log table?

    I still don't understand this though:

    So here's how it should work:-
    developer_log.company should query developer.name
    developer_log.project should query developer.development
    developer_log.property should query developer.type
    www.SQLrecipes.com A free cookbook for SQL recipes.

    I didn't believe someone could make over $19,000 a month...
    ...with Google Adsense, until I read this.

  9. #9
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi wwms,
    Which field will be used to store the search criteria in the developer_log table?
    It could be any one or all of these fields:-
    developer_log.company
    developer_log.project
    developer_log.property
    I still don't understand this though
    Ok. Here's a crude SELECT statement I wrote which I know will not work. Nevertheless, it should give you an idea about what I'm trying to achieve.
    PHP Code:
    $query1=("SELECT * FROM table WHERE '%developer_log.company%' Like developer.name or '%developer_log.project%' Like developer.development or '%developer_log.property%' Like developer.type"); 
    I hope it makes more sense now.

  10. #10
    SitePoint Enthusiast
    Join Date
    Sep 2005
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You first have to query the develper_log table. Possibly SELECT * FROM developer_log.

    Then loop through all the rows and get the data you want.

    Within the loop you will then do something like:

    PHP Code:
     $query1=("SELECT * FROM table 
    WHERE developer.name LIKE '%
    {$row[company]}%'
    OR developer.development LIKE '%
    {$row[project]}%' 
    OR developer.type LIKE '%
    {$row[property]}%' 
    "
    ); 
    www.SQLrecipes.com A free cookbook for SQL recipes.

    I didn't believe someone could make over $19,000 a month...
    ...with Google Adsense, until I read this.

  11. #11
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your reply, wwms.
    You first have to query the develper_log table. Possibly SELECT * FROM developer_log.
    Okay, I understand this part.
    Then loop through all the rows and get the data you want.
    Okay, I understand this part.
    Within the loop you will then do something like:
    This doesn't seem to make any difference. My full code is below. What am I doing wrong?
    PHP Code:
    $query1=("SELECT * FROM developer_log");

    $result1=mysql_query($query1);
    $num=mysql_num_rows($result1);

    while (
    $row1 mysql_fetch_array($result1))
    {
    echo
    "<b>Year Approved: </b> ".$row1['company'].
    "<p><b>Name of Developer: </b> ".$row1['project'].
    "<p><b>Address: </b> ".$row1['property'].
    "<p>"
    ;
    $query2=("SELECT * FROM developer WHERE developer.name LIKE '%{$row1[company]}%' OR developer.development LIKE '%{$row1[project]}%' OR developer.type LIKE '%{$row1[property]}%'");
    $result2=mysql_query($query2);


  12. #12
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Running a separate query for every row in developer_log is so inefficient as to likely cripple your application once it is used by more than a handful of people at a time. I suggest you reconsider and consolidate this into a single query.

    If you're unsure how to do this, it will be worth the time to learn.

  13. #13
    SitePoint Enthusiast
    Join Date
    May 2006
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You got some good answers on the Code Newbie forums too http://codenewbie.com/forum/php/4195...ntax-help.html

  14. #14
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you're unsure how to do this, it will be worth the time to learn
    So how do I learn?
    You got some good answers on the Code Newbie forums too http://codenewbie.com/forum/php/419...yntax-help.html
    I didn't understand the explanation there. Furthermore, a JOIN was proposed. I don't think a JOIN will work because there is nothing in common between the both tables.

  15. #15
    SitePoint Enthusiast
    Join Date
    Sep 2005
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try

    PHP Code:
    $query2"SELECT * FROM developer WHERE name LIKE '%{$row1['company']}%' OR development LIKE '%{$row1['project']}%' OR type LIKE '%{$row1['property']}%'";

    echo 
    $query2
    Notice that you should echo the query. If it does not work, copy the query as it was echoed to your browser into the phpMyAdmin SQL tab. Run the query again. PhpMyAdmin will return an error code with some information. Post if back here.

    Note: Dan brings up a good point. Even though you cannot use a JOIN in your case [EDIT: You can, see the answer a few posts below], using the LIKE search with wildcards on both ends (%) will cripple your query when your table grows to a few thousand rows, because MySQL will have to scan through every row and every word (i.e. It can't use the indeces). You will want to do full text search.... but get this working first then read up on full text search.

    Dan, I think he has to loop through each row, I can't think of another way around it (without knowing more about the problem.). However this will be a cron task so it can be scheduled for low activity periods.
    Last edited by wwms; May 27, 2006 at 13:08.
    www.SQLrecipes.com A free cookbook for SQL recipes.

    I didn't believe someone could make over $19,000 a month...
    ...with Google Adsense, until I read this.

  16. #16
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your patience wwms. I ran the echoed query on PHPMyAdmin's SQL & the results were exactly what I'm looking for. It looks like the query is working fine.

    However, when the code is run on my webpage, it is still querying developer_log & displaying the results of developer_log. It's supposed to query developer & display the results of developer. Perhaps the position of $query2 is not suitable?

    The query results from my webpage is displayed below:-
    HTML Code:
    Year Approved: PAKADIRI
    
    Name of Developer: Name of Project
    
    Address: Type of Property
    
    SELECT * FROM developer WHERE name LIKE '%PAKADIRI%' OR development LIKE '%Name of Project%' OR type LIKE '%Type of Property%'
    
    Year Approved: Name of Company
    
    Name of Developer: Name of Project
    
    Address: Type of Property
    
    SELECT * FROM developer WHERE name LIKE '%Name of Company%' OR development LIKE '%Name of Project%' OR type LIKE '%Type of Property%'
    
    Year Approved: Name of Company
    
    Name of Developer: Name of Project
    
    Address: Type of Property
    
    SELECT * FROM developer WHERE name LIKE '%Name of Company%' OR development LIKE '%Name of Project%' OR type LIKE '%Type of Property%'

  17. #17
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello wwms,
    Thanks a lot for your patience & guidance. I figured out the final part in a crude manner & here's my code:-
    PHP Code:
    $query1=("SELECT * FROM developer_log"); 

    $result1=mysql_query($query1); 
    $num=mysql_num_rows($result1); 

    while (
    $row1 mysql_fetch_array($result1)) 

    "<b></b> ".$row1['company']. 
    "<p><b></b> ".$row1['project']. 
    "<p><b></b> ".$row1['property']. 
    "<p>"


    $query2"SELECT * FROM developer WHERE name LIKE '%{$row1['company']}%' OR development LIKE '%{$row1['project']}%' OR type LIKE '%{$row1['property']}%'"
    $result2=mysql_query($query2);

        while (
    $row2 mysql_fetch_array($result2)) 
        { 
        echo
    "<b>Name of Developer: </b> ".$row2['name']. 
        
    "<p><b>Name of Project: </b> ".$row2['development']. 
        
    "<p><b>Type of Property: </b> ".$row2['type'].
        
    "<p><b>Levels: </b> ".$row2['levels'].
        
    "<p><b>Number of Units: </b> ".$row2['quantity']. 
        
    "<hr>" 
        

        }

    This is how the final product looks like:-
    HTML Code:
    Name of Developer: PAKADIRI DEVELOPER SDN.BHD. 470-B, TINGKAT 2 JALAN KAMARUDDIN 20400 KUALA TERENGGANU TERENGGANU
    Name of Project: TAMAN KALUNGAN INTAN 3385 hingga lot 3404 DUNGUN TERENGGANU 6926/10-1999/749
    Type of Property: RUMAH SEBUAH(DETACHED HOUSE)
    Levels: 1
    Number of Units: 1 
    
    --------------------------------------------------------------------------
    Name of Developer: PAKADIRI DEVELOPER SDN.BHD. 470-B, TINGKAT 2 JALAN KAMARUDDIN 20400 KUALA TERENGGANU TERENGGANU
    Name of Project: TAMAN KALUNGAN IMPIAN PT 25895 - PT 25918, HS(M) 16701 - 16724 KUALA TERENGGANU TERENGGANU 6926-8/01-2008/4
    Type of Property: RUMAH BERKEMBAR(SEMI DETACHED HOUSE)
    Levels: 2
    Number of Units: 24 
    
    --------------------------------------------------------------------------
    Name of Developer: PAKADIRI DEVELOPER SDN.BHD. 470-B, TINGKAT 2 JALAN KAMARUDDIN 20400 KUALA TERENGGANU TERENGGANU
    Name of Project: TAMAN KALUNGAN INTAN 3385 hingga lot 3404 DUNGUN TERENGGANU 6926/10-1999/749
    Type of Property: RUMAH TERES/KELOMPOK(TERRACE/CLUSTER HOUSE)
    Levels: 2
    Number of Units: 19 
    
    --------------------------------------------------------------------------
    Name of Developer: PAKADIRI DEVELOPER SDN.BHD. 470-B, TINGKAT 2 JALAN KAMARUDDIN 20400 KUALA TERENGGANU TERENGGANU
    Name of Project: TAMAN KALUNGAN MAWAR PECAHAN LOT 6940 (PT 15717P - 15740P) KUALA TERENGGANU TERENGGANU 6926/05-2004/516
    Type of Property: RUMAH TERES/KELOMPOK(TERRACE/CLUSTER HOUSE)
    Levels: 1
    Number of Units: 24 
    
    --------------------------------------------------------------------------
    Name of Developer: PAKADIRI DEVELOPER SDN.BHD. 470-B, TINGKAT 2 JALAN KAMARUDDIN 20400 KUALA TERENGGANU TERENGGANU
    Name of Project: TAMAN KALUNGAN MELOR PT 9390 - PT 9413 MARANG TERENGGANU 6926-5/01-2005/80
    Type of Property: RUMAH TERES/KELOMPOK(TERRACE/CLUSTER HOUSE)
    Levels: 2
    Number of Units: 24 
    
    --------------------------------------------------------------------------
    Name of Developer: PAKADIRI DEVELOPER SDN.BHD. 470-B, TINGKAT 2 JALAN KAMARUDDIN 20400 KUALA TERENGGANU TERENGGANU
    Name of Project: TAMAN KALUNGAN MELOR FASA 2 PT 9571 - 9595 MARANG TERENGGANU 6926-6/04-2006/371
    Type of Property: RUMAH TERES/KELOMPOK(TERRACE/CLUSTER HOUSE)
    Levels: 2
    Number of Units: 25 
    
    --------------------------------------------------------------------------
    Name of Developer: PAKADIRI DEVELOPER SDN.BHD. 470-B, TINGKAT 2 JALAN KAMARUDDIN 20400 KUALA TERENGGANU TERENGGANU
    Name of Project: TAMAN KALUNGAN MELOR - FASA 3 PT 10040 - PT 10066 MARANG TERENGGANU 6926-7/03-2007/324
    Type of Property: RUMAH TERES/KELOMPOK(TERRACE/CLUSTER HOUSE)
    Levels: 2
    Number of Units: 27 
    
    --------------------------------------------------------------------------
    Name of Developer: PAKADIRI DEVELOPER SDN.BHD. 470-B, TINGKAT 2 JALAN KAMARUDDIN 20400 KUALA TERENGGANU TERENGGANU
    Name of Project: TAMAN KALUNGAN MURNI 51 KUALA TERENGGANU TERENGGANU 6926/10-1999/748
    Type of Property: RUMAH TERES/KELOMPOK(TERRACE/CLUSTER HOUSE)
    Levels: 2
    Number of Units: 63 
    
    --------------------------------------------------------------------------
    I appreciate your guidance once again :-)

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    running a query inside a loop is pretty inefficient -- use a join instead
    Code:
    select DL.company
         , DL.project
         , DL.property
         , D.name
         , D.development
         , D.type
         , D.levels
         , D.quantity
      from developer_log as DL
    left outer
      join developer as D
        on D.name LIKE concat('%',DL.company,'%')
        or D.development LIKE concat('%',DL.project,'%')
        or D.type LIKE concat('%',DL.property,'%')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot r937 for your advice. It works fine but it gives me 2 blank records at the end of my query. Why?

    My code:
    PHP Code:
    $query1=("select DL.company, DL.project, DL.property, D.name, D.development, D.type, D.levels, D.quantity from developer_log as DL left outer join developer as D on D.name LIKE concat('%',DL.company,'%') or D.development LIKE concat('%',DL.project,'%') or D.type LIKE concat('%',DL.property,'%')"); 

    $result1=mysql_query($query1); 
    $num=mysql_num_rows($result1); 

    while (
    $row1 mysql_fetch_array($result1)) 
        { 
        echo
    "<b>Name of Developer: </b> ".$row1['name']. 
        
    "<p><b>Name of Project: </b> ".$row1['development']. 
        
    "<p><b>Type of Property: </b> ".$row1['type'].
        
    "<p><b>Levels: </b> ".$row1['levels'].
        
    "<p><b>Number of Units: </b> ".$row1['quantity']. 
        
    "<hr>" 
        

        } 
    The query results(note the last 2 records are blank):
    HTML Code:
    Name of Developer: PAKADIRI DEVELOPER SDN.BHD. 470-B, TINGKAT 2 JALAN KAMARUDDIN 20400 KUALA TERENGGANU TERENGGANU
    Name of Project: TAMAN KALUNGAN MURNI 51 KUALA TERENGGANU TERENGGANU 6926/10-1999/748
    Type of Property: RUMAH TERES/KELOMPOK(TERRACE/CLUSTER HOUSE)
    Levels: 2
    Number of Units: 63 
    --------------------------------------------------------------------------
    Name of Developer: 
    Name of Project: 
    Type of Property: 
    Levels: 
    Number of Units: 
    --------------------------------------------------------------------------
    Name of Developer: 
    Name of Project: 
    Type of Property: 
    Levels: 
    Number of Units:

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    run your query in a front-end app like phpmyadmin and see if the blank rows are still there

    if so, then you have blank rows in your data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your reply r937. I ran the query in PHPMyAdmin & yes, there were 2 blank rows. However, these blank rows come from developer_log & not developer. The select statement is supposed to display the results of developer only. I suspect that the JOIN caused this blank rows to appear. Are there any workarounds you can think of?

    I can see that your code is more efficient. Just some minor tweaking is necessary.

    The screen shot of my PHPMyAdmin is attached.

    Thanks a lot.
    Attached Images Attached Images

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, sorry, the reason you see those "blank" rows is because of the LEFT OUTER JOIN

    let me explain -- your original query (post #17) obtains rows from developer_log, then loops through all the related rows from developer

    so i assumed (incorrectly) that you wanted all rows from developer_log, whether they had developer rows associated with them or not

    thus the LEFT OUTER JOIN

    change it to INNER JOIN and the "blank" rows problem goes away
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot for the tutorial, r937. Yes, it works beautifully well now. I truly appreciate your guidance & it looks like I've got to do some reading on joins to understand what's going on :-)

    Have a good day :-)

  24. #24
    SitePoint Enthusiast
    Join Date
    Sep 2005
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    running a query inside a loop is pretty inefficient -- use a join instead
    Code:
    select DL.company
         , DL.project
         , DL.property
         , D.name
         , D.development
         , D.type
         , D.levels
         , D.quantity
      from developer_log as DL
    left outer
      join developer as D
        on D.name LIKE concat('%',DL.company,'%')
        or D.development LIKE concat('%',DL.project,'%')
        or D.type LIKE concat('%',DL.property,'%')

    Nice. Thanks r937, I never thought of using a join like that. I will post a summary of this on my website :-)
    www.SQLrecipes.com A free cookbook for SQL recipes.

    I didn't believe someone could make over $19,000 a month...
    ...with Google Adsense, until I read this.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •