Re: [PHP-DB] Forum Script
Hello Marcjon, Sunday, March 7, 2004, 6:12:49 PM, you wrote: ML If I'm not being clear or I'm not using proper netiquette, I appologize, ML I'm new to this (mailing lists). Any help would be appreciated! Nothing wrong at all, but it'd much easier for us if you could post your MySQL tables so we can see the relationship between them and see what fields you have, etc. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] How to calculate size of INT(x) field
Hi all, Sorry for such a newbie question! But I have been digging through the O'Reilly MySQL book + MySQL Cookbook and cannot find an answer to what I think is a very simple question: When creating an unsigned INT field, how does the value in brackets (if given) limit the size of the field? I.e. what is the difference in possible maximum values held between an INT(10) and an INT(4)? I know MySQL will create an INT(10) as standard but I'm not sure I need it to be able to hold a number that high. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] only showing partial info of a field in mysql...
Hello Tristan, Thursday, March 4, 2004, 10:27:34 AM, you wrote: TPrsc You know in PHPmyADMIN... TPrsc if you hav a large field, and hit browse, you only see some fo the text... TPrsc is this a PHP thing, or a MySQL thing... more improtantly, how can I do TPrsc that?? I don't know how PHPmyAdmin does it, but you can do this in SQL directly without having to do it in PHP. Look at the SQL functions: LEFT(), MID(), RIGHT() and SUBSTRING(). I.e. SELECT LEFT(longfield, 100) FROM table This will bring back the first 100 characters from the longfield column (assuming it has 100 characters in it). -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT
Hello peppe, Thursday, March 4, 2004, 11:28:30 AM, you wrote: p Hi I have a table users with columns name email and access p In email I have values [EMAIL PROTECTED] etc in access I have values 1,2,3,4,7,8 p How can I filter value 2 ? p Beacause I need only users with value 2 to send email SELECT * FROM users WHERE access=2 -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re[2]: [PHP-DB] SELECT
Hello peppe, Thursday, March 4, 2004, 12:02:40 PM, you wrote: p The problem is in access values are like string 1,2,3,4,5 Sorry, I didn't realise that. In this case FIND_IN_SET (which I think Ignatius suggested too). -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] How do you make an IN span multiple tables?
Hi, I'm a bit stuck with the following: I need to select data from 3 different tables and want to do it via a SELECT ... IN query. Each table has a primary key called thread_ref. I would like to do something like this: SELECT * FROM table1, table2, table3 WHERE thread_ref IN (1,2,3,4,5,6,7,8,9,10) Now the problem is that MySQL complains about the thread_ref being ambiguous - which I know that it is. But putting a table name in-front of it will limit the IN query to that specific table and cause no results to be returned. It is possible that not all of the tables will contain all of the thread_ref's in question. Does anyone have any ideas or is it just not possible using this method? I'm not using MySQL 4 otherwise I would have looked at using a UNION. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] working with ' in SQL
Hello Adam,
Thursday, March 4, 2004, 3:30:32 PM, you wrote:
AW Hi, I have an SQL statement that is ran when data is submitted through a
AW form. When someone types in a word with an ' such as farmer's the SQL
AW statement fails. I tried $_POST[data] =
AW addslashes($_POST[data]); before
AW executing the SQL statement, but the statement still fails. any
AW suggestions? using words without a ' works fine.
$_POST['data'] = addslashes($_POST['data']);
will work - you need the quotes or the array element won't be found.
Here is a function that will addslashes to an entire array (in this
instance $_POST) if you want:
?
function addslash ($item1, $key)
{
$item1 = addslashes($item1);
}
array_walk($_POST, 'addslash');
?
--
Best regards,
Richard Davey
http://www.phpcommunity.org/wiki/296.html
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Alterations + all numeric tables
Hello Pavel,
Tuesday, March 2, 2004, 2:05:44 PM, you wrote:
PL 398(rows) * 37(average) = 14KB, sorting is more faster
PL etc...
I have been reading over the MySQL Manual today (specifically the
Optimisation sections) and one thing caught my eye:
In some cases, MySQL can read rows from the index without even
consulting the datafile. If all columns used from the index are
numeric, only the index tree is used to resolve the query.
I have followed your advice and moved the variable length and char
fields out of my core table so all I am left with are the following:
threadid int(4) unsigned primary
boardid tinyint(3) unsigned
modified timestamp
created timestamp
status enum('l','p','h','d')
My question is - if I change the status field from an enum to a tiny
int (so L=1, P=2, etc) will MySQL consider the table to be purely
numeric? Or would the timestamps stop this from happening? If it does
then maybe I would see some more performance increases.
--
Best regards,
Richard Davey
http://www.phpcommunity.org/wiki/296.html
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Optimising LIMITs - alter table order by...
Hello Pavel, Tuesday, March 2, 2004, 7:20:03 AM, you wrote: PL 2. ALTER TABLE `board` ORDER BY `threadid` DESC; I never knew you could do this - it's quite fascinating this list sometimes :) I do have a question though - if I use this order by table alteration, does MySQL remember it, or will I need to keep doing it every now and again? For example if I view the data in my table I can see the IDs like this: 1 2 3 250 5 6 323 (etc) This is simply where MySQL has gone back and re-used an old slot for a new record. However if I use your query technique for bringing back a block of records knowing they're in the exact right sequence - will MySQL just fragment this again over time? PL Split your table in two tables, like: I can't see a real benefit of doing this. In my mind 675 bytes per thread isn't really that much. The only thing I could split away would be the subject of the thread (associated messages are held elsewhere) but in making a smaller table I'd just have to join the data back in again, surely? -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re[2]: [PHP-DB] Optimising LIMITs - alter table order by...
Hello Pavel,
Tuesday, March 2, 2004, 12:33:17 PM, you wrote:
PL
http://www.mysql.com/documentation/mysql/bychapter/manual_SQL_Syntax.html#ALTER_TABLE
PL ORDER BY allows you to create the new table with the rows in a specific
PL order. Note that the table will not remain in this order after inserts
PL and deletes. In some cases, it might make sorting easier for MySQL if
Figured as much, thanks for the reference. I hope one day MySQL will
produce a manual that isn't one horrendous long document per section.
PL You need to split the table and move post messages to another table if
PL you want to get higher perfomance for (2).
With regard to disk seeking, here is my table structure:
CREATE TABLE `thread` (
`threadid` int(10) unsigned NOT NULL auto_increment,
`site_user_id` varchar(32) NOT NULL default '',
`boardid` tinyint(3) unsigned NOT NULL default '0',
`subject` varchar(200) NOT NULL default '',
`modified` timestamp(14) NOT NULL,
`created` timestamp(14) NOT NULL,
`status` enum('L','P','H','D') NOT NULL default 'L',
PRIMARY KEY (`threadid`),
FULLTEXT KEY `subject` (`subject`),
KEY `boardid` (`boardid`),
KEY `site_user_id` (`site_user_id`),
KEY `created` (`created`),
KEY `status` (`status`)
) TYPE=MyISAM;
As well as moving the subject field to another table (because it's the
only non-defined length field), would another way of speeding up the
disk seek be to turn it from a varchar(200) into a char(200)? I know
it means a larger table size, but MySQL should then be able to
calculate exactly where to jump to in the file?
PL All MySQL functions should be very optimized, but there may be some
PL exceptions. BENCHMARK(loop_count,expression) is a great tool to find out
PL if this is a problem with your query.
I will look at this now.
I had been toying with the idea of creating a cache table that held
the 200 most recent threads, pre-sequenced so I just bring back that
instead of having to query the database unless they go beyond that 200
limit.
Your comments (and MySQL manual posts) have been very useful, thank
you.
--
Best regards,
Richard Davey
http://www.phpcommunity.org/wiki/296.html
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Alterations
Hello Pavel, Tuesday, March 2, 2004, 2:05:44 PM, you wrote: PL Note! IMHO [snip] Wow. I'm speechless - thank you, that was probably the single most useful post I've ever read on this list. You sir are a genius. PL Change `site_user_id` FROM VARCHAR(32) TO CHAR(32) It was a char(32) - it holds an MD5 key, for some reason MySQL-Front exported the description as a varchar, sorry for the confusion! -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] global variables
Hello Torsten,
Monday, March 1, 2004, 4:48:39 PM, you wrote:
TL I build a small web interface to a database.
This should be on the PHP General list, not DB - but even so, I'll
answer your question:
TL if(!isset($page_no))
TL {
TL $page_no = login;
TL }
TL I declared the $page_no in my
TL #variables.php as
TL $v_dec[page_no] = $page_no.
You don't check $v_dec in your manage.php script though, you check to
see if $page_no is set which it never will be, because no-where do you
give it a value.
--
Best regards,
Richard Davey
http://www.phpcommunity.org/wiki/296.html
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Optimising LIMITs
Hi all, I have what is probably a quite standard question and would love to know how you would all approach this scenario: I have a table in a database that has approx. 190,000 records in it. The table is currently 128MB in size and I'm happy that it is well constructed with no data duplication and sensible indexes. I'm using MySQL 3.28.58. and my question is about querying this volume of data efficiently. The table holds forum threads (several years worth) so a common query running on the table is to bring back the top 50 or 100 threads from a board within the forum. To do this I'm using a LIMIT on my query and for the paging through the data (i.e. the first 100 threads, the next 100, etc) I use the LIMIT n,x syntax. Threads are sorted by date (most recent to the top). This is fine and it works well but I'm concerned it's not the most efficient way to do this because the use of LIMIT is causing the whole table to be scanned each time. Here is a typical (simplified) query: SELECT * FROM thread WHERE thread.status='L' AND thread.boardid=1 ORDER BY created DESC LIMIT 100,50 This takes over 1.02 seconds to process. Running an EXPLAIN on my query shows that it's using one key (boardid), but in the Extra field it shows it is having to use a filesort on the data. 5701 rows were used in order to bring back the final 50 - that's every single thread for this board. What I'm trying to figure out is a more efficient way of selecting a block of 50 or 100 records from any point in my table without MySQL needing to sort/check them all first. One thought I did have was that the Primary Key on my table is called threadid - and I thought that instead of bring back the data in my original query, I could collect nothing but the thread IDs and then use a separate query that does something like: SELECT * FROM thread WHERE threadid IN (...) (where ... = all of the IDs previously selected). Would the fact that threadid is my primary key make the original LIMIT/sort faster? Any thoughts appreciated. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re[2]: [PHP-DB] global variables
Hello Torsten, Monday, March 1, 2004, 6:30:11 PM, you wrote: TL You mean this will unset $v_dec[page_no]? Well $page_no at that point in your script equals nothing, so yes - it will always make $v_dec equal nothing. TL This explains why at reload of index.php happens what happend. TL But how to solve it? I _have_ to store some variables anywhere. Yes agreed - but you cannot store them in a variable because they will be lost each time the page reloads. Why not store them in a cookie? -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re[2]: [PHP-DB] Optimising LIMITs
Hello Jeffrey, Monday, March 1, 2004, 5:27:41 PM, you wrote: JM Primary keys aren't any faster than normal keys. They're simply for JM identification. (correct me if I'm wrong, that is my understanding) JM If you don't already have an index on created, I'd do that. I do - here's a question though, what is the best way to have an index? To combine multiple fields into one index - or to have one field per index? I ask because MySQL will appear to select the best possible index for the query and sometimes it's wrong - I read somewhere you can control which index it uses, but if you had a combined index would this make things any more/less effective? JM If you want to optimize things further, I would run the query at midnight JM each morning, and instead of paging through the entire table, just page JM through that temporary table. Figured someone might suggest that - I will have to look into it. Thanks. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP] Re: [PEAR] Re: PEAR DB 1.6.0 has been released
Hello Justin, Monday, February 23, 2004, 4:11:40 PM, you wrote: JP3) A unified architecture for mysql and Oracle. You don't have to JP remember different functions for use with different databases. That abstraction only abstracts the functions to connect to, select and query the database, surely? Oracle/SQL Server/MySQL all use slightly different SQL syntax - so if the query you pass in has to be unique depending on the database in hand - you're already having to remember a stack of system specific SQL and will have to code your application to take into consideration all of those different syntax. What I'm trying to say is that abstracting the connection to the database is the easy part, writing an application that will support them all is another matter. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re[2]: [PHP] Re: [PEAR] Re: PEAR DB 1.6.0 has been released
Hello Justin, Monday, February 23, 2004, 5:30:16 PM, you wrote: JP The real hairy part is dealing with joins as the syntax is very JP different across the DBs. This can be solved by simply not doing them or I wish :) JP using a syntax that most use. Or you can try to use DB_DataObject, but JP I'm not sure it works for all DBs yet. Yeah, I thought as much. I think if I was creating an application to be distributed open-source on the Internet it'd make life easier for other developers if it used something like the PEAR-DB class, but when you're building your own/companies site and know the environment it lessens the value somewhat. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re[2]: [PHP-DB] multi-language site
Hello Ignatius, Thursday, January 15, 2004, 6:41:44 PM, you wrote: IR Gettext is NOT an automated translation service. What you do is mark strings IR to translate in the original application, then have them translated by a IR human. When confronted with a marked string to echo, the application will IR look up in the translated file for the corresponding translation. I use something very similar to this on a few of my sites. I had to get the whole sentences translated though - you cannot just get individual words translated and string them together because as you know, the order of the words varies dramatically from language to language. -- Best regards, Richardmailto:[EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] db php question
Hello, Thursday, January 15, 2004, 7:09:25 PM, you wrote: r I have a very crude script set up that I am using to keep track of IP r addresses, MAC addresses, etc and it dumps into mysql. I am using a r column called id which is my primary key and auto-increments. My r question is that I want to make sure that I don't get duplicate IP r addresses added into the database and right now it is a varchar field r and anything can be put in there. If I change the column and make it my r primary key will that prohibit duplicate entries? Is there a better way r to do this? I am not a programmer either so I am having to hack my way Set the IP column type to be Unique. It doesn't have to be a Primary Key just to be unique. r through this. The second thing that I wanted to do was to be able to r run a simple web form search for either ip addresses or computer name r and return results. From what I have seen this seems pretty complex - Actually, it's pretty simple. If you can post your SQL table structure here someone could show you how to write a PHP script to display that information pretty quickly I would have thought. -- Best regards, Richardmailto:[EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] (PHP-DB) Re: Using PHP with Vb6 to display Spatial Data.
Hello jonah, Monday, January 12, 2004, 9:48:35 AM, you wrote: jm data and process information from the data. The issue is how to jm access the data through the worldwide web. I want to know whether jm PHP can be used to interface with VB and the data accessed using a jm browser. Depends entirely on how the data is stored. SQL Server, SQLLite, Access Databse (ick), another ODBC compliant source, text file, etc etc etc. -- Best regards, Richardmailto:[EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: SQL WHERE datetime NOW
Hello John, Tuesday, January 6, 2004, 5:20:22 PM, you wrote: J On further reading, it appears LIMIT stops the query running once x number J of records are retrieved without sorting the whole table first. Where did you read this? If you profile your query you'll notice that in say a 10,000 record table, if you LIMIT 0,50 but order by something generic (say an ID or date stamp) it'll still order all 10,000 records before returning the 50 you asked for. (One of the main reasons I rarely use LIMIT) J Is there a function to get the top x number of results, as there is in MS J Access? Eg search on an auto-increment and get the last 10 records added? LIMIT 50,-1 Retrieve from row 50 to last. -- Best regards, Richardmailto:[EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PHP/DB speed
Hello Robin,
Sunday, December 21, 2003, 10:15:35 PM, you wrote:
RK The code I am using is thus and is retrieving around 2,500 records:
RK $result = mysql_query($sql)
RK while ($row = mysql_fetch_array($result))
RK {
RK build OPTION stmt
RK }
You're building a select list of 2500 elements? No wonder it's slow.
That must be generating a few hundred KB's worth of HTML on its own,
let alone anything else on the page.
If you're not using all 2500 rows, you need to optimise your query to
reflect this.
--
Best regards,
Richardmailto:[EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re[2]: [PHP-DB] Planetside
Hello Cal, Friday, December 19, 2003, 10:28:19 PM, you wrote: CE What is the Planetside database? It's a pay-to-play on-line multi-player FPS game run by Sony. I don't think you could interface with it directly, at least I'd be pretty surprised if you can. -- Best regards, Richardmailto:[EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dumping database/restoring
Hello John, Thursday, December 4, 2003, 11:01:20 AM, you wrote: DJ The question is how do people out there DJ back up their database residing on a server to a local file? Ideally I would DJ download the database and save on a CD, and be able to restore from this if DJ the worst happened. Personally, I use mysqldump! Free and works :) Creates a .sql file which mysqlimport can re-create your DB from. .sql file is plain text so can be viewed in a text editor, burnt to CD, zipped, etc. -- Best regards, Richardmailto:[EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] cookies
Hello Maris, Thursday, December 4, 2003, 10:40:33 AM, you wrote: MK Could you share with your experience and point out where MK is the problem here. I tried different variations for MK domain parameter in setcookie command but it didnt help It's a security mechanism built into cookies. They cannot be cross-domain. They can cross sub-domains (i.e. *.launchcode.co.uk) but a cookie set for one domain cannot be read by another. Think of the anarchy that would happen if they could (being able to read and mess with say Hotmail cookies from your own site for example). -- Best regards, Richardmailto:[EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re[2]: [PHP-DB] cookies
Hello Maris, Thursday, December 4, 2003, 12:24:10 PM, you wrote: MK img nocache border=0 width=2 height=1 MK src=http://www.cookiedomain.com/cookies/cookies.php MK isnt it refering and setting cookie for www.cookiedomain.com MK even if this HTML file is on another domain? No, because by that point the Header has already been sent. You can't issue a cookie after a header has gone and use it in the same page session. As the PHP manual so elegantly puts it - this is a protocol restriction. Your browser will look for cookies at the start of the page request, not in the middle of it (where your image is). At that point, the header is already sent. -- Best regards, Richardmailto:[EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: Fw: [PHP-DB] PhpmyAdmin from external computers
Hello Andy, Thursday, December 4, 2003, 3:27:11 PM, you wrote: AL Im afraid ive already tried that. Are you sure your MySQL server is actually configured to ALLOW someone to login remotely? I know most of the ones I work with aren't. This isn't a setting you'll find in phpMyAdmin btw. Most MySQL servers are locked down in this way for (very good) security reasons, to the point that it wouldn't surprise me if this wasn't the default after installation (I don't know for sure it is though). -- Best regards, Richardmailto:[EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Remote DB Connection
Hello Omelin,
Wednesday, December 3, 2003, 6:05:52 PM, you wrote:
OM @mysql_connect ('ipaddres-server2', 'DBName', 'DBPwd') ;
OM Is their anything else I need to consider to connect ? ,
The server must allow it :) other than that, not really.
--
Best regards,
Richardmailto:[EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Redirect.
Hello gfmb, Tuesday, December 2, 2003, 11:18:40 AM, you wrote: gli I have a problem.How can I redirect from a PHP script to gli another script in asp, php or a HTML page? gli This script redirect to another script in function of the values of a variable. This isn't really a database related question, is it? See PHP-General. -- Best regards, Richardmailto:[EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Annoying Server Responses
Hello Richard, Tuesday, December 2, 2003, 6:51:40 PM, you wrote: HR Sorry to bother all of you with this, but are any of you still getting HR either of these responses each time you post to this list? Yes (he says waiting for another to arrive). -- Best regards, Richardmailto:[EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Selecting every 50th row
Hi php-db, I have a table with approx. 250,000 rows in it. The primary key is an ID field. All I am wondering is - is there any way at all from MySQL to directly select every 50th row in this table? I.e. row 1, 50, 100, 150, etc and return the ID of each row, ideally with some kind of limit. Even if it was only possible to select the IDs of two rows, i.e. row 1 and row 50, that would be good too. It's almost like I need a step function, like in a FOR NEXT statement, only SQL-side. -- Best regards, Richard mailto:[EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] multiple queries
Hello Cameron, Tuesday, December 2, 2003, 1:08:19 AM, you wrote: CS $sql = SELECT * from dates where store=$store and date=$date; CS ultimately I would like to display data for 3 days on either side of CS this. The following is un-tested, but a quick look over the MySQL manual should firm this up for you: SELECT * FROM dates WHERE store='$store' AND date DATE_SUB(now(), interval 3 day) AND date DATE_ADD(now(), interval 3 day) -- Best regards, Richardmailto:[EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Can you SUM(IF) from two different tables at the same time?
Hugh Bothwell [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... Instead, you want a query which returns a record with thread count and message count for each group. SELECT COUNT(DISTINCT threadid) AS threads, COUNT(*) AS messages FROM thread LEFT JOIN message ON message.threadid=thread.id GROUP BY boardid ASC WHERE boardid IN ( 1, 2 ) Thanks for the query Hugh, but it gives me: You have an error in your SQL syntax near 'WHERE boardid IN(1,2)' at line 7 In the 5th line I changed thread.id to thread.threadid so the name was correct but the same error occurs. After much testing I managed to get the following: SELECT COUNT(DISTINCT thread.threadid) AS threads, COUNT(*) AS messages, thread.boardid FROM thread LEFT JOIN message ON message.threadid=thread.threadid GROUP BY thread.boardid ASC to give a perfectly satisfactory result but have no idea if this is perversing SQL again or not! (it at least makes more sense now I guess). As soon as I add the where IN clause it errors. Can anyone recommend ANY good books on SQL query design? I don't want (or care) about database administration, I just want to know what constitutes a good database design and lots and lots of query examples/tutorials. Cheers, Richard -- Fatal Design http://www.fatal-design.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Can you SUM(IF) from two different tables at the same time?
Hi all, I'm using the following two queries: 1. SELECT SUM(IF(boardid='2',1,0)) AS b2, SUM(IF(boardid='1',1,0)) AS b1, COUNT(*) AS total FROM thread 2. SELECT SUM(IF(boardid='2',1,0)) AS b2, SUM(IF(boardid='1',1,0)) AS b1, COUNT(*) AS total FROM message This literally brings back a total number of threads and messages posted to the two respective boards (boardid1 and 2). All I am wondering is if this can be combined into a single query or does the very nature of using the SUM(IF) not allow this to happen? Regards, Richard -- Fatal Design http://www.fatal-design.com Atari / DarkBASIC / Coding / Since 1995 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
