offline solutions for database?
Hi, If a client wants an offline solution for a mysql database online, what do you think? Is that even possible? The mysql database right now is with the hosting company(with a private server hosting plan) somewhere in US while the client's physical office is in Africa. What kind of options do they have? I was just shocked when they said that. Anyway, I don't know where to post this type of question so forgive me for posting it here. Any suggestions is greatly and gratefully appreciated! Thanks! Jordan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1006: Can't create database
I was told to try this: chown mysql.mysql -R /var/lib/mysql chmod 750 -R /var/lib/mysql and it worked afterward. So that's the datadir permission problem. How can find the user mysql password? The hosting company which I got the dedicated server package from set that up of course. Should I go to phpAdmin and reset that password so that I know what it is from now on? Thanks again! Jordan Tom Crimmins wrote: [snip] I'm using version 3.23.58. I tried to create a database foo using phpAdmin(logged in as root) and got: ERROR 1006: Can't create database 'foo'. (errno: 13) ... drwx--x--x 2 mysql root 4096 Sep 15 10:34 mysql [/snip] perror 13 Error code 13: Permission denied File permissions look ok at that level, and I would assume that mysql user can get to that directory. You could login to your linux box as root then 'su - mysql' and see if you can create a directory in the mysql datadir as the mysql user. This isn't a grant table issue because I believe that will give you an access denied error. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa
Re: ERROR 1006: Can't create database
Thanks! That makes senses. And once I chmod as instructed, I can create directory in the mysql folder. Tom Crimmins wrote: [snip] How can find the user mysql password? The hosting company which I got the dedicated server package from set that up of course. Should I go to phpAdmin and reset that password so that I know what it is from now on? [/snip] The mysql linux user should not be able to login interactivly, just as they should not have a shell. To do anything as the mysql user, simply become root, then 'su - mysql'. You will not be asked for a password. example: [EMAIL PROTECTED] tom]$ su - Password: [EMAIL PROTECTED] root]# su - mysql -bash-2.05b$ id uid=100(mysql) gid=101(mysql) groups=101(mysql) -bash-2.05b$ --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1006: Can't create database
Hi, I'm really stuck here. I'm using version 3.23.58. I tried to create a database foo using phpAdmin(logged in as root) and got: ERROR 1006: Can't create database 'foo'. (errno: 13) I checked [EMAIL PROTECTED] in the user table and it has all priviledges. Went to linux and logged in as root, logged into mysql as root, got the same error when creating a database. I searched all over the cyberspace and forums for answers. They all asked me to check 2 things: disk usage and permissions. For disk usage this is what I've got: FilesystemSize Used Avail Use% Mounted on /dev/hda3 73G 1.7G 67G 3% / /dev/hda1 76M 6.2M 66M 9% /boot none 251M 0 251M 0% /dev/shm I don't think I'm running out of space(btw I'm very new to the LAMP thing). Also I checked permissions and this is what I got: drwx--x--x 2 mysql root 4096 Sep 15 10:34 mysql So I don't know what else I can do. I can't do anything if I can't create a database. Your help is greatly appreciated! Thanks! Jordan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie Q: How to display Search Results in a secure way?
Hi,
I'm still learning on this. Pls. excuse me if I simply overlooked
something.
Originally I had this:
?
// list matches
while (list($fid, $title, $lname, $fname) = mysql_fetch_row($result))
{
echo table border=2 width=400 cellpadding=2 cellspacing=2tr;
echo td width=200a
href=FacDetails.php?fid=$fid$fnamenbsp;$lname/a/tdtd
width=200$title/td;
echo /tr/table;
}
?
to list the search results and display them as a link which will take
the user to a detailed page. However, the fid has to show up as part of
the URL. I want to hide the fid as I don't want people to view the other
detailed records just by randomly entering the number for the fid. So I
tried to modify it and came up with the following:
form action=FacDetails.php4 method=post
?
// list matches
while (list($fid, $title, $lname, $fname) = mysql_fetch_row($result))
{
echo table border=2 width=400 cellpadding=2 cellspacing=2tr;
echo td width=200$fnamenbsp;$lname/tdtd
width=200$title/tdtdinput type=hidden name=fid value=? echo
$fid; ?input type=submit name=submit value=Choose this
faculty/td;
echo /tr/table;
}
?
/form
Basically I was trying to see if I can simply add a submit button to
every row so that fid will be invisible to the users. But it didn't
work. It kept telling me: Parse error: parse error, unexpected
T_STRING, expecting ',' or ';' in
/home/virtual/site482/fst/var/www/html/PHP/latest/SearchFaculty_Rslt.php4
on line 145 where line 145 is the line where the 2nd echo statement is
located as above.
I tried to move the input type=hidden..?echo $fid;? section up,
just below the form tag. Still same error for the same line 145.
My questions are:
1) What's wrong with my script? How can I fix that?
or
2) Is there another way to achieve my goal without using the Submit
button at all?
Thanks for reading this and all your help.
Jordan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie Q: How to display Search Results in a secure way?
Thanks!
That helped me get pass that error. However, maybe my logic is wrong, when I
clicked the Submit button, it took me to the detail page of the last search
result. I figured I'd need to move that input tag for the hidden value
somewhere instead. Tried a few places but still didn't work.
any comments?
thanks!
Jordan
Patrick Shoaf wrote:
I am not very familiar with php, but why are you using an echo within an
echo statement?
echo td width=200$fnamenbsp;$lname/tdtd
width=200$title/tdtdinput type=hidden name=fid value=? echo
$fid; ?input type=submit name=submit value=Choose this
faculty/td;
why not try this
echo td width=200$fnamenbsp;$lname/td;
echo td width=200$title/td;
echo tdinput type=\hidden\ name=\fid\ value=\$fid\;
echo input type=\submit\ name=\submit\ value=\Choose this
faculty\/td;
Also, most languages require you to escape when used within quotes.
At 01:32 PM 10/17/2003, Jordan Morgan wrote:
Hi,
I'm still learning on this. Pls. excuse me if I simply overlooked
something.
Originally I had this:
?
// list matches
while (list($fid, $title, $lname, $fname) = mysql_fetch_row($result))
{
echo table border=2 width=400 cellpadding=2 cellspacing=2tr;
echo td width=200a
href=FacDetails.php?fid=$fid$fnamenbsp;$lname/a/tdtd
width=200$title/td;
echo /tr/table;
}
?
to list the search results and display them as a link which will take
the user to a detailed page. However, the fid has to show up as part of
the URL. I want to hide the fid as I don't want people to view the other
detailed records just by randomly entering the number for the fid. So I
tried to modify it and came up with the following:
form action=FacDetails.php4 method=post
?
// list matches
while (list($fid, $title, $lname, $fname) = mysql_fetch_row($result))
{
echo table border=2 width=400 cellpadding=2 cellspacing=2tr;
echo td width=200$fnamenbsp;$lname/tdtd
width=200$title/tdtdinput type=hidden name=fid value=? echo
$fid; ?input type=submit name=submit value=Choose this
faculty/td;
echo /tr/table;
}
?
/form
Basically I was trying to see if I can simply add a submit button to
every row so that fid will be invisible to the users. But it didn't
work. It kept telling me: Parse error: parse error, unexpected
T_STRING, expecting ',' or ';' in
/home/virtual/site482/fst/var/www/html/PHP/latest/SearchFaculty_Rslt.php4
on line 145 where line 145 is the line where the 2nd echo statement is
located as above.
I tried to move the input type=hidden..?echo $fid;? section up,
just below the form tag. Still same error for the same line 145.
My questions are:
1) What's wrong with my script? How can I fix that?
or
2) Is there another way to achieve my goal without using the Submit
button at all?
Thanks for reading this and all your help.
Jordan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Patrick J. Shoaf, Systems Engineer
mailto:[EMAIL PROTECTED][EMAIL PROTECTED]
Midmon Internet Services, LLC
100 Third Street
Charleroi, PA 15022
http://www.midmon.com
Phone: 724-483-2400 ext. 105
or888-638-6963
Fax: 724-489-4386
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie Q: How to display Search Results in a secure way?
Thanks millions Dan! That's exactly what I need. And I used your method and it worked
beautifully!
I'm so excited! Thanks again!
Jordan
Dan Greene wrote:
To make this back to being a MySQL question
What if you used the encode or md5 functions when you retrieve the list of results,
and then do your lookup on the item details by matching it to decoding the same id?
ex. get list-
select fid as open_fid, MD5(fid) as crypt_id
from your_table
where whatever your criteria is
Which when you make url will look like:
echo a href=\FacDetails.php?fid=$crypt_id\$fname/a
which will generate something like:
a href=FacDetails.php?fid=23rh23kjne2323j4k23n234lJordan/a
and on your lookup, do
select col1, col2, col3
from FacDetails
where MD5(fid) = $fid;
My $0.02 cents...
At 01:32 PM 10/17/2003, Jordan Morgan wrote:
Hi,
I'm still learning on this. Pls. excuse me if I simply overlooked
something.
Originally I had this:
?
// list matches
while (list($fid, $title, $lname, $fname) =
mysql_fetch_row($result))
{
echo table border=2 width=400 cellpadding=2
cellspacing=2tr;
echo td width=200a
href=FacDetails.php?fid=$fid$fnamenbsp;$lname/a/tdtd
width=200$title/td;
echo /tr/table;
}
?
to list the search results and display them as a link
which will take
the user to a detailed page. However, the fid has to show
up as part of
the URL. I want to hide the fid as I don't want people to
view the other
detailed records just by randomly entering the number for
the fid. So I
tried to modify it and came up with the following:
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie select statement question
oh thanks! I guess that's why I'm a newbie.
Diana Soares wrote:
Look at:
if ($tenureid=3)
You're not comparing $tenureid with 3, you're assigning 3 to
$ternureid... If you want to compare both values, you must use the
operator == (and not only =).
On Fri, 2003-10-10 at 05:43, Jordan Morgan wrote:
Hi,
I have the following statement:
echo $tenureidP;
if ($tenureid=3)
{
// get faculty employment record - award date
$sql = select TD.Award_Date from TenureDescription TD LEFT JOIN
InstitutionEmployment IE on TD.TenureDescriptionID=IE.Tenure WHERE
IE.FacultyMember='$fid';
$result = mysql_db_query($database, $sql, $connection) or die (Error in query:
$sql. . mysql_error());
// obtain data from resultset
list($tenuredate) = mysql_fetch_row($result);
echo BDate Tenure Granted/B: ;
echo fixDate($tenuredate);
echo P;
}
else {
echo BDate Tenure Granted/B: NullP;
}
and I can't figure out why I always get the following result:
2
Date Tenure Granted: 31 Dec 1969
---
or
1
Date Tenure Granted: 31 Dec 1969
---
when 1) tenureid 3, and 2) no 31 Dec 1969 date in the database anywhere.
The only thing I can think of is that I modified the dropdown box for the tenure
date on the data entry page like this:
tr
tdDate Tenure Grantedbrfont size=-2(in mm-dd- format)/font/td
td
select name=tmm
option value=0 selected='selected'/option
? for ($x=1; $x=12; $x++) { echo option value=\ . sprintf(%02d, $x) .
\ . sprintf(%02d, $x) . /option; } ?
/select -
select name=tdd
option value=0 selected='selected'/option
? for ($x=1; $x=31; $x++) { echo option value=\ . sprintf(%02d, $x) .
\ . sprintf(%02d, $x) . /option; } ?
/select -
select name=t
option value=0 selected='selected'/option
!-- display from 1970 to (current year) --
? for ($x=(date(Y, mktime())); $x=1970; $x--) { echo option
value=$x$x/option; } ?
/select
/td
/tr
by adding option value=0 selected='selected'/option to those 3 fields as I
want null to be a default selection. but I can't imagine why that'll mess up the
if statement evaluation.
It seems that the 1st if statement just runs whatever the tenureid is.
Can anyone help me on this? I'm using PHP 4.2.2 and MySQL 3.23.54 btw.
Thanks millions!
Jordan
--
Diana Soares
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Jordan Morgan
Information Analyst - GeorgiaFIRST HRMS Project
Board of Regents
Office of Information and Instructional Technology
1865 West Broad Street, Athens, GA 30606-3539
Phone: (706) 369-6232
Fax: (706) 369-6429
mailto:[EMAIL PROTECTED] http://www.usg.edu
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie select statement question
The date is stored as dates instead of timestamps. Yes, that fixDate function is
defined by me and it worked great with other dates I have stored. I'm really
thinking my dropdown fields for the MM, DD, and on the data entry page has
somethng wrong with it.
do you think so?
ps: thanks for checking for me btw.
Nobody wrote:
I am not sure if this has any significance - but isn't 31 Dec 1969 the day
before the UNIX epoch (1 Jan 1970 - i think)?
Jordan, how are the dates stored in the database - as dates or unix
timestamps?
Also, the function fixDate isn't in the online PHP manual - is it a function
defined by you? - could that not be doing something weird?
Rory McKinley
Nebula Solutions
+27 82 857 2391
[EMAIL PROTECTED]
There are 10 kinds of people in this world,
those who understand binary and those who don't (Unknown)
- Original Message -
From: Diana Soares [EMAIL PROTECTED]
To: Jordan Morgan [EMAIL PROTECTED]
Cc: mysql [EMAIL PROTECTED]
Sent: Friday, October 10, 2003 8:58 AM
Subject: Re: newbie select statement question
Look at:
if ($tenureid=3)
You're not comparing $tenureid with 3, you're assigning 3 to
$ternureid... If you want to compare both values, you must use the
operator == (and not only =).
On Fri, 2003-10-10 at 05:43, Jordan Morgan wrote:
Hi,
I have the following statement:
echo $tenureidP;
if ($tenureid=3)
{
// get faculty employment record - award date
$sql = select TD.Award_Date from TenureDescription TD LEFT JOIN
InstitutionEmployment IE on TD.TenureDescriptionID=IE.Tenure WHERE
IE.FacultyMember='$fid';
$result = mysql_db_query($database, $sql, $connection) or die (Error
in query: $sql. . mysql_error());
// obtain data from resultset
list($tenuredate) = mysql_fetch_row($result);
echo BDate Tenure Granted/B: ;
echo fixDate($tenuredate);
echo P;
}
else {
echo BDate Tenure Granted/B: NullP;
}
and I can't figure out why I always get the following result:
2
Date Tenure Granted: 31 Dec 1969
---
or
1
Date Tenure Granted: 31 Dec 1969
---
when 1) tenureid 3, and 2) no 31 Dec 1969 date in the database
anywhere.
The only thing I can think of is that I modified the dropdown box for
the tenure date on the data entry page like this:
tr
tdDate Tenure Grantedbrfont size=-2(in mm-dd-
format)/font/td
td
select name=tmm
option value=0 selected='selected'/option
? for ($x=1; $x=12; $x++) { echo option value=\ . sprintf(%02d,
$x) . \ . sprintf(%02d, $x) . /option; } ?
/select -
select name=tdd
option value=0 selected='selected'/option
? for ($x=1; $x=31; $x++) { echo option value=\ . sprintf(%02d,
$x) . \ . sprintf(%02d, $x) . /option; } ?
/select -
select name=t
option value=0 selected='selected'/option
!-- display from 1970 to (current year) --
? for ($x=(date(Y, mktime())); $x=1970; $x--) { echo option
value=$x$x/option; } ?
/select
/td
/tr
by adding option value=0 selected='selected'/option to those 3
fields as I want null to be a default selection. but I can't imagine why
that'll mess up the if statement evaluation.
It seems that the 1st if statement just runs whatever the tenureid is.
Can anyone help me on this? I'm using PHP 4.2.2 and MySQL 3.23.54 btw.
Thanks millions!
Jordan
--
Diana Soares
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Jordan Morgan
Information Analyst - GeorgiaFIRST HRMS Project
Board of Regents
Office of Information and Instructional Technology
1865 West Broad Street, Athens, GA 30606-3539
Phone: (706) 369-6232
Fax: (706) 369-6429
mailto:[EMAIL PROTECTED] http://www.usg.edu
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie select statement question
Hi,
I have the following statement:
echo $tenureidP;
if ($tenureid=3)
{
// get faculty employment record - award date
$sql = select TD.Award_Date from TenureDescription TD LEFT JOIN
InstitutionEmployment IE on TD.TenureDescriptionID=IE.Tenure WHERE
IE.FacultyMember='$fid';
$result = mysql_db_query($database, $sql, $connection) or die (Error in query: $sql.
. mysql_error());
// obtain data from resultset
list($tenuredate) = mysql_fetch_row($result);
echo BDate Tenure Granted/B: ;
echo fixDate($tenuredate);
echo P;
}
else {
echo BDate Tenure Granted/B: NullP;
}
and I can't figure out why I always get the following result:
2
Date Tenure Granted: 31 Dec 1969
---
or
1
Date Tenure Granted: 31 Dec 1969
---
when 1) tenureid 3, and 2) no 31 Dec 1969 date in the database anywhere.
The only thing I can think of is that I modified the dropdown box for the tenure date
on the data entry page like this:
tr
tdDate Tenure Grantedbrfont size=-2(in mm-dd- format)/font/td
td
select name=tmm
option value=0 selected='selected'/option
? for ($x=1; $x=12; $x++) { echo option value=\ . sprintf(%02d, $x) . \ .
sprintf(%02d, $x) . /option; } ?
/select -
select name=tdd
option value=0 selected='selected'/option
? for ($x=1; $x=31; $x++) { echo option value=\ . sprintf(%02d, $x) . \ .
sprintf(%02d, $x) . /option; } ?
/select -
select name=t
option value=0 selected='selected'/option
!-- display from 1970 to (current year) --
? for ($x=(date(Y, mktime())); $x=1970; $x--) { echo option
value=$x$x/option; } ?
/select
/td
/tr
by adding option value=0 selected='selected'/option to those 3 fields as I want
null to be a default selection. but I can't imagine why that'll mess up the if
statement evaluation.
It seems that the 1st if statement just runs whatever the tenureid is.
Can anyone help me on this? I'm using PHP 4.2.2 and MySQL 3.23.54 btw.
Thanks millions!
Jordan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbies asking help with LEFT JOIN
Hi, I'm working on a faculty system and currently I'm building a search which allows users to generate a list of faculty withint a department based on their first name, last name, rank, and tenure status. I could do it without the tenure status criteria however I can't get it work if the tenure status is included in the query. Rank field on the search page is generated by using the Rank table while the Tenure Status field on the search page is generated by using the TenureType table. Structure of 4 tables used in the query: FacultyPerson: FacultyPersonID tinyint(4) unsigned PRI auto_increment FirstName varchar(25) LastName varchar(25) InstitutionEmployment: EmploymentID int(11) Noauto_increment FacultyMember int(11) Department int(11) Rank int(11) Title varchar(50) Tenure int(11) (FacultyMember=FacultyPerson.FacultyPersonID and Tenure=TenureDescription.TenureDescriptionID) TenureDescription: TenureDescriptionID int(11) FacultyMember int(11) Tenure_Status int(11) Award_Date date (Tenure_Status=TenureType.TenureTypeID) TenureType: TenureTypeID int(11) Tenure_Type varchar(25) Provided that dept exists and all 4(first name, last name, rank, and tenure status) criteria have been entered, my query is: SELECT DISTINCT InstitutionEmployment.Title, FacultyPerson.LastName, FacultyPerson.FirstName from InstitutionEmployment, FacultyPerson, TenureDescription, TenureType where InstitutionEmployment.Department='$dept' AND FacultyPerson.FacultyPersonID=InstitutionEmployment.FacultyMember AND InstitutionEmployment.Tenure=TenureDescription.TenureDescriptionID AND TenureDescription.Tenure_Status=TenureType.TenureTypeID AND FacultyPerson.LastName LIKE '%.$lname.%' AND FacultyPerson.FirstName LIKE '%.$fname.%' AND InstitutionEmployment.Rank='$rank' AND TenureDescription.Tenure_Status='$tenure' ORDER BY FacultyPerson.FirstName; It seemed that my query returns results without verifying the tenure status criteria at all. I think my logic is right but it doesn't work. With the complexity of the query, I don't know how to 1) make it work and 2) make it work using LEFT JOIN. Any help is highly appreciated. Thanks! Regards, Jordan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbies asking help with LEFT JOIN
Thanks for the prompt response David. However, it still doesn't work. The search page is here: http://www.jordymaeproductions.com/PHP/latest/SearchFaculty.php4 I have a faculty from IT department named Debbie Farmer who is an instructor with a tenure status of Not on Tenure Track. So if I typed in D in FirstName, L in LastName, chose Instructor in Rank and Not on Tenure Track in Tenure Status field, her record was pulled. But if I changed the the tenure status to the wrong tenure status, her record still shows up. The query was supposed to trigger when if (!empty($lname) !empty($fname) !empty($rank) !empty($tenure)). So I don't know what's missing to cause her record pulled out even without the incorrect tenure status. I don't like to type too much either so I gotta learn to use the table reference thingy. ;) Any ideas to solve my query error? Thanks millions! Jordan David Sears wrote: I didn't have the fortitude to populate the tables with data, so I don't know if this will work for you, but to use explicit joins try something like the sample below. (Note that I don't like to type as much as you do:-) select distinct IE.title, FP.LastName, FP.FirstName from InstitutionEmployment IE left join FacultyPerson FP on IE.FacultyMember=FP.FacultyPersonID left join (TenureDescription TD inner join TenureType TT on TD.Tenure_Status=TT.TenureTypeID) on IE.Tenure=TD.TenureDescriptionID where IE.Department = '$dept' and FP.LastName like '%.$lname.%' and FP.FirstName like '%.$fname.%' and IE.Rank = '$rank' and TD.Tenure_Status like '$tenure' order by FP.FirstName; -Original Message- From: Jordan Morgan [mailto:[EMAIL PROTECTED] Sent: Sunday, September 14, 2003 1:29 AM To: [EMAIL PROTECTED] Subject: Newbies asking help with LEFT JOIN Hi, I'm working on a faculty system and currently I'm building a search which allows users to generate a list of faculty withint a department based on their first name, last name, rank, and tenure status. I could do it without the tenure status criteria however I can't get it work if the tenure status is included in the query. Rank field on the search page is generated by using the Rank table while the Tenure Status field on the search page is generated by using the TenureType table. Structure of 4 tables used in the query: FacultyPerson: FacultyPersonID tinyint(4) unsigned PRI auto_increment FirstName varchar(25) LastName varchar(25) InstitutionEmployment: EmploymentID int(11) Noauto_increment FacultyMember int(11) Department int(11) Rank int(11) Title varchar(50) Tenure int(11) (FacultyMember=FacultyPerson.FacultyPersonID and Tenure=TenureDescription.TenureDescriptionID) TenureDescription: TenureDescriptionID int(11) FacultyMember int(11) Tenure_Status int(11) Award_Date date (Tenure_Status=TenureType.TenureTypeID) TenureType: TenureTypeID int(11) Tenure_Type varchar(25) Provided that dept exists and all 4(first name, last name, rank, and tenure status) criteria have been entered, my query is: SELECT DISTINCT InstitutionEmployment.Title, FacultyPerson.LastName, FacultyPerson.FirstName from InstitutionEmployment, FacultyPerson, TenureDescription, TenureType where InstitutionEmployment.Department='$dept' AND FacultyPerson.FacultyPersonID=InstitutionEmployment.FacultyMember AND InstitutionEmployment.Tenure=TenureDescription.TenureDescriptionID AND TenureDescription.Tenure_Status=TenureType.TenureTypeID AND FacultyPerson.LastName LIKE '%.$lname.%' AND FacultyPerson.FirstName LIKE '%.$fname.%' AND InstitutionEmployment.Rank='$rank' AND TenureDescription.Tenure_Status='$tenure' ORDER BY FacultyPerson.FirstName; It seemed that my query returns results without verifying the tenure status criteria at all. I think my logic is right but it doesn't work. With the complexity of the query, I don't know how to 1) make it work and 2) make it work using LEFT JOIN. Any help is highly appreciated. Thanks! Regards, Jordan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unexpected $ on line XX
Hi, Can anyone of you kindly tell me what that error usually means? The line number given is always the last line of the script and I'm sure it means something in particular but I just don't know. Parse error: parse error, unexpected $ in XX.php4 on line 167 Thanks! Jordan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbies asking help with LEFT JOIN
I tried the parentheses () first and still it didn't work. :(
David Sears wrote:
That's parentheses () around the inner join, not braces {}.
-Original Message-
From: Jordan Morgan [mailto:[EMAIL PROTECTED]
Sent: Sunday, September 14, 2003 12:56 PM
To: David Sears
Cc: [EMAIL PROTECTED]
Subject: Re: Newbies asking help with LEFT JOIN
Thanks for the prompt response David. However, it still doesn't work.
The search page is here:
http://www.jordymaeproductions.com/PHP/latest/SearchFaculty.php4
I have a faculty from IT department named Debbie Farmer who is an instructor
with a tenure status of Not on Tenure Track. So if I typed in D in
FirstName, L in LastName, chose Instructor in Rank and Not on Tenure Track
in Tenure Status field, her record was pulled. But if I changed the the
tenure status to the wrong tenure status, her record still shows up. The
query was supposed to trigger when if (!empty($lname) !empty($fname)
!empty($rank) !empty($tenure)). So I don't know what's missing to cause
her record pulled out even without the incorrect tenure status.
I don't like to type too much either so I gotta learn to use the table
reference thingy. ;)
Any ideas to solve my query error?
Thanks millions!
Jordan
David Sears wrote:
I didn't have the fortitude to populate the tables with data, so I
don't know if this will work for you, but to use explicit joins try
something like the sample below. (Note that I don't like to type as
much as you do:-)
select distinct IE.title, FP.LastName, FP.FirstName
from InstitutionEmployment IE
left join FacultyPerson FP on IE.FacultyMember=FP.FacultyPersonID
left join (TenureDescription TD
inner join TenureType TT on TD.Tenure_Status=TT.TenureTypeID) on
IE.Tenure=TD.TenureDescriptionID where IE.Department = '$dept'
and FP.LastName like '%.$lname.%'
and FP.FirstName like '%.$fname.%'
and IE.Rank = '$rank'
and TD.Tenure_Status like '$tenure'
order by FP.FirstName;
-Original Message-
From: Jordan Morgan [mailto:[EMAIL PROTECTED]
Sent: Sunday, September 14, 2003 1:29 AM
To: [EMAIL PROTECTED]
Subject: Newbies asking help with LEFT JOIN
Hi,
I'm working on a faculty system and currently I'm building a search
which allows users to generate a list of faculty withint a department
based on their first name, last name, rank, and tenure status. I could
do it without the tenure status criteria however I can't get it work
if the tenure status is included in the query.
Rank field on the search page is generated by using the Rank table
while the Tenure Status field on the search page is generated by using
the TenureType table.
Structure of 4 tables used in the query:
FacultyPerson:
FacultyPersonID tinyint(4) unsigned PRI auto_increment
FirstName varchar(25)
LastName varchar(25)
InstitutionEmployment:
EmploymentID int(11) Noauto_increment
FacultyMember int(11)
Department int(11)
Rank int(11)
Title varchar(50)
Tenure int(11)
(FacultyMember=FacultyPerson.FacultyPersonID and
Tenure=TenureDescription.TenureDescriptionID)
TenureDescription:
TenureDescriptionID int(11)
FacultyMember int(11)
Tenure_Status int(11)
Award_Date date
(Tenure_Status=TenureType.TenureTypeID)
TenureType:
TenureTypeID int(11)
Tenure_Type varchar(25)
Provided that dept exists and all 4(first name, last name, rank, and
tenure
status) criteria have been entered, my query is:
SELECT DISTINCT InstitutionEmployment.Title, FacultyPerson.LastName,
FacultyPerson.FirstName from InstitutionEmployment, FacultyPerson,
TenureDescription, TenureType where
InstitutionEmployment.Department='$dept'
AND FacultyPerson.FacultyPersonID=InstitutionEmployment.FacultyMember AND
InstitutionEmployment.Tenure=TenureDescription.TenureDescriptionID AND
TenureDescription.Tenure_Status=TenureType.TenureTypeID AND
FacultyPerson.LastName LIKE '%.$lname.%' AND FacultyPerson.FirstName
LIKE
'%.$fname.%' AND InstitutionEmployment.Rank='$rank' AND
TenureDescription.Tenure_Status='$tenure' ORDER BY
FacultyPerson.FirstName;
It seemed that my query returns results without verifying the tenure
status criteria at all. I think my logic is right but it doesn't work.
With the complexity of the query, I don't know how to 1) make it work
and 2) make it work using LEFT JOIN.
Any help is highly appreciated.
Thanks!
Regards,
Jordan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Jordan Morgan
Information Analyst - GeorgiaFIRST HRMS Project
Board of Regents
Office of Information and Instructional Technology
1865 West Broad Street, Athens, GA 30606-3539
Phone: (706) 369-6232
Fax: (706
Re: Newbies asking help with LEFT JOIN
shoot, my MySQL is of version 3.23.54. :(( how do I modify mine to make it work
then? Thanks!
** it's hard to be a newbie**
David Sears wrote:
I see the syntax error output on your search page. I've tested the query
against empty tables on mysql version 4.0.14 without error. I copied the
query from the output of your search page to my client, and it still doesn't
error on 4.0.14.
I guess I should have asked which version of the server you're using. I'm
not sure when all the various joins took effect. There may be some such
info in the mysql docs.
-Original Message-
From: Jordan Morgan [mailto:[EMAIL PROTECTED]
Sent: Sunday, September 14, 2003 2:45 PM
To: David Sears
Cc: [EMAIL PROTECTED]
Subject: Re: Newbies asking help with LEFT JOIN
I tried the parentheses () first and still it didn't work. :(
David Sears wrote:
That's parentheses () around the inner join, not braces {}.
-Original Message-
From: Jordan Morgan [mailto:[EMAIL PROTECTED]
Sent: Sunday, September 14, 2003 12:56 PM
To: David Sears
Cc: [EMAIL PROTECTED]
Subject: Re: Newbies asking help with LEFT JOIN
Thanks for the prompt response David. However, it still doesn't work.
The search page is here:
http://www.jordymaeproductions.com/PHP/latest/SearchFaculty.php4
I have a faculty from IT department named Debbie Farmer who is an
instructor with a tenure status of Not on Tenure Track. So if I
typed in D in FirstName, L in LastName, chose Instructor in Rank and
Not on Tenure Track in Tenure Status field, her record was pulled. But
if I changed the the tenure status to the wrong tenure status, her
record still shows up. The query was supposed to trigger when if
(!empty($lname) !empty($fname)
!empty($rank) !empty($tenure)). So I don't know what's missing to cause
her record pulled out even without the incorrect tenure status.
I don't like to type too much either so I gotta learn to use the table
reference thingy. ;)
Any ideas to solve my query error?
Thanks millions!
Jordan
David Sears wrote:
I didn't have the fortitude to populate the tables with data, so I
don't know if this will work for you, but to use explicit joins try
something like the sample below. (Note that I don't like to type as
much as you do:-)
select distinct IE.title, FP.LastName, FP.FirstName
from InstitutionEmployment IE
left join FacultyPerson FP on IE.FacultyMember=FP.FacultyPersonID
left join (TenureDescription TD
inner join TenureType TT on TD.Tenure_Status=TT.TenureTypeID) on
IE.Tenure=TD.TenureDescriptionID where IE.Department = '$dept' and
FP.LastName like '%.$lname.%' and FP.FirstName like '%.$fname.%'
and IE.Rank = '$rank'
and TD.Tenure_Status like '$tenure'
order by FP.FirstName;
-Original Message-
From: Jordan Morgan [mailto:[EMAIL PROTECTED]
Sent: Sunday, September 14, 2003 1:29 AM
To: [EMAIL PROTECTED]
Subject: Newbies asking help with LEFT JOIN
Hi,
I'm working on a faculty system and currently I'm building a search
which allows users to generate a list of faculty withint a
department based on their first name, last name, rank, and tenure
status. I could do it without the tenure status criteria however I
can't get it work if the tenure status is included in the query.
Rank field on the search page is generated by using the Rank table
while the Tenure Status field on the search page is generated by
using the TenureType table.
Structure of 4 tables used in the query:
FacultyPerson:
FacultyPersonID tinyint(4) unsigned PRI auto_increment
FirstName varchar(25)
LastName varchar(25)
InstitutionEmployment:
EmploymentID int(11) Noauto_increment
FacultyMember int(11)
Department int(11)
Rank int(11)
Title varchar(50)
Tenure int(11) (FacultyMember=FacultyPerson.FacultyPersonID and
Tenure=TenureDescription.TenureDescriptionID)
TenureDescription:
TenureDescriptionID int(11)
FacultyMember int(11)
Tenure_Status int(11)
Award_Date date
(Tenure_Status=TenureType.TenureTypeID)
TenureType:
TenureTypeID int(11)
Tenure_Type varchar(25)
Provided that dept exists and all 4(first name, last name, rank, and
tenure
status) criteria have been entered, my query is:
SELECT DISTINCT InstitutionEmployment.Title, FacultyPerson.LastName,
FacultyPerson.FirstName from InstitutionEmployment, FacultyPerson,
TenureDescription, TenureType where
InstitutionEmployment.Department='$dept'
AND
FacultyPerson.FacultyPersonID=InstitutionEmployment.FacultyMember
AND
InstitutionEmployment.Tenure=TenureDescription.TenureDescriptionID
AND TenureDescription.Tenure_Status=TenureType.TenureTypeID AND
FacultyPerson.LastName LIKE '%.$lname.%' AND FacultyPerson.FirstName
LIKE
'%.$fname.%' AND InstitutionEmployment.Rank='$rank
Re: Newbies asking help with LEFT JOIN
WOW! It worked! What's the darn difference? Man, you're the greatest! I'm really
really glad that we have people like you who are so willing and kind enough to
help others!
Your help is greatly appreciated! :)
Jordan
David Sears wrote:
OK,
The server is complaining about the parentheses. Here's a version of the
query which is not nested. It may actually be close to what you want...:)
select distinct IE.title, FP.LastName, FP.FirstName
from InstitutionEmployment IE
left join FacultyPerson FP on IE.FacultyMember=FP.FacultyPersonID
left join TenureDescription TD on IE.Tenure=TD.TenureDescriptionID
left join TenureType TT on TD.Tenure_Status=TT.TenureTypeID
where IE.Department = '$dept'
and FP.LastName like '%.$lname.%'
and FP.FirstName like '%.$fname.%'
and IE.Rank = '$rank'
and TD.Tenure_Status like '$tenure'
order by FP.FirstName;
-Original Message-
From: Jordan Morgan [mailto:[EMAIL PROTECTED]
Sent: Sunday, September 14, 2003 4:28 PM
To: David Sears
Subject: Re: Newbies asking help with LEFT JOIN
Hmm did what you suggested using LEFT JOIn and remove the parentheses
but I still got the following message:
Error in query: select distinct FP.FacultyPersonID, IE.Title, IE.Department,
FP.LastName, FP.FirstName, IE.School from InstitutionEmployment IE left join
FacultyPerson FP on IE.FacultyMember=FP.FacultyPersonID left join
TenureDescription TD left join TenureType TT on
TD.Tenure_Status=TT.TenureTypeID on IE.Tenure=TD.TenureDescriptionID where
IE.Department = '1' and TD.Tenure_Status='1' order by FP.FirstName. You have
an error in your SQL syntax near 'left join TenureType TT on
TD.Tenure_Status=TT.TenureTypeID on IE.Tenure=TD.Tenu' at line 1
I'm so lost
David Sears wrote:
One more thought: I removed the parentheses altogether (with no other
changes) and the query still parses correctly on 4.0.14. You might
try the same on your server.
-Original Message-
From: Jordan Morgan [mailto:[EMAIL PROTECTED]
Sent: Sunday, September 14, 2003 3:00 PM
To: David Sears
Cc: [EMAIL PROTECTED]
Subject: Re: Newbies asking help with LEFT JOIN
shoot, my MySQL is of version 3.23.54. :(( how do I modify mine to
make it work then? Thanks!
** it's hard to be a newbie**
David Sears wrote:
I see the syntax error output on your search page. I've tested the
query against empty tables on mysql version 4.0.14 without error. I
copied the query from the output of your search page to my client,
and it still doesn't error on 4.0.14.
I guess I should have asked which version of the server you're
using. I'm not sure when all the various joins took effect. There
may be some such info in the mysql docs.
-Original Message-
From: Jordan Morgan [mailto:[EMAIL PROTECTED]
Sent: Sunday, September 14, 2003 2:45 PM
To: David Sears
Cc: [EMAIL PROTECTED]
Subject: Re: Newbies asking help with LEFT JOIN
I tried the parentheses () first and still it didn't work. :(
David Sears wrote:
That's parentheses () around the inner join, not braces {}.
-Original Message-
From: Jordan Morgan [mailto:[EMAIL PROTECTED]
Sent: Sunday, September 14, 2003 12:56 PM
To: David Sears
Cc: [EMAIL PROTECTED]
Subject: Re: Newbies asking help with LEFT JOIN
Thanks for the prompt response David. However, it still doesn't
work.
The search page is here:
http://www.jordymaeproductions.com/PHP/latest/SearchFaculty.php4
I have a faculty from IT department named Debbie Farmer who is an
instructor with a tenure status of Not on Tenure Track. So if I
typed in D in FirstName, L in LastName, chose Instructor in Rank
and Not on Tenure Track in Tenure Status field, her record was
pulled. But if I changed the the tenure status to the wrong tenure
status, her record still shows up. The query was supposed to
trigger when if
(!empty($lname) !empty($fname)
!empty($rank) !empty($tenure)). So I don't know what's missing to
cause her record pulled out even without the incorrect tenure
status.
I don't like to type too much either so I gotta learn to use the
table reference thingy. ;)
Any ideas to solve my query error?
Thanks millions!
Jordan
David Sears wrote:
I didn't have the fortitude to populate the tables with data, so
I don't know if this will work for you, but to use explicit
joins try something like the sample below. (Note that I don't
like to type as much as you do:-)
select distinct IE.title, FP.LastName, FP.FirstName from
InstitutionEmployment IE left join FacultyPerson FP on
IE.FacultyMember=FP.FacultyPersonID
left join (TenureDescription TD
inner join TenureType TT on TD.Tenure_Status=TT.TenureTypeID) on
IE.Tenure=TD.TenureDescriptionID where IE.Department = '$dept'
and FP.LastName like
Re: Unexpected $ on line XX
Thanks! Paul DuBois wrote: At 3:29 PM -0400 9/14/03, Jordan Morgan wrote: Hi, Can anyone of you kindly tell me what that error usually means? The line number given is always the last line of the script and I'm sure it means something in particular but I just don't know. Parse error: parse error, unexpected $ in XX.php4 on line 167 It means your PHP script is malformed. But it's not a MySQL issue. Thanks! Jordan -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jordan Morgan Information Analyst - GeorgiaFIRST HRMS Project Board of Regents Office of Information and Instructional Technology 1865 West Broad Street, Athens, GA 30606-3539 Phone: (706) 369-6232 Fax: (706) 369-6429 mailto:[EMAIL PROTECTED] http://www.usg.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie Question: how to set field a lookup field from another table?
Hi, I'm very new to MySQL. I normally use MS Access but my project needs MySQL. I'm able to set a field(categoryID) in table A(product) a lookup field to another table(category) in Access but I can't find anywhere that teaches me how to do that in MySQL. I'm managing MySQL through a web interface provided by my hosting company. I can use SQL statements as well as clicking a few buttons. Any advice is highly appreciated. Thanks a bunch! Jordan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
