[PHP-DB] SELECT WHERE length of content question
Is there a command in mySQL that would allow me to SELECT the rows where the `fax` column is more than 11 characters long? OR Do I need to use PHP to assess this? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
Re: [PHP-DB] SELECT WHERE length of content question
Have a look at this: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_char-length On Thu, Mar 10, 2011 at 9:49 AM, Ron Piggott ron.pigg...@actsministries.org wrote: Is there a command in mySQL that would allow me to SELECT the rows where the `fax` column is more than 11 characters long? OR Do I need to use PHP to assess this? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
Re: [PHP-DB] SELECT WHERE length of content question
On Wed, Mar 9, 2011 at 17:49, Ron Piggott ron.pigg...@actsministries.org wrote: Is there a command in mySQL that would allow me to SELECT the rows where the `fax` column is more than 11 characters long? There is. SELECT * FROM tblName WHERE CHAR_LENGTH(fax) = 11; (Presuming you meant greater than or equal to eleven, as in an invalid US/Canadian phone number.) -- /Daniel P. Brown Network Infrastructure Manager http://www.php.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT where something exists but something else does not
Hi! I think I have solved your problem... I have attached an sql file to create the sample DB I created for my test and a PHP file for you to run to see how I have implemented it. First create the tables with the .sql file an then execute the .php file from your browser. Hope these help. - Original Message - From: Beau Lebens [EMAIL PROTECTED] To: PHP DB (E-mail) [EMAIL PROTECTED] Sent: Thursday, February 21, 2002 3:59 AM Subject: [PHP-DB] SELECT where something exists but something else does not Hey guys, I am a little stuck here, i know how to botch a solution together using a bunch of queries and PHP manipulation, but i am sure there is a more elegant way to do this one; i have some tables (i won't put their full defs, just bits that are relevant (they are huge)) TABLE students FIELDS studentID fname lname title studentNo TABLE theses FIELDS thesisID studentID title TABLE thesis_reports FIELDS reportID thesisID year now, what we are dealing with here is records of theses and the reports that students are required to submit relating to them (yearly). i need to be able to pull up a record of students who have a record of a thesis (something in table theses) but do NOT have an entry in thesis_reports for this year yet. an attempt at some SQL that sort of pretends to do the right thing :) SELECT DISTINCT(students.studentID), students.title, students.fname, students.lname, students.studentNo FROM students, theses, thesis_reports WHERE theses.studentID=students.studentID AND thesis_reports.thesisID=theses.thesisID AND thesis_reports.year != '2002' does that make sense? what it actually returns is just any student with any record in the DB under thesis_reports that doesn't eqal '2002', even if they happen to also have one that *is* for 2002 (ie. my test student has a report for 1999, 2001 and 2002, but still gets selected by that -- Beau Lebens, Technical Officer Science and Mathematics Education Centre Curtin University of Technology, GPO Box U1987 Perth, Western Australia 6845 CRICOS provider code 00301J t: +61 8 9266-7297 (has voice-mail) f: +61 8 9266-2503 (ATT: Beau Lebens) e: [EMAIL PROTECTED] w: http://learnt.smec.curtin.edu.au/ -- 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] SELECT where something exists but something else does not
I think I forgot something usefull... - Original Message - From: Beau Lebens [EMAIL PROTECTED] To: PHP DB (E-mail) [EMAIL PROTECTED] Sent: Thursday, February 21, 2002 3:59 AM Subject: [PHP-DB] SELECT where something exists but something else does not Hey guys, I am a little stuck here, i know how to botch a solution together using a bunch of queries and PHP manipulation, but i am sure there is a more elegant way to do this one; i have some tables (i won't put their full defs, just bits that are relevant (they are huge)) TABLE students FIELDS studentID fname lname title studentNo TABLE theses FIELDS thesisID studentID title TABLE thesis_reports FIELDS reportID thesisID year now, what we are dealing with here is records of theses and the reports that students are required to submit relating to them (yearly). i need to be able to pull up a record of students who have a record of a thesis (something in table theses) but do NOT have an entry in thesis_reports for this year yet. an attempt at some SQL that sort of pretends to do the right thing :) SELECT DISTINCT(students.studentID), students.title, students.fname, students.lname, students.studentNo FROM students, theses, thesis_reports WHERE theses.studentID=students.studentID AND thesis_reports.thesisID=theses.thesisID AND thesis_reports.year != '2002' does that make sense? what it actually returns is just any student with any record in the DB under thesis_reports that doesn't eqal '2002', even if they happen to also have one that *is* for 2002 (ie. my test student has a report for 1999, 2001 and 2002, but still gets selected by that -- Beau Lebens, Technical Officer Science and Mathematics Education Centre Curtin University of Technology, GPO Box U1987 Perth, Western Australia 6845 CRICOS provider code 00301J t: +61 8 9266-7297 (has voice-mail) f: +61 8 9266-2503 (ATT: Beau Lebens) e: [EMAIL PROTECTED] w: http://learnt.smec.curtin.edu.au/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php thesis_test.zip Description: application/compressed -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] SELECT where something exists but something else does not
Hey guys, I am a little stuck here, i know how to botch a solution together using a bunch of queries and PHP manipulation, but i am sure there is a more elegant way to do this one; i have some tables (i won't put their full defs, just bits that are relevant (they are huge)) TABLE students FIELDS studentID fname lname title studentNo TABLE theses FIELDS thesisID studentID title TABLE thesis_reports FIELDS reportID thesisID year now, what we are dealing with here is records of theses and the reports that students are required to submit relating to them (yearly). i need to be able to pull up a record of students who have a record of a thesis (something in table theses) but do NOT have an entry in thesis_reports for this year yet. an attempt at some SQL that sort of pretends to do the right thing :) SELECT DISTINCT(students.studentID), students.title, students.fname, students.lname, students.studentNo FROM students, theses, thesis_reports WHERE theses.studentID=students.studentID AND thesis_reports.thesisID=theses.thesisID AND thesis_reports.year != '2002' does that make sense? what it actually returns is just any student with any record in the DB under thesis_reports that doesn't eqal '2002', even if they happen to also have one that *is* for 2002 (ie. my test student has a report for 1999, 2001 and 2002, but still gets selected by that -- Beau Lebens, Technical Officer Science and Mathematics Education Centre Curtin University of Technology, GPO Box U1987 Perth, Western Australia 6845 CRICOS provider code 00301J t: +61 8 9266-7297 (has voice-mail) f: +61 8 9266-2503 (ATT: Beau Lebens) e: [EMAIL PROTECTED] w: http://learnt.smec.curtin.edu.au/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Select where date is in period
how are the times stored in the db ?
if they are dates or timestamps the comparison will need to be single quoted
instead of:
My attempt, one of many,
$result = mysql_query("select * from plant.eqpt where date_online =
$checkdate AND where date_offline = $checkdate order by type,
size");
generated the same warning.
Still searching for a solution
try
$result = mysql_query("select * from plant.eqpt where date_online
='".$checkdate."' AND date_offline = '".$checkdate."' order by
type,size");
you also had 2 WHERE clauses. there can be only one. like the highlander.
so if $checkdate was 2001-03-22 then the query would look like this to the
db:
select * from plant.eqpt where date_online ='2001-03-22' AND date_offline
='2001-03-22' order by type,size
i haven't used between on dates but it is valid SQL. good for ranges of
numbers.
Steve Brett
Internal Development
EMIS Ltd.
"Privileged and /or Confidential information may be contained in this
message. If you are not the original addressee indicated in this message (or
responsible for delivery of the message to such person), you may not copy or
deliver this message to anyone. In such case, please delete this message,
and notify us immediately. Opinions, conclusions and other information
expressed in this message are not given or endorsed by my firm or employer
unless otherwise indicated by an authorised representative independently of
this message."
Egton Medical Information Systems Limited. Registered in England. No
2117205.
Registered Office: Park House Mews, 77 Back Lane, Off Broadway, Horsforth,
Leeds, LS18 4RF
-Original Message-
From: boclair [mailto:[EMAIL PROTECTED]]
Sent: 22 March 2001 12:45
To: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Select where date is in period
___Morris___
The database has a table of equipments with date fields for
date_online and date_offline.
The requirement is to find which equipments were available on a date
specified by the user, this being a variable created by the user.
My attempts at scripting for the condition where the date_online =
'specified variable' and the date_offline = 'specified variable'
totally fail for syntax says mySQL (and possibly for method)
___Richard___
Have you checked at the BETWEEN (x and y) clause?
e.g.
SELECT * FROM table WHERE searchdate BETWEEN (date_online AND
date_offline)
Not tested this on MySQL, but the above is perfectly legal SQL92.
___Morris_
Thanks for that. I searched the Manual, yet again and could not find
this or like syntax. I tried it though and got
Warning: Supplied argument is not a valid MySQL result resource.
My attempt, one of many,
$result = mysql_query("select * from plant.eqpt where date_online =
$checkdate AND where date_offline = $checkdate order by type,
size");
generated the same warning.
Still searching for a solution
Tim Morris
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail:
[EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Select where date is in period
i think i also used = to indicate 'equal or greater to than' instead of =
like you have ...
Steve Brett
Internal Development
EMIS Ltd.
"Privileged and /or Confidential information may be contained in this
message. If you are not the original addressee indicated in this message (or
responsible for delivery of the message to such person), you may not copy or
deliver this message to anyone. In such case, please delete this message,
and notify us immediately. Opinions, conclusions and other information
expressed in this message are not given or endorsed by my firm or employer
unless otherwise indicated by an authorised representative independently of
this message."
Egton Medical Information Systems Limited. Registered in England. No
2117205.
Registered Office: Park House Mews, 77 Back Lane, Off Broadway, Horsforth,
Leeds, LS18 4RF
-Original Message-
From: boclair [mailto:[EMAIL PROTECTED]]
Sent: 22 March 2001 12:45
To: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Select where date is in period
___Morris___
The database has a table of equipments with date fields for
date_online and date_offline.
The requirement is to find which equipments were available on a date
specified by the user, this being a variable created by the user.
My attempts at scripting for the condition where the date_online =
'specified variable' and the date_offline = 'specified variable'
totally fail for syntax says mySQL (and possibly for method)
___Richard___
Have you checked at the BETWEEN (x and y) clause?
e.g.
SELECT * FROM table WHERE searchdate BETWEEN (date_online AND
date_offline)
Not tested this on MySQL, but the above is perfectly legal SQL92.
___Morris_
Thanks for that. I searched the Manual, yet again and could not find
this or like syntax. I tried it though and got
Warning: Supplied argument is not a valid MySQL result resource.
My attempt, one of many,
$result = mysql_query("select * from plant.eqpt where date_online =
$checkdate AND where date_offline = $checkdate order by type,
size");
generated the same warning.
Still searching for a solution
Tim Morris
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail:
[EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Select where date is in period
"boclair" [EMAIL PROTECTED] wrote in message
005101c0b2d2$4e6b3920$[EMAIL PROTECTED]">news:005101c0b2d2$4e6b3920$[EMAIL PROTECTED]...
|
| - Original Message -
| From: boclair [EMAIL PROTECTED]
| To: [EMAIL PROTECTED]
| Sent: Thursday, March 22, 2001 10:45 PM
| Subject: Re: [PHP-DB] Select where date is in period
|
|
|
| ___Morris___
|
| The database has a table of equipments with date fields for
| date_online and date_offline.
|
| The requirement is to find which equipments were available on a
date
| specified by the user, this being a variable created by the user.
|
| My attempts at scripting for the condition where the date_online
=
| 'specified variable' and the date_offline = 'specified variable'
| totally fail for syntax says mySQL (and possibly for method)
|
| ___Richard___
|
| Have you checked at the BETWEEN (x and y) clause?
|
| e.g.
|
|SELECT * FROM table WHERE searchdate BETWEEN (date_online
AND
| date_offline)
|
| Not tested this on MySQL, but the above is perfectly legal SQL92.
|
| ___Morris_
|
| Thanks for that. I searched the Manual, yet again and could not
| find
| this or like syntax. I tried it though and got
| Warning: Supplied argument is not a valid MySQL result resource.
|
| My attempt, one of many,
|
| $result = mysql_query("select * from plant.eqpt where date_online
=
| $checkdate AND where date_offline = $checkdate order by type,
| size");
|
| generated the same warning.
|
|
|
| Please check this solution for syntax and method. MySQL didn't give
| errors or warnings and results were as required.
|
| $result = mysql_query("select * from plant.eqpt where date_online =
| $checkdate $checkdate = date_offline order by type, size");
|
| or can use AND in lieu of
|
| or can place where conditions in brackets as
|
| where (date_online = $checkdate $checkdate = date_offline)
Sorry. Careless as usual. The variable $checkdate was enclosed in
single quotes as
$result = mysql_query("select * from plant.eqpt where date_online =
'$checkdate' '$checkdate' = date_offline order by type, size");
Tim Morris
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Select where date is in period
The database has a table of equipments with date fields for date_online and date_offline. The requirement is to find which equipments were available on a date specified by the user, this being a variable created by the user. My attempts at scripting for the condition where the date_online = 'specified variable' and the date_offline = 'specified variable' totally fail for syntax says mySQL (and possibly for method) I would be grateful for help or a pointer to a reference. Tim Morris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Select where
On Wed, 21 Mar 2001, boclair wrote: This is simple but I cannot see where I am going wrong I have a table members with one of the fields status, varchar(10) The values may be active or retired or deceased or null If I run the select SELECT * FROM members WHERE status = 'deceased'; I getMySQL said: You have an error in your SQL syntax near '\'deceased\';' at line 1 Will somebody show me the correct syntax Tim Morris you need to pass the quotes to mysql, hence you have to backslash them in the php code. Try : $query="SELECT * FROM members WHERE status = \"deceased\""; -- Ben History is curious stuff You'd think by now we had enough Yet the fact remains I fear They make more of it every year. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Select where
- Original Message - From: boclair [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 21, 2001 12:02 AM Subject: [PHP-DB] Select where This is simple but I cannot see where I am going wrong I have a table members with one of the fields status, varchar(10) The values may be active or retired or deceased or null If I run the select SELECT * FROM members WHERE status = 'deceased'; I getMySQL said: You have an error in your SQL syntax near '\'deceased\';' at line 1 Will somebody show me the correct syntax ___ $query = "SELECT * FROM members where status='deceased'" Then just call the $query in your script ___ Thanks, I only gave the mySQL but the php scripting is $deceased = mysql_query(SELECT * FROM members where status=\'deceased\'"); and later while ($myrow = mysql_fetch_row($deceased)) MySQL now says in relation to the *while* line Warning: Supplied argument is not a valid MySQL result resource Tim Morris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Select where
Thanks, I only gave the mySQL but the php scripting is
$deceased = mysql_query(SELECT * FROM members where
status=\'deceased\'");
and later
while ($myrow = mysql_fetch_row($deceased))
MySQL now says in relation to the *while* line
Warning: Supplied argument is not a valid MySQL result resource
Yikes! I hope those are typos, and that you didn't cut and paste that query
from your script. If you did, then it needs some work. Try:
$deceased = mysql_query("SELECT * FROM members where status='deceased'");
You need the leading double quote before SELECT, and you don't need to
escape the single quotes.
- Darryl
--
Darryl Friesen, B.Sc., Programmer/Analyst[EMAIL PROTECTED]
Education Research Technology Services, http://gollum.usask.ca/
Department of Computing Services,
University of Saskatchewan
--
"Go not to the Elves for counsel, for they will say both no and yes"
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
