RE: [PHP-DB] query help
The most performant methods are to use well parameterized stored procedure or a prepared parameterized statement. : Ashay -Original Message- From: Niel Archer [mailto:n...@chance.now] Sent: Wednesday, November 17, 2010 6:30 AM To: php-db@lists.php.net Subject: Re: [PHP-DB] query help > Hello PHP Gurus, > > I need your help on an insert query. > > I wanted to know if there is way to insert an array of values into a > DB. An eg would explain this better : > > If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 > columns > > I was thinking of having a single function which will perform the > insert on any insert which happens on the entire website. > > Eg : This function can be called with 2 parameters, the first > parameter the table name, and the second parameter is an array of > values which will be inserted into the table. > eg : Users has these columns [1]ID [2] Name [3]Location so the > function call would be something like * > insert_into_tbale(users,array[user_values])* > ** > Does this make sense ? Is this a good method to follow ? > > Thanks in advance ! > > Vinay Kannan. You don't give any info about the database engine, but assuming you're using MySQL take a look at http://dev.mysql.com/doc/refman/5.0/en/insert.html Specifically you can use your idea to build an INSERT/VALUE version of the syntax INSERT INTO table (col1, col2, .colN.) VALUES (col1Value1, col2value1, colNvalue1), (col1Value2, col2value2, colNvalue2), ... -- Niel Archer niel.archer (at) blueyonder.co.uk -- 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] query help
Hey, You can also try PEAR module MDB2 to insert an array into a table. The function is: "executeMultiple". This works for other SQL queries too. Check this link please: http://pear.php.net/manual/en/package.database.mdb2.intro-execute.php Cheers, -Original Message- From: Artur Ejsmont [mailto:ejsmont.ar...@gmail.com] Sent: 17 noiembrie 2010 16:07 To: Vinay Kannan Cc: PHP DB; php mysql Subject: Re: [PHP-DB] query help well i guess you could do that. but it gets complicated after a while and there will be a lot of work and probably after a while you will get into some problems. You have to handle escaping, special types etc. Then what about performance? how to query the data ... using similar approach or is it just for inserts? there is a bit of work to make it really usable i guess. I am not saying its wrong though, I have seen this approach twice ... in general its possible. Maybe better choice would be to try to use some orm ? there are plenty of frameworks out there the only problem is the learning curve may be steep. What others think? art On 17 November 2010 13:51, Vinay Kannan wrote: > Hello PHP Gurus, > > I need your help on an insert query. > > I wanted to know if there is way to insert an array of values into a > DB. An eg would explain this better : > > If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 > columns > > I was thinking of having a single function which will perform the > insert on any insert which happens on the entire website. > > Eg : This function can be called with 2 parameters, the first > parameter the table name, and the second parameter is an array of > values which will be inserted into the table. > eg : Users has these columns [1]ID [2] Name [3]Location so the > function call would be something like * > insert_into_tbale(users,array[user_values])* > ** > Does this make sense ? Is this a good method to follow ? > > Thanks in advance ! > > Vinay Kannan. > -- Visit me at: http://artur.ejsmont.org/blog/ -- 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] query help
> Hello PHP Gurus, > > I need your help on an insert query. > > I wanted to know if there is way to insert an array of values into a DB. An > eg would explain this better : > > If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 columns > > I was thinking of having a single function which will perform the insert on > any insert which happens on the entire website. > > Eg : This function can be called with 2 parameters, the first parameter the > table name, and the second parameter is an array of values which will be > inserted into the table. > eg : Users has these columns [1]ID [2] Name [3]Location > so the function call would be something like * > insert_into_tbale(users,array[user_values])* > ** > Does this make sense ? Is this a good method to follow ? > > Thanks in advance ! > > Vinay Kannan. You don't give any info about the database engine, but assuming you're using MySQL take a look at http://dev.mysql.com/doc/refman/5.0/en/insert.html Specifically you can use your idea to build an INSERT/VALUE version of the syntax INSERT INTO table (col1, col2, .colN.) VALUES (col1Value1, col2value1, colNvalue1), (col1Value2, col2value2, colNvalue2), ... -- Niel Archer niel.archer (at) blueyonder.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query help
From: "Vinay Kannan" To: "PHP DB" , "php mysql" Sent: Wednesday, November 17, 2010 2:51:35 PM GMT +01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna Subject: [PHP-DB] query help Hello PHP Gurus, I need your help on an insert query. I wanted to know if there is way to insert an array of values into a DB. An eg would explain this better : If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 columns I was thinking of having a single function which will perform the insert on any insert which happens on the entire website. Eg : This function can be called with 2 parameters, the first parameter the table name, and the second parameter is an array of values which will be inserted into the table. eg : Users has these columns [1]ID [2] Name [3]Location so the function call would be something like * insert_into_tbale(users,array[user_values])* ** Does this make sense ? Is this a good method to follow ? Thanks in advance ! Vinay Kannan. Hi Vinay, You may want to try codeigniter. More info here. http://codeigniter.com/user_guide/database/active_record.html#insert with kind regards, Max. Max Kimambo Franz-Stenzer-Straße, 51 12679, Berlin. T: +493057706550 (new number) M: +4917649520175 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query help
On Wed, Nov 17, 2010 at 8:51 AM, Vinay Kannan wrote: > Hello PHP Gurus, > > I need your help on an insert query. > > I wanted to know if there is way to insert an array of values into a DB. An > eg would explain this better : > > If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 columns > > I was thinking of having a single function which will perform the insert on > any insert which happens on the entire website. > > Eg : This function can be called with 2 parameters, the first parameter the > table name, and the second parameter is an array of values which will be > inserted into the table. > eg : Users has these columns [1]ID [2] Name [3]Location > so the function call would be something like * > insert_into_tbale(users,array[user_values])* > ** > Does this make sense ? Is this a good method to follow ? > > Thanks in advance ! > > Vinay Kannan. > codeigniter works this way. But they have a lot of extra functionality to keep the data safe for inserts. Check it out. -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query help
well i guess you could do that. but it gets complicated after a while and there will be a lot of work and probably after a while you will get into some problems. You have to handle escaping, special types etc. Then what about performance? how to query the data ... using similar approach or is it just for inserts? there is a bit of work to make it really usable i guess. I am not saying its wrong though, I have seen this approach twice ... in general its possible. Maybe better choice would be to try to use some orm ? there are plenty of frameworks out there the only problem is the learning curve may be steep. What others think? art On 17 November 2010 13:51, Vinay Kannan wrote: > Hello PHP Gurus, > > I need your help on an insert query. > > I wanted to know if there is way to insert an array of values into a DB. An > eg would explain this better : > > If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 columns > > I was thinking of having a single function which will perform the insert on > any insert which happens on the entire website. > > Eg : This function can be called with 2 parameters, the first parameter the > table name, and the second parameter is an array of values which will be > inserted into the table. > eg : Users has these columns [1]ID [2] Name [3]Location > so the function call would be something like * > insert_into_tbale(users,array[user_values])* > ** > Does this make sense ? Is this a good method to follow ? > > Thanks in advance ! > > Vinay Kannan. > -- Visit me at: http://artur.ejsmont.org/blog/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] query help
Hello PHP Gurus, I need your help on an insert query. I wanted to know if there is way to insert an array of values into a DB. An eg would explain this better : If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 columns I was thinking of having a single function which will perform the insert on any insert which happens on the entire website. Eg : This function can be called with 2 parameters, the first parameter the table name, and the second parameter is an array of values which will be inserted into the table. eg : Users has these columns [1]ID [2] Name [3]Location so the function call would be something like * insert_into_tbale(users,array[user_values])* ** Does this make sense ? Is this a good method to follow ? Thanks in advance ! Vinay Kannan.
[PHP-DB] Query help
Hey Everyone, I can use some help here I have query where one can selects an "style" then a "area" and finally "rating" through a drop menu. When some selects a rating they select a range of ratings. For example: Style: Traditional Area: Yosemite Rating: From: 5.5 To: 5.10c This should pull up all the rock climbs that are in Yosemite, that are traditional style and are between the rating 5.5 to 5.10c. Here is my query: "SELECT * FROM routes, users WHERE area='$area' AND style='$style' BETWEEN rating='[$rating1]' AND rating='[$rating2]' GROUP BY route ORDER BY rating ASC "; For some reason which I am not seeing, this query is not doing what it should be doing. Does anyone have any suggestions? Thanks, Craig -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query Help!
Hi Everyone, Happy Easter to you all. I have this query, reasoning for posting on this Mailing List is under it. :) SELECT `username` , `score` FROM round" . $round_number . " WHERE 1 ORDER BY `score` DESC LIMIT 0, 30 Is there a way to make it output the $round_number -1 (minus one number)? I need this to be a temp. meassure as I only need the number previous to the current $round_number for a few days. Is there a way in the query I can subtrack the value by 1 and change it later when I want to show the current round? If it's too much hassles I can add $prev_round_number and change the values each week but thought there might be another way. J Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Query Help...
Thanks for all the advice, I thought it might have something to do with being a reserved word. I changed the table name from group to grps, and everything works fine now. -Original Message- From: Rob Bryant [mailto:[EMAIL PROTECTED] Sent: Monday, March 10, 2003 11:39 AM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] Query Help... - Original Message - From: "NIPP, SCOTT V (SBCSI)" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, March 10, 2003 9:23 AM Subject: [PHP-DB] Query Help... > I am having a lot of trouble with a query that works fine from a > basic SQL command line, but fails in my web script. Here is the portion of > code including the query: > > mysql_select_db($database, $Prod); > $query_groups = "SELECT name FROM group"; > $groups = mysql_query($query_groups, $Prod) or die(mysql_error()); > > Here is the error I am receiving: > > You have an error in your SQL syntax near '"group"' at line 1 > Have you tried using backticks in your query? E.g., $query_groups = "SELECT name FROM `group`"; -- rob -- 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] Query Help...
- Original Message - From: "NIPP, SCOTT V (SBCSI)" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, March 10, 2003 9:23 AM Subject: [PHP-DB] Query Help... > I am having a lot of trouble with a query that works fine from a > basic SQL command line, but fails in my web script. Here is the portion of > code including the query: > > mysql_select_db($database, $Prod); > $query_groups = "SELECT name FROM group"; > $groups = mysql_query($query_groups, $Prod) or die(mysql_error()); > > Here is the error I am receiving: > > You have an error in your SQL syntax near '"group"' at line 1 > Have you tried using backticks in your query? E.g., $query_groups = "SELECT name FROM `group`"; -- rob -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Query Help...
Scott, 'Group' is a reserved word so you can't use it for a column name. try changing it to 'groups' or soething else that makes sense but is safe. George > -Original Message- > From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED] > Sent: 10 March 2003 5:24 pm > To: '[EMAIL PROTECTED]' > Subject: [PHP-DB] Query Help... > > > I am having a lot of trouble with a query that works fine from a > basic SQL command line, but fails in my web script. Here is the > portion of > code including the query: > > mysql_select_db($database, $Prod); > $query_groups = "SELECT name FROM group"; > $groups = mysql_query($query_groups, $Prod) or die(mysql_error()); > > Here is the error I am receiving: > > You have an error in your SQL syntax near '"group"' at line 1 > > I am wondering if for some reason group is trying to be > interperetted as the "GROUP BY" MySQL function? I am running PHP 4.2.3 > against a MySQL DB on an Apache 1.3.27 server. 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 > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query Help...
I am having a lot of trouble with a query that works fine from a basic SQL command line, but fails in my web script. Here is the portion of code including the query: mysql_select_db($database, $Prod); $query_groups = "SELECT name FROM group"; $groups = mysql_query($query_groups, $Prod) or die(mysql_error()); Here is the error I am receiving: You have an error in your SQL syntax near '"group"' at line 1 I am wondering if for some reason group is trying to be interperetted as the "GROUP BY" MySQL function? I am running PHP 4.2.3 against a MySQL DB on an Apache 1.3.27 server. 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
[PHP-DB] Query Help
I've implemented the scheme I briefly outlined in my last post (locking for data integrity), and now I'm writing queries to get totals from my logs, and I think I might be over-complicating things, and I'd like some alternative views on the subject. My "grandiose scheme" works like this: Problem: logging unique users that are authenticated for access to my companies website into a MySQL database table, only valid data to report is total number of unique authentications. Solution: because of the high volume of unique authentications being logged (over 15k per day), I periodically "condense" the data into 3 tables beyond the main table that logs users as they come in. My tables look like this: +---+ | access_log| +---+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-+---+ | timestamp | timestamp(14) | YES | MUL | NULL| | | code | text | | | NULL| | | refer | text | | | NULL| | | ip| varchar(64) | | | | | | access| varchar(128) | | MUL | 0 | | +---+---+--+-+-+---+ NOTE: The access field in the access_log table correlates to the client field in the table below. Logging was an afterthought initially, but then it became very important, so I cannot change the field now without a lot of debugging. Doing this database cleanup wasn't a priority either until a couple of months later when the size of the access_log table grew out of control (over 1GB on disk, 800k records). +-+ | access_log_daily/weekly/monthly | ++---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-+---+ | client | varchar(32) | | | | | | total | bigint(20)| | | 0 | | | stamp | timestamp(14) | YES | | NULL| | ++---+--+-+-+---+ I've set up a cron jobs that run these queries periodically: Daily: LOCK TABLES access_log WRITE, access_log_daily WRITE; INSERT INTO access_log_daily (client, total) SELECT access, COUNT(timestamp) AS total FROM access_log GROUP BY access; DELETE FROM access_log; UNLOCK TABLES; Weekly: LOCK TABLES access_log_daily WRITE, access_log_weekly WRITE; INSERT INTO access_log_weekly (client, total) SELECT client, SUM(total) as total FROM access_log_daily GROUP BY client; DELETE FROM access_log_daily; UNLOCK TABLES; Monthly: LOCK TABLES access_log_weekly WRITE, access_log_monthly WRITE; INSERT INTO access_log_monthly (client, total) SELECT client, SUM(total) as total FROM access_log_weekly GROUP BY client; DELETE FROM access_log_weekly; UNLOCK TABLES; So this way, the access_log table is never bigger than the total number of users for a 24 hour period, but we can still look into it if a problem arises where we need someone's ip address or the auth code they were given, and the other tables can only be as big as the interval of "condensation", times the total number of clients, and store totals, so it *should* be easy to gather information from them. In my reporting scripts, I need to be able to find the grand totals for the past hour, day, week, month, and year. My first idea was to use multiple queries and just add all of the results together, but I'm not sure if this is the best way, or if it will reflect the most accurate per-period results. I appreciate any input anyone has. Thanks, -- Josh -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Query Help
Well, there's two ways I can think of: 1) SELECT category FROM table ORDER BY category; Then cycle through the returned list of categories and assign the category to $category. For each category, do: SELECT * FROM table WHERE category=$category ORDER BY minor_category; 2) SELECT * FROM table ORDER BY category,minor_category; Read category from first return; store in $category Then cycle through returned list. Whenever the category different from $category, make your break in the listing and set $category to new category. -Original Message- From: Shannon Doyle [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 16, 2001 5:05 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Query Help Hi people, I have a small problem with a select cause that I need to use. I basically need the following. Select * from table where page =3D=3D page_name The fields in the db are code,description,unit,price,category,minor_category Then the display needs to be something along the lines of Category Name Minor Category Code Description Unit Price Minor Category Code Description Unit Price Category Name Minor Category Code Description Unit Price The number of minor categories differ for each of the categories, as to = do the number of products in each minor_category. I could hard code in the Category Names, however, they are meant to be = dynamic as they will be changing on a monthly basis. Any help would be appreciated. Thanks Shannon ___ Shannon Doyle Web Design Consultant BIGBLUE Internet Pty Ltd 149 Hutt St, Adelaide SA 5000 Ph +61 8 8232 1444 Fax +61 8 8232 8577 http://www.bigblue.net.au -- 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]
[PHP-DB] Query Help
Hi people, I have a small problem with a select cause that I need to use. I basically need the following. Select * from table where page =3D=3D page_name The fields in the db are code,description,unit,price,category,minor_category Then the display needs to be something along the lines of Category Name Minor Category Code Description Unit Price Minor Category Code Description Unit Price Category Name Minor Category Code Description Unit Price The number of minor categories differ for each of the categories, as to = do the number of products in each minor_category. I could hard code in the Category Names, however, they are meant to be = dynamic as they will be changing on a monthly basis. Any help would be appreciated. Thanks Shannon ___ Shannon Doyle Web Design Consultant BIGBLUE Internet Pty Ltd 149 Hutt St, Adelaide SA 5000 Ph +61 8 8232 1444 Fax +61 8 8232 8577 http://www.bigblue.net.au -- 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] Query help
Hi, I haven't seen the previous posts so I'm not sure want you want to do, but it looks like that you need to specify a join condition. If freerooms.room and booking.room are the corresponding fields from your table the query should look something like this SELECT count(freerooms.room), WEEK(MAX(booking.date),1), WEEK(MIN(booking.date),1) as total FROM freerooms LEFT JOIN booking ON freerooms.room=booking.room USING(room); or SELECT count(f.room),WEEK(MAX(b.date),1), WEEK(MIN(b.date),1) FROM freerooms f, booking b WHERE f.room=b.room Hope this helps Dobromir Velev -Original Message- From: Matt Williams <[EMAIL PROTECTED]> To: Andrey Hristov <[EMAIL PROTECTED]> Cc: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Date: Monday, November 19, 2001 10:26 Subject: RE: [PHP-DB] Query help >This doesn't work either. > >SELECT count(freerooms.room), WEEK(MAX(booking.date),1), >WEEK(MIN(booking.date),1) as total FROM freerooms LEFT JOIN booking >USING(room); > >> > All good so far >> > now if I run this >> >> > SELECT count(f.room),WEEK(MAX(b.date),1), WEEK(MIN(b.date),1) >> FROM freero >> > oms f, booking b; >> > ->+---+-+-+ >> > | count(f.room) | WEEK(MAX(b.date),1) | WEEK(MIN(b.date),1) | >> > +---+-+-+ >> > | 24817 | 48 | 40 | >> > +---+-+-+ >> > > >What I have found is > >freerooms.room has 83 rows and booking has 299. >The value returned for count(f.room) is these two figures multiplied.??? > >Why would it do this? >And how can I get it to return the real value only > >Regards > >M: > > >-- >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] Query help
This doesn't work either. SELECT count(freerooms.room), WEEK(MAX(booking.date),1), WEEK(MIN(booking.date),1) as total FROM freerooms LEFT JOIN booking USING(room); > > All good so far > > now if I run this > > > > SELECT count(f.room),WEEK(MAX(b.date),1), WEEK(MIN(b.date),1) > FROM freero > > oms f, booking b; > > ->+---+-+-+ > > | count(f.room) | WEEK(MAX(b.date),1) | WEEK(MIN(b.date),1) | > > +---+-+-+ > > | 24817 | 48 | 40 | > > +---+-+-+ > > What I have found is freerooms.room has 83 rows and booking has 299. The value returned for count(f.room) is these two figures multiplied.??? Why would it do this? And how can I get it to return the real value only Regards M: -- 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] Query help
Ooops SELECT count(f.room),WEEK(MAX(b.date),1), WEEK(MIN(b.date),1) FROM freerooms as f LEFT JOIN booking AS b USING(room); Apolgizes - Original Message - From: "Andrey Hristov" <[EMAIL PROTECTED]> To: "Matt Williams" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, November 16, 2001 5:32 PM Subject: Re: [PHP-DB] Query help > SELECT count(f.room),WEEK(MAX(b.date),1), WEEK(MIN(b.date),1) FROM freero > oms f, booking b; > > will be > SELECT count(f.room),WEEK(MAX(b.date),1), WEEK(MIN(b.date),1) FROM freero > oms f LEFT JOIN booking USING(room) b; > > room is the field which connects two tables. If it has different name change it in >USING > Other problems? > Write.. > > Regards, > > Andrey Hristov > IcyGEN Corporation > http://www.icygen.com > BALANCED SOLUTIONS > > - Original Message - > From: "Matt Williams" <[EMAIL PROTECTED]> > To: "Php-Db@Lists. Php." <[EMAIL PROTECTED]> > Sent: Friday, November 16, 2001 5:20 PM > Subject: [PHP-DB] Query help > > > > Not really this list but I'm after a quick bit of help. > > If I run this query > > > > select count(room) from freerooms; > > ->83 > > > > and this > > > > SELECT WEEK(MAX(date),1), WEEK(MIN(date),1)as totalweeks FROM booking; > > ->48 40 > > > > All good so far > > now if I run this > > > > SELECT count(f.room),WEEK(MAX(b.date),1), WEEK(MIN(b.date),1) FROM freero > > oms f, booking b; > > ->+---+-+-+ > > | count(f.room) | WEEK(MAX(b.date),1) | WEEK(MIN(b.date),1) | > > +---+-+-+ > > | 24817 | 48 | 40 | > > +---+-+-+ > > > > It seems to be mulitplying out for some reason but I'm stuck as to how/why > > and how to resolve it. > > > > TIA > > > > M: > > > > > > -- > > 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] > > -- 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] Query help
SELECT count(f.room),WEEK(MAX(b.date),1), WEEK(MIN(b.date),1) FROM freero oms f, booking b; will be SELECT count(f.room),WEEK(MAX(b.date),1), WEEK(MIN(b.date),1) FROM freero oms f LEFT JOIN booking USING(room) b; room is the field which connects two tables. If it has different name change it in USING Other problems? Write.. Regards, Andrey Hristov IcyGEN Corporation http://www.icygen.com BALANCED SOLUTIONS - Original Message - From: "Matt Williams" <[EMAIL PROTECTED]> To: "Php-Db@Lists. Php." <[EMAIL PROTECTED]> Sent: Friday, November 16, 2001 5:20 PM Subject: [PHP-DB] Query help > Not really this list but I'm after a quick bit of help. > If I run this query > > select count(room) from freerooms; > ->83 > > and this > > SELECT WEEK(MAX(date),1), WEEK(MIN(date),1)as totalweeks FROM booking; > ->48 40 > > All good so far > now if I run this > > SELECT count(f.room),WEEK(MAX(b.date),1), WEEK(MIN(b.date),1) FROM freero > oms f, booking b; > ->+---+-+-+ > | count(f.room) | WEEK(MAX(b.date),1) | WEEK(MIN(b.date),1) | > +---+-+-+ > | 24817 | 48 | 40 | > +---+-+-+ > > It seems to be mulitplying out for some reason but I'm stuck as to how/why > and how to resolve it. > > TIA > > M: > > > -- > 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]
[PHP-DB] Query help
Not really this list but I'm after a quick bit of help. If I run this query select count(room) from freerooms; ->83 and this SELECT WEEK(MAX(date),1), WEEK(MIN(date),1)as totalweeks FROM booking; ->4840 All good so far now if I run this SELECT count(f.room),WEEK(MAX(b.date),1), WEEK(MIN(b.date),1) FROM freero oms f, booking b; ->+---+-+-+ | count(f.room) | WEEK(MAX(b.date),1) | WEEK(MIN(b.date),1) | +---+-+-+ | 24817 | 48 | 40 | +---+-+-+ It seems to be mulitplying out for some reason but I'm stuck as to how/why and how to resolve it. TIA M: -- 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] query help
can anyone figure out why the following code works fine when both select variables = "ALL" and also when only 1 of the 2 ="ALL" but NOT when BOTH select variables equal something other than "ALL"? here is the code: What area of the city would you like to dine in? \n"; $select.="ALL\n"; while(list($AreaTemp)=mysql_fetch_array($mysql_result)) { $select.="$AreaTemp\n"; } $select.=""; echo "$select"; ?> What type of cuisine do you feel like? \n"; $select.="ALL\n"; while(list($CuisineTemp)=mysql_fetch_array($mysql_result)) { $select.="$CuisineTemp\n"; } $select.=""; echo "$select"; ?> Scott Stinsman Academic Coordinator General Internal Medicine 1215 Blockley Hall 423 Guardian Drive Philadelphia, PA 19104-6021 (phone) 215-662-7623 (fax) 215-349-5091 (email) [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]