Re: [PHP-DB] Joining Tables for Search
Hello Harry,
you can use left join:
http://dev.mysql.com/doc/mysql/en/JOIN.html
$query =
SELECT members.id, firstname, familyname, middlename, fathername,
region3.region3, region4, members.country, country.countryname,
MATCH(firstname, middlename, familyname, fathername) AGAINST ('$keywords' IN
BOOLEAN MODE) AS score
FROM members left join country on members.country = country.id
left join region3 on members.region3 = region3.id
WHERE MATCH(firstname, middlename, familyname, fathername) AGAINST ('$keywords'
IN BOOLEAN MODE)
ORDER BY score DESC;
good luck.
HG Hi,
HG I want to run a query on a table while joining it with other tables.
HG Table 1- members
HG id - firstname - middlename - - country - region3
HG Fields country and region3 hold the integer codes for country and region3 is state
HG Table 2- country
HG id - countryname
HG Countryname is where the actual Country name is stored and id is the country code
HG Similarly Table 3 - region3
HG id - region3 where region3 is the state name and id is the region code.
HG I perform the following query to search the names in the database but the result
only gives me the integer values for Country and Region3. That's because, the code is
only stored in the database.
HG But I need to join the table 23 with 1 so I can extract names of country or
region3 rather than integer values.
HG How would I modify the query below?
HG $keywords are the search words.
HG $query =
HGSELECT id, firstname, familyname, middlename, fathername, region3, region4,
country,
HG MATCH(firstname, middlename, familyname, fathername) AGAINST
('$keywords' IN BOOLEAN MODE) AS score
HG FROM members
HGWHERE MATCH(firstname, middlename, familyname, fathername) AGAINST
('$keywords' IN BOOLEAN MODE) ORDER BY score DESC;
HG Thanks
HG Harmeet
--
Best regards,
Pablo
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Empty query result
Hi all. I am querying a database of single information multiple times using a simple 'select' statement. However, whenever the data in the DB is empty or is 0 (zero), then it throws an error. However, I don't want it to throw an error, I just want it to move on to the next query. This is being shown on a webpage I have. Note that I am using a function for this select statement because I just have to change the identifier that I am looking for - saves space. So I don't use mysql_error() to show my errors, I redirect to a global 'error' page. Is there a way to get around this empty set problem? Yes, I know about using @ to suppress warnings, but it's more than this. Thanks a bunch, ~Philip -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Unable to parse message
What mail program are you using? I'm getting mail parsing errors.
In addition, you have your From set as:
Harry G harry (removethis)@gabha.net
Likely, ( and ) aren't supposed to be in there (invalid chars) and it
also makes it impossible to reply to you. There is no other from or
reply to in the headers.
Answer to your question inline below.
On Mon, 26 Jul 2004 08:47:08 +1000, wrote:
Hi,
I want to run a query on a table while joining it with other tables.
Table 1- members
id - firstname - middlename - - country - region3
Fields country and region3 hold the integer codes for country and region3 is state
Table 2- country
id - countryname
Countryname is where the actual Country name is stored and id is the country code
Similarly Table 3 - region3
id - region3 where region3 is the state name and id is the region code.
I perform the following query to search the names in the database but the result
only gives me the integer values for Country and Region3. That's because, the code
is only stored in the database. But I need to join the table 23 with 1 so I can
extract names of country or region3 rather than integer values.
How would I modify the query below?
$keywords are the search words.
You want a JOIN, obviously. I'm assuming you're using Mysql. If you
aren't, you may need a different syntax.
$query =
SELECT id, firstname, familyname, middlename, fathername, region3, region4,
country,
country.countryname
MATCH(firstname, middlename, familyname, fathername) AGAINST ('$keywords'
IN BOOLEAN MODE) AS score
FROM members
LEFT JOIN country ON members.country = country.id
WHERE MATCH(firstname, middlename, familyname, fathername) AGAINST
('$keywords' IN BOOLEAN MODE) ORDER BY score DESC;
And do the same kind of thing for region3.
--
DB_DataObject_FormBuilder - The database at your fingertips
http://pear.php.net/package/DB_DataObject_FormBuilder
paperCrane --Justin Patrin--
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Empty query result
Pablo,
Hello Philip,
pse.. can you paste the error information?
There is no known empty set problem.
Here's my function:
function getMysqlSelectResultForUsername($identifier, $userName, $link)
{
$queryResult = mysql_query(select $identifier from theTable where
username = '$userName', $link);
if (!($queryResult = mysql_result($queryResult, 0))) {
session_register('_selectIdentifierFromTheTable');
header(location:error.php);
exit;
}
return $queryResult;
}
Here's the code that calls it:
$hall = getMysqlSelectResultForUsername('buildingID', $_userName,
$link);
$roomNum = getMysqlSelectResultForUsername('roomNum', $_userName,
$link);
$phone1 = getMysqlSelectResultForUsername('phone1', $_userName, $link);
$phone2 = getMysqlSelectResultForUsername('phone2', $_userName, $link);
$lastLogin = getMysqlSelectResultForUsername('lastLogin', $_userName,
$link);
If the mysql_result($queryResult, 0) returns zero/nothing, then it goes
to the 'error.php' page. However, I just want it to move on. For
example, if there is no 'phone2' given in the database, I want it to
just assign $phone2 to zero/nothing, not go to the error page.
Hope this helps.
Thanks,
~Philip
--
Best regards,
Pablo
PT Hi all.
PT I am querying a database of single information multiple times
using a
PT simple 'select' statement. However, whenever the data in the DB is
PT empty or is 0 (zero), then it throws an error. However, I don't
want it
PT to throw an error, I just want it to move on to the next query.
PT This is being shown on a webpage I have. Note that I am using a
PT function for this select statement because I just have to change
the
PT identifier that I am looking for - saves space. So I don't use
PT mysql_error() to show my errors, I redirect to a global 'error'
page.
PT Is there a way to get around this empty set problem? Yes, I know
PT about using @ to suppress warnings, but it's more than this.
PT Thanks a bunch,
PT ~Philip
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Empty query result
Why not...??
if (!($queryResult =
mysql_result($queryResult, 0)))
{
return 0;
}
- Original Message -
From: Philip Thompson [EMAIL PROTECTED]
Date: Monday, July 26, 2004 12:57 pm
Subject: Re: [PHP-DB] Empty query result
Pablo,
Hello Philip,
pse.. can you paste the error information?
There is no known empty set problem.
Here's my function:
function
getMysqlSelectResultForUsername($identifier,
$userName,
$link){
$queryResult = mysql_query(select $identifier
from theTable
where
username = '$userName', $link);
if (!($queryResult =
mysql_result($queryResult, 0))) {
session_register('_selectIdentifierFromTheTable');
header(location:error.php);
exit;
}
return $queryResult;
}
Here's the code that calls it:
$hall =
getMysqlSelectResultForUsername('buildingID',
$_userName,
$link);
$roomNum =
getMysqlSelectResultForUsername('roomNum',
$_userName,
$link);
$phone1 =
getMysqlSelectResultForUsername('phone1',
$_userName,
$link);$phone2 =
getMysqlSelectResultForUsername('phone2',
$_userName, $link);
$lastLogin =
getMysqlSelectResultForUsername('lastLogin',
$_userName,
$link);
If the mysql_result($queryResult, 0) returns
zero/nothing, then it
goes
to the 'error.php' page. However, I just want it
to move on. For
example, if there is no 'phone2' given in the
database, I want it
to
just assign $phone2 to zero/nothing, not go to the
error page.
Hope this helps.
Thanks,
~Philip
--
Best regards,
Pablo
PT Hi all.
PT I am querying a database of single
information multiple times
using a
PT simple 'select' statement. However, whenever
the data in the
DB is
PT empty or is 0 (zero), then it throws an
error. However, I
don't
want it
PT to throw an error, I just want it to move on
to the next query.
PT This is being shown on a webpage I have.
Note that I am using a
PT function for this select statement because I
just have to
change
the
PT identifier that I am looking for - saves
space. So I don't use
PT mysql_error() to show my errors, I redirect
to a global
'error'
page.
PT Is there a way to get around this empty
set problem? Yes, I
know PT about using @ to suppress warnings, but
it's more than this.
PT Thanks a bunch,
PT ~Philip
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit:
http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Empty query result
Hello Philip, pse.. can you paste the error information? There is no known empty set problem. -- Best regards, Pablo PT Hi all. PT I am querying a database of single information multiple times using a PT simple 'select' statement. However, whenever the data in the DB is PT empty or is 0 (zero), then it throws an error. However, I don't want it PT to throw an error, I just want it to move on to the next query. PT This is being shown on a webpage I have. Note that I am using a PT function for this select statement because I just have to change the PT identifier that I am looking for - saves space. So I don't use PT mysql_error() to show my errors, I redirect to a global 'error' page. PT Is there a way to get around this empty set problem? Yes, I know PT about using @ to suppress warnings, but it's more than this. PT Thanks a bunch, PT ~Philip -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Empty query result
On Tuesday 27 July 2004 00:57, Philip Thompson wrote:
Here's my function:
function getMysqlSelectResultForUsername($identifier, $userName, $link)
{
$queryResult = mysql_query(select $identifier from theTable where
username = '$userName', $link);
if (!($queryResult = mysql_result($queryResult, 0))) {
session_register('_selectIdentifierFromTheTable');
header(location:error.php);
exit;
}
return $queryResult;
}
Here's the code that calls it:
$hall = getMysqlSelectResultForUsername('buildingID', $_userName,
$link);
$roomNum = getMysqlSelectResultForUsername('roomNum', $_userName,
$link);
$phone1 = getMysqlSelectResultForUsername('phone1', $_userName, $link);
$phone2 = getMysqlSelectResultForUsername('phone2', $_userName, $link);
$lastLogin = getMysqlSelectResultForUsername('lastLogin', $_userName,
$link);
What you're doing is extremely inefficient. You could do in a single query
what you're currently doing in 5 queries. Use a query like:
SELECT col1, col2, col3, coln, FROM table WHERE colx = 'something'
Then use mysql_fetch_row() or siblings to get the column contents.
If the mysql_result($queryResult, 0) returns zero/nothing, then it goes
to the 'error.php' page. However, I just want it to move on. For
example, if there is no 'phone2' given in the database, I want it to
just assign $phone2 to zero/nothing, not go to the error page.
Well you wrote the code so that it will go to the error page.
--
Jason Wong - Gremlins Associates - www.gremlins.biz
Open Source Software Systems Integrators
* Web Design Hosting * Internet Intranet Applications Development *
--
Search the list archives before you post
http://marc.theaimsgroup.com/?l=php-db
--
/*
If little green men land in your back yard, hide any little green women
you've got in the house.
-- Mike Harding, The Armchair Anarchist's Almanac
*/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Empty query result
Thanks all! I will try some of the suggestions that you guys gave. I
appreciate it.
~Philip
On Jul 26, 2004, at 12:52 PM, Pablo M. Rivas wrote:
Hello Philip,
read:
http://www.php.net/manual/en/function.mysql-result.php
when you do $queryResult = mysql_result($queryResult,0) you get
the error... so, you can do something like this:
function getMysqlSelectResultForUsername($identifier, $userName,
$link)
{
$queryResult = mysql_query(select $identifier from theTable
where
username = '$userName', $link);
if ($queryResult) {
$queryResult = mysql_result($queryResult, 0);
}
return $queryResult;
}
$hall = getMysqlSelectResultForUserName('buildingID',
$_userName,$link);
if (!$hall) die (error)
or you can do:
function getMySqlSelectResultForUserName($identifier,
$userName,$link) {
return mysql_query(select $identifier from TheTable where
username='$userName',$link);
}
$hall= getMysqlSelectResultForUsername('buildingID', $_userName,
$link);
if (!$hall) die(not found building!!!);
$roomNum = getMysqlSelectResultForUsername('roomNum',
$_userName,$link);
$phone1 = getMysqlSelectResultForUsername('phone1', $_userName,
$link);
$phone2 = getMysqlSelectResultForUsername('phone2', $_userName,
$link);
$lastLogin = getMysqlSelectResultForUsername('lastLogin',
$_userName,$link);
if ((!$roomNum) or (!$phone1) or (!$phone2) or (!$lastLogin)) {
echo data is missing;
} else {
...
}
PT Pablo,
--
Best regards,
Pablo
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Unable to parse message
You should remove (removethis) to send via email. This is just a
precaution because often Newsgroups are scanned for email address and added
spammers list. Just want to avoid that.
Justin Patrin [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
What mail program are you using? I'm getting mail parsing errors.
In addition, you have your From set as:
Harry G harry (removethis)@gabha.net
Likely, ( and ) aren't supposed to be in there (invalid chars) and it
also makes it impossible to reply to you. There is no other from or
reply to in the headers.
Answer to your question inline below.
On Mon, 26 Jul 2004 08:47:08 +1000, wrote:
Hi,
I want to run a query on a table while joining it with other tables.
Table 1- members
id - firstname - middlename - - country - region3
Fields country and region3 hold the integer codes for country and
region3 is state
Table 2- country
id - countryname
Countryname is where the actual Country name is stored and id is the
country code
Similarly Table 3 - region3
id - region3 where region3 is the state name and id is the region code.
I perform the following query to search the names in the database but
the result only gives me the integer values for Country and Region3. That's
because, the code is only stored in the database. But I need to join the
table 23 with 1 so I can extract names of country or region3 rather than
integer values.
How would I modify the query below?
$keywords are the search words.
You want a JOIN, obviously. I'm assuming you're using Mysql. If you
aren't, you may need a different syntax.
$query =
SELECT id, firstname, familyname, middlename, fathername,
region3, region4, country,
country.countryname
MATCH(firstname, middlename, familyname, fathername) AGAINST
('$keywords' IN BOOLEAN MODE) AS score
FROM members
LEFT JOIN country ON members.country = country.id
WHERE MATCH(firstname, middlename, familyname, fathername)
AGAINST ('$keywords' IN BOOLEAN MODE) ORDER BY score DESC;
And do the same kind of thing for region3.
--
DB_DataObject_FormBuilder - The database at your fingertips
http://pear.php.net/package/DB_DataObject_FormBuilder
paperCrane --Justin Patrin--
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Joining Tables for Search
Thanks Pablo, that worked.
Pablo M. Rivas [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Hello Harry,
you can use left join:
http://dev.mysql.com/doc/mysql/en/JOIN.html
$query =
SELECT members.id, firstname, familyname, middlename, fathername,
region3.region3, region4, members.country, country.countryname,
MATCH(firstname, middlename, familyname, fathername) AGAINST
('$keywords' IN BOOLEAN MODE) AS score
FROM members left join country on members.country = country.id
left join region3 on members.region3 = region3.id
WHERE MATCH(firstname, middlename, familyname, fathername) AGAINST
('$keywords' IN BOOLEAN MODE)
ORDER BY score DESC;
good luck.
HG Hi,
HG I want to run a query on a table while joining it with other tables.
HG Table 1- members
HG id - firstname - middlename - - country - region3
HG Fields country and region3 hold the integer codes for country and
region3 is state
HG Table 2- country
HG id - countryname
HG Countryname is where the actual Country name is stored and id is the
country code
HG Similarly Table 3 - region3
HG id - region3 where region3 is the state name and id is the region
code.
HG I perform the following query to search the names in the database but
the result only gives me the integer values for Country and Region3. That's
because, the code is only stored in the database.
HG But I need to join the table 23 with 1 so I can extract names of
country or region3 rather than integer values.
HG How would I modify the query below?
HG $keywords are the search words.
HG $query =
HGSELECT id, firstname, familyname, middlename, fathername,
region3, region4, country,
HG MATCH(firstname, middlename, familyname, fathername) AGAINST
('$keywords' IN BOOLEAN MODE) AS score
HG FROM members
HGWHERE MATCH(firstname, middlename, familyname, fathername)
AGAINST ('$keywords' IN BOOLEAN MODE) ORDER BY score DESC;
HG Thanks
HG Harmeet
--
Best regards,
Pablo
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Match Against Query with Integers
Hi
Here's the senario...
Table 1- members
id - firstname - middlename - - country - region3
Fields country and region3 hold the integer codes for country and region3 is state
Table 2- country
id - countryname
Countryname is where the actual Country name is stored and id is the country code
Similarly Table 3 - region3
id - region3 where region3 is the state name and id is the region code.
I want to query country code and state code in the database but the fields are integer
values. I am trying to modify the following query, but I think it will not work for
integers? Is that correct??
$keywords are the contry state codes (integers).
$query =
SELECT id, firstname, familyname, middlename, fathername, region3, region4,
country,
MATCH(country, region3, region4) AGAINST ('$keywords' IN BOOLEAN MODE) AS
score
FROM members
WHERE MATCH(country, region3, region4) AGAINST ('$keywords' IN BOOLEAN MODE)
ORDER BY score DESC;
Thanks
Harry
