join problem

stefke

Ok, have two tables
fieldnames are between () and in Dutch

one with short courses (kort)
one with long courses (lang05-06)

both tables have the same field called (trefwoorden)

I want to make a search form people can query that field trefwoorden in the two tables

Can't get it to work, what the hell is wrong with the following sql statement?

$sql = "SELECT count(*) FROM kort LEFT JOIN lang0506 USING (trefwoorden) WHERE kort.trefwoorden LIKE '%$trefwoorden%' OR lang0506.trefwoorden LIKE '%$trefwoorden%'";
Any help will be greatly appreciated

paulnaj

Hi Stefke,

I think it's just that you are missing a 'GROUP BY' clause to match the 'aggregate' function COUNT() ...

Also, BTW, I don't think you need the 'OR' clause, so ...

<?php
$sql = "
	SELECT count(*) 
	FROM kort 
	LEFT JOIN lang0506 USING (trefwoorden) 
	WHERE kort.trefwoorden LIKE '%".$trefwoorden."%'
	GROUP BY kort.trefwoorden
";
?>

... should do it!

Paul 🙂

stefke

tnx for the help
but it does not work

somebody else told me a join is not a good option, I should use UNION between two sql statements

never heard of thet, going to look for it on Google and give it a try

but any other help is still welcome, I need this resolved by monday so I can go on with some other scripts

paulnaj

Hi again,

When you say it does not work, what do you mean?

Do you get an error? If so, what does it say?

Does it return any results? If so, what's wrong with them?

Is [kort.trefwoorden] a numeric field or a string field?

Using a UNION suggests that there is NO relationship between the tables, and you look at each table separately.

UNION merely concatenates two result sets with the same fields into one resultset If they are related, then you need to JOIN them together (there is an alternative just using WHERE statements, but it is just equivalent to using JOIN).

Paul.

stefke

I tried union and now I get

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /wh36542/public_html/trefwoord.php on line 265

Here's the complete php code

<?php

mysql_connect ('localhost', 'xxxx', 'x');
mysql_select_db ('db');

$result=mysql_query("SELECT id, titel, trefwoorden FROM kort WHERE trefwoorden LIKE '%$trefwoorden%' UNION ALL SELECT id, titel, trefwoorden FROM lang0506 WHERE trefwoorden LIKE '%$trefwoorden%'");

while ($row = mysql_fetch_array($result)) {

echo "<a href=\"http://www.syntra-ab.be/detailkort.php?id=$row[id]\">");
echo $row["titel"];
echo "</a>";
}

stefke

Originally posted by paulnaj
Hi again,

When you say it does not work, what do you mean?

Do you get an error? If so, what does it say?

Does it return any results? If so, what's wrong with them?

Is [kort.trefwoorden] a numeric field or a string field?

Using a UNION suggests that there is NO relationship between the tables, and you look at each table separately.

UNION merely concatenates two result sets with the same fields into one resultset If they are related, then you need to JOIN them together (there is an alternative just using WHERE statements, but it is just equivalent to using JOIN).

Paul.

kort.trefwoorden is a set of search words as is lang0506.trefwoorden, it's a string of 5 words separated by comma's

both tables are NOT related

with your code, I simply get nothing

I just want one search box on the homepage that queries the two tables on "trefwoorden"

shouldn't be this hard I thought :-)

on a single table it works fine for both tables, but when I want to bring them together, it simply does not work

paulnaj

Ok, understood,

First of all, make sure your version of MySQL supports UNION, check that out in the manual.

Not sure about the 'ALL' ... try ...

$result=mysql_query("
SELECT id, titel, trefwoorden
FROM kort
WHERE trefwoorden
LIKE '%$trefwoorden%'

UNION

SELECT id, titel, trefwoorden
FROM lang0506
WHERE trefwoorden
LIKE '%$trefwoorden%'
");

Paul.

stefke

union or union all, both not working
still the same error

I opened a ticket to check my sql version supports union

I'm beginning to suspect it does not, but can't check it myself as we are on a shared server.

Thanks for all your help though and if anyone can resolve this without union, be my guest ....

Stefan

stefke

the answer is just in

it's not supported

so back to joins, any suggestions?

mrhappiness

if it's only COUNT, why don't you query those 2 tables separatly?

stefke

Originally posted by mrhappiness
if it's only COUNT, why don't you query those 2 tables separatly?

euh, yeah why not?
I don't know, is that possible? I only made scripts with ony sql query so far.

How do you get the results in one table than?

Any snippet of code or good tutorial I can get started with?

mrhappiness

$sql = "SELECT count(*) 
FROM kort 
WHERE trefwoorden LIKE  '%$trefwoorden%'";
$r = mysql_query($sql);
$x = mysql_fetch_row($r);
$count_kort = $x[0];

$sql = "SELECT count(*) 
FROM lang0506 
WHERE trefwoorden LIKE  '%$trefwoorden%'";
$r = mysql_query($sql);
$x = mysql_fetch_row($r);
$count_lang0506 = $x[0];

like this?