PHP MYSQL query from multiple tables

Question

0.00/5 (No votes)

See more:

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

<?php                   <br mode="hold" /?>  	//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."<br /><a href="".$appLink."" target="_blank">".$appName."</a>";
		}
	}
	
	if ($hint=="")
	{
		$response="no suggestions...";
	}
	else
	{
		$response=$hint;
	}
	                                                                                                                     
	echo $response;   
?>

Posted 2-Nov-11 4:10am

Sebastien Geoffrion

Add a Solution

Comments

Bala Selvanayagam 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 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 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 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 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 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Sebastien Geoffrion · Accepted Answer · 2011-11-07T07:00:00

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

<?php                   <br mode="hold" /?>  	//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."<br /><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;   
?>