Re: [PHP-DB] select from two tables
I would try replacing: echoi by ; echo ($albumby[xoops_artist.artist]); echoibr; with this: echoi by ; echo $albumby[artist]; echoibr; or even better this(notice the dots instead of different echos and the single qoutes): echo'i by '.$albumby[artist].'ibr'; I would say that you have a coloumn called artist in both tables. that is why it echoing nothing. I have also had trouble with SELECT * with cross-table joins. I would replace it with the fields you need only. Like this: SELECT * xoops_artist.artist, xoops_album.artist etc... this would fixed the problem with coloumns with the same name also -- JJ Harrison [EMAIL PROTECTED] www.tececo.com Herman Verkade [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... Well, I'm just a beginner myself, but I would say that: echoi by ; echo ($albumby[xoops_artist.artist]); echoibr; should at least be: echoi by ; echo ($album[xoops_artist.artist]); echoibr; or even: echoi by ; echo ($album[artist]); echoibr; Hope this helps, Herman -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: n00b deleting entries from db.
thx. Dan Koken [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... Jj Harrison wrote: I know how to delete stuff from a db. Now I want to delete all the earliest entires from the database except the latest 75 entries. Is it easy or hard? I would say easy.. I know about limit but it doesn't seem to work the other way. Assuming you have nothing to associate your records with time of entry, I agree LIMIT seems to be the easiest. If you have sub query try something like this. DELETE from file limit (SELECT count(*) - 75, ',', count(*) from file) If no sub query you probably have to do it with 2 queries. One to get the number of records and the other to substitute the variable $num_records into the DELETE as DELETE from file file limit $num_records - 75, $num_records HTH Dan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] PHP4-PostgreSQL-Solaris
Hi! I´ve got a simple question: I´m running PostgreSQL on SPARC-Solaris 8. Now I want to use PHP4 with this database, e.g. a kind webinterface that reads data from the database. But PHP can´t connect to PostgreSQL. I know now that I compiled PHP wrong. But what are the exact compiling options I have to use? Thx in advance Jan -- GMX - Die Kommunikationsplattform im Internet. http://www.gmx.net -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Percentage...
How, using the group by and where statments can I return the percentage of something? ie: pid| oid| uid | 1 |2 |1 1 |2 |1 1 |2 |1 1 |1 |1 would return oid | percent 2| 75 1| 25 Thx, -- JJ Harrison [EMAIL PROTECTED] www.tececo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Percentage...
Plz ignore this. found it after more searching... -- JJ Harrison [EMAIL PROTECTED] www.tececo.com Jj Harrison [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... How, using the group by and where statments can I return the percentage of something? ie: pid| oid| uid | 1 |2 |1 1 |2 |1 1 |2 |1 1 |1 |1 would return oid | percent 2| 75 1| 25 Thx, -- JJ Harrison [EMAIL PROTECTED] www.tececo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: Percentage...
Hi JJ. I know we all should do what you did, but I've seen this kind of mails quite a lot lately in these lists. I'm sure that many of you, like me, keep these mails for further reading and reference... it would be nice if you also state what was the answer to your original question. I mean, if you came up with a question that some of us might have in the future, it would be nice to have the answer off-line (in our mail soft) instead of going on-line for search... Shear it with others! Thanks, C. -Original Message- From: JJ Harrison [mailto:[EMAIL PROTECTED]] Sent: Monday, July 29, 2002 7:03 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] Re: Percentage... Plz ignore this. found it after more searching... -- JJ Harrison [EMAIL PROTECTED] www.tececo.com Jj Harrison [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... How, using the group by and where statments can I return the percentage of something? ie: pid| oid| uid | 1 |2 |1 1 |2 |1 1 |2 |1 1 |1 |1 would return oid | percent 2| 75 1| 25 Thx, -- JJ Harrison [EMAIL PROTECTED] www.tececo.com -- 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] Dropdown list question...
I am populating a dropdown list from a database. This is working
fine with one minor glitch... For some reason, the first entry in the
database does not seem to be getting populated into the list. I do not see
anything wrong, and was hoping that someone else might be able to spot a
problem. Here is the code that generates the dropdown list:
$query_systems = SELECT Name FROM systems;
$systems = mysql_query($query_systems, $Test) or die(mysql_error());
$row_systems = mysql_fetch_assoc($systems);
$totalRows_systems = mysql_num_rows($systems);
$sys_list = select size=\1\ name=\system\\n;
$sys_list .= optionSystem Name/option\n;
$sys_list .= option---/option\n;
while($name = mysql_fetch_row($systems)) {
$sys_list .= option$name[0]/option\n;
}
$sys_list .= /select\n;
Thanks in advance for the help.
Scott Nipp
Phone: (214) 858-1289
E-mail: [EMAIL PROTECTED]
Web: http:\\ldsa.sbcld.sbc.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dropdown list question...
I am populating a dropdown list from a database. This is working
fine with one minor glitch... For some reason, the first entry in the
database does not seem to be getting populated into the list. I do not see
anything wrong, and was hoping that someone else might be able to spot a
problem. Here is the code that generates the dropdown list:
$query_systems = SELECT Name FROM systems;
$systems = mysql_query($query_systems, $Test) or die(mysql_error());
$row_systems = mysql_fetch_assoc($systems);
$totalRows_systems = mysql_num_rows($systems);
$sys_list = select size=\1\ name=\system\\n;
$sys_list .= optionSystem Name/option\n;
$sys_list .= option---/option\n;
while($name = mysql_fetch_row($systems)) {
$sys_list .= option$name[0]/option\n;
}
$sys_list .= /select\n;
mysql_fetch_assoc (http://www.php.net/manual/en/function.mysql-fetch-assoc.php) and
mysql_fetch_row (http://www.php.net/manual/en/function.mysql-fetch-row.php)
both return a row.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Dropdown list question...
Thanks for the answers. This has resolved my problem.
-Original Message-
From: Andrey Hristov [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 29, 2002 11:21 AM
To: NIPP, SCOTT V (SBCSI)
Cc: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Dropdown list question...
- Original Message -
From: NIPP, SCOTT V (SBCSI) [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, July 29, 2002 7:12 PM
Subject: [PHP-DB] Dropdown list question...
I am populating a dropdown list from a database. This is working
fine with one minor glitch... For some reason, the first entry in the
database does not seem to be getting populated into the list. I do not
see
anything wrong, and was hoping that someone else might be able to spot a
problem. Here is the code that generates the dropdown list:
$query_systems = SELECT Name FROM systems;
$systems = mysql_query($query_systems, $Test) or die(mysql_error());
$row_systems = mysql_fetch_assoc($systems);
Here is your error. You do fetch_assoc and then you are not using it.
Comment it out.
$totalRows_systems = mysql_num_rows($systems);
$sys_list = select size=\1\ name=\system\\n;
$sys_list .= optionSystem Name/option\n;
$sys_list .= option---/option\n;
while($name = mysql_fetch_row($systems)) {
$sys_list .= option$name[0]/option\n;
}
$sys_list .= /select\n;
Thanks in advance for the help.
Scott Nipp
Phone: (214) 858-1289
E-mail: [EMAIL PROTECTED]
Web: http:\\ldsa.sbcld.sbc.com
Regards,
Andrey Hristov
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Is there a size limit in mysql_fetch_array?
I'm using mysql_fetch_array (with mysql_query) to retrieve data and very simply display it in an HTML table, which has been working fine up until I started fetching data from a TEXT column that has greater than 255 characters in it. I can see in my database that all the data is there, but mysql_fetch_array will only grab the first 255 bytes of it. Is there a limit built into mysql_fetch_array? What can you recommend I use instead, to make sure I'm always getting the complete contents of a database column? Thank you very much, in advance, Paul Worthington [EMAIL PROTECTED] -- The views expressed here are those of the user, not necessarily those of Evolving Systems, Inc. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] time field query problems.
On Tuesday 30 July 2002 02:41, Steve Bradwell wrote: Sorry to repost but I typed in the wrong sql statement in my previous post. For some reason the below statement is not working. Can anyone tell me why? Select EDIT_LOCK from ordmaster where EDIT_LOCK + INTERVAL 10 MINUTE now() AND ORDER_NO = '5' AND EDIT_LOCK 0; I think you have to use something like: ... WHERE DATE_ADD(EDIT_LOCK, ...) ... Check manual for details. -- Jason Wong - Gremlins Associates - www.gremlins.com.hk Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* Intuition, however illogical, is recognized as a command prerogative. -- Kirk, Obsession, stardate 3620.7 */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] time field query problems.
Ya I checked and the manual said that with a version 3.23 or higher you can use + and - signs instead of the date_add subtract. Either way it just returns null. Ever tried somthing like this? Thanks, Steve. -Original Message- From: Jason Wong [mailto:[EMAIL PROTECTED]] Sent: Monday, July 29, 2002 3:13 PM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] time field query problems. On Tuesday 30 July 2002 02:41, Steve Bradwell wrote: Sorry to repost but I typed in the wrong sql statement in my previous post. For some reason the below statement is not working. Can anyone tell me why? Select EDIT_LOCK from ordmaster where EDIT_LOCK + INTERVAL 10 MINUTE now() AND ORDER_NO = '5' AND EDIT_LOCK 0; I think you have to use something like: ... WHERE DATE_ADD(EDIT_LOCK, ...) ... Check manual for details. -- Jason Wong - Gremlins Associates - www.gremlins.com.hk Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* Intuition, however illogical, is recognized as a command prerogative. -- Kirk, Obsession, stardate 3620.7 */ -- 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] Auto Increment Problems....
Once again... ALTER TABLE table1 AUTO_INCREMENT = 500 Dan On Monday, July 29, 2002, at 10:44 PM, [EMAIL PROTECTED] wrote: rite, my primary key column (id) is set to auto_increment as usual which is very handy. But when I delete a row, the auto_increment just keeps incrementing and there's this 'hole' left where I deleted the row! Apart from this looking ugly, it poses another problem. In my PHP script where I can add new rows, I query the table, checking how many rows in the table altogether and set the new id as the next number, but this doesnt work if theres 'holes' in the id field, as the new record tries to overwrite another id. So I've 2 questions 1) Can the next auto_increment value be 'set' by a SQL query 2) Can I get a SQL query to INSERT INTO the first 'hole' it finds in the ID column?? TIA -- 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] Auto Increment Problems....
why, is this question asked a lot??? anywa, thanks, i was gonna try something like that from something i read in the mysql manual, but it only mentioned it passing and didnt give an example or anything. tanx Daniel Brunner [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... Once again... ALTER TABLE table1 AUTO_INCREMENT = 500 Dan On Monday, July 29, 2002, at 10:44 PM, [EMAIL PROTECTED] wrote: rite, my primary key column (id) is set to auto_increment as usual which is very handy. But when I delete a row, the auto_increment just keeps incrementing and there's this 'hole' left where I deleted the row! Apart from this looking ugly, it poses another problem. In my PHP script where I can add new rows, I query the table, checking how many rows in the table altogether and set the new id as the next number, but this doesnt work if theres 'holes' in the id field, as the new record tries to overwrite another id. So I've 2 questions 1) Can the next auto_increment value be 'set' by a SQL query 2) Can I get a SQL query to INSERT INTO the first 'hole' it finds in the ID column?? TIA -- 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] Auto Increment Problems....
Yeah, it's asked a lot!! And I've always answered that question( it's not the only question I answer!!! ) But Good Luck!! And your welcome!!! Dan On Monday, July 29, 2002, at 10:51 PM, [EMAIL PROTECTED] wrote: why, is this question asked a lot??? anywa, thanks, i was gonna try something like that from something i read in the mysql manual, but it only mentioned it passing and didnt give an example or anything. tanx Daniel Brunner [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... Once again... ALTER TABLE table1 AUTO_INCREMENT = 500 Dan On Monday, July 29, 2002, at 10:44 PM, [EMAIL PROTECTED] wrote: rite, my primary key column (id) is set to auto_increment as usual which is very handy. But when I delete a row, the auto_increment just keeps incrementing and there's this 'hole' left where I deleted the row! Apart from this looking ugly, it poses another problem. In my PHP script where I can add new rows, I query the table, checking how many rows in the table altogether and set the new id as the next number, but this doesnt work if theres 'holes' in the id field, as the new record tries to overwrite another id. So I've 2 questions 1) Can the next auto_increment value be 'set' by a SQL query 2) Can I get a SQL query to INSERT INTO the first 'hole' it finds in the ID column?? TIA -- 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 Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP] Auto Increment Problems....
Op dinsdag 30 juli 2002 05:44, schreef Georgie Casey:
So I've 2 questions
INSERT TABLE id values('');
leave it empty
1) Can the next auto_increment value be 'set' by a SQL query
2) Can I get a SQL query to INSERT INTO the first 'hole' it finds in the ID
column??
I don't think so. You can mark the row, when you delete it.
Make the field mark (or you a field you already have) b.e small int (1)
In stead of deleting, you insert empty values and set mark to 1
If you insert a row, you need a extra query:
SELECT id where mark=1 SORT BY id ASC
If no row found insert with id (see above) else use the id found;
UPDATE TABLE SET i mark='0'. WHERE id='$first_ID_with_mark_is_1'
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Auto Increment Problems....
On Mon, 29 Jul 2002 20:44:54 -0700 Georgie Casey Georgie Casey [EMAIL PROTECTED] wrote: rite, my primary key column (id) is set to auto_increment as usual which is very handy. But when I delete a row, the auto_increment just keeps incrementing and there's this 'hole' left where I deleted the row! Apart from this looking ugly, it poses another problem. In my PHP script where I can add new rows, I query the table, checking how many rows in the table altogether and set the new id as the next number, but this doesnt work if theres 'holes' in the id field, as the new record tries to overwrite another id. So I've 2 questions 1) Can the next auto_increment value be 'set' by a SQL query 2) Can I get a SQL query to INSERT INTO the first 'hole' it finds in the ID column?? TIA Why do you want to create your own ID when one is created for you automatically? If you want to know how many rows are in a table, you can use SELECT COUNT(*) FROM table (at least using MySQL). If you need the ID of the last row inserted to be able to stick that ID into another table, using PHP, try mysql_insert_id(). -- Raquel Happiness is not the absence of conflict; it is the ability to cope with it. --Unknown -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] dynamic pages
I would like to have the same page (test.php) have different text in it depending on a database entry. which is easy enough. but I want it to be on the fly. example list would be created on the fly depending on databse entries. which ever entry was picked (lets say STLT) it would open up my template test.php and it would but the STLT info on the page. Is there a way to do this without turning on Global_Variables which come standard off in php4.2.2 I'm using apache 2.0.39, PHP4.2.2, FreeBSD4.6, Mysql3.23.51 My database entries would be deparmentpageartical template stlt1body of page test.php aps 1body of page test.php srp 1body of page test2.php main 1body of page test.php the list would be created by a query that looks for all page 1 entries from there the main page would show up(default), but once you pick from the list it would show the database enteries for that department. |---|--- |main | |STLT | STLT was picked show STLT artical database entry |aps | this is are STLT page |srp | | | |---|--- |---|--- |main | |stlt | APS was picked show APS artical database entry |APS | this is are APS page |srp | | | |---|--- I assume that Gobal_Variables are off for a reason. Rolando -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Auto Increment Problems....
Firstly, don't cross post unless the question *really* has to do with both (in this
case, it should be just db).
Secondly, your id field should only be used as a reference to a row (not showing order
of record). Auto increments are exactly that, the rdbms will take care of creating the
increment.
eg. you have this table
tblName
ID | field1 | field2
Your insert sql should be something like this:
INSERT INTO tblName VALUES ('','value1', 'value2')
OR
INSERT INTO tblName SET field1 = 'value1', field2 = 'value2'
The reason why the 'hole' is there, is to maintain data integrity. Say you have two
tables and they relate to each other (through the id). If you delete a record from one
and it relates to something in the other table, if you add a new record using the old
id, it will join with the second table, when it shouldn't. Confusing? yeah its just
cause i can't explain it right.
If you want to use numbering for your records, create it dynamically when you display
the data.
Adam
--- Original Message ---
rite,
my primary key column (id) is set to auto_increment as usual which is very
handy. But when I delete a row, the auto_increment just keeps incrementing
and there's this 'hole' left where I deleted the row!
Apart from this looking ugly, it poses another problem. In my PHP script
where I can add new rows, I query the table, checking how many rows in the
table altogether and set the new id as the next number, but this doesnt work
if theres 'holes' in the id field, as the new record tries to overwrite
another id.
So I've 2 questions
1) Can the next auto_increment value be 'set' by a SQL query
2) Can I get a SQL query to INSERT INTO the first 'hole' it finds in the ID
column??
TIA
[PHP-DB] Auto Increment Problems....
rite, my primary key column (id) is set to auto_increment as usual which is very handy. But when I delete a row, the auto_increment just keeps incrementing and there's this 'hole' left where I deleted the row! Apart from this looking ugly, it poses another problem. In my PHP script where I can add new rows, I query the table, checking how many rows in the table altogether and set the new id as the next number, but this doesnt work if theres 'holes' in the id field, as the new record tries to overwrite another id. So I've 2 questions 1) Can the next auto_increment value be 'set' by a SQL query 2) Can I get a SQL query to INSERT INTO the first 'hole' it finds in the ID column?? TIA -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] ORDER BY from 2 tables
i assume this is a simple question... how can I SELECT * FROM 2 different tables in the same query, ORDER BYing the 'hits' column, which both tables have. eg, 2 tables i have are similiar and i want to merge them and then select everything ordering by hits -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: ORDER BY from 2 tables
See if your database supports the UNION clause... On Mon, 29 Jul 2002, Georgie Casey wrote: i assume this is a simple question... how can I SELECT * FROM 2 different tables in the same query, ORDER BYing the 'hits' column, which both tables have. eg, 2 tables i have are similiar and i want to merge them and then select everything ordering by hits -- PHP General 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: time field query problems.
Hi. On Mon 2002-07-29 at 14:41:30 -0400, [EMAIL PROTECTED] wrote: Sorry to repost but I typed in the wrong sql statement in my previous post. Ah. Okay. For some reason the below statement is not working. Can anyone tell me why? Select EDIT_LOCK from ordmaster where EDIT_LOCK + INTERVAL 10 MINUTE now() AND ORDER_NO = '5' AND EDIT_LOCK 0; Regardless of the original problem, you should use EDIT_LOCK NOW() - INTERVAL 10 MINUTE because this variant has no expression on the left side and therefore could use an index, if there is one on EDIT_LOCK (MySQL does not optimize expressions, in the few cases where this would be possibible). -EDIT_LOCK is a MySQL (ver 3.23.49-max) time field, allows nulls, default is NULL. Oh. Is it really a TIME field, not TIMESTAMP? In this case you would compare a time (without date) with a whole datetime value from NOW(). These values cannot be compared reasonable. Additionally +- INTERVAL only works on DATE or DATETIME/TIMESTAMP values, otherwise you have to use DATE_SUB/DATE_ADD. In this case, you would need something like SELECT EDIT_LOCK FROM ordmaster WHERE EDIT_LOCK DATE_FORMAT( NOW() - INTERVAL 10 MINUTE, %T ) AND ORDER_NO = '5' AND EDIT_LOCK 0; This does not handle the special cases on day change, but I presume that this already had been thought of, or else a TIME instead of a DATETIME field makes no sense. Greetings, Benjamin. -- [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Time Allocation Issue
I'm writing a web application in PHP to do referee scheduling for soccer games. For each game (a single row in a table) I have a cell for referee. The cell stores an integer that I can do a join on with another table of people. I need a way to prevent people from being able to schedule themselves for multiple slots at the same time. (All I care about is start time, for now I'm going to ignore the issue of a game ending after the next one has started.) I have tried doing just a unique index on referee, date, and time but the issue is I use a 0 to indicate that the slot is open. Because of that I don't see a way to do it natively in mysql as such each time I do an insert I think I am going to need to do a select right before to make sure there no conflicts. Is there any better more efficient way to do this? Perhaps natively in mysql. TIA Thanks Jefferson Cowart [EMAIL PROTECTED] Support Open Instant Messaging Protocols http://www.petitiononline.com/openIM/petition.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] time field query problems.
Steve, For some reason the below statement is not working. Can anyone tell me why? Select EDIT_LOCK from ordmaster where EDIT_LOCK + INTERVAL 10 MINUTE now() AND ORDER_NO = '5' AND EDIT_LOCK 0; -EDIT_LOCK is a MySQL (ver 3.23.49-max) time field, allows nulls, default is NULL. If this cannot be done in a query, whats the best way to compare time in php? The best way to compare time in PHP is to use the MySQL RDBMS that is managing/retrieving the data for you. Recommendation 1: do not use a Time field (you did mean the back 'half' of a Date-time field didn't you?). Because you are (apparently only) using this field to temporarily lock a row, the value is only ever used for computation (cf display). A Timestamp field is best for computation - a Time field for presentation. Consider also storing such data as an integer field or beware the automatic update feature for Timestamp fields. Recommendation 2: re-consider the (default) use of NULL - this may be the root of the question you're asking: what if the row has never been 'locked' and attempt the (above) SELECT? (then the last comparison clause would be illogical) If the default were zero (0 or 00:00:00) and the retrieval logic updated slightly, things should be less complicated. Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Time Allocation Issue
Jefferson, I'm writing a web application in PHP to do referee scheduling for soccer games. For each game (a single row in a table) I have a cell for referee. The cell stores an integer that I can do a join on with another table of people. I need a way to prevent people from being able to schedule themselves for multiple slots at the same time. (All I care about is start time, for now I'm going to ignore the issue of a game ending after the next one has started.) I have tried doing just a unique index on referee, date, and time but the issue is I use a 0 to indicate that the slot is open. Because of that I don't see a way to do it natively in mysql as such each time I do an insert I think I am going to need to do a select right before to make sure there no conflicts. Is there any better more efficient way to do this? Perhaps natively in mysql. TIA Speaking for referees everywhere, I'd say not to bother with 'time' because I wouldn't want to cope with more than one 90-minute game in a day - but perhaps you're working on rapid-fire short-game tournaments or somesuch... There are two issues here: firstly has a referee been assigned to control each/every game - or does this (one) game have a referee assigned to it? Secondly, when a referee is assigned, is (s)he in fact 'available'. Sounds like we should be using a project planning package! The first question is answered by SELECT gameId FROM games WHERE refereeId = 0; and/or SELECT refereeId FROM games WHERE gameId = ?; The second 'fails' if you can SELECT gameId FROM games WHERE refereeId = ? AND gameDate = ? AND gameTime = ? (ie no rows returned implies the referee is available, one row that (s)he is assigned, and more than one row that you have a scheduling snafu!) The two queries are logically quite separate. Will attempting to implement both aspects in a single query actually help your system? MySQL: Insofar as your realisation that games occupy time slots 'from' and 'to' moments in time, you might like to check out the BETWEEN comparator. Many consider it good practise to 'check' with a SELECT before performing an UPDATE or INSERT. Soccer refereeing: Please consider that hard-working referees deserve a rest between matches! Also that running another game is not the only reason why a referee might not be available to you. Hope this helps, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE: Time Allocation Issue
Thank you that did exactly what I was looking for. Thanks Jefferson Cowart [EMAIL PROTECTED] Support Open Instant Messaging Protocols http://www.petitiononline.com/openIM/petition.html -Original Message- From: Ryan Fox [mailto:[EMAIL PROTECTED]] Sent: Monday, July 29, 2002 16:08 To: Jefferson Cowart Cc: [EMAIL PROTECTED] Subject: Re: Time Allocation Issue I have tried doing just a unique index on referee, date, and time but the issue is I use a 0 to indicate that the slot is open. snip Is there any better more efficient way to do this? Use null to indicate the slot is open. Your unique index can have multiple null values, but not multiple zero values. I believe this is what you are looking for. Cheers, Ryan Fox [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] time field query problems.
Sorry to repost but I typed in the wrong sql statement in my previous post. For some reason the below statement is not working. Can anyone tell me why? Select EDIT_LOCK from ordmaster where EDIT_LOCK + INTERVAL 10 MINUTE now() AND ORDER_NO = '5' AND EDIT_LOCK 0; -EDIT_LOCK is a MySQL (ver 3.23.49-max) time field, allows nulls, default is NULL. If this cannot be done in a query, whats the best way to compare time in php? Thanks, Steve. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
