SitePoint Sponsor |
|
User Tag List
Results 1 to 24 of 24
-
May 26, 2006, 08:43 #1
- 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>"
;
}
?>
-
May 26, 2006, 08:54 #2
- 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.
-
May 26, 2006, 09:04 #3
- 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
the SQL you need to create the tables
It seems you only need an INNER JOIN between the tables
But is you simply copy and paste the DDL
-
May 26, 2006, 09:15 #4
- 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.
-
May 26, 2006, 09:38 #5
- 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 ;
-
May 26, 2006, 10:51 #6
- 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.
-
May 26, 2006, 11:18 #7
- 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.
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
I trust I'm making sense here?
Thank you once again.
-
May 26, 2006, 14:46 #8
- 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.typewww.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.
-
May 26, 2006, 15:00 #9
- 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?
developer_log.company
developer_log.project
developer_log.property
I still don't understand this though
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");
-
May 26, 2006, 15:14 #10
- 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.
-
May 26, 2006, 15:47 #11
- 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.
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 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);
}
-
May 26, 2006, 15:58 #12
- 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.Try Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more
-
May 26, 2006, 16:29 #13
- 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
-
May 26, 2006, 20:43 #14
- 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
You got some good answers on the Code Newbie forums too http://codenewbie.com/forum/php/419...yntax-help.html
-
May 26, 2006, 21:33 #15
- 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;
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.
-
May 26, 2006, 21:54 #16
- 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%'
-
May 26, 2006, 23:07 #17
- 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>"
;
}
}
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 --------------------------------------------------------------------------
-
May 27, 2006, 04:13 #18
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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,'%')
-
May 27, 2006, 06:22 #19
- 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>"
;
}
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:
-
May 27, 2006, 06:39 #20
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
-
May 27, 2006, 07:43 #21
- 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.
-
May 27, 2006, 08:03 #22
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
-
May 27, 2006, 08:12 #23
- 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 :-)
-
May 27, 2006, 13:06 #24
- Join Date
- Sep 2005
- Posts
- 44
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by r937
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