Query 1 table with values of another table

[transfield]

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 
$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>" 
; 
} 
?>

[wwms]

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.

[transfield]

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.

[wwms]

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.

[transfield]

Thanks for the tutorial wwms. I think this is what you are looking for:-

-- 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 ;

[wwms]

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...

[transfield]

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.

[wwms]

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

[transfield]

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.

$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.

[wwms]

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:

 $query1=("SELECT * FROM table 
WHERE developer.name LIKE '%{$row[company]}%'
OR developer.development LIKE '%{$row[project]}%' 
OR developer.type LIKE '%{$row[property]}%' 
"); 

[transfield]

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?

$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);
} 

[Dan Grossman]

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.

[avRedline]

You got some good answers on the Code Newbie forums too http://codenewbie.com/forum/php/4195...ntax-help.html

[transfield]

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.

[wwms]

Try

$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.

[transfield]

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:-

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%'

[transfield]

Hello wwms,
Thanks a lot for your patience & guidance. I figured out the final part in a crude manner & here's my 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:-

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 :-)

[r937]

running a query inside a loop is pretty inefficient -- use a join instead

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,'%')

[transfield]

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:

$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):

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:

[r937]

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

[transfield]

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.

[r937]

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

[transfield]

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 :-)

[wwms]

running a query inside a loop is pretty inefficient -- use a join instead

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 :-)