Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: PHP MySQL
Hi im trying to make a query thats gonna go into 4 tables i have in my mysql data base and it will do a drop down list, wich is a search box, then people can click on a name and it will open the document they searched for.
 
heres the problem, im capable of doing this with only 1 table, as soon i try to include another table it doesnt work...
 
i searched the web and i saw some join query but those doesnt work... heres my code for 1 table wich is working.
 
this is a file that is called from a html page :(livesearch.mysql.php)
 
<?php                   
  	//Get the q parameter from URL                          
	$q=$_GET["q"];
	$hint="";
 
	$link = mysql_connect('*server*', '*username*', '*pswd*');
	if (!$link)
	{
		die('Could not connect: '.mysql_error());
	}
	
	mysql_select_db("*database*");
	
	$query='SELECT * FROM procedures WHERE Nom LIKE "%'.$q.'%"'; 
	
	$result = mysql_query($query);
	$row_count=mysql_num_rows($result);
 
	while ($row = mysql_fetch_array($result))
	{
		$appName = $row['Nom'];
		$appLink = $row['Lien_Nom'];
		
		if ($hint=="")
		{
			$hint="<a href="".$appLink."" target="_blank">".$appName."</a>";
		}
		else
		{
			$hint=$hint."
<a href="".$appLink."" target="_blank">".$appName."</a>";
		}
	}
	
	if ($hint=="")
	{
		$response="no suggestions...";
	}
	else
	{
		$response=$hint;
	}
	                                                                                                                     
	echo $response;   
?>
Posted 2-Nov-11 4:10am
Comments
Bala Selvanayagam at 2-Nov-11 11:46am
   
You have mentioned that when you do the join operation the query does not work / does not bring any results ?
 
If so, where it the query combining four tables ? and what do you mean by does not work ? any error messages / it does not bring any data ?
Sebastien Geoffrion at 2-Nov-11 13:33pm
   
hi bala, ya when i start my search theres simply no information comming out, it gives the error message from my verification
 
CODE:
 
if ($hint=="")
{
$response="no suggestions...";
}
else
{
$response=$hint;
}


the code that i tried was

CODE:
 
SELECT * FROM procedures, applications WHERE procedures.Nom = applications.Nom


maybe its me who made a mistake in my command or dint make my tables correctly...

but what i want is to be able to make a list with all the filds(Nom) from multiple tables
Bala Selvanayagam at 2-Nov-11 13:44pm
   
What happens when you directly run
 
SELECT * FROM procedures, applications WHERE procedures.Nom = applications.Nom
 
on the mySQL ? also what is the mySQL version you are using ?
Sebastien Geoffrion at 7-Nov-11 7:45am
   
sry for the late response was on a project, so i tested it on the mysql server and it dint return anything, so the command isnt good.
 
my Mysql version is Version du serveur: 5.5.15
 
Bala Selvanayagam at 7-Nov-11 8:03am
   
most probably there is no matching records in both tables for your join.
 
look at the link http://www.tizag.com/mysqlTutorial/mysqljoins.php which might give you an idea where you are going wrong in your query
Sebastien Geoffrion at 7-Nov-11 12:14pm
   
thx but ive already looked that up,
i kinda found my problem but still dont know how to make it work.
 
for you to make a union or join query you need to have a relation between the tables you want to query, where the command :procedures.Nom = applications.Nom
from : SELECT * FROM procedures, applications WHERE procedures.Nom = applications.Nom
 
wich means that the field (nom) from the tables procedres and applications needs to be exactly the same wich in my case is not true, because the sql command bring out ever line were procedures.Nom and applications.nom is the same...
 
in my case i really cant make any relations between my tables since every table is a different subject and have theyre own names and links and other fields...
 
ive tried making a field (column) that is called search and gave it a value of 1, and then make the search but it gives me my data in triple or even more, since in the 2 table i dont have the same quantity of entries....
 
if anyone knows how to make a query from 2 or more table without them having any relation between them that would be great...
 

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Ive finally found out how to do it, its actually a UNION query and not a Join, since join requires a fields with the exact same data, wich a union query will fuse the data together even tho theres no data resemblance in none of youre tables.
 
this is the link where i found my info:
 
- http://www.w3schools.com/Sql/sql_union.asp
 

here my code for people who would be looking how to do this too.
 
-so this code will search my mysql database for a document name and the link for that document.
-and will make a search box where you only need to input a single letter and it will give you a list of all entries from the database where the letter youve inputed in found.
-then it will make a list where you see the name of the documents, ad if you click on the name it will open the document.
 
<?php                   
  	//Get the q parameter from URL                          
	$q=$_GET["q"];
	$hint="";
 
	$link = mysql_connect('pcmu-seb99', 'Support5000', 'bob');
	if (!$link)
	{
		die('Could not connect: '.mysql_error());
	}
	
	mysql_select_db("support5000");
	
	$query='SELECT Nom, Lien_Nom FROM procedures WHERE Nom LIKE "%'.$q.'%"
	UNION ALL
	SELECT Nom, Lien_Nom FROM applications WHERE Nom LIKE "%'.$q.'%"
	UNION ALL
	SELECT Nom, Lien_Nom FROM faqs WHERE Nom LIKE "%'.$q.'%"
	UNION ALL
	SELECT Nom, Lien_Nom FROM reg WHERE Nom LIKE "%'.$q.'%"';
	
	$result = mysql_query($query);
	$row_count=mysql_num_rows($result);
 
	while ($row = mysql_fetch_array($result))
	{
		$appName = $row['Nom'];
		$appLink = $row['Lien_Nom'];
		
		if ($hint=="")
		{
			$hint="<a href="".$appLink."" target="_blank">".$appName."</a>";
		}
		else
		{
			$hint=$hint."
<a href="".$appLink."" target="_blank">".$appName."</a>";
		}
	}
	// Set output to "no suggestion" if no hint were found
	// or to the correct values
	if ($hint=="")
	{
		$response="no suggestions...";
	}
	else
	{
		$response=$hint;
	}
	                                                                                                                     
	//output the response
	echo $response;   
?>
  Permalink  
Comments
Bala Selvanayagam at 7-Nov-11 13:36pm
   
great 5ed
Bala Selvanayagam at 7-Nov-11 13:58pm
   
May be use UNION not UNION ALL,
 
UNION ALL result set will have duplicate values, if you have the same Nom,Lien_Nom in more than one table but UNION will bring only the distinct
Sebastien Geoffrion at 7-Nov-11 14:57pm
   
actually i do not have the same entry in any of my tables since its deferent categories, like i have 1 for documentation, 1 for application, one for registry keys and 1 for faqs.
 
so for me i union or union all doesnt make a big difference and since its a dynamique search box i dont mind having a couple of name that almost look alike.
 
but thx alot for the info it might help someone

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web01 | 2.8.141022.2 | Last Updated 7 Nov 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100